数据导出导入
<>利用CSV存储引擎加载数据：CSV存储引擎基于CSV格式文件存储数据，CSV格式是纯文本格式的文件，以逗号分隔取值。CSV引擎表的所有列值不能为空。Excel可以直接打开
有个文件loaddata.txt内含数据如下：
1000001,景一,郑州,jingyi@data.com
1000002,景十,开封,jingshi@data.com
1000003,景百,洛阳,jingbai@data.com
1000004,景千,许昌,jingqian@data.com
1000005,景万,漯河,jingwan@data.com
把loaddata.txt加载到数据库
system@jason>create table ld_csv1(id int not null default ‘0’,username varchar(5) not null,city varchar(6) not null,email varchar(50)not null) engine=csv default charset=gbk;
Query OK, 0 rows affected (0.03 sec)
[mysql@linux01 jason]$ pwd
/mysql/data/jason
[mysql@linux01 jason]$ ls -ltr |grep csv
-rw-rw—-. 1 mysql mysql 8656 4月 13 08:47 ld_csv1.frm
-rw-rw—-. 1 mysql mysql 35 4月 13 08:47 ld_csv1.CSM
-rw-rw—-. 1 mysql mysql 0 4月 13 08:47 ld_csv1.CSV —->数据文件
直接vi打开ld_csv1.CSV文件把loaddata.txt文件的内容复制进去，或者
cat loaddata.txt > /mysql/data/jason/ld_csv1.CSV
查询ld_csv1表对象：
system@jason>select * from ld_csv1;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000001 | 景一 | 郑州 | jingyi@data.com |
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
+———+———-+——+——————-+
5 rows in set (0.01 sec)
数据都加载进去了，CSV引擎表类似oracle中的外部表。，如果查询结果集有乱码，检查客户端/服务端/交互中的字符集设置均应该GBK，简单的讲就是：set names gbk;
<>mysqlimport命令行工具导入数据：
创建InnoDB引擎表
system@jason>create table ld_cmd(id int(11) default 0,username varchar(5),city varchar(6),email varchar(50)) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)
创建临时文件，原因是mysqlimport导入数据要求数据文件名与表对象名相同
cp loaddata.txt ld_cmd.txt
导入数据：mysqlimport -usystem -p’oralinux’ -S /mysql/conf/mysql.sock jason –default-character-set=gbk –fields-terminated-by=’,’ /mysql/tmp/ld_cmd.txt
导入报错：[mysql@linux01 ~]$ mysqlimport -usystem -p’oralinux’ -S /mysql/conf/mysql.sock jason –default-character-set=gbk –fields-terminated-by=’,’ /mysql/tmp/ld_cmd.txt
Warning: Using a password on the command line interface can be insecure.
mysqlimport: Error: 1290, The MySQL server is running with the –secure-file-priv option so it cannot execute this statement, when using table: ld_cmd
解决办法：
system@jason>show variables like ‘%secure%’
-> ;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| secure_auth | ON |
| secure_file_priv | NULL |
+——————+——-+
secure_file_priv为NULL修改my.cnf添加：
[mysqld]
secure_file_priv=’/mysql/tmp’
修改后执行报错：
[mysql@linux01 mysql]$ mysqlimport -usystem -p’oralinux’ -S /mysql/conf/mysql.sock jason –default-character-set=gbk –fields-terminated-by=’,’ /mysql/tmp/ld_cmd.txt
Warning: Using a password on the command line interface can be insecure.
mysqlimport: Error: 1366, Incorrect string value: ‘\xBE\xB0\xD2\xBB’ for column ‘username’ at row 1, when using table: ld_cmd
字符集文件，–default-character-set=gbk 而建表语句charset=utf8 修改建表语句如下：
create table ld_cmd(id int(11) default 0,username varchar(5),city varchar(6),email varchar(50)) engine=innodb charset=gbk’;
再次导入成功：
[mysql@linux01 mysql]$ mysqlimport -usystem -p’oralinux’ -S /mysql/conf/mysql.sock jason –default-character-set=gbk –fields-terminated-by=’,’ /mysql/tmp/ld_cmd.txt
Warning: Using a password on the command line interface can be insecure.
jason.ld_cmd: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
system@jason>select * from ld_cmd;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000001 | 景一 | 郑州 | jingyi@data.com |
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
+———+———-+——+——————-+
5 rows in set (0.00 sec)
mysqlimport –help可以查看其所支持的所有参数。
-u 指定连接的用户名
-p 指定连接的密码
-h 指定连接的主机地址
-P 指定连接的目标服务的端口号默认3306
-S 指定以socket方式连接
-h和-S二者可以选一
–default-character-set=name：设置默认字符集，中文环境GBK是必需的。如果您要处理的文件不是GBL编码那么使用吗UTP8
-d,–delete：在导入数据前，先删除对象中所有的记录。
-f,–force：如果导入时遇到错误，仍继续执行
-i,–ignore：如果插入的记录中发现重复值，那么该条记录不处理。
-r,–replace：如果插入记录中发现重复键，则覆盖就记录
-L,–local：从执行mysqlimport命令的客户端本地读取文件（如果不指定，则表示从服务端的对应路径下去读取文件）
-l,–lock-tables：导入时先锁定表
-s,–silent：以静默方式导入数据，就是不输出操作结果了。
–ignore-lines=#：跳过文件的前n行记录
–use-threads=#：并行方式加载数据，也就是启动多个线程加载数据。
<>分列
1、假如文件的格式是这样的该怎么导入呢：
1000001#景一#郑州#jingyi@data.com
那么使用–fields-terminated-by=# 指定每列的分隔符
2、1000001#景#一#郑州#jingyi@data.com 这样怎么导入呢：
修改文件内容为：1000001#”景#一”#”郑州”#”jingyi@data.com”使用–fields-enclosed-by=\” 这个参数指定列值的限定符，这里为双引号。
3、如果数据文件中有的字符有限定符，有些没有这种情况下使用–fields-optionally-enclosed-by=[xxx]指定列的限定符，与上面的参数功能相同，唯一区别是，如果字符集有限定符那么就使用限定符，没有的话就忽略
mysqlimport -usystem -p’oralinux’ -S /mysql/conf/mysql.sock jason -d –default-character-set=gbk –fields-terminated-by=’#’ –fields-enclosed-by=\” /mysql/tmp/ld_cmd.txt
<>换行很容易
1000006#胡#
三#成都#huer@data.com
1000007#胡四#钓鱼
岛#husi@data.com
如上所示，不是简单的一条记录就是一行，而是一条记录可能包含多行，遇到这种情况使用–lines-terminated-by参数指定文件中记录行的结束符，默认是换行符
修改文件如下：
1000006#”胡#
三”#”成都”#”huer@data.com”###
1000007#”胡四”#”钓鱼
岛”#”husi@data.com”###
执行命令如下：
mysqlimport -usystem -p’oralinux’ -S /mysql/conf/mysql.sock jason -d –default-character-set=gbk –fields-terminated-by=’#’ –fields-enclosed-by=\” –lines-terminated-by=’###\n’ /mysql/tmp/ld_cmd.txt
<>SQL语句导入数据
创建表：create table ld_sql(id int(11) default 0,username varchar(5),city varchar(6) ,email varchar(50)) engine=innodb default charset=gbk;
执行LOAD DATA INFILE语句加载数据：
system@jason>load data infile ‘/mysql/tmp/loaddata.txt’ into table ld_sql charset gbk fields terminated by ‘,’;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
system@jason>select * from ld_sql;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000001 | 景一 | 郑州 | jingyi@data.com |
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
+———+———-+——+——————-+
5 rows in set (0.00 sec)
load data infile默认的列分隔是Tab符，我们现在是,所以使用terminated by ‘,’指定分隔符是, mysqlimport命令实际上就是load data infile语句的命令行调用接口。所以mysqlimport命令的许多参数能在load data infile语句中找到对应的语法。
<>字符集处理
set names或系统变量character_set_client的设置对于导入的数据无效。这种情况下如果要导入的文件中使用的字符集和当前系统变量character_set_database指定的字符集不同，那么导入后字符就极有可能出现乱码
比如当前有个gbk编码格式的文件loaddata.txt，当前会话中与字符集相关变量的设置：show variables like ‘char%’;
system@jason>show variables like ‘char%’;
+————————–+————————+
| Variable_name | Value |
+————————–+————————+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /mysql/share/charsets/ |
+————————–+————————+
导入执行语句load data infile ‘/mysql/tmp/loaddata.txt’ into table ld_sql fields terminated by ‘,’;
system@jason>load data infile ‘/mysql/tmp/loaddata.txt’ into table ld_sql fields terminated by ‘,’;
ERROR 1300 (HY000): Invalid utf8 character string: ”
这种情况下再怎么设置会话中的字符集也是无效的。即使我们事先通过set names指定会话的字符集为gbk也没用。
这就需要用到load data infile中的character set charset_name 这是处理字符集的，不过character set在这里是用于处理数据的字符集，而不是数据保存时的字符集（保存时的字符集仍是由表对象或表中字符列的字符集而定）
system@jason>load data infile ‘/mysql/tmp/loaddata.txt’ into table ld_sql charset gbk fields terminated by ‘,’;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
system@jason>select * from ld_sql;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000001 | 景一 | 郑州 | jingyi@data.com |
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
| 1000001 | 景一 | 郑州 | jingyi@data.com |
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
+———+———-+——+——————-+
10 rows in set (0.00 sec)
除了通过设置character set子句来控制字符集外，也可以设置character_set_database环境变量的值，使之与要处理的塑化剂文件字符集相同：
truncate table ld_sql;
set character_set_database=gbk;
show variables like ‘character%’;
system@jason>truncate table ld_sql;
Query OK, 0 rows affected (0.01 sec)
system@jason>set character_set_database=gbk;
Query OK, 0 rows affected (0.00 sec)
system@jason>show variables like ‘character%’;
+————————–+————————+
| Variable_name | Value |
+————————–+————————+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /mysql/share/charsets/ |
+————————–+————————+
8 rows in set (0.00 sec)
system@jason>load data infile ‘/mysql/tmp/loaddata.txt’ into table ld_sql fields terminated by ‘,’;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
system@jason>select * from ld_sql;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000001 | 景一 | 郑州 | jingyi@data.com |
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
+———+———-+——+——————-+
5 rows in set (0.00 sec)
<>要导入的数据文件放哪儿
客户端导入：
[mysql@linux02 ~]$ mysql -usystem -poralinux -h linux01
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.34-log JASON
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
system@(none)>
system@(none)>use jason
Database changed
system@jason>
system@jason> truncate table ld_sql;
Query OK, 0 rows affected (0.04 sec)
system@jason>load data local infile ‘/home/mysql/loaddata.txt’ into table ld_sql charset gbk fields terminated by ‘,’;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
system@jason>select * from ld_sql;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000001 | 景一 | 郑州| jingyi@data.com |
| 1000002 | 景十 | 开封| jingshi@data.com |
| 1000003 | 景百 | 洛阳| jingbai@data.com |
| 1000004 | 景千 | 许昌| jingqian@data.com |
| 1000005 | 景万 | 漯河| jingwan@data.com |
+———+———-+——+——————-+
5 rows in set (0.00 sec)
这样从linux02主机的mysql客户端导入到linux01的mysql服务端成功导入。客户端导入方式会比服务端速度慢点，如果mysql启动时禁用了local-infile选项，那么就不允许以local方式导入了。
如果制定了secure_file_priv系统变量那么要处理的文件必须位于该参数指定的路径下。
IGNORE子句其实是用来控制导入数据过程中，遇到重复记录时的处理方式，不仅有IGNORE子句还有REPLCACE子句，出现两个子句时基本处理逻辑如下：
如果指定了replace子句，则出现重复值时会替换当前存在的记录
如果指定了igore子句，当插入时遇到重复值会跳过重复的记录
如果上述两个选项都没有指定，处理行为依赖于是否指定了LOCAL关键字，没有指定LOCAL的话，则出现重复记录时就会报错，如果指定了LOCAL,则默认处理行为与IGNORE相同。
<>数据文件的前N行记录不想到处理办法
system@jason>truncate table ld_sql;
Query OK, 0 rows affected (0.02 sec)
跳过数据文件的第一行：
system@jason>load data local infile ‘/home/mysql/loaddata.txt’ into table ld_sql charset gbk fields terminated by ‘,’ ignore 1 lines;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
system@jason>select * from ld_sql;
+———+———-+——+——————-+
| id | username | city | email |
+———+———-+——+——————-+
| 1000002 | 景十 | 开封| jingshi@data.com |
| 1000003 | 景百 | 洛阳| jingbai@data.com |
| 1000004 | 景千 | 许昌| jingqian@data.com |
| 1000005 | 景万 | 漯河| jingwan@data.com |
+———+———-+——+——————-+
4 rows in set (0.00 sec)
<>列和行的精确处理
FIELDS和LINES子句语法
如果既没有指定FIELDS也没有指定LINES，那么实际上默认语句当同于下列写法：
fields terminated by ‘\t’ enclosed by ” escaped by ‘\\’
lines terminated by ‘\n’ starting by ”
fields terminated by：指定列的分隔符，默认是tab符(\t)
enclosed by：指定列的包含符，默认为空。
escaped by：指定转义符，默认是’\’
lines terminated by：指定换行符默认是换行(\n)
starting by：指定每行开始的位置（跟字符相关）
<>sql语句导出数据
select …. into outfile;
子句解释：
fields和lines的语法，对于load data infile和select … into outfile语句来说都是相同的。功能也一样
into outfile子句是在标准的select语句智商做的扩展
只要select能够查询的结果集，就可以被输出到外部文件
第四跟第三有所关联，前面说的是外部文件，这个outfile不仅仅可以是标准的列分隔文本，也可以是into dumpfile，甚至还可以是into var_name,将结果集输出到某个变量。
示例；select * from ld_sql into outfile ‘/mysql/tmp/ld_sql_out.txt’;
system@jason>select * from ld_sql into outfile ‘/mysql/tmp/ld_sql_out.txt’;
Query OK, 4 rows affected (0.00 sec)
[mysql@linux01 tmp]$ cat /mysql/tmp/ld_sql_out.txt
1000002 景十 开封 jingshi@data.com
1000003 景百 洛阳 jingbai@data.com
1000004 景千 许昌 jingqian@data.com
1000005 景万 漯河 jingwan@data.com
字符集同样适用charcter set子句，中文环境设置GBK，实在不行直接指定UTF8但是比GBK字符集对占用1/3的空间
select … into dumpfile…
