MySQL Load Data InFile 文件内容导入数据库和 Into OutFile导出数据到文件

1、常用如下：
Load Data InFile 'C:/Data.txt' Into Table `TableTest` Lines Terminated By '\r\n';
这个语句，字段默认用制表符隔开，每条记录用换行符隔开，在Windows下换行符为“\r\n”
C:/Data.txt 文件内容如下面两行：
1 A
2 B
“1”和“A”之间有一个制表符
这样就导进两条记录了。

2、自定义语法
Load Data InFile 'C:/Data.txt' Into Table `TableTest` Fields Terminated By ',' Enclosed By '"' Escaped By '"' Lines Terminated By '\r\n';

Fields Terminated By ',' Enclosed By '"' Escaped By '"'  则表示每个字段用逗号分开，内容包含在双引号内
Lines Terminated By '\r\n'  则表示每条数据用换行符分开

3、和 Load Data InFile 相反的是Into OutFile 为导出数据到文件
Select * From `TableTest` Into OutFile 'C:/Data_OutFile.txt'; 表示把表的数据导出

4、实例说明

实例文本文件/tmp/t0.txt：

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

实例表结构：

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 

执行导入：

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

Query OK, 19 rows affected (0.01 sec)
Records: 19 Deleted: 0 Skipped: 0 Warnings: 0

相关的参数说明：
load data infile '/tmp/t0.txt' ignore into table `t0` character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`);

关于character set gbk;
这个字符集一定要写，要不然就会乱码或者只导入一部分数据。

关于ignore into table
因为 name 列加了唯一索引，加这个是为了避免重复数据插入报错。
假如我们再次运行这个导入语句就会发现

Query OK, 0 rows affected (0.00 sec)
Records: 19 Deleted: 0 Skipped: 19 Warnings: 0

没有任何值导入，因为里面已经有了相同的值。

我们也可以用replace into table 去代替使用ignore into table

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

Query OK, 38 rows affected (0.00 sec)
Records: 19 Deleted: 19 Skipped: 0 Warnings: 0

此时，将前面插入的19条数据删除，将此次执行的数据插入，select 会发现，主键id变了。

 

问题：

MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方法：
我们需要通过下面命令查看 secure-file-priv 当前的值是什么
mysql> show variables like '%secure%';
根据显示字段secure_file_priv的目录值将导入路径放到该目录下即可。