84.MySQL导出CSV格式的文件

1.csv格式的文件用于异构数据库迁移是非常方便的。那么mysql如何导出csv格式文件呢

mysql  -uroot -p'rootroot' -ss -e "select MainCode,InnerCode,SecuCode,SecuAbbr  from jydb.Bond_Code ;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > Bond_Code.csv
mysql: [Warning] Using a password on the command line interface can be insecure.

按where 条件导出。导出后需要通过head 命令检查是否有:

[hjroot@jydb1 ~]$ head Bond_Code.csv
"Logging to file '/data/mysql/logs/query.log'"
"2","2","000001","00国债01"
"5","5","000002","00国债02"
"8","8","125002","万科转债"
"10","10","000003","00国债03"
"16","16","000005","00国债05"
"19","19","000006","00国债06"
"22","22","000007","00国债07"
"25","25","000008","00国债08"
"27","27","000009","00国债09"

我们发现第一行的日志信息也被写入了csv文件。所以需要删除第一行。需要注意，并不是所有的数据库导出csv文件时都是将日志信息写入。所以导出后需要人工检查是否有首行的日志，如果有就删除，如果没有则忽略。

备份后第一行生产的日志需要删除。
sed -i '1d' Bond_Code.csv