Export the relational table in MySQL to excel

The test environment is win7, office 2010, and MySQL 5.5.42.

 

1: Create a test table with three fields, two of which are used for demonstration.

1 mysql> create table tt(a int(11) not null,b varchar(30),c varchar(30)); 

 

1 mysql> insert into TT values (1, 'beijing', 'peking University '), (2, 'shanghai', 'shanghai'), (3, 'shenzhen ', 'shenzhen Emy ');

 

2: export a relational table to an Excel file using the OUTFILE method. The effect of the following two methods is the same.

1 mysql> select b,c from tt into outfile 'd:\xls_tt.xls';

 

1 mysql> select b,c into outfile 'd:\xlstt.xls' from tt;

 

3: Find the exported xls file under drive D, and all the exported files are garbled, as shown below:

These images contain images, and images.

This is completely unavailable. "At this time, the file is UTF-8 encoded, And it is garbled when opened in Excel. Open test.xls in text format, save it as, select ANSI encoding in the encoding format, and save". After this operation, it will not be garbled, but it needs to be handled manually.

 

4: With the help of network friends, find the following conversion method

1 mysql> select convert(b using gb2312),convert(c using gb2312) into outfile 'c:\xlstt12.xls' from tt;

In this way, everything is normal when you open the exported file.

Note:

1. If there is no Chinese Character in the relational table, that is, all the content is in English, no conversion is required, and the export result can be displayed normally. If there is a Chinese character, conversion is required.