How to import and export data in MySQL?

MySQL is a commonly used database management system that allows users to store and access large amounts of data. In practical applications, we often need to import data into a MySQL database or export data from the database. This article will introduce how to use MySQL to import and export data, and provide corresponding code examples.

1. Data import

Data import is the process of importing data from external files or other databases into the MySQL database. In MySQL, there are many ways to import data. Two common methods will be introduced below: using the LOAD DATA INFILE statement and using the mysqlimport command.

1. Use the LOAD DATA INFILE statement

The LOAD DATA INFILE statement can import data from a text file into a table in the MySQL database. The specific usage is as follows:

LOAD DATA INFILE '文件路径'
INTO TABLE 表名
[字段分隔符]
[行分隔符];

Among them, 'file path' refers to the path of the text file to be imported; the table name is the target table name to which the data is to be imported; the field separator and row separator are optional, and the default is are tab and newline characters.

The following is an example to import data from the file C:/data.txt into a table named users, using commas as field separators:

LOAD DATA INFILE 'C:/data.txt'
INTO TABLE users
FIELDS TERMINATED BY ',';

2. Use mysqlimport Command

mysqlimport is a command line tool provided by MySQL that can import data from external files into the MySQL database. The specific usage is as follows:

mysqlimport [选项] 数据库名 表名 文件路径

The following is an example to import the data in the file C:/data.txt into a table named users:

mysqlimport --local --fields-terminated-by=, testdb users C:/data.txt

Among them, the --local option means Use a local file to import, --fields-terminated-by=, specify the field separator to be a comma.

2. Data export

Data export is the process of exporting data in the MySQL database to external files or other databases. In MySQL, there are many ways to export data. Two common ways will be introduced below: using the SELECT INTO OUTFILE statement and using the mysqldump command.

1. Use the SELECT INTO OUTFILE statement

The SELECT INTO OUTFILE statement can export the query results to a text file. The specific usage is as follows:

SELECT 列名
INTO OUTFILE '文件路径'
FROM 表名
WHERE 条件;

The following is an example to export the data in the table named users to the file C:/data.txt:

SELECT *
INTO OUTFILE 'C:/data.txt'
FROM users;

2. Use the mysqldump command

mysqldump is a command line tool provided by MySQL that can export the data and structure of the entire database or a specific table. The specific usage is as follows:

mysqldump [选项] 数据库名 [表名] > 文件路径

The following is an example to export the table named users in the database named testdb to the file C:/data.txt:

mysqldump -u root -p testdb users > C:/data.txt

Among them, - The u option specifies the username, and the -p option indicates that a password is required.

Summary:

This article introduces how to import and export data in MySQL. For data import, you can use the LOAD DATA INFILE statement or the mysqlimport command; for data export, you can use the SELECT INTO OUTFILE statement or the mysqldump command. Through these methods, we can easily import or export data to the MySQL database.

The above is the detailed content of How to import and export data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!