Select INTO outfile export data SQL statement

MySQL management data can certainly be very convenient to export data, but when the amount of data is very large, phpmyadmin is likely to occur execution timeout, in fact, it is easy to use SELECT INTO OutFile solution!

SELECT ... The main function of the into OUTFILE statement is that you can quickly dump a table onto the server machine. If you want to create a result file on a partial client host other than the server host, you cannot use the Select ... into outfile. In this case, you should use the command on the client host, such as "Mysql–e" SELECT ... "> file_name" to generate the file.

SELECT ... Into outfile is the complement of the load data infile; The syntax for the exort_options part of the statement includes partial fields and lines clauses that are used concurrently with the load data infile statement

is as follows: (Export the Wp_posts table in the database WordPress to D disk)

The code is as follows	Copy Code
SELECT * into outfile ' d:data.txt ' from wp_posts

It is important to note that using the SELECT INTO OutFile statement, you must have execute permissions, the path must exist, or the current database directory will be used.

Example 2

The code is as follows

Copy Code

Table structure and test data CREATE TABLE ' tab ' ( ' ID ' INT (1) Not NULL auto_increment PRIMARY KEY, ' Name ' VARCHAR not NULL, ' Add ' VARCHAR CHARACTER SET GBK not NULL ) Engine=innodb DEFAULT Charset=utf8; INSERT into ' tab ' (' Name ', ' Add ') VALUES (' Chinese ', ' this column will not be garbled '), (' Chinese characters ', ' this column will not be garbled '); SELECT * from ' tab '; +----+------+--------------+ | ID | name | Add | +----+------+--------------+ | 1 | Chinese | This column will not be garbled | | 2 | Chinese Characters | This column will not be garbled | +----+------+--------------+ Rows in Set (0.00 sec)

Code

The code is as follows	Copy Code
Using variables in into outfile SET @tmp_sql = CONCAT ( "SELECT * into OutFile ' C:\\sql_", Date_format (Now (), '%y%m%d%h%i%s '), ". txt ' FIELDS terminated by ', ' optionally enclosed by" ' LINES terminated by ' RN ' from ' tab '; PREPARE S1 from @tmp_sql; EXECUTE S1; DROP PREPARE S1;

The above code generates a "Sql_ current time. txt" file under C disk to avoid errors in the into outfile file

Note: Directly on the command line, select Display is not garbled, but use into outfile export has garbled, at this time must modify the display of garbled column character set