Selectintooutfile: SQL statement for exporting data

The selectintooutfile command is a common command used in mysql to export data. next I will introduce the usage of selectintooutfile. if you need it, refer to it.

The select into outfile command is a common command used in mysql to export data. next I will introduce the usage of select into outfile. if you need it, refer to it.

Mysql can easily export data by managing data. However, when the data volume is large, phpmyadmin may encounter execution timeout. In fact, it is easy to use select into outfile!

The main function of the SELECT... into outfile statement is to allow you to quickly dump a table to a server machine. If you want to create a result file on a client host other than the server host, you cannot use SELECT... into outfile. In this case, you should use the command "mysql-e" SELECT... "> file_name" on the client host to generate a file.

SELECT... into outfile is the complement of load data infile. the syntax used for the exort_options part of the statement includes some FIELDS and LINES clauses, which are used together with the load data infile statement

As follows: (export the wp_posts table in the database wordpress to disk D)

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

Note: to use the select into outfile statement, you must have the execution permission and the path must exist. Otherwise, you must use the current database directory,

Example 2

The code is as follows:

Table structure and test data Create table 'tab '( 'Id' INT (1) not null AUTO_INCREMENT primary key, 'Name' VARCHAR (10) not null, 'Add' VARCHAR (20) character set gbk NOT NULL ) ENGINE = InnoDB default charset = utf8; Insert into 'tab' ('name', 'ADD') VALUES ('Chinese', 'This column won't be garbled '), ('Chinese characters', 'this column will not be garbled '); SELECT * FROM 'tab '; + ---- + ------ + -------------- + | Id | name | add | + ---- + ------ + -------------- + | 1 | Chinese | this column is not garbled | | 2 | Chinese characters | this column is not garbled | + ---- + ------ + -------------- + Rows in set (0.00 sec)

Code

The code is as follows:
Use 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 specified code will generate the “SQL _current time .txt" file under the C drive to avoid the INTO OUTFILE file error.

Note: No garbled characters are displayed in the SELECT command line directly, but garbled characters exist after being exported using into outfile. in this case, you must modify the character set of the columns with garbled characters.