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变量设置为空,或者将文本拷贝到默认路径下。
再次执行,成功。