mysql 导出csv格式并解决乱码

mysql 导出csv格式并解决乱码

导出csv

方法一

root@95d956bb2012:/# mysql -u root -p -e "select * from wordpress.wp_users" |sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g'  > /tmp/wp_users.csv

方法二

SELECT * INTO OUTFILE '/data/db/mysql3306/filedir/a.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM  wordpress.wp_users;

/data/db/mysql3306/filedir/该路径是mysql配置文件my.cnf中指定的唯一导出目录，secure_file_priv的位置,若没有设置，则指定/tmp/即可

secure_file_priv            = /data/db/mysql6633/filedir

解决中文乱码

sed -i '1s/^/\xEF\xBB\xBF/' /tmp/wp_users.csv