How to import data into mysql

Mysql method of importing data: 1. Use the "mysql -u username -p password

1. Mysql command import

Use mysql command import syntax format:

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

Example:

# mysql -uroot -p123456 < student.sql

The above command will import the entire backup database student.sql.

2. Source command to import

source command to import the database. You need to log in to the database terminal first:

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

3. Use LOAD DATA Importing 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, but the user must specify at least 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 in the inserted 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);

4. Use mysqlimport to import data

The mysqlimport client provides a command line interface for the LOAD DATA INFILEQL statement. Most options of mysqlimport correspond directly to the LOAD DATA INFILE clause.

To import data from the file dump.txt into the mytbl data table, you can use the following command:

$ mysqlimport -u root -p --local mytbl dump.txt
password *****

The mysqlimport command can specify options to set the specified format. The command statement format is as follows:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

Use the --columns option in the mysqlimport statement to set the order of columns:

$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****

Recommended tutorial: mysql video tutorial

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!