Importing Data into MySQL

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

mysql -u your_username -p -h your_host -P your_port -D your_database

1. Import using the mysql command

The syntax for importing using the mysql command is:

mysql -u your_username -p -h your_host -P your_port -D your_database

your_username, your_host, your_port, and your_database are your MySQL username, host, port, and database respectively.

Example:

# mysql -uroot -p123456 < example.sql

The above command will import the entire backed-up database example.sql.

After executing the above command, the system will ask for the MySQL user's password. Enter the password and press Enter.

In this way, MySQL will execute the statements in the SQL file and import the data into the specified database.

Please note that if the SQL file contains statements to create a database, make sure the database already exists before performing the import. If the file contains statements to create tables, make sure the tables do not exist or are empty, to avoid conflicts when importing data.

2. Import using the source command

To import a database using the source command, you need to first log in to the database terminal:

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

The advantage of using the source command is that you can execute it directly in the MySQL command line without having to exit MySQL and use other commands.

3. Import data using LOAD DATA

MySQL provides the LOAD DATA INFILE statement to insert data. The following example reads the file dump.txt from the current directory and inserts the data from that file 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 by path from the client host. If it is not specified, the file is read by path on the server.

You can explicitly specify the delimiter for column values and the end-of-line marker in the LOAD DATA statement, but the default markers are the tab character and the newline.

The syntax of the FIELDS and LINES clauses is the same in both commands. 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. However, 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';

By default, LOAD DATA inserts data according to the column order in the data file. If the columns in the data file do not match the columns in the table being inserted into, you need to specify the column order.

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

4. Import data using mysqlimport

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

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 column order:

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

Common mysqlimport options

Option	Function
-d or --delete	Delete all information in the data table before importing new data into it.
-f or --force	Regardless of whether errors are encountered, mysqlimport will force-continue inserting data.
-i or --ignore	mysqlimport skips or ignores rows that have the same unique key; data in the imported file will be ignored.
-l or -lock-tables	Locks the table before data is inserted, thus preventing users' queries and updates from being affected while you are updating the database.
-r or -replace	This option has the opposite function of the -i option; it will replace records in the table that have the same unique key.
--fields-enclosed- by= char	Specifies what encloses the data records in the text file. In many cases, data is enclosed in double quotes. By default, data is not enclosed by any character.
--fields-terminated- by=char	Specifies the delimiter between data values. In period-delimited files, the delimiter is a period. You can use this option to specify the delimiter between data. The default delimiter is the tab character (Tab).
--lines-terminated- by=str	This option specifies the delimiter string or character between lines of data in the text file. By default, mysqlimport uses newline as the line delimiter. You can choose to use a string instead of a single character: a newline or a carriage return.

Other commonly used options of the mysqlimport command include -v to display the version, -p to prompt for the password, and so on.

Other extensions

MySQL Programming Language