Mysql Data Import and Export II

Author: limodou

 

Now MySQL is used more and more, and I also use it to make my own message board. In the process of use, we need to manage it slowly
The function requires a step-by-step grasp, not only me, but also the requirements of many netizens. There are some questions about how to export data from MySQL,
It can be used locally or in other database systems; and existing data can be imported into the MySQL database. Now I am learning this.
The two questions are summarized in detail. In fact, the MySQL manual is very detailed, but I will mention these two aspects.
After obtaining the information, add your own understanding. For more details, refer to the relevant chapter of the database.

Data Export
Data export can be performed in the following ways:

Use the select into OUTFILE 'filename' statement
Use the mysqldump Utility
Use the select into OUTFILE 'filename' statement

You can execute it in the MySQL command line or in the PHP program. The following uses the MySQL command line as an example. When used in PHP
Change to the corresponding query for processing. However, when using this command, you must have the file permission. If we have a database named
Phptest. One of the tables is driver. Now we need to unload the driver into a file. Run the following command:

Mysql> Use phptest;
Database changed
Mysql> select * from driver into OUTFILE 'a.txt ';
Query OK, 22 rows affected (0.05 Sec)

Then, the table driverts from the data warehouse to the.txt file. Note that the file name must be enclosed in single quotes. Where is the file?
What about it? There is a data directory under the MySQL Directory, which is the place where the database files are stored. Each database occupies a separate subdirectory, so
The phptest directory is C:/MySQL/data/phptest (Note: My MySQL is installed under C:/MySQL ). Now we are going to... txt
It is. Open this file, which may be:

1 Mika hakinnen 1
2 David Coulthard 1
3 Michael Schumacher 2
4 Rubens Barrichello 2
...

There may be many records. Each field is separated by a tab (/T ). You can modify the directory of the output file name
In the specified position. For example, you can change 'a.txt 'to'./a.txt 'or'/a.txt '. './A.txt' is stored in the C:/MySQL/data directory,
The '/a.txt' file is stored in the C:/directory. Therefore, the SELECT command considers the current directory as the database storage directory. Here is
C:/MySQL/data.

You can also use the SELECT command to specify the delimiter between fields and escape characters, including characters and record line separators, when the file is detached.
. Column:

Fields
Terminated by '/t'
[Optionally] enclosed''
Escaped '//'
Lines
Terminated by '/N'

Terminated indicates that fields are separated.
[Optionally] enclosed indicates the characters used to include a field. If optionally is used, only char and verchar are used.
Included
Escaped indicates what to use as escape characters when escaping is required
Lines terminated indicates the number of records separated by lines.
The default values listed above are optional. If this parameter is not selected, the default values are used. It can be modified as needed. An example is provided.
Sub-statement:
Mysql> select * from driver into OUTFILE 'a.txt 'fields terminated by', 'enabledby '"';
Query OK, 22 rows affected (0.06 Sec)

The result may be as follows:

"1", "Mika", "hakinnen", "1"
"2", "David", "Coulthard", "1"
"3", "Michael", "Schumacher", "2"
"4", "Rubens", "Barrichello", "2"
...

Each field is separated by commas (,), and each field is included by commas. Note that the row record delimiter can be
One String, please test it yourself. However, if the output file exists in the specified directory, an error is reported. delete the file before testing.
Try it.

Use the mysqldump Utility

From the preceding select method, we can see that the output file only contains data but does not have a table structure. Besides, only one table can be processed at a time.
It is not easy to process multiple tables. However, you can write the SELECT command into an SQL file (it should be easy to copy the text ).
Then run the command line: MySQL database name <A. SQL. However, mysqldump can be used to unload the SQL file format information (that is, the content is SQL
You can also extract plain data text or extract only SQL files containing the created table structure but no data.
Table operations are more flexible and powerful than the select method. We recommend that you use this method. The following is a detailed description.

Let's take a look at the simplest:

Mysqldump phptest> A. SQL

Possible results:
# MySQL dump 7.1
#
# HOST: localhost Database: phptest
#--------------------------------------------------------
# Server version 3.22.32-ware-Debug

#
# Table structure for table 'driver'
#
Create Table Driver (
Drv_id int (11) default '0' not null auto_increment,
Drv_forename varchar (15) default ''not null,
Drv_surname varchar (25) default ''not null,
Drv_team int (11) 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 multiple tables exist, they are listed below. It can be seen that this file is a complete SQL file, if you want to import it to other
You can use the command line in the database to make it easy: MySQL phptest <A. SQL. If you upload data from the local server to the server, you can
Upload the file and load the data on the server using the command line.

If you only want to unload the table creation command, the command is as follows:

Mysqldump-D phptest> A. SQL

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows:

Mysqldump-T phptest> A. SQL

What should I do if I only want data and do not want any SQL commands?

Mysqldump-T./phptest driver

Only when the-t parameter is specified can the plain text file be detached, indicating the directory where the data is detached, And./indicates the current directory, that is
Mysqldump the same directory. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is
. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.

You can specify the field separator, including characters, escape fields, and line record separators, in the same way as the select method.
The parameters are listed below:

-- Fields-terminated-by = field separator
-- Fields-enclosed-by = field inclusion character
-- Fields-optionally-enclosed-by = field inclusion character, only used in char and verchar Fields
-- Fields-escaped-by = Escape Character
-- Lines-terminated-by = line record Separator
I think you should understand what these parameters mean. An example is as follows:

Mysqldump-T./-- fields-terminated-by =, -- fields-enclosed-by =/"phptest driver

Output result:

"1", "Mika", "hakinnen", "1"
"2", "David", "Coulthard", "1"
"3", "Michael", "Schumacher", "2"
"4", "Rubens", "Barrichello", "2"
...

Note the usage of characters.

Summary
The preceding method uses the select and mysqldump utilities to unload text. Select is suitable for processing using programs, while mysqldump
It is a manual operation. It also provides powerful export functions and can process the entire database or multiple tables specified in the database. You can
Rows are used.

There are also some methods, such as copying database files directly, but the database system after moving should be consistent with the original system. This
.

Import
Similar to export, there are two ways to import data:
Use the load data infile 'filename' command
Use the mysqlimport Utility
Since these two processes are similar to the export parameters, they are only inverse operations, so we only give examples of how to use several commands,
You can refer to the manual on your own.

Use the load command:

Load data infile 'driver.txt 'into Table driver fields terminated by', 'enabledby '"';

Use the mysqlimport utility:

Mysqlimport -- fields-terminated-by =, -- fields-enclosed-by =/"phptest driver.txt

The article is not detailed. I hope you can refer to the manual.