load data infile和select into outfile语句是配套的。select into outfile语句是将检索出来的数据按格式导出到文件中 , 数据迁移跨数据库系统时 , 该选项很有用 , 因为它可以指定分隔符。load data infile是将带有格式的数据文件导入到表中。
导出、导入数据时需要指定格式(如不指定 , 则使用默认)。格式涉及几个方面 : 字段分隔符、行分隔符、引用符号、转义符号。
还需注意一点 , 默认情况下(MySQL 5.6.34之后)这两个语句无法执行成功 , 因为全局变量secure_file_priv的默认值为null , 它表示禁用这两种语句的导入导出。
所以应该将其设置为空(不指定任何值)或者指定一个目录 , 将来该目录中的所有文件都可以进行mysql file类的交互。当然 , 变量指定的目录必须已经存在 , 且mysql系统用户和组必须对该目录有读写权限。
mkdir /data
chown -R mysql.mysql /data这个变量是全局静态变量 , 只能在mysqld实例未启动的时候才能修改。所以将其写入配置文件。
[mysqld]
secure-file-priv=/data
# 或者
# secure-file-priv=查看变量。
select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/ |
+---------------------------+再看这两个语句的语法 :
SELECT ... INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
[export_options]
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[export_options]
[IGNORE number {LINES|ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]其中'char'表示只能使用一个字符 , 'string'表示可以指定多个字符。
fields terminated by 'string'指定字段分隔符；enclosed by 'char'指定所有字段都使用char符号包围 , 如果指定了optionally则只用在字符串和日期数据类型等字段上 , 默认未指定；escaped by 'char'指定转义符。lines starting by 'string'指定行开始符 , 如每行开始记录前空一个制表符；lines terminated by 'string'为行分隔符。
要注意 , 在几种情况下需要使用转义符 : 数据中含有转义符本身或者字段分隔符。当指定了字段引用符enclosed by时 , 如果数据中含有字段引用符 , 则也需要转义 , 若未指定enclosed by , 则默认不使用字段引用符 , 所以无需转义。
以下为它们的默认值 :
fileds terminated by '\t' enclosed by '' escaped by '\\'
lines terminated by '\n' starting by ''看上去语法还挺复杂的 , 使用示例来说明就很清晰易懂了。
给定如下表结构和数据。
create or replace table t(id int primary key,sex char(3),name char(20),ins_day date);
insert into t values(1,'nan','longshuai1','2010-04-19'),
(2,'nan','longshuai2','2011-04-19'),
(3,'nv','xiaofang1','2012-04-19'),
(4,'nv','xiaofang2','2013-04-19'),
(5,'nv','xiaofang3','2014-04-19'),
(6,'nv','xiaofang4','2015-04-19'),
(7,'nv','tun\'er','2016-04-19'),
(8,'nan','longshuai3','2017-04-19');1. select into outfile导出数据
使用默认设置 :
select * from t into outfile '/data/t_data.sql';
\! cat /data/t_data.sql
1 nan longshuai1 2010-04-19
2 nan longshuai2 2011-04-19
3 nv xiaofang1 2012-04-19
4 nv xiaofang2 2013-04-19
5 nv xiaofang3 2014-04-19
6 nv xiaofang4 2015-04-19
7 nv tun'er 2016-04-19
8 nan longshuai3 2017-04-19指定字段分隔符"," , 使用单引号包围各字段 , 每行前加上制表符。
select * from t into outfile '/data/t_data1.sql' fields terminated by ',' enclosed by '\'' lines starting by '\t' terminated by '\n';
\! cat /data/t_data1.sql
'1','nan','longshuai1','2010-04-19'
'2','nan','longshuai2','2011-04-19'
'3','nv','xiaofang1','2012-04-19'
'4','nv','xiaofang2','2013-04-19'
'5','nv','xiaofang3','2014-04-19'
'6','nv','xiaofang4','2015-04-19'
'7','nv','tun\'er','2016-04-19'
'8','nan','longshuai3','2017-04-19'2. load data infile导入数据
要导入格式化后的纯数据 , 可以使用load data infile , 加载纯数据的插入方式比直接执行insert插入至少快20多倍。但在内部 , 它们其实是等价行为 , load data infile也会触发insert相关触发器。
其中可以使用local关键字表示从客户端主机读取文件 , 如果没有指定local则表示从服务端主机读取文件。
fields和lines的相关选项和select ... into outfile是一样的 , 只不过load data infile多了几个选项。其中ignore N lines|rows表示忽略前N行数据不导入 , col_name_or_user_var表示按此处给定的字段和顺序来导入数据 , set col_name=expr表示对列进行一些表达式运算 , 如给某数值字段加5 , 给某字符串列尾部加上@qq.com字符等。
例如要加载如下文件到test.t表中。
cat /data/t_data.txt
1 nan longshuai1 2010-04-19
2 nan longshuai2 2011-04-19
3 nv xiaofang1 2012-04-19
4 nv xiaofang2 2013-04-19
5 nv xiaofang3 2014-04-19
6 nv xiaofang4 2015-04-19
7 nv tun'er 2016-04-19
8 nan longshuai3 2017-04-19首先删除表中数据 , 再导入。
truncate test.t;
load data infile '/data/t_data.sql' into table test.t fields terminated by '\t';将如下包含字段分隔符"," , 字段引用符"'" , 转义符"" , 行前缀"\t"的文件加载到test.t表中。
[root@xuexi ~]# cat /data/t_data1.sql
'1','nan','longshuai1','2010-04-19'
'2','nan','longshuai2','2011-04-19'
'3','nv','xiaofang1','2012-04-19'
'4','nv','xiaofang2','2013-04-19'
'5','nv','xiaofang3','2014-04-19'
'6','nv','xiaofang4','2015-04-19'
'7','nv','tun\'er','2016-04-19'
'8','nan','longshuai3','2017-04-19'首先删除表中数据 , 然后加载。
truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n';若要忽略前两行 , 则 :
truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' ignore 2 rows;如果想在id列值加上5 , 则 :
truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set id=id+5;如果想name列后加上"@qq.com"字符串 , 则 :
truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com');如果想同时执行上面两个set , 则 :
truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com'), id=id+5;3. mysqldump导出数据
和select into outfile功能类似的语句还有 : 此方法导出的数据中还包含了列名。新版本mariadb版本的命令已经替换为mariadb-dump
mysql -uroot -p123456 -e "select * from test.t">/tmp/t_data2.sql
cat /tmp/t_data2.sql
id sex name ins_day
1 nan longshuai1 2010-04-19
2 nan longshuai2 2011-04-19
3 nv xiaofang1 2012-04-19
4 nv xiaofang2 2013-04-19
5 nv xiaofang3 2014-04-19
6 nv xiaofang4 2015-04-19
7 nv tun'er 2016-04-19
8 nan longshuai3 2017-04-19虽说select ... into outfile导出数据后可修改性和加载性非常强 , 但是毕竟没有导出结构。要导出结构 , 可以使用mysqldump的"--tab"选项 , 它既会导出表的结构定义语句到同表名的.sql文件中 , 还会导出数据到同表名的.txt文件中。
mysqldump -uroot -p123456 --tab /data test t;
ls -l /data/t.*
-rw-r--r-- 1 root root 1408 Apr 19 14:46 /data/t.sql # test.t表定义语句
-rw-rw-rw- 1 mysql mysql 211 Apr 19 14:46 /data/t.txt # test.t表内数据mysqldump的"--tab"选项同样可以指定各种分隔符。如"--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=..."。以下是指定字段分隔符为","。
mysqldump -uroot -p123456 --tab /data --fields-terminated-by=',' test t;
cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-194. mysqlimport导入数据
mysqlimport和load data infile的本质是一样的。mysqlimport在执行时会像服务端发送load data infile来加载数据 , 并且mysqlimport支持多进程并行导入多张表的数据。
mysqlimport的语法和load data infile基本一致。不同的是它在MySQL/MariaDB的外部执行 , 且可以一次性并行多线程导入多张表(并非并行导入一张表) , 所以能更快地导入所有数据。
最新mariadb版本的命令已经替换为mariadb-import
mysqlimport [OPTIONS] database textfile...注意 : mysqlimport只能指定数据库名来导入 , 所以导入的文件名必须和数据库中的表名相对应(文件名后缀无所谓)。例如文件名为stu2.sql , 而表名为student则无法导入 , 它会找stu2这个表。
例如 , 将以下格式的文件t.txt使用mysqlimport导入到test.t表中 :
[root@xuexi ~]# cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19
[root@xuexi ~]# mysqlimport -uroot -p123456 --fields-terminated-by=',' test '/data/t.txt'使用"--use-threads"选项可以指定导入线程数。
例如 , 下面指定两个线程 , 导入两张表到数据库test库中的t1和t2表中。
mysqlimport -uroot -p123456 --use-threads=2 --fields-terminated-by=',' test '/data/t1.txt' '/data/t2.txt'参考链接
MariaDB/MySQL备份和恢复(二)：数据导入、导出 - 骏马金龙 - 博客园