Mysql imports data to excel, mysqlexcel
Create a test data table first
Create table users (
Username varchar (40) not null,
Password varchar (40) not null) charset = utf8;
Insert data
Insertinto users values ("admin", "admin ");
Insertinto users values ("Liyang silence", "Wang Peikun ");
Use the following sentence to write data in the database to the test.xls file.
Mysql> select username, password from users into outfile "d: \ test.xls ";
(Note: mysql must have the write permission on the file path)
(Alternatively, use the following statement to export the data to excel: select * into outfile "d: \ test1.xls" from test ;)
Because the default office Code is gb2312, and the server may use UTF-8, the excel file may contain garbled characters, as shown below:
We can recode a field as follows:
Mysql> select convert (username using gb2312), convert (password using gb2312) from
Users into outfile "d: \ test.xls ";
This is the normal display in the file:
Mysql> select convert (username using gb2312), convert (password using gb2312) from
Users into outfile "d: \ test.xls ";