Export mysql data to excel
The query statement is output directly.
Example： select * into outfile '/data/var-3307/catid.xls' from help_cat where 1 order by cat_id desc limit 0,20; select * into outfile '导出文件存放目录' from 表面和查询条件等信息！
Please pay attention to permissions, if it is not working, put it in the /tmp directory
In the shell command line, open execl in text mode, then save as, and select ansi code in the code to save the
Example： echo "select * from db_web.help_cat where 1 order by sort desc limit 0,20" | mysql -h127.0.0.1 -uroot > /data/sort.xls
Echo query statement pipeline login mysql link mode> directed output file
Query directed output is an Excel file suffix, and then transcoded.
Example: mysql db_web -uroot -e "select * from help_cat where 1 order by type desc limit 0,20" > /data/type.xls
mysql link information database user name and password and then execute query statement, direct output.
Download the file locally and open it if the Chinese characters are garbled, because the office defaults to gb2312 encoding, and the server-side generation is likely to be utf-8 encoding. At this time, you have two choices:
1. Use iconv to encode on the server-side Conversion
iconv -futf8 -tgb2312 -otype1.xls type.xls
2. If the conversion is not smooth, it will prompt:
iconv: illegal input sequence at position 1841
A similar error can be solved as follows:
first download type.xls, this time the file is utf-8 encoded, opened with excel, garbled. Open type.xls in text mode, then save as, select ANSI encoding to save in the encoding.