Now MySQL is used more and more, and I also use it to make my own message board. In the process of use, it is necessary to gradually master its management functions, not only me, but also the requirements of many netizens. Now there are some questions about how to export data from MySQL to be used locally or on other database systems; and how to import existing data into MySQL database is now using MySQL more and more, I also used it as my own message board. In the process of use, it is necessary to gradually master its management functions, not only me, but also the requirements of many netizens. There are some questions about how to export data from MySQL for use locally or on other database systems, and how to import existing data into MySQL databases. Now, let me summarize these two questions. the content is not very detailed. In fact, the MySQL manual is very detailed, but I have extracted these two aspects and added a little understanding of myself. For more details, see the relevant chapters 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 it to the corresponding query for processing. However, when using this command, you must have the file permission. For example, we have a database named phptest, and 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. So where is this file? 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 ). Well, now we are going in. a.txt is it. 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 ). Then we can modify the Directory of the output file name to put it in the specified location. For example, "a.txt" can be changed 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 line delimiter. 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 included with ESCAPED, which indicates what is used as the escape character when escaping. lines terminated indicates what is used to separate records in each row.
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 as follows:
Mysql> select * from driver into outfile "a.txt" fields terminated by "," enclosed """;
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"
...
We can see that each field is separated with "," and each field is included. Note that the line record delimiter can be a string. Please test it yourself. However, if the output file exists in the specified directory, an error is reported. delete the file before testing.
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. Moreover, it is not easy to process only one table at a time. However, you can write the select command into an SQL file (it should be easy to copy the text), and then execute the command line: mysql database name is 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. You can see that this file is a complete SQL file. if you want to import it to other databases, you can use the command line method to conveniently: mysql phptest <a. SQL. If you upload the data from the local server to the server, you can upload the file and load the data on the server using the command line method.
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 a. 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 is manual. It also provides powerful export functions and can process the entire database or multiple tables specified in the database. You can use it as needed.
There are also some methods, such as copying database files directly, but the database system after moving should be consistent with the original system. I will not mention it here.
Import
Similar to export, there are two ways to import data:
Use the load data infile "filename" command
Use the mysqlimport utility
Use SQL files
Since the first two processes are similar to the export operations, they are only inverse operations. Therefore, we only provide examples for using several commands and do not explain them. you can refer to the Manual on your own.
Use the load command:
Load data infile "driver.txt" into table driver fields terminated by "," enclosed """;
Use 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 database name in the command line.
The article is not detailed. I hope you can refer to the manual.
Reprinted: PHP Technical Network
