MySQL Load Statement Detailed introduction _mysql

The syntax of load

Copy Code code as follows:

LOAD DATA [Low_priority | CONCURRENT] [local] INFILE ' File_name.txt '
[REPLACE | IGNORE]
Into TABLE tbl_name
[FIELDS
[Terminated by ' string ']
[[optionally] enclosed by ' char ']
[Escaped by ' char ']
]
[LINES
[Starting by ' string ']
[Terminated by ' string ']
]
[IGNORE number LINES]
[(Col_name_or_user_var,...)]
[SET col_name = expr,...)]

The load DATA infile statement is used to read rows from a text file at high speed and to mount a table. The file name must be a literal string.

The character set indicated by the CHARACTER_SET_DATABASE system variable is used to interpret the information in the file. The set names and character_set_client settings do not affect the interpretation of the input.

A typical example

Copy Code code as follows:

LOAD DATA local INFILE ' data.txt ' into TABLE tbl_name
FIELDS terminated by ', '
Optionally enclosed by ' "'
LINES terminated by ' \ n '

If you want to load only a partial column of a table, you should specify a column list:

Copy Code code as follows:

LOAD DATA local INFILE ' persondata.txt ' into TABLE persondata (col1,col2);

The path to the file

If local is specified, it is considered to be related to the connected client:

1. If local is specified, the file is read by clients on the client host and sent to the server. The file is given a full path name to specify the exact location. If a relative path name is given, this name is understood to be the same as the directory where the client was started.

2. If the local is not specified, the file must reside on the server host and be read directly by the server.

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

1. If an absolute path name is given, the server uses this path name.
2. If a relative path name with one or more boot components is given, the server searches for files relative to the server data directory.
3. If a file name is given without a boot component, the server looks for the file in the database directory of the default database.

Note that these rules mean that a file named./myfile.txt is read from the server data directory, and the same file named MyFile.txt is read from the database directory in the default database.

Using absolute path load data from the client

Copy Code code as follows:

LOAD DATA local INFILE '/import/data.txt ' into TABLE db2.my_table;

Using relative path load data from the server

The following load data statement reads the file data.txt from the DB1 database directory because DB1 is the current database. Even if the statement explicitly loads the file into the table in the DB2 database, it is read from the DB1 directory.

Copy Code code as follows:

Use DB1;
LOAD DATA INFILE ' data.txt ' into TABLE db2.my_table;

IGNORE number Lines option

The IGNORE number lines option can be used to ignore rows at the beginning of a file.

You can use ignore 1 lines to skip a header row that contains column names:

Copy Code code as follows:

LOAD DATA INFILE '/tmp/test.txt ' into TABLE test IGNORE 1 LINES;

Replace and ignore

Some input records copy the original record to the unique key value. The Replace and ignore keywords are used to control the operations of these input records.

If you specify replace, the input line replaces the existing row (in other words, a row with the same value for a primary or unique index as the original row).

If you specify ignore, the input rows that copy the existing rows to the unique key value are skipped.

If you do not specify either of these options, the operation depends on whether the local keyword is specified. When you do not use local, an error occurs when a duplicate key value occurs, and the remaining text files are ignored. When using local, the default runs are the same as when the ignore is specified; This is because in the middle of running, the server has no way to abort the transfer of files

Impact of indexes

If you use the load DATA INFILE on an empty MyISAM table, all of the non unique indexes are created in a separate batch (for repair table). When you have many indexes, this usually makes the load DATA infile much faster. Typically, the LOAD DATA infile will be very fast, but in some extreme cases you can use ALTER TABLE before you load a file into a table ... DISABLE keys to close the load DATA INFILE, or use alter TABLE after loading files ... The Enable key creates the index again, making it faster to create the index.

Default values for fields and lines

If you do not specify a fields clause, the default value is the value that assumes that you write the following statement:

Copy Code code as follows:

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

If you do not specify a lines clause, the default value is the value that assumes that you write the following statement:

Copy Code code as follows:

LINES terminated by ' \ n ' starting by '

In other words, when the input value is read, the default value causes the load DATA infile to run as follows:

Copy Code code as follows:

Look for the boundary of the row at the new line.
No row prefixes are skipped.
Breaks the row into fields at the tabs.
You do not want the field to be enclosed in any quotation mark characters.
Text characters that are interpreted as part of a field value when a tab character, a new line, or before ' \ ' is present.

Conversely, when you write an output value, the default value causes the Select ... Into outfile is run as follows:

Copy Code code as follows:

Writes a tab character between fields.
Do not include the field in any quotation mark character.
Use ' \ ' to escape when tabs, new rows, or ' \ ' appear in field values.
Writes a new line at the end of a row.

Note that to write fields escaped by ' \ \, you must specify two backslashes for the value to be read, used as a single SLR slash.

Note: If you have generated a text file on a Windows system, you may have to use lines terminated by ' \ r \ n ' to read the file correctly, because Windows programs typically use two characters as a line terminator. Part of a program that, when writing a file, may use \ r as a line terminator. To read such a file, you should use lines terminated by ' \ R '.

Starting lines option

If all the rows you want to read contain a common prefix that you want to ignore, you can use ' prefix_string ' to skip the prefix (and the characters before the prefix). If a row does not include a prefix, the entire row is skipped. Note: Prefix_string will appear in the middle of a line.

Use the following test.txt as the file source

Copy Code code as follows:

xxx "Row", 1
Something xxx "Row", 2

Use the following SQL to import data

Copy Code code as follows:

LOAD DATA INFILE '/tmp/test.txt ' into the TABLE test LINES starting by "XXX";

Finally, only the data ("row", 1) and ("Row", 2) are obtained.

Terminated lines option

If the jokes is delimited by a row of percent percent, you can do this by reading the file containing jokes:

Copy Code code as follows:

LOAD DATA INFILE '/tmp/jokes.txt ' into the TABLE jokes FIELDS terminated by ' LINES terminated by ' \n%%\n ' (joke);

terminated,enclosed,escaped field option

Terminated is used to control the separator for a field and can be multiple characters.

Enclosed by is the quotation mark used to control the field, must be a single character, and if you omit the word optionally, all fields are included in the enclosed by string, and if you specify optinally, enclosed The by character is used only to contain values in a column that has a string data type, such as Char, BINARY, text, or enum.

SELECT ... into outfile export data, enclosed by ' "', ignoring optionally

Copy Code code as follows:

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

SELECT ... into outfile export data, enclosed by ' "', specify optionally

Copy Code code as follows:

1, "A string", 100.20

Escaped by is used to escape, FIELDS escaped by value must be a single character.

If the fields escaped by character is a null character, no characters are escaped and null is NULL output instead of \ n. It is not a good idea to specify an empty escape character, especially if the field value of the data contains characters from any just given list.

If the enclosed by character appears within the field value, the enclosed by character is escaped by using the escaped by character as the prefix.