Summarize two methods of importing data into MySQL

MySQL Importing data

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

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 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);

Use mysqlimport imports 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 *****

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

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

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

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

【Related Recommendations】

1. Special Recommendation:"php Programmer Toolbox" V0.1 version Download

2. Free mysql online video tutorial

3. Those things about database design

The above is the detailed content of Summarize two methods of importing data into MySQL. For more information, please follow other related articles on the PHP Chinese website!