MySQL Import Data

In this chapter, we introduce several simple MySQL data import commands.

1、mysql 　command import

The syntax format for importing data using the mysql command is：

mysql　-uUsername　　　　-pPassword　　　　<　　To import the database data(w3codebox.sql)

Example：

#　mysql　-uroot　-p123456　<　w3codebox.sql

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

2、source 　command import

The source command to import the database requires logging into the database terminal first:

mysql>　create　database　abc;　　　　　　#　Create database
mysql>　use　abc;　　　　　　　　　　　　　　　　　　#　Use the created database　
mysql>　set　names　utf8;　　　　　　　　　　　#　Set encoding
mysql>　source　/home/abc/abc.sql　　#　Import backup database

3Using 　LOAD DATA to import data

MySQL provides the LOAD DATA INFILE statement to insert data. In the following example, the file dump.txt in the current directory will be read, and the data in the file will be inserted into the mytbl table in 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 by path. If not specified, the file is read from the server by path.

You can explicitly specify the column separator and line terminator in the LOAD DATA statement, but the default markers are the locator and newline characters.

The syntax of the FIELDS and LINES clauses in 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 subclauses (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 inserts data by default according to the order of columns in the data file. If the columns in the data file are not consistent with the columns in the insertion table, you need to specify the order of the columns.

For example, if the column order in the data file is a, b, c, but the column order for insertion into the 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);

4Using mysqlimport to import data

The mysqlimport client provides a command-line interface for the LOAD DATA INFILEQL statement. Most of the 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　*****

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

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

Introduction to commonly used options of mysqlimport

Common options for the mysqlimport command include -v Show version (version), -p Prompt for input password (password) and so on.