Sometimes you need to export data from MySQL database to an external storage file. Data in MySQL database can be exported to sql text file, xml file or html file. This section describes the common methods of data export.
11.4.1 Exporting a text file using SELECT...INTO OUTFILE
When exporting data from a MySQL database, you are allowed to use a SELECT statement containing an export definition for the data export operation. The file is created on the server host, so you must have file write permission (FILE permission) to use this syntax. "SELECT... .INTO OUTFILE 'filename'" form of the SELECT statement writes the selected rows to a file, and filename cannot be a pre-existing file.SELECT.... The basic format of the SELECT...INTO OUTFILE statement is as follows:
SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]
You can see SELECT columnlist FROM table WHERE condition for a query statement, the query results to return to meet the specified conditions of one or more records; INTO OUTFILE statement is the role of the previous SELECT statement to query the results of the export to the name of " filename" in the external file; [OPTIONS] for the optional parameter options, OPTIONS part of the syntax, including FIELDS and LINES clauses, the possible values are as follows:
FIELDS TERMINATED BY 'value': sets the separator character between fields, which can be a single or multiple characters, and by default is a tab (\t).
FIELDS [OPTIONALLY] ENCLOSED BY 'value': set the enclosing character of the field, it can only be a single character, if the OPTIONALLY keyword is used, only character data fields such as CHAR and VERCHAR are enclosed.
FIELDS ESCAPED BY 'value': set how to write or read special characters, can only be a single character, that is, set the escape character, the default value is "\".
LINES STARTING BY 'value': set the character at the beginning of each line of data, can be single or multiple characters, by default no character is used.
LINES TERMINATED BY 'value': set the character at the end of each line of data, can be a single or multiple characters, the default value is "\n".
Both the FIELDS and LINES clauses are self-selecting, but if both are specified, FIELDS must precede LINES.
Using the SELECT.... ...INTO OUTFILE statement to dump a table to the server very quickly. If you want to create a result file on some client host other than the server host, you cannot use the SELECT... INTO OUTFILE statement. . INTO OUTFILE, you should use the "MySQL -e "SELECT ..." MySQL -e "SELECT ..." > file_name" should be used to generate the file.
SELECT... .INTO OUTFILE is the complement of LOAD DATA INFILE, and the syntax used for the OPTIONS portion of the statement includes some of the FIELDS and LINES clauses that are used in conjunction with the LOAD DATA INFILE statement.
[Example 11.10] Use SELECT.... ...INTO OUTFILE to export records from the person table in the test_db database to a text file, the SQL statement is as follows:
mysql> SELECT * FROM test_db.person INTO OUTFILE 'D:/';
The following error message is reported after the statement is executed:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
This is because MySQL has permission restrictions on the exported directory by default, which means that you need to specify the directory when you use the command line to do the export. So what is the specified directory?
The command to query the specified directory is as follows:
show global variables like '%secure%';
The results of the implementation are as follows:
+-------------------------+-----------------------------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------------------------+
|require_secure_transport | OFF |
|secure_file_priv | D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\|
+-------------------------+-----------------------------------------------+
Because of the secure_file_priv configuration, it must be exported to the D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\ directory, which is the specified directory. If you want to customize the export path, you need to modify the configuration file. Open the path D:\ProgramData\MySQL\MySQL Server 9.0, open the file with Notepad and search for the following code:
secure-file-priv="D:/ProgramData/MySQL/MySQL Server 9.0/Uploads"
Add # comment out before the above code and add the following:
secure-file-priv="D:/"
The results are shown in Figure 11.1.
Figure 11.1 Setting the export path of a data table
After restarting the MySQL server, use SELECT... again. ...INTO OUTFILE to export the records from the person table in the test_db database to a text file with the following SQL statement:
mysql>SELECT * FROM test_db.person INTO OUTFILE 'D:/'; Query OK, 1 row affected (0.01 sec)
Since the INTO OUTFILE clause is specified, SELECT saves the values of the three fields queried to the C:\ file. Open the file with the following contents:
1 Green 21 Lawyer 2 Suse 22 dancer 3 Mary 24 Musician 4 Willam 20 sports man 5 Laura 25 \N 6 Evans 27 secretary 7 Dale 22 cook 8 Edison 28 singer 9 Harry 21 magician 10 Harriet 19 pianist
By default, MySQL uses tabs (\t) to separate different fields that are not surrounded by other characters. By default, when NULL is encountered, "\N" is returned, which represents the null value, where the backslash (\) represents the escape character; if you use the By default, when NULL is encountered, "\N" is returned, representing the null value, where the backslash (\) represents the escape character; if the ESCAPED BY option is used, N is preceded by the specified escape character.
[Example 11.11] Use the SELECT.... .INTO OUTFILE statement to export the records in the test_db database person table to a text file, using the FIELDS option and the LINES option, requiring that fields be separated by commas, that all field values be enclosed in double quotes, and defining the escape character as a single quote "\'". The SQL statement is as follows:
SELECT * FROM test_db.person INTO OUTFILE "D:/" FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\'' LINES TERMINATED BY '\r\n';
This statement will import all records from the person table into a text file in the D drive directory.
"FIELDS TERMINATED BY ','" means that fields are separated by commas; "ENCLOSED BY '\"'" means that each field is caused by double quotes;" ESCAPED BY '\'" means that the system's default escape character is replaced by single quotes; "LINES TERMINATED BY '\r\n'" means that each line is terminated by a carriage return line feed to ensure that each record occupies one line.
After successful execution, a file is generated under the D drive. Open the file with the following contents:
"1","Green","21","Lawyer" "2","Suse","22","dancer" "3","Mary","24","Musician" "4","Willam","20","sports man" "5","Laura","25",'N' "6","Evans","27","secretary" "7","Dale","22","cook" "8","Edison","28","singer" "9","Harry","21","magician" "10","Harriet","19","pianist"
As you can see, all field values are induced by double quotes; the null value in the fifth record is represented as "N", i.e., single quotes are used to replace the backslash escape character.
Example 11.12] Use the SELECT.... .INTO OUTFILE statement to export the records in the test_db database person table to a text file, using the LINES option, requiring that each row of records begin with the string ">" and end with the string "<end>", the SQL statement is as follows. ", the SQL statement is as follows:
SELECT * FROM test_db.person INTO OUTFILE "D:/" LINES STARTING BY '> ' TERMINATED BY '<end>';
After the statement is executed successfully, a file is generated under the D disk. Open the file with the following contents:
> 1 Green 21 Lawyer <end>> 2 Suse 22 dancer <end>> 3 Mary 24 Musician <end>> 4 Willam 20 sports man <end>> 5 Laura 25 \N <end>> 6 Evans 27 secretary <end>> 7 Dale 22 cook <end>> 8 Edison 28 singer <end>> 9 Harry 21 magician <end>> 10 Harriet 19 pianist <end>
You can see that although all the field values are exported to a text file, all the records do not have a line break, which occurs because the TERMINATED BY option replaces the system's default line breaks. If you want to display line breaks, you need to modify the export statement:
SELECT * FROM test_db.person INTO OUTFILE "D:/" LINES STARTING BY '> ' TERMINATED BY '<end>\r\n';
After executing the statement, the line feed displays each record with the following result:
> 1 Green 21 Lawyer <end> > 2 Suse 22 dancer <end> > 3 Mary 24 Musician <end> > 4 Willam 20 sports man <end> > 5 Laura 25 \N <end> > 6 Evans 27 secretary <end> > 7 Dale 22 cook <end> > 8 Edison 28 singer <end> > 9 Harry 21 magician <end> > 10 Harriet 19 pianist <end>
11.4.2 Exporting a text file using the mysqldump command
In addition to exporting text files using the SELECT... INTO OUTFILE statement, you can also use the mysqldump command. section 11.1 begins with an introduction to backing up databases using mysqldump, which is a tool that can export data not only as a sql file containing CREATE and INSERT, but also as a plain text files.
mysqldump creates a file containing the CREATE TABLE statement that creates the table and a file containing its data. the basic syntax format for mysqldump to export a text file is as follows:
mysqldump -T path-u root -p dbname [tables] [OPTIONS]
Only specified -T parameter can export plain text files; path indicates the directory of the exported data; tables to specify the name of the table to be exported, if not specified, will be exported to the database dbname in all the tables; [OPTIONS] for the optional parameter options, these options need to be combined with the use of the -T option. OPTIONS common values are:
--fields-terminated-by=value: Sets the separator character between fields, which can be a single or multiple characters, and by default is a tab (\t).
--fields-enclosed-by=value: Sets the enclosing character of the field.
--fields-optionally-enclosed-by=value: Sets the enclosing character of the field, which can only be a single character and can only include character data fields such as CHAR and VERCHAR.
--fields-escaped-by=value: control how to write or read special characters, can only be a single character, i.e., set the escape character, the default value is \.
--lines-terminated-by=value: set the character at the end of each line, can be single or multiple characters, default value is "\n".
The OPTIONS here are set differently from the OPTIONS in the SELECT...INTO OUTFILE statement, and the value values following the middle sign of each value are not enclosed in quotation marks.
[Example 11.13] Use mysqldump to export the records in the person table of the test_db database to a text file with the following SQL statement:
mysqldump -T D:\ test_db person -u root -p
If the statement is executed successfully, there will be two files under the system D drive directory, respectively and. containing the CREATE statement that creates the person table, the contents of which are as follows:
-- MySQL dump 10.13 Distrib 9.0.1, for Win64 (x86_64) -- -- Host: localhost Database: test_db -- ------------------------------------------------------ -- Server version 9.0.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; SET NAMES utf8mb4 ; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `person` -- DROP TABLE IF EXISTS `person`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `person` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(40) NOT NULL DEFAULT '', `age` int(11) NOT NULL DEFAULT '0', `info` char(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-07-25 16:40:55
The information in the backup file is described in Section 11.1.1.
Contains the data in the packet, which is as follows:
1 Green 21 Lawyer 2 Suse 22 dancer 3 Mary 24 Musician 4 Willam 20 sports man 5 Laura 25 \N 6 Evans 27 secretary 7 Dale 22 cook 8 Edison 28 singer 9 Harry 21 magician 10 Harriet 19 pianist
[Example 11.14] Use the mysqldump command to export the records from the person table in the test_db database to a text file using the FIELDS option, which requires that fields be spaced using commas "," between them, that all character-type field values be induced with double quotes, and that the escape character be defined as "?" , each row of records ending with "\r\n", the SQL statement is as follows:
mysqldump -T D:\ test_db person -u root -p --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n Enter password: ******
The above statement should be entered in a line, after the successful execution of the statement, there will be two files under the system D disk directory, respectively, and. Containing the CREATE statement to create the person table, its content is the same as that in [Example 11.13]; the content of the file is different from that in [Example 11.13], which is displayed as follows:
1,"Green",21,"Lawyer" 2,"Suse",22,"dancer" 3,"Mary",24,"Musician" 4,"Willam",20,"sports man" 5,"Laura",25,?N 6,"Evans",27,"secretary" 7,"Dale",22,"cook" 8,"Edison",28,"singer" 9,"Harry",21,"magician" 10,"Harriet",19,"pianist"
As you can see, only the values of character types are induced by double quotes, while the values of numeric types are not; the NULL in the fifth row of records is indicated as "?N", using "?" instead of the default "\".
11.4.3 Exporting a text file using the mysql command
mysql is a feature-rich tool command, using them you can also execute SQL commands in command line mode and import query results into text files. Compared to mysqldump, the results exported by mysql tools are more readable.
If the MySQL server is a separate machine and the user is operating on a client, to import the data results to the client.
The basic syntax format for exporting a data text file using mysql is as follows:
mysql -u root -p --execute= "SELECT statement" dbname >
The command uses the --execute option, which means that the statement following the option is executed and exits, and the statement following it must be caused by double quotes; dbname is the name of the database to be exported; the different columns in the exported file are separated by tabs, and the first line contains the names of the fields.
[Example 11.15] Use the mysql command to export the records from the person table in the test_db database to a text file with the following SQL statement:
mysql -u root -p --execute="SELECT * FROM person;" test_db > D:\
After the statement is executed, there will be a text file named " " in the directory of system D drive with the following contents:
id name age info 1 Green 21 Lawyer 2 Suse 22 dancer 3 Mary 24 Musician 4 Willam 20 sports man 5 Laura 25 NULL 6 Evans 27 secretary 7 Dale 22 cook 8 Edison 28 singer 9 Harry 21 magician 10 Harriet 19 pianist
As you can see, the file contains the name of each field and each record, and the display format is the same as that of the results of a SELECT query at the MySQL command line.
Use mysql commands can also specify the display format of the query results, if a record has many fields, a line may not be fully displayed, you can use the --vartical parameter will be divided into multiple lines to display each record.
[Example 11.16] Use the mysql command to export the records from the person table in the test_db database to a text file, and use the --vertical parameter to display the results with the following SQL statement:
mysql -u root -p --vertical --execute="SELECT * FROM person;" test_db > D:\
After the statement is executed, the contents of the D:\ file are as follows:
*** 1. row *** id: 1 name: Green age: 21 info: Lawyer *** 2. row *** id: 2 name: Suse age: 22 info: dancer *** 3. row *** id: 3 name: Mary age: 24 info: Musician *** 4. row *** id: 4 name: Willam age: 20 info: sports man *** 5. row *** id: 5 name: Laura age: 25 info: NULL *** 6. row *** id: 6 name: Evans age: 27 info: secretary *** 7. row *** id: 7 name: Dale age: 22 info: cook *** 8. row *** id: 8 name: Edison age: 28 info: singer *** 9. row *** id: 9 name: Harry age: 21 info: magician *** 10. row *** id: 10 name: Harriet age: 19 info: pianist
As you can see, after the SELECT query results are exported to a text file, the display format changes, and if the records in the person table are very long, displaying them in this way will make them easier to read.
mysql can also export query results to an html file, using the --html option.
[Example 11.17] Use MySQL commands to export records from the person table in the test_db database to an html file with the following SQL statements:
mysql -u root -p --html --execute="SELECT * FROM person;" test_db > D:\
Successful execution of the statement will create a file on the D drive, which will be displayed in the browser as shown in Figure 11.2.
To export table data to an xml file, use the --xml option.
[Example 11.18] Use the mysql command to export records from the person table in the test_db database to an xml file with the following SQL statement:
mysql -u root -p --xml --execute="SELECT * FROM person;" test_db >D:\
Successful execution of the statement will create a file on the D drive, which will be displayed in the browser as shown in Figure 11.3.
