mysql select into方式导入导出

前提条件是要设置secure_file_priv,该参数是需要重启实例的

SELECT * FROM tb_hxl01 INTO OUTFILE '/opt/mysql5733/secure_file/tb_hxl01.txt' FIELDS TERMINATED BY ',';

或是

select * into outfile '/opt/mysql5733/secure_file/tb_hxl0222.txt' fields terminated by ',' from tb_hxl01 where id=2;

导出文件内容如下
[root@localhost secure_file]# more tb_hxl01.txt
1,name01,2021-05-20 08:50:56,2021-05-20 08:50:56
2,name02,2021-05-20 08:50:56,2021-05-20 08:50:56
3,name03,2021-05-20 08:50:56,2021-05-20 08:50:56
4,name04,2021-05-20 08:50:56,2021-05-20 08:50:56
5,name05,2021-05-20 08:50:56,2021-05-20 08:50:56
6,name06,2021-05-20 08:50:56,2021-05-20 08:50:56
7,name07,2021-05-20 08:50:57,2021-05-20 08:50:57
8,name08,2021-05-20 08:50:57,2021-05-20 08:50:57
9,name09,2021-05-20 08:50:57,2021-05-20 08:50:57
10,name10,2021-05-20 08:50:57,2021-05-20 08:50:57
11,name11,2021-05-20 08:50:57,2021-05-20 08:50:57
12,name12,2021-05-20 08:50:57,2021-05-20 08:50:57
13,name13,2021-05-20 08:50:57,2021-05-20 08:50:57
14,name14,2021-05-20 08:50:57,2021-05-20 08:50:57
15,name15,2021-05-20 08:50:57,2021-05-20 08:50:57
16,name16,2021-05-20 08:50:58,2021-05-20 08:50:58
17,name17,2021-05-20 08:50:58,2021-05-20 08:50:58
18,name18,2021-05-20 08:50:58,2021-05-20 08:50:58
19,name19,2021-05-20 08:50:58,2021-05-20 08:50:58
20,name20,2021-05-20 08:50:59,2021-05-20 08:50:59

尝试导入到另外一个表
create table tb_hxl02 like tb_hxl01;

LOAD DATA INFILE '/opt/mysql5733/secure_file/tb_hxl01.txt' INTO TABLE tb_hxl02
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

查看表数据
mysql> select * from tb_hxl02;
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 1 | name01 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 2 | name02 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 3 | name03 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 4 | name04 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 5 | name05 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 6 | name06 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 7 | name07 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 8 | name08 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 9 | name09 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 10 | name10 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 11 | name11 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 12 | name12 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 13 | name13 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 14 | name14 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 15 | name15 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 16 | name16 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 17 | name17 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 18 | name18 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 19 | name19 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 20 | name20 | 2021-05-20 08:50:59 | 2021-05-20 08:50:59 |
+----+--------+---------------------+---------------------+
20 rows in set (0.00 sec)

SELECT s.supervision_code into outfile '/home/middle/mysql57/secure_file/aa.txt' fields terminated by ','
FROM ss_supervision_code s
WHERE s.hospital_code='430104004042'
and s.storage_date='20210515' and s.pack_layer='1';

也可以保存为csv格式(避免乱码)

SELECT s.supervision_code into outfile '/home/middle/mysql57/secure_file/aa.csv' fields terminated by ','
FROM ss_supervision_code s
WHERE s.hospital_code='430104004042'
and s.storage_date='20210515' and s.pack_layer='1';

windows下导出(换行符号为\r\n)

SELECT * into outfile 'D:\\mysql57\\secure_file\\aa.txt' FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM db_test.tb_test s;

windows下需要file权限

grant file on *.* to 'dmladmin'@'%';