How to import data into MySQL?

MySQL method of importing data: 1. Use the "mysql -u username -p password < database data file to be imported" command to directly import data; 2. First log in to the database terminal, and then use "source Use the "Data File Path" command to import data; 3. Use the "LOAD DATA INFILE" statement to import data.

#How to import data into MySQL? The following article will introduce you to several simple MySQL data import commands. I hope it will be helpful to you.

1. Mysql command import

Use the mysql command to import the syntax format:

mysql -u用户名 -p密码 < 要导入的数据库数据文件

Example:

# mysql -uroot -p123456 < test.sql

The above command will backup The entire database test.sql is imported.

2. Source command to import

source command to import the database. You need to log in to the database terminal first, and then use the following command:

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

3. Use LOAD DATA to import Data

MySQL provides the LOAD DATA INFILE statement to insert data. In the following example, the file dump.txt will be read from the current directory and the data in the file will be inserted into the mytbl table of the current database.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

If the LOCAL keyword is specified, it indicates that the file is read from the client host according to the path. If not specified, the file is read by path on the server.

You can explicitly specify column value delimiters and end-of-line markers in the LOAD DATA statement, but the default markers are positioners and newlines.

The syntax of the FIELDS and LINES clauses of the two commands is the same. Both clauses are optional, but if both are specified, the FIELDS clause must appear before the LINES clause.

If the user specifies a FIELDS clause, its clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY and ESCAPED BY) are also optional, however, the user must at least Specify one of them.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA By default, data is inserted in the order of columns in the data file. If the columns in the data file are inconsistent with the columns inserted into the table, you need to specify the order of the columns.

For example, the column order in the data file is a, b, c, but the column order in the inserted table is b, c, a, then the data import syntax is as follows:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

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