MySQL Load statement batch input data _mysql

Fortunately, MySQL provides some methods for batch data entry, making it easy to add data to the table. These methods are described in this section, as well as in the next section. This section describes the SQL language-level workaround.

1. Basic grammar

Syntax: LOAD DATA [local] INFILE ' file_name.txt ' [REPLACE | IGNORE] into table tbl_name the load DATA infile statement is read from a text file to a table at a high speed. If you specify a local keyword, read the file from the client host. If local is not specified, the file must be located on the server. (Local is available in MySQL3.22.6 or later versions.) )

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

The Replace and Ignore keywords control the repetitive processing of existing unique key records. If you specify replace, the new line replaces the existing row with the same unique key value. If you specify ignore, skip the input of duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored.

If you use local keywords to load data from a native file, the server has no way to stop the transmission of the file in the middle of the operation, so the default behavior is as if ignore was specified.

2, the search principle of documents

When locating files on the server host, the server uses the following rules:

If an absolute path name is given, the server uses the path name.

If you give a relative pathname with one or more predecessor parts, the server searches for files relative to the server's data directory.

If you give a filename without a front part, the server looks for files in the database directory of the current database.

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

mysql> use DB1; mysql> LOAD DATA INFILE "./data.txt" into TABLE db2.my_table;

3, the syntax of fields and lines clauses

If you specify a fields clause, each of its clauses (terminated by, [optionally] enclosed by and escaped by) is optional, except that you must specify at least one of them.

If you do not specify a fields clause, the default value is the same as if you wrote it:

FIELDS terminated by ' \ t ' enclosed by ' escaped by ' \ '

If you do not specify a lines clause, the default value is the same as if you wrote it:

LINES terminated by ' \ n '

In other words, when the default value results in reading input, the LOAD DATA infile behaves as follows:

Finding line boundaries at line breaks

To break a row into a field at a locator

Do not expect fields to be encapsulated by any quote character

A locator, line break, or "\" that starts with "\" is interpreted as a partial literal character of a field value

The LOAD DATA infile can be used to read files obtained from external sources. For example, a file in dBASE format will have fields delimited by commas and surrounded by double quotes. If the line in the file is terminated by a newline character, the command shown below describes the field and row processing options that you will use to load the file:

mysql> LOAD DATA INFILE ' data.txt ' into the TABLE tbl_name FIELDS terminated by ', ' enclosed by ' "' LINES TERMINATE D by ' \ n ';

Any field or row-handling option can specify an empty string ('). If not NULL, the FIELDS [optionally] enclosed by and FIELDS escaped by value must be a single character. FIELDS terminated by and lines terminated by value can be more than one character. For example, write a row that is terminated by a carriage return newline (CR+LF), or read a file containing such a line, specifying a lines terminated by ' \ r \ n ' clause.

FIELDS [optionally] enclosed by the bounding character of the control field. For the output (SELECT ...). into outfile), if you omit optionally, all fields are surrounded by the enclosed by character. An example of such an output (using a comma as the field delimiter) appears 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,enclosed by characters are used only to surround 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, you may produce output that cannot be read correctly by the load DATA infile. For example, if the escape character is empty, the output shown above appears as follows. Notice that the second field in line fourth contains a comma that follows the quotation marks (incorrectly) as if you want 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

FIELDS terminated by and lines terminated by the first character of the by value

ASCII 0 (in effect, the subsequent escape characters are written in ASCII ' 0 ' instead of a 0-value byte)

If the fields escaped by character is empty, no characters are escaped. Specifying an idle character may not be a good idea, especially if the field value in your data contains any of the characters in the table you just gave.

For input, if the fields escaped by character is not empty, the appearance of the character is stripped and the subsequent character is literally a part of the field value. The exception is an escaped "0" or "n" (that is, or \ n If the escape character is "\"). These sequences are interpreted as ASCII 0 (a 0-valued byte) and null. See the following rules for null processing.

Summarize

Loading data for a database is one of the important responsibilities of an administrator, and because of its importance, MySQL provides a very wide range of methods. The main of these are listed in this section:

1. Use INSERT, replace statement

2. Use Insert/replace ... SELECT statement

3. Use the load DATA infile statement

4, use the utility program Mysqlimport