MySQL import data

MySQL import data

There are two simple ways to import data exported by MySQL in MySQL.

1. Mysql command import

Use the mysql command import syntax format:

mysql -u username -p Password < Database data to be imported (demo.sql)

Example:

# mysql -uroot -p123456 < demo.sql

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

2. Import the source command

To import the database using the source command, 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 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, 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 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);

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 database_name dump.txt
password *****

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"  database_name dump.txt
password *****

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

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

Introduction to common options of mysqlimport

Options	Function
-d or --delete	Delete all information in the data table before importing new data into the data table
-f or --force	Regardless of whether an error is encountered, mysqlimport will force to continue inserting data
-i or --ignore	mysqlimport skips or ignores those with the same unique Line, data in the import file will be ignored.
-l or -lock-tables	Lock the table before data is inserted, thus preventing, When you update the database, user queries and updates are affected.
-r or -replace	This option has the opposite effect of the -i option; this option will replace There are records with the same unique key in the table.
--fields-enclosed- by= char	Specify what is enclosed in the data records in the text file. In many cases Data is enclosed in double quotes. By default data is not enclosed in characters.
--fields-terminated- by=char	Specifies the delimiter between the values of each data, in a period-delimited file, 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 data between lines in the text file delimited string or characters. By default, mysqlimport uses newline as the line separator.You can optionally replace a single character with a string: A new line or a carriage return.

Commonly used options of the mysqlimport command include -v to display the version (version), -p to prompt for a password (password), etc.

MySQL import data

4. Use mysqlimport to import data

Introduction to common options of mysqlimport

php.cn