mysql 导出 csv 文件以及注意事项

方式一

mysqldump的方式

mysqldump -u root -p pass -t -T/tmp/ self_database self_table --fields-terminated-by=',' --fields-escaped-by='' --fields-optionally-enclosed-by='';

方式二

sql 语句的方式

SELECT * FROM table 
into outfile '/tmp/table.csv' 
fields terminated by "," 
optionally enclosed by '' 
escaped by '' 
lines terminated by '\n';

带表头导出

SELECT * from (
    select 'id','名字' union select id,name from table
) t
into outfile '/tmp/table.csv'
fields terminated by ","
escaped by ''
optionally enclosed  by ''
lines terminated by '\n';

字段解释

• fields terminated by 字段的分隔符，默认是tab字符（\t）
• escaped by 转义字符（默认的是反斜杠）
• optionally enclosed by 字段包裹符（包裹字段值两边）
• lines terminated by 行与行之间的分隔

注意事项

注意事项一：

outfile输出的文件路径必须是 mysql.ini 配置中支持的安全路径，

使用其他路径或者未配置会有如下报错

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

配制方法:
在 mysql.ini 中设置

secure-file-priv="/tmp"

注意事项二：

由于 csv 格式是用英文逗号分割每列数据的，

把导出的 csv 文件用编辑器打开数据是这样的

1,1,test_key,c107b5ba3c82821d6c65dbf046801415,1669355480,NULL,NULL

所以如果导出的数据值中本身就带有逗号会导致 csv 中展示的格式混乱

解决办法：使用双引号包裹有问题的字段

SELECT id,ifnull(name, "") FROM table 
into outfile '/tmp/table.csv' 
fields terminated by "," 
optionally enclosed by '"' 
escaped by '"' 
lines terminated by '\n';

注意事项三：

大数字科学记数法问题

导出来的文件中其实源数据是对的，但是在 wps 中看到会显示为科学记数法

解决方式 1：

在 wps 设置单元格式

解决方式 2：

导出的时候拼接字符串使其强制设置为字符串

SELECT id,concat(amount, '\t') FROM table 
into outfile '/tmp/table.csv' 
fields terminated by "," 
optionally enclosed by '' 
escaped by '' 
lines terminated by '\n';

最后举个完整的例子：

导出表 table，带表头，表中name的值有逗号，amount字段的值有大数字

SELECT * from (
    select '字段1','字段2','字段3' union select id,concat(amount, '\t'),ifnull(name, "") from table
) t
into outfile '/tmp/table.csv'
fields terminated by ","
escaped by '"'
optionally enclosed  by '"'
lines terminated by '\n';

tips:能使用select语句，就能使用where条件，这样能操作的空间就很大，可以自行摸索