Shulou(Shulou.com)06/01 Report--

This article will explain in detail the method of exporting a table by mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Export method: through the "SELECTI...INTO OUTFILE" statement, use the "SELECT column name FROM table [WHERE statement] INTO OUTFILE 'target file' [OPTIONS]" format statement to export the contents of the table into a text file.

Through the import and export of data tables, we can move data between MySQL database server and other database servers. Export refers to copying data from an MySQL data table to a text file. There are many ways to export data, and this section focuses on exporting data using SELECTI...INTO OUTFILE statements.

In MySQL, you can use the SELECTI...INTO OUTFILE statement to export the contents of a table to a text file. The basic format of the SELECT...INTO OUTFILE statement is as follows:

SELECT column name FROM table [WHERE statement] INTO OUTFILE 'target file' [OPTIONS]

This statement uses SELECT to query the required data and INTO OUTFILE to export the data. Where the target file is used to specify the file to which the query's records are exported. It is important to note here that the target file cannot be an existing file.

[OPTIONS] is an optional parameter. The syntax of the OPTIONS section includes FIELDS and LINES clauses. Common values are:

FIELDS TERMINATED BY 'string': sets the string to be a delimiter between fields, which can be one or more characters, and the tab'\ t' by default.

FIELDS [OPTIONALLY] ENCLOSED BY 'character': sets the character to include character fields such as CHAR, VARCHAR, and TEXT. If OPTIONALLY is used, it can only be used to include character fields such as CHAR and VARCHAR.

FIELDS ESCAPED BY 'character': sets how to write or read special characters, only for a single character, that is, set escape characters. The default value is'\'.

LINES STARTING BY 'string': sets the character at the beginning of each line, which can be single or multiple characters, and no characters are used by default.

LINES TERMINATED BY 'string': sets the character at the end of each line, which can be single or multiple characters. The default value is'\ n'.

Note: both the FIELDS and LINES clauses are optional, but if both are specified, FIELDS must precede LINES.

Example 1

Let's use the SELECT...INTO OUTFILE statement to export the records in the person table in the test database. The SQL statement and the run result are as follows:

Mysql > SELECT * FROM test.person INTO OUTFILE'Uploads 5.7Universe Uploads sec person.txtupload scarf query OK, 5 rows affected (0.05 sec)

Then find the person.txt file according to the exported path, and the contents of the file are as follows:

1 Java 122 MySQL 133 C 154 C++ 225 Python 18

The person table data was successfully exported.

Note: the following errors may occur when exporting:

The MySQL server is running with the-secure-file-priv option so it cannot execute this statement

This is because MySQL restricts the export path of the data. MySQL import and export files can only be imported and exported from files under the specified path of the secure-file-priv variable.

There are two solutions:

1) first use the show variables like'% secure%'; statement to view the secure-file-priv variable configuration.

Mysql > show variables like'% secure%'\ gateway * 1. Row * * Variable_name: require_secure_transport Value: OFF** 2. Row * * Variable_name: secure_auth Value: ON** 3. Row * * Variable_name: secure_file_priv Value: C:\ ProgramData\ MySQL\ MySQL Server 5.7\ Uploads\ 3 rows in set 1 warning (0.04 sec)

The value of secure_file_priv specifies the path to the MySQL import and export file. Change the path of the export file in the SQL statement to the specified path of the variable, and then perform the import and export operation. You can also modify the value of secure-file-priv in the my.ini configuration file, and then restart the service.

2) if the secure_file_priv value is NULL, export is prohibited. You can add the secure_file_priv= setting path statement to the my.ini file under the MySQL installation path, and then restart the service.

Example 2

Use the SELECT...INTO OUTFILE statement to export the records from the person table in the test database to a text file, use the FIELDS option and the LINES option, require fields to be separated, and character data enclosed in double quotation marks. Each record begins with -. The SQL statement is as follows:

SELECT * FROM test.person INTO OUTFILE 'Cvu FIELDS TERMINATED BY'\, 'OPTIONALLY ENCLOSED BY'\ "'LINES STARTING BY'\-'TERMINATED BY'\ r\ n'

Where:

FIELDS TERMINATED BY',': indicates that fields are separated by,

ENCLOSED BY'\ ": indicates that each field is enclosed in double quotation marks

LINES STARTING BY'\ -': indicates that each line begins with-

TERMINATED BY'\ r\ n 'means that each line ends with a carriage return newline character, ensuring that each record is on one line.

The person.txt file is as follows:

-1, "Java", 12-2, "MySQL", 13-3, "C", 15-4, "C++", 22-5, "Python", 18

As you can see, each record begins with -, each data is separated by, and all field values are enclosed in double quotes.

This is the end of the method of exporting a table by mysql. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.