MySQL load operation

Reprinted from Http://www.cnblogs.com/ggjucheng/archive/2012/11/05/2755683.html

The syntax of load

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 load 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. Set names and character_set_client settings do not affect the interpretation of the input.

A typical example

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 some of the columns of a table, you should specify a list of columns:

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

path to the file

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

• If local is specified, the file is read by clients on the client's 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, the name is understood to be the same as the directory where the client was started.
• If local is not specified, the file must be located on the server host and read directly by the server.

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

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

Note that these rules imply that files named./myfile.txt are read from the server data directory, while the same files named MyFile.txt are read from the database directory of the default database.

Use absolute path from client load data

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

Use relative path load data from 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 also read from the DB1 directory.

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 starting header row that contains the column name:

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

Replace and Ignore

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

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

If you specify ignore, the input line that copies the original row to the unique key value is skipped.

If neither of these options is specified, the operation depends on whether the local keyword is specified. When you do not use local, errors occur when duplicate keyword values occur, and the remaining text files are ignored. When using local, the default behavior is the same as when the ignore is specified, because in the middle of the run, the server has no way to abort the transfer of the file.

Impact of Indexes

If you use load DATA INFILE for an empty MyISAM table, all non-unique indexes are created in a separate batch (for repair table). This usually makes the load DATA infile much faster when you have many indexes. Typically, the load DATA infile is very fast, but in some extreme cases you can use ALTER TABLE before loading the file into the table ... DISABLE keys Close Load DATA INFILE, or use alter TABLE after loading the file ... Enable keys to create indexes again, making indexing faster.

default values for fields and lines

If you do not specify the fields clause, the default value is assumed to be when you write the following statement:

Fields TERMINATED by ' \ t ' enclosed by ' \ escaped by ' \ \ '

If you do not specify the lines clause, the default value is assumed to be when you write the following statement:

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:

Finds the boundary of a row at a new line. Does not skip any row prefixes. Breaks a row into a field at a tab. You do not want the field to be included in any quotation mark characters. When a tab appears, a new line, or a ' \ ' before ' \ ', it is understood as a literal character that is part of the field value.

Conversely, when the output value is written, the default value causes the Select ... into outfile works as follows:

Writes a tab between fields. Do not include fields in any quotation-mark characters. Use ' \ ' to escape when a tab, new line, or ' \ ' appears in the field value. Writes a new line at the end of the row.

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

Note: If you have generated a text file in your Windows system, you may have to use lines TERMINATED by ' \ r \ n ' to properly read the file because the Windows program typically uses two characters as a line terminator. Some programs, when writing files, may use \ r as the line terminator. To read such a file, you should use lines TERMINATED by ' \ R '.

Starting lines Options

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 character 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

xxx "row", 1something xxx "Row", 2

Use the following SQL to import data

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

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

TERMINATED Lines Options

If the jokes is delimited by a row of percent, you can do this if you want to read the file that contains the jokes:

LOAD DATA INFILE '/tmp/jokes.txt ' into TABLE jokes fields TERMINATED by "LINES TERMINATED by ' \n%%\n ' (joke);

terminated,enclosed,escaped Field Options

The terminated is used to control the delimiter of a field, which can be multiple characters.

Enclosed by is used to control the field of quotation marks, must be a single character, if you omit the word optionally, all fields are included in the enclosed by string, if you specify optinally, then enclosed The by character is only used 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

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

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

1, "A string", 100.20

Escaped by is used for escaping, and the 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 used as the 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 any characters from the list just given.

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

MySQL load operation