mysql bulk load data using the LOAD statement

Earlier in this chapter discussed how to use SQL to insert data into a table. However, if you need to add many records to a table, it is inconvenient to use SQL statements to enter data. Fortunately, MySQL provides methods for bulk data entry, making it easy to add data to the table. This section, as well as the next section, describes these methods. This section describes the SQL language-level workarounds.

1, the basic syntax

Syntax: LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name LOAD The DATA INFILE statement is read from a text file at a high speed into a table. If you specify the LOCAL keyword, read the file from the client host. If LOCAL is not specified, the file must be on the server. (LOCAL is available in MySQL 3.2.2.6 or later.)

For security reasons, when reading a text file located on the server, the file must be in the database directory or readable by everyone. In addition, you must have file permissions on the server host in order to use LOAD DATA INFILE on the server's files. See Chapter VII Database Security.

The REPLACE and IGNORE keywords control repetitive processing of existing unique key records. If you specify REPLACE, the new line will replace the existing line with the same unique key value. If you specify IGNORE, skip input for duplicate rows of existing rows with unique keys. If you do not specify either option, an error occurs when the duplicate key is found, and the rest of the text file is ignored.

If you use the LOCAL keyword to load data from a local file, the server can not stop the file transfer during the operation, so the default behavior is as if IGNORE was specified.

2, the document search principle

When looking for files on the server host, the server uses the following rules:

If you give an absolute path name, the server uses that path name.

If you give a relative path name with one or more front components, the server searches for files relative to the server's data directory.

If you give a file name without a leading part, the server looks for the file in the current database's database directory.

Note that these rules mean that a file like "./myfile.txt" is read from the server's data directory and a file given as "myfile.txt" is read from the current database's database directory. Also note that for the following statements, the db1 file is read from the database directory instead of db2:

3, FIELDS and LINES clause syntax

If you specify a FIELDS clause, each of its clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is optional, except you must specify at least one of them.

If you do not specify a FIELDS clause, the default is the same as if you wrote it like this:

FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'

If you do not specify a LINES clause, the default is the same as if you wrote this:

LINES TERMINATED BY 'n'

In other words, the default value causes LOAD DATA INFILE to behave as follows when reading input:

Find line boundaries at newline

Divide the line into fields at the locator

Do not expect fields to be enclosed by any quote characters

Will be from the "" at the beginning of the newline or "" is the interpretation of the value of the field part of the literal characters

LOAD DATA INFILE can be used to read files obtained from external sources. For example, files in dBASE format will have comma-delimited and double-quoted fields. If the line in the file is terminated with a newline, the command shown below shows the fields and line handling options that you will use to load the file:

Any field or line handling option can specify an empty string (''). If not empty, FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values ​​must be a single character. FIELDS TERMINATED BY and LINES TERMINATED BY values ​​can be more than one character. For example, write a line terminated by a carriage return line feed (CR + LF), or read a file that contains such a line, specifying a LINES TERMINATED BY 'rn' clause.

FIELDS [OPTIONALLY] ENCLOSED BY control surrounded by characters of the field. For output (SELECT ... INTO OUTFILE), if you omit OPTIONALLY, all fields are surrounded by ENCLOSED BY characters. An example of such an output (using a comma as a field delimiter) is shown below:

"1", "a string", "100.20"

"2", "a string containing a, comma", "102.20"

"3", "a string containing a" quote "," 102.20 "

"4", "a string containing a", quote and comma "," 102.20 "

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose CHAR and VARCHAR fields:

1, "a string", 100.20

2, "a string containing a, comma", 102.20

3, "a string containing a" quote ", 102.20

4, "a string containing a", quote and comma ", 102.20

Note that the occurrence of the ENCLOSED BY character in a field value is escaped by using the ESCAPED BY character as its prefix. Also note that if you specify an empty ESCAPED BY value, it may produce an output that can not be correctly read by LOAD DATA INFILE. For example, if the escape character is blank, the output shown above is shown below. Notice that the second field in the fourth line contains a comma following the quotation marks, which (incorrectly) seems to terminate the field:

1, "a string", 100.20

2, "a string containing a, comma", 102.20

3, "a string containing a" quote ", 102.20

4, "a string containing a", quote and comma ", 102.20

FIELDS ESCAPED BY Controls how special characters are written or read. If the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on the output:

FIELDS ESCAPED BY character

FIELDS [OPTIONALLY] ENCLOSED BY character

The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

ASCII 0 (actually writes subsequent escaped characters as ASCII'0 'instead of a zero value byte)

If the FIELDS ESCAPED BY character is empty, no characters are escaped. Specifying an empty escape character may not be a good idea, especially if the field values ​​in your data contain any of the characters in the table just given.

For input, if the FIELDS ESCAPED BY character is not empty, the occurrence of the character is stripped and subsequent characters literally act as a part of the field value. The exception is an escaped "0" or "N" (ie, or N, if the escape character is ""). These sequences are interpreted as ASCII 0 (a zero-valued byte) and NULL. See the rules for NULL handling below.

to sum up

It is one of the important responsibilities of the administrator to load data into the database, and because of the importance, the methods MySQL provides are numerous. The main ones are listed in this section:

1, use the INSERT, REPLACE statement

2, use the INSERT / REPLACE ... SELECT statement

3, use the LOAD DATA INFILE statement

4, use the utility mysqlimport