mysql导入2000W csv格式文件

 

技术贴，只做练习用，导完后请删除数据。

 

网上有一篇文章

如何快速导入 2000W 到 Mysql

 

可惜我没有phpMyAdmin啊，也不会php啊，

 

其实csv就是个文本文件

 

用基础的方法

 

创建kf数据库，

创建表

CREATE TABLE `kfinfo` (
  `Name` varchar(100) DEFAULT NULL,
  `CardNo` varchar(50) DEFAULT NULL,
  `Descriot` varchar(150) DEFAULT NULL,
  `CtfTp` varchar(50) DEFAULT NULL,
  `CtfId` varchar(50) DEFAULT NULL,
  `Gender` varchar(50) DEFAULT NULL,
  `Birthday` varchar(50) DEFAULT NULL,
  `Address` varchar(200) DEFAULT NULL,
  `Zip` varchar(50) DEFAULT NULL,
  `Dirty` varchar(50) DEFAULT NULL,
  `District1` varchar(50) DEFAULT NULL,
  `District2` varchar(50) DEFAULT NULL,
  `District3` varchar(50) DEFAULT NULL,
  `District4` varchar(50) DEFAULT NULL,
  `District5` varchar(50) DEFAULT NULL,
  `District6` varchar(50) DEFAULT NULL,
  `FirstNm` varchar(50) DEFAULT NULL,
  `LastNm` varchar(50) DEFAULT NULL,
  `Duty` varchar(50) DEFAULT NULL,
  `Mobile` varchar(50) DEFAULT NULL,
  `Tel` varchar(50) DEFAULT NULL,
  `Fax` varchar(50) DEFAULT NULL,
  `EMail` varchar(50) DEFAULT NULL,
  `Nation` varchar(50) DEFAULT NULL,
  `Taste` varchar(150) DEFAULT NULL,
  `Education` varchar(50) DEFAULT NULL,
  `Company` varchar(50) DEFAULT NULL,
  `CTel` varchar(50) DEFAULT NULL,
  `CAddress` varchar(50) DEFAULT NULL,
  `CZip` varchar(50) DEFAULT NULL,
  `Family` varchar(50) DEFAULT NULL,
  `Version` varchar(50) DEFAULT NULL,
  `id` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

关键是 存储引擎MyISAM，这种效率高，（不支持事务）每个表有单独的存储文件（多个），方便管理。

存储引擎innodb 一般默认的都是innodb，效率也不低，支持事务，

其一般默认用共享表空间，用一个文件存储数据，ibdata1，共享表空间也可以配置多个存储文件。

可以通过调整innodb_file_per_table使用独享表空间，每个表有自己的存储文件。

 

因为我的库默认是innodb，不想调整了，就为单独的表指定存储引擎就行，这样这个表有自己的存储文件，不会影响其他的库。

 

如果你对数据完整性，要求不高，可以先不建主键，

建了主键后，会发现有些数据可能有问题，需要调整。

 

处理数据

csv中有一些特殊字符不好处理，

比如：\  这个还好可以加入 ESCAPED BY '' 来处理

但是数据中有回车换行实在不好处理，因为判断一行的结束也使用的的回车换行

 

只能用笨办法处理数据了

用UltraEdit打开文件，用正则搜索

搜索^p^p来处理多个回车换行

搜索^p,处理回车换行，有的不需处理（如：前一个值是id列），处理时先看清id

搜素%[~,]+^p处理回车换行 

 

还有一些特殊的需要修改的记录

id：6713304,6808632,9670832,16800059,17496919,18601591

 

还有一些需要修改的记录

id：5332595,16838052,16838057

把"tsai jason"的双引号去掉

 

平均一个文件有5个左右的地方需要处理。

这种方法好像太笨了，如果有能自动处理回车换行的方法就好了。

 

导入

至此数据整理的差不多了，其实导入很快，一个文件一分钟就能导进去，1秒6万左右。

要是没有主键导入的更快，嗖嗖的。

 

mysql命令：LOAD DATA INFILE

 

语法 ……

http://www.2cto.com/database/201108/99655.html

http://douglaslau.iteye.com/blog/1471643

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

 

mysql> LOAD DATA INFILE 'E:\\2000W\\1-200W.csv' 
INTO TABLE kfinfo 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '' 
LINES TERMINATED BY '\r\n' 
ignore 1 lines;

 

从第二个文件起，ignore 2 lines; （忽略前几行）

 

 Navicat支持导入csv格式，选择（txt）。不过用工具导没有用命令快。

 

可能用到的sql

select id,CtfId from kfinfo order by id;

select * from kfinfo where id = ''; 

select count(*) from kfinfo;

--TRUNCATE kfinfo;

 TRUNCATE超快，会清数据文件，很好用。

 

最后导入20050144，貌似比用php导入的多，估计是他没做数据整理。

库大小，3.41G

弄这个这么费劲，还不如弄那个1.71G的。

 

之后可对你感兴趣的列创建索引。如Name，CtfId，Mobile

 

ALTER TABLE `kfinfo`
ADD INDEX `idx_kfinfo_name` (`Name`) USING BTREE ;

  创建索引很慢很慢，请耐心等待。

 

OK了，可以查询了。

 

PS：可惜没有我。

 

评论

1 楼 gxz1989611 2014-01-29