Create a test data table first
CREATE TABLE Users (
Username varchar (+) NOT NULL,
Password varchar (+) not null) Charset=utf8;
Inserting data
Insertinto Users values ("admin", "admin");
Insertinto Users values ("Silence", "Wang Peikun");
Use the following statement to write data from the database to the Test.xls file
Mysql>select Username,password from the users into OutFile "D:\\test.xls";
(Note that MySQL has write permission for the path to the file here.)
( or use the following statement to export to Excel select* into outfile "D:\\test1.xls" from test; )
Because office defaults to gb2312 encoding, and the server may be using UTF-8 encoding, Excel files can be garbled, as follows:
We can re-encode 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";
MySQL Imports data into Excel