Data in MySQL exported as Excel file statement

The code is as follows	Copy Code
Mysql>select * from xi_table into outfile ' D:test.xls ';

Export to TXT file:

The code is as follows	Copy Code
SELECT * from xi_table into outfile ' d:test.txt ';

By default, the tab is used to divide fields, and records use N segmentation. You can customize three parameters.

The code is as follows	Copy Code
SELECT * Fromhx_9enjoy into outfile '/tmp/9enjoy_hx.txt ' FIELDS terminated by ', ' optionallyenclosed by ' "' LINES Terminat ED by ' n ';

Use, split fields, double quotes to qualify field contents, N Split records.

You can also specify record output for certain conditions:

The code is as follows	Copy Code
SELECT * Fromhx_9enjoy WHERE ID < into outfile '/tmp/9enjoy_100.txt '

If there are garbled characters in Excel when you open it we can export the set encoding for gb2312 to use Iconv for encoding conversion on the server side:

The code is as follows	Copy Code
Iconv-futf8-tgb2312-otest2.xls Test.xls

if Linux may encounter MySQL into outfile error:can ' t create/write to file error, this problem is the issue of permissions

Solutions

The code is as follows	Copy Code
sudo vi/etc/apparmor.d/usr.sbin.mysqld

Add the/data writable directory inside, as follows:

The code is as follows	Copy Code
... /usr/sbin/mysqld { ... /VAR/LOG/MYSQL/R, /var/log/mysql/* RW, /var/run/mysqld/mysqld.pid W, /var/run/mysqld/mysqld.sock W, /data/* RW, }

Then reload:

The code is as follows	Copy Code
Sudo/etc/init.d/apparmor Reload/restart

You also need to restart MySQL, or there will be some inexplicable errors, such as:
ERROR 1286 (42000) at line 1:unknown table engine ' InnoDB '

Restart MySQL:

code is as follows	copy code
sudo Service MySQL stop  sudo service MySQL start