MySQL: load data infile 需要注意的点

load data infile 批量将文本导入到mysql的表中。主要是为了加快导入记录的速度。

---给个例子：

1.文本如下 t0.txt：

"我爱你","20","相貌平常，经常耍流氓！哈哈"
"李奎","21","相貌平常，经常耍流氓！哈哈"
"王二米","20","相貌平常，经常耍流氓！哈哈"
"老三","24","很强"
"老四","34","XXXXX"
"老五","52","***%*￥*￥*￥*￥"
"小猫","45","中间省略。。。"
"小狗","12","就会叫"
"小妹","21","PP的很"
"小坏蛋","52","表里不一"
"上帝他爷","96","非常英俊"
"MM来了","10","。。。"
"歌颂党","20","社会主义好"
"人民好","20","的确是好"
"老高","10","学习很好"
"斜三","60","眼睛斜了"
"中华之子","100","威武的不行了"
"大米","63","我爱吃"
"苹果","15","好吃"

 2.创建一个表t0

 CREATE TABLE `t0` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `name` char(20) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 3.mysql下执行：

 load data infile '/root/t0.txt' ignore into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`);

报错：

The MySQL server is running with the --secure-file-priv option so it cannot ...

解决：

secure-file-priv的值有三种情况：

secure_file_prive=null ––限制mysqld 不允许导入导出

secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下

secure_file_priv=’’ – --不对mysqld 的导入 导出做限制

step3:查看你的secure-file-priv设置：

show variables like ‘%secure%’;

将secure_file_priv变量设置为空，或者将文本拷贝到默认路径下。

再次执行，成功。

---

相关问题：主从同步下，load data infile所带来的问题及解决方法