MySQL data import and export

Now MySQL is used more and more, and I also use it to make my own message board. During use,
Slowly
We need to manage it.
The function requires a step-by-step grasp, not only me, but also the requirements of many netizens. Now there are some problems:
On how
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
Just me
This is the learning situation.
The two questions are summarized in detail. In fact, the MySQL manual is very detailed, but I
Related
Introduction to 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. In mysql command line
Example. In
In php
Change to the corresponding query for processing. However, when using this command, the user is required to have the file permission.
Limits. For example
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: add a ticket to the file name
Quotation marks.
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 is on a single
Exclusive 1
Sub-directories, so
The phptest directory is c: \ mysql \ data \ phptest (note: my mysql is installed under c: \ mysql ).
Okay, now
In our step, a.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 ). So we can repair
Change output
File name directory
In the specified position. For example, you can change 'a.txt 'to'./a.txt 'or'/a.txt '. Put './a.txt' in
C: \ my
SQL \ data Directory,
The '/a.txt' file is stored in the c: \ directory. Therefore, the select command considers the current directory as a database
Storage object
Here is
C: \ mysql \ data.
The select command can also specify the delimiter and escape character between fields when the file is detached,
Word inclusion
Delimiter and record line separator
. 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
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. You can
Required
Modify rows. An example is provided.
Sub-statement:
Mysql> select * from driver into outfile 'a.txt 'fields terminated
', 'En
Closed '"';
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
Yes, line
The record separator can be
One string, Please test it yourself. However, if the output file exists in the specified directory
Will be reported
Error. Delete and test again
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. And
, Once
Only one table can be processed.
It is not easy to process multiple tables. However, you can write the select command into an SQL file
This should
It's easy.)
Then run the command line: mysql database name format Mail
(That is, the content is SQL
Command), you can also extract plain data text or extract only SQL files containing the created table structure but no data
Same
Multiple
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. This file is a complete SQL file, as shown in figure
If you want
It is imported to other data
You can use the command line in the database to make it easy: mysql phptest <a. SQL. If you extract data from the local
Pass
On 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 the-T parameter can be specified to unload a plain text file, indicating the directory for unloading data ,./
When
And
Mysqldump the same directory. If no driver table is specified, the data of the entire database is detached. Each table
Will generate
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.
The field separator, including characters, can also be specified for the detached data file like the select method.
, Escape
Field, line record separator.
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 = \ "phptes
T driv
Er
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 applicable
Program
While mysqldump
It is a manual operation. It also provides powerful export functions and can process the entire library or
Multiple tables.
You can
Rows are used.
There are also some methods, such as copying database files directly, but the mobile database system
System and original
The system should be consistent. 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 only several types
To use
,
You can refer to the Manual on your own.
Use the load command:
Load data infile 'driver.txt 'into table driver fields terminated'
, 'Enc
Losed '"';
Use the mysqlimport utility:
Mysqlimport -- fields-terminated-by =, -- fields-enclosed-by = \ "phptest d
River.
Txt
The article is not detailed. I hope you can refer to the manual.
Winamp:
I faint, so troublesome. It is recommended that you use phpmyadmin data to import and export table structures conveniently.
Now!