[mysql tutorial] MySQL import data

MySQL Import Data

MySQL can use two simple methods to import data exported by 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 both 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, 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';

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 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: *& Use the --columns option in the *

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

mysqlimport statement 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 new data is imported into the data table

-f or --force Whether encountered or not Error, mysqlimport will force to continue inserting data

-i or --ignore mysqlimport skips or ignores rows with the same unique keyword, and the data in the imported file will be ignored.

-l or -lock-tables Lock the table before data is inserted, thus preventing user queries and updates from being affected when you update the database.

-r or -replace This option has the opposite effect of the -i option; this option will replace records with the same unique key in the table.

--fields-enclosed- by= char Specifies how to enclose the data records in the text file. In many cases, the data is enclosed in double quotes. By default data is not enclosed in characters.

--fields-terminated- by=char Specifies the separator between the values ​​of each data. In a period-delimited file, the separator is a period. You can use this option to specify the delimiter between data. The default delimiter is tab

--lines-terminated- by=str This option specifies the string or character that separates data between lines in the text file. By default, mysqlimport uses newline as the line separator. You can choose to replace a single character with a string: a new line or a carriage return.

Mysqlimport command commonly used options include -v to display version (version), -p to prompt for password (password), etc.

The above is the content of [mysql tutorial] MySQL importing data. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!