Export MySQL query data to csv file

created at 08-05-2021 views: 5

Export MySQL query data to csv file

to .csv:

# mysql  -h  xx.xx.xx.xx  -u root -p'MySQL@123'  -Bse "select id,name,phone,oId,openId from database.table"  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > file.csv
# mysql -u root -p'MySQL@123'  

> select id,name,phone,oId,openId from database.table into outfile '/tmp/file.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';

to .xls:

# mysql -u root -p'MySQL@123'  

>  select id, convert((name) using gbk) as name, phone,oId,openId from database.table into outfile  '/tmp/file.xls';

CSV is a text file, but its format is easy to import into excel, and xls is a table file, a set of things from Microsoft.

If you export the .xls excel file directly, fields of languages like Chinese needs to be transferred, otherwise it will be garbled when you open it. The structure of this table is the encoding of utf8.

created at:08-05-2021
edited at: 08-05-2021: