4.4 如何把 MySQL 表数据导出到 CSV 文件

create table t1(id bigint not null  auto_increment, c1 char(10), c2 varchar(10), primary key(id));
insert into t1 (c1, c2) values(' 中 国 ',' 中 国 ');
insert into t1 (c1, c2) values(' 中,国 ',' "中 国" ');
insert into t1 (c1, c2) values(' 中
国 ','中
国 ');
insert into t1 (c1, c2) values(' 中\\国 ',' "中\\国" ');

MariaDB [test]> select * from t1;
+----+----------+-------------+
| id | c1       | c2          |
+----+----------+-------------+
|  1 |  中 国   |  中 国      |
|  2 |  中,国   |  "中 国"    |
|  3 |  中
国   | 中
国      |
|  4 |  中\国   |  "中\国"    |
+----+----------+-------------+
4 rows in set (0.00 sec)

MariaDB [test]> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.01 sec)

MariaDB [test]> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.00 sec)

MariaDB [test]> select * from t1 into outfile '/tmp/t1.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n' ;
Query OK, 3 rows affected (0.00 sec)

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

#cat -n tmp/t1.csv
     1  "1"," 中 国"," 中 国 "
     2  "2"," 中,国"," \"中 国\" "
     3  "3"," 中\
4  国","中 \
5  国 "
     6  "4"," 中\\国"," \"中\\国\" "

MariaDB [test]> create table t2 like t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> load data infile '/tmp/t1.csv' into table t2 fields terminated by ',' enclosed by '"' lines terminated by '\n' ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> select * from t2;
+----+----------+-------------+
| id | c1       | c2          |
+----+----------+-------------+
|  1 |  中 国   |  中 国      |
|  2 |  中,国   |  "中 国"    |
|  3 |  中
国   | 中
国      |
|  4 |  中\国   |  "中\国"    |
+----+----------+-------------+
4 rows in set (0.00 sec)