本篇来演示MySQL的导入和导出。
MySQL之导出
可以使用以下两种方法进行MySQL的导出，分别为：
- SELECT ... INTO OUTFILE ...
- Mysqldump工具
1、SELECT ... INTO OUTFILE ...
1）语法如下：
SELECT * FROM tablename INTO OUTFILE 'target_file' [option];其中option参数可以是以下选项：
- FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符‘ ’）；
- FIELDS [OPTIONALLY] ENCLOSED BY 'char' （字段引用符，如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上，默认不使用引用符）；
- FIELDS ESCAPED BY ‘char’ （转义字符，默认为‘’）；
- LINES STARTING BY ‘string’ （每行前都加此字符串，默认‘’）；
- LINES TERMINATED BY ‘string’ （行结束符，默认为‘ ’）；
2）使用默认选项导出数据库test下的t_mm表；
mysql> select *from t_mm into outfile '/usr/local/mysql/mysql-files/emp.txt';
Query OK, 6 rows affected (0.04 sec)
mysql> system more /usr/local/mysql/mysql-files/emp.txt;
1 Alen 2018-03-09 15:33:03
3 UK 2018-03-09 15:35:55
4 Japan 2018-03-09 12:36:16
6 USA 2018-03-09 13:19:35
8 Begin MMM.... 2018-03-09 14:54:40
10 Hello 2018-03-12 15:33:22
mysql> 注：如果有同名的文件，则导出失败；
3）使用带选项的语句导出数据库test下的t_mm表；
mysql> select *from t_mm into outfile '/usr/local/mysql/mysql-files/emp.txt' fields terminated by ',' enclosed by '"';
Query OK, 6 rows affected (0.00 sec)
mysql> system more /usr/local/mysql/mysql-files/emp.txt;
"1","Alen","2018-03-09 15:33:03"
"3","UK","2018-03-09 15:35:55"
"4","Japan","2018-03-09 12:36:16"
"6","USA","2018-03-09 13:19:35"
"8","Begin MMM....","2018-03-09 14:54:40"
"10","Hello","2018-03-12 15:33:22"
mysql> 4）如上，第一列是数字类型，不希望用引号引起，语句可改为：
mysql> select *from t_mm into outfile '/usr/local/mysql/mysql-files/emp.txt' fields terminated by ',' optionally enclosed by '"';
Query OK, 6 rows affected (0.00 sec)
mysql> system more /usr/local/mysql/mysql-files/emp.txt;
1,"Alen","2018-03-09 15:33:03"
3,"UK","2018-03-09 15:35:55"
4,"Japan","2018-03-09 12:36:16"
6,"USA","2018-03-09 13:19:35"
8,"Begin MMM....","2018-03-09 14:54:40"
10,"Hello","2018-03-12 15:33:22"
mysql> 2、Mysqldump工具
1）语法如下：
mysqldump -uusername -p -T target_dir dbname tablename [option]其中option参数可以是以下选项：
- --fields-terminated-by=name（字段分隔符）；
- --fields-enclosed-by=name（字段引用符）；
- --fields-optionally-enclosed-by=name（字段引用符，只用在char、varchar和text等字符型字段上）；
- --fields-escaped-by=name（转义字符）；
- --lines-terminated-by=name（记录结束符）；
2）使用默认选项导出数据库test下的所有表；
mysql> system mysqldump -uroot -p -T /usr/local/mysql/mysql-files/ test;
Enter password:
mysql> system ls -l /usr/local/mysql/mysql-files/
total 16
-rw-r--r--. 1 root root 1437 Mar 13 22:11 t_emp.sql
-rw-rw-rw-. 1 mysql mysql 111 Mar 13 22:11 t_emp.txt
-rw-r--r--. 1 root root 1435 Mar 13 22:11 t_mm.sql
-rw-rw-rw-. 1 mysql mysql 171 Mar 13 22:11 t_mm.txt
mysql>
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
1 张三 2018-03-13 18:53:44
3 李四 2018-03-13 18:53:44
5 Alen 2018-03-13 17:37:34
7 USA 2018-03-13 17:39:45
mysql> system more /usr/local/mysql/mysql-files/t_emp.sql--导出表定义脚本3）使用带选项的语句导出数据库test下的t_emp表；
mysql> system mysqldump -uroot -p -T /usr/local/mysql/mysql-files/ test t_emp --fields-terminated-by=',' --fields-optionally-enclosed-by='"';
Enter password:
mysql> system ls -l /usr/local/mysql/mysql-files/
total 8
-rw-r--r--. 1 root root 1437 Mar 13 22:18 t_emp.sql
-rw-rw-rw-. 1 mysql mysql 127 Mar 13 22:18 t_emp.txt
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
1,"张三","2018-03-13 18:53:44"
3,"李四","2018-03-13 18:53:44"
5,"Alen","2018-03-13 17:37:34"
7,"USA","2018-03-13 17:39:45"
mysql> 以上，可以发现，mysqldump和select...into outfile...的选项和语法非常相似，其实，mysqldump实际调用的就是后者提供的接口，并在其上增添了新功能而已。
MySQL之导入
MySQL的导入功能和导出相对应，也有两种导入方式，分别为：
- LOAD DATA INFILE...
- MySQLImport工具
1、LOAD DATA INFILE...
1）语法如下：
LOAD DATA [LOCAL] INFILE 'filename' INTO TABLE tablename [option]其中option参数可以是以下选项：
- FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符‘ ’）；
- FIELDS [OPTIONALLY] ENCLOSED BY 'char' （字段引用符，如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上，默认不使用引用符）；
- FIELDS ESCAPED BY ‘char’ （转义字符，默认为‘’）；
- LINES STARTING BY ‘string’ （每行前都加此字符串，默认‘’）；
- LINES TERMINATED BY ‘string’ （行结束符，默认为‘ ’）；
- IGNORE number LINES（忽略输入文件中的前n行数据）；
- （col_name_or_user_var，...）（按照列出的字段顺序和字段数量加载数据）；
- SET col_name=expr，...将列做一定的数值转换后再加载；
2）加载导出的t_emp表数据；
mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' optionally enclosed by '"';
Query OK, 4 rows affected (0.02 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select *from t_emp;
+----+--------+---------------------+
| id | name | cdate |
+----+--------+---------------------+
| 1 | 张三 | 2018-03-13 18:53:44 |
| 3 | 李四 | 2018-03-13 18:53:44 |
| 5 | Alen | 2018-03-13 17:37:34 |
| 7 | USA | 2018-03-13 17:39:45 |
+----+--------+---------------------+
4 rows in set (0.00 sec)
mysql> 3）如果不希望加载前2行，可以用以下语句：
mysql> truncate table t_emp;
Query OK, 0 rows affected (0.06 sec)
mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' ignore 2 lines;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select *from t_emp;
+----+------+---------------------+
| id | name | cdate |
+----+------+---------------------+
| 5 | Alen | 2018-03-13 17:37:34 |
| 7 | USA | 2018-03-13 17:39:45 |
+----+------+---------------------+
2 rows in set (0.00 sec)
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
1,"张三","2018-03-13 18:53:44"
3,"李四","2018-03-13 18:53:44"
5,"Alen","2018-03-13 17:37:34"
7,"USA","2018-03-13 17:39:45"
mysql> 4）使用LINES STARTING BY ‘string’选项过滤数据；
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
Alen1,"张三","2018-03-13 18:53:44"
3,"李四","2018-03-13 18:53:44"
Alen5,"Alen","2018-03-13 17:37:34"
7,"USA","2018-03-13 17:39:45"
mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' lines starting by 'Alen';
Query OK, 2 rows affected (0.05 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select *from t_emp;
+----+--------+---------------------+
| id | name | cdate |
+----+--------+---------------------+
| 1 | 张三 | 2018-03-13 18:53:44 |
| 5 | Alen | 2018-03-13 17:37:34 |
+----+--------+---------------------+
2 rows in set (0.00 sec)
mysql> 注：LINES STARTING BY ‘string’选项时，可跳过指定字符的前缀以及前缀之前的所有字符，如果某行不包含该指定字符，则该行记录也会被跳过。
5）表中的列数多于数据文件时的处理；
mysql> desc t_emp;
+-------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| cdate | datetime | YES | | CURRENT_TIMESTAMP | |
| sex | char(1) | YES | | NULL | |
+-------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
1,"张三","2018-03-13 18:53:44"
3,"李四","2018-03-13 18:53:44"
5,"Alen","2018-03-13 17:37:34"
7,"USA","2018-03-13 17:39:45"
mysql> truncate table t_emp;
Query OK, 0 rows affected (0.07 sec)
mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' (id,name,cdate);
Query OK, 4 rows affected (0.06 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name | cdate | sex |
+----+--------+---------------------+------+
| 1 | 张三 | 2018-03-13 18:53:44 | NULL |
| 3 | 李四 | 2018-03-13 18:53:44 | NULL |
| 5 | Alen | 2018-03-13 17:37:34 | NULL |
| 7 | USA | 2018-03-13 17:39:45 | NULL |
+----+--------+---------------------+------+
4 rows in set (0.00 sec)
mysql> 6）表中的列数少于数据文件时的处理；
mysql> desc t_emp;
+-------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| cdate | datetime | YES | | CURRENT_TIMESTAMP | |
| sex | int(1) | YES | | NULL | |
+-------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
1,1,"张三","2018-03-13 18:53:44",25
3,1,"李四","2018-03-13 18:53:44",24
5,1,"Alen","2018-03-13 17:37:34",30
7,0,"Lucy","2018-03-13 17:39:45",20
mysql> truncate table t_emp;
Query OK, 0 rows affected (0.07 sec)
mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' (id,sex,name,cdate,@tmp);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name | cdate | sex |
+----+--------+---------------------+------+
| 1 | 张三 | 2018-03-13 18:53:44 | 1 |
| 3 | 李四 | 2018-03-13 18:53:44 | 1 |
| 5 | Alen | 2018-03-13 17:37:34 | 1 |
| 7 | Lucy | 2018-03-13 17:39:45 | 0 |
+----+--------+---------------------+------+
4 rows in set (0.00 sec)
mysql> 7）表中的列数和数据文件不一样，并且数据文件的列类型也不同时的处理；
mysql> desc t_emp;
+-------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| cdate | datetime | YES | | CURRENT_TIMESTAMP | |
| sex | int(1) | YES | | NULL | |
+-------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
mysql> system more /usr/local/mysql/mysql-files/t_emp.txt
1,"男","张三","2018-03-13 18:53:44",25
3,"男","李四","2018-03-13 18:53:44",24
5,"男","Alen","2018-03-13 17:37:34",30
7,"女","Lucy","2018-03-13 17:39:45",20
mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp
-> fields terminated by ','
-> enclosed by '"' (id,@tmp_sex,name,cdate,@tmp)
-> set sex=if(@tmp_sex='男',1,0);
Query OK, 4 rows affected (1.05 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name | cdate | sex |
+----+--------+---------------------+------+
| 1 | 张三 | 2018-03-13 18:53:44 | 1 |
| 3 | 李四 | 2018-03-13 18:53:44 | 1 |
| 5 | Alen | 2018-03-13 17:37:34 | 1 |
| 7 | Lucy | 2018-03-13 17:39:45 | 0 |
+----+--------+---------------------+------+
4 rows in set (0.00 sec)
mysql> 2、MySQLImport工具
1）语法如下：
mysqlimport -uroot -p [local] dbname tab.txt [option]其中option参数可以是以下选项：
- --fields-terminated-by=name（字段分隔符）；
- --fields-enclosed-by=name（字段引用符）；
- --fields-optionally-enclosed-by=name（字段引用符，只用在char、varchar和text等字符型字段上）；
- --fields-escaped-by=name（转义字符）；
- --lines-terminated-by=name（记录结束符）；
- --ignore-lines=number（忽略前几行）；
注：mysqlimport命令实际上就是LOAD DATA INFILE语句的命令行调用接口，mysqlimport导入数据要求数据文件名与表名相同，更详细的命令可通过 mysqlimport --help查看。
2）用法和MySQLdump相同，举例省略...