mysql select into方式导入导出

 

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

mysql> show variables like '%secure%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| require_secure_transport | OFF                         |
| secure_auth              | ON                          |
| secure_file_priv         | /opt/mysql5733/secure_file/ |
+--------------------------+-----------------------------+
3 rows in set (0.01 sec)

 

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 ' ';


查看表数据
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';