MySQL Data Import
This section introduces several simple commands for importing data into MySQL.
1. Import using mysql command
The syntax for importing data using the mysql command is as follows:
mysql -uusername -ppassword < database_data_to_be_imported(tutorialpro.sql)
Example:
# mysql -uroot -p123456 < tutorialpro.sql
The above command imports the entire backup database tutorialpro.sql.
2. Import using source command
To import a database using the source command, you need to log in to 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
3. Import 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 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 by path on the server.
You can explicitly specify the column delimiter and line end marker in the LOAD DATA statement, but the default markers are tab and newline characters.
The syntax of the FIELDS and LINES clauses in both commands is the same. Both clauses are optional, but if both are specified, the FIELDS clause must appear before the LINES clause.
If a FIELDS clause is specified, its sub-clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) are also optional, but at least one must be specified.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';
LOAD DATA by default inserts data in the order of columns in the data file. If the columns in the data file do not match the columns in the table, 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, the data import syntax is as follows:
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
-> INTO TABLE mytbl (b, c, a);
4. Import using mysqlimport
The mysqlimport client provides a command-line interface for the LOAD DATA INFILE statement. Most options in mysqlimport directly correspond to LOAD DATA INFILE clauses.
To import data from the dump.txt file into the mytbl table, you can use the following command:
$ mysqlimport -u root -p --local mytbl dump.txt
password *****
mysqlimport command can specify options to set the specified format, the command syntax is as follows:
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
--lines-terminated-by="\r\n" mytbl dump.txt
password *****
mysqlimport statement uses the --columns option to set the column order:
$ mysqlimport -u root -p --local --columns=b,c,a \
mytbl dump.txt
password *****
Common Options for mysqlimport
| Option | Function |
|---|---|
| -d or --delete | Delete all information in the data table before importing new data |
| -f or --force | Regardless of whether errors are encountered, mysqlimport will force the continuation of data insertion |
| -i or --ignore | mysqlimport skips or ignores rows with the same unique key, and the data in the import file will be ignored |
| -l or -lock-tables | Lock the table before data is inserted to prevent user queries and updates from being affected while updating the database |
| -r or -replace | This option is the opposite of the -i option; it replaces records with the same unique key in the table |
| --fields-enclosed-by=char | Specifies how data records in the text file are enclosed. In many cases, data is enclosed in double quotes. By default, data is not enclosed by characters |
| --fields-terminated-by=char | Specifies the delimiter between column values. In a period-separated file, the delimiter is a period. You can use this option to specify the delimiter between data |
| --lines-terminated-by=str | This option specifies the delimiter string or character between rows in the text file. By default, mysqlimport uses newline as the row delimiter. You can choose to replace a single character with a string: a new line or a carriage return |
Common options for mysqlimport also include -v to display the version, and -p to prompt for a password.