load data infile 批量將文本導(dǎo)入到mysql的表中。主要是為了加快導(dǎo)入記錄的速度。

---給個(gè)例子：

1.文本如下 t0.txt：

"我愛你","20","相貌平常，經(jīng)常耍流氓！哈哈"
"李奎","21","相貌平常，經(jīng)常耍流氓！哈哈"
"王二米","20","相貌平常，經(jīng)常耍流氓！哈哈"
"老三","24","很強(qiáng)"
"老四","34","XXXXX"
"老五","52","***%*￥*￥*￥*￥"
"小貓","45","中間省略。。。"
"小狗","12","就會(huì)叫"
"小妹","21","PP的很"
"小壞蛋","52","表里不一"
"上帝他爺","96","非常英俊"
"MM來了","10","。。。"
"歌頌黨","20","社會(huì)主義好"
"人民好","20","的確是好"
"老高","10","學(xué)習(xí)很好"
"斜三","60","眼睛斜了"
"中華之子","100","威武的不行了"
"大米","63","我愛吃"
"蘋果","15","好吃"

?2.創(chuàng)建一個(gè)表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下執(zhí)行：

?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`);

報(bào)錯(cuò)：

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

解決：

secure-file-priv的值有三種情況：

secure_file_prive=null ––限制mysqld 不允許導(dǎo)入導(dǎo)出

secure_file_priv=/path/ – --限制mysqld的導(dǎo)入導(dǎo)出只能發(fā)生在默認(rèn)的/path/目錄下

secure_file_priv=’’ – --不對(duì)mysqld 的導(dǎo)入 導(dǎo)出做限制

step3:查看你的secure-file-priv設(shè)置：

show variables like ‘%secure%’;

將secure_file_priv變量設(shè)置為空，或者將文本拷貝到默認(rèn)路徑下。

再次執(zhí)行，成功。

---

相關(guān)問題：主從同步下，load data infile所帶來的問題及解決方法

總結(jié)

以上是生活随笔為你收集整理的MySQL: load data infile 需要注意的点的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。