Data export method of MySQL

Several methods of MySQL data export

Some questions from the web are about how to export data from MySQL for use on a local or other database system, and to import existing data into a MySQL database. Data export There are several ways to export data: Using the SELECT INTO outfile "filename" statement Using the Mysqldump utility Using the SELECT INTO outfile "filename" statement It can be executed either under MySQL's command line or in a PHP program. Let me take the following example under the MySQL command line. When used in PHP, it is changed to the corresponding query for processing. However, when using this command, the user is required to have file permissions. If we have a library for phptest, there is a table for driver. Now you have to unload the driver into a file. Execute command: mysql> use phptest; Database Changed Mysql> SELECT * from driver to outfile "A.txt"; Query OK, rows affected (0.05 sec) The above can be done to unload the table driver from the database into the A.txt file. Note file names are quoted in single quotes. So where is this file? There is a data directory under the MySQL directory, which is where the database files are placed. Each library is in a separate subdirectory, so the Phptest directory is c:\mysql\data\phptest (note: My MySQL is installed under C:\mysql). OK, now we go in, a.txt is it. Open this file, which might be: 1 Mika Hakinnen 1 2 David Coulthard 1 3 Michael Schumacher 2 4 Rubens Barrichello 2 ... There may be a lot of records. Each field is separated by a tab (\ t). Then we can modify the directory of the output file name so that it is placed in the specified location. such as "A.txt" can be changed to "./a.txt" or "/a.txt". where "./a.txt" was placed under the C:\mysql\data directory, The "/a.txt" file is placed in the C: \ directory. So the Select command considers the current directory to be the storage directory for the database, which is C:\mysql\data. You can also use the Select command to specify delimited characters, escape characters, including characters, and record line separator characters between fields when unloading a file. are listed below: Fields TERMINATED by "\ T" [Optionally] Enclosed by "" escaped by "\ \" LINES TERMINATED by "\ n" TERMINATED indicates field separation [Optionally] Enclosed indicates what characters are included in the field, and if optionally is used, only char and Verchar are included escaped indicate what to use as escape characters when escaping, lines TERMINATED means what separates each row of records The default values are listed above, and the entries are optional, and the default values are used if not selected. Modifications can be made as needed. Give an example as follows: Mysql> SELECT * from driver to outfile "a.txt" Fields terminated by "," enclosed by ""; Query OK, rows affected (0.06 sec) The results may be as follows: "1", "Mika", "Hakinnen", "1" "2", "David", "Coulthard", "1" "3", "Michael", "Schumacher", "2" "4", "Rubens", "Barrichello", "2" ... You can see that each field is delimited with ",", and each field is included with "". Note that the row record delimiter can be a string, so you should test it yourself. However, if the output file in the specified directory if there is an error, first remove the test. Using the Mysqldump utility As you can see from the Select method above, the output file has only data, not a table structure. Also, only one table can be processed at a time, and it is not easy to process multiple tables. However, you can write the Select command to a SQL file (it should be easy to copy the text) and then execute it at the command line: MySQL library name Let's start with the simplest one: Mysqldump phptest > A.sql The possible results are as follows: # MySQL Dump 7.1 # # Host:localhost Database:phptest #-------------------------------------------------------- # Server Version 3.22.32-shareware-debug # # Table structure for table "Driver" # CREATE TABLE Driver ( drv_id Int (one) DEFAULT "0" not NULL auto_increment, Drv_forename varchar (page) DEFAULT "" Not NULL, Drv_surname varchar (+) DEFAULT "" Not NULL, Drv_team Int (one) DEFAULT "0" not NULL, PRIMARY KEY (drv_id) ); # # Dumping data for table "Driver" # INSERT into Driver VALUES (1, "Mika", "Hakinnen", 1); INSERT into Driver VALUES (2, "David", "Coulthard", 1); INSERT into Driver VALUES (3, "Michael", "Schumacher", 2); INSERT into Driver VALUES (4, "Rubens", "Barrichello", 2); ... If there are multiple tables, they are listed below. Can see this file is a full SQL file, if you want to import it into the other database can be through the command line way, very convenient: MySQL phptest < a.sql. If you upload data locally to the server, you can upload the file and then mount the data on the server through the command line. If you only want to unload the build instruction, the command is as follows: mysqldump-d phptest > A.sql If you only want to unload the SQL command that inserts the data, and you do not need to create a table command, the command is as follows: Mysqldump-t phptest > A.sql So what should I do if I only want the data and I don't want any SQL commands? mysqldump-t./Phptest Driver Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, which is the Mysqldump the same directory. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions. For the unloaded data file, you can also specify the field delimiter, including the character, Escape field, and row record delimiter, as with the Select method. The parameters are listed below: --fields-terminated-by= Field Delimiter --fields-enclosed-by= field include character --fields-optionally-enclosed-by= field include character, only on Char and Verchar fields --fields-escaped-by= Escape character --lines-terminated-by= Line Record delimiter I think we should understand the meaning of these parameters. An example is as follows: mysqldump-t./--fields-terminated-by=,--fields-enclosed-by=\ "Phptest driver The output is: "1", "Mika", "Hakinnen", "1" "2", "David", "Coulthard", "1" "3", "Michael", "Schumacher", "2" "4", "Rubens", "Barrichello", "2" ... Please note the use of characters. Summary The above is a way to unload text using the Select and Mysqldump utilities. The select is suitable for processing by the program, while the mysqldump is manual, provides powerful export capabilities, and can handle the entire library, or multiple tables specified in the library. You can use it at your own discretion and on demand. There are also some methods, such as direct database file copy can be, but the mobile database system and the original system should be consistent. There is no longer a mention here. Import Similar to exporting, there are two ways to import: Using the load DATA INFILE "filename" command Using the Mysqlimport utility Using SQL files Since the first two processing is similar to the export process, only their inverse operation, so only a few examples of the use of commands, no longer explain, you can consult the manual. Using the load command: Load data infile "Driver.txt" into the table driver fields terminated by "," enclosed by ""; Using the Mysqlimport utility: Mysqlimport--fields-terminated-by=,--fields-enclosed-by=\ "Phptest driver.txt For the third type, you can use the SQL file exported by mysqldump to execute the MySQL library name at the command line Exclusive from: http://hi.baidu.com/breezedancer/blog/item/3020c21b4da2d8faae5133e0.html

Data export method of MySQL