Summarize the example tutorial of exporting data from MySQL

MySQL Export Data

In MySQL you can use the SELECT...INTO OUTFILE statement to simply export data to a text file.

Use the SELECT ... INTO OUTFILE statement to export data

In the following example, we export the data of the data table tutorials_tbl to the /tmp/tutorials.txt file:

mysql> SELECT * FROM tutorials_tbl 
    -> INTO OUTFILE '/tmp/tutorials.txt';

You You can set the specified format of data output through command options. The following example is to export CSV format:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

In the following example, a file is generated, and each value is separated by commas. This format can be used by many programs.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT ... INTO OUTFILE statement has the following attributes:

LOAD DATA INFILE is the reverse operation of SELECT ... INTO OUTFILE, SELECT syntax. To write data from a database to a file, use SELECT ... INTO OUTFILE, and to read the file back into the database, use LOAD DATA INFILE.

SELECT...INTO OUTFILE 'file_name' form of SELECT can write the selected lines into a file. The file is created on the server host, so you must have FILE permissions to use this syntax.

The output cannot be an existing file. Prevent file data from being tampered with.

You need to have an account logged into the server to retrieve files. Otherwise SELECT ... INTO OUTFILE will have no effect.

In UNIX, the file is readable after it is created, and the permissions are owned by the MySQL server. This means that, although you will be able to read the file, you may not be able to delete it.

Export table as raw data

mysqldump is a utility used by mysql to transfer storage databases. It mainly generates a SQL script, which contains the necessary commands to create the database from scratch, such as CREATE TABLE INSERT, etc.

Using mysqldump to export data requires using the --tab option to specify the directory specified by the export file. The target must be writable.

The following example exports the data table tutorials_tbl to the /tmp directory:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp TUTORIALS tutorials_tbl
password ******

Export data in SQL format

Export data in SQL format to the specified file, as follows:

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

The content of the file created by the above command is as follows:

-- MySQL dump 8.23
--
-- Host: localhost    Database: TUTORIALS
---------------------------------------------------------
-- Server version       3.23.58
--
-- Table structure for table `tutorials_tbl`
--
CREATE TABLE tutorials_tbl (
  tutorial_id int(11) NOT NULL auto_increment,
  tutorial_title varchar(100) NOT NULL default '',
  tutorial_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (tutorial_id),
  UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE=MyISAM;
--
-- Dumping data for table `tutorials_tbl`
--
INSERT INTO tutorials_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tutorials_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tutorials_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

If you need to export the data of the entire database, you can use the following command:

$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ******

If you need to back up all databases, you can Use the following command:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

--all-databases option is added in MySQL 3.23.12 and later versions.

This method can be used to implement the database backup strategy.

Copy data tables and databases to other hosts

If you need to copy data to other MySQL servers, you can specify the database name and data table in the mysqldump command.

Execute the following command on the source host to back up the data to the dump.txt file:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

If you back up the database completely, there is no need to use a specific table name.

If you need to import the backed up database into the MySQL server, you can use the following command. You need to confirm that the database has been created using the following command:

$ mysql -u root -p database_name < dump.txt
password *****

You can also use the following command to export the The data is directly imported to the remote server, but please ensure that the two servers are connected and can access each other:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

The above command uses a pipeline to export the Data is imported to the specified remote host.

【related suggestion】

1. Special recommendation: "php Programmer Toolbox" V0.1 version download

2. Free mysql online video tutorial

3. Those things about database design

The above is the detailed content of Summarize the example tutorial of exporting data from MySQL. For more information, please follow other related articles on the PHP Chinese website!