Teach you how to export mysql data to Excel

Export mysql data to excel

method one:

The query statement is output directly.
Syntax format:

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 表面和查询条件等信息!
Insert picture description here


Please pay attention to permissions, if it is not working, put it in the /tmp directory

Method Two:

In the shell command line, open execl in text mode, then save as, and select ansi code in the code to save the
syntax grid

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

Insert picture description here

Method three:

Query directed output is an Excel file suffix, and then transcoded.
Syntax format:

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.

Insert picture description here


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.