MySQLload statement details _ MySQL

This article mainly introduces the MySQLload statement in detail. This article describes the basic syntax of load, file path, configuration options, STARTINGLINES options, TERMINATEDLINES options, and so on. For more information, see Load syntax

The code is 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 quickly read rows from a text file and LOAD a table. The file name must be a text string.

The character set indicated by character_set_database system variables is used to interpret information in the file. The setting of set names and character_set_client does not affect the interpretation of input.

Typical examples

The code is as follows:

Load data local infile 'data.txt 'into table tbl_name
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n'

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

The code is as follows:

Load data local infile 'persondata.txt 'into table persondata (col1, col2 );

File path

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

1. if LOCAL is specified, the file will be read by the client on the client host and sent to the server. The file will be given a complete path name to specify the exact location. If a relative path name is given, the name is considered as relative to the directory where the client is started.

2. if LOCAL is not specified, the file must be located on the server host and be directly read by the server.

When locating files on the 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 pilot components is given, the server searches for files relative to the server data directory.
3) If a file name without a boot component is specified, the server searches for the file in the Database Directory of the default database.

Note that these rules mean that files named./myfile.txt will be read from the server data directory, and files named myfile.txt will be read from the Database Directory of the default database.

Load data from the client using the absolute path

The code is as follows:

Load data local infile '/import/data.txt' into table db2.my _ table;

Use the relative path load data from the server

The following LOAD data sentence reads the file data.txt from the db1data warehouse directory because db1 is the current database. Even if the statement explicitly loads the file into the db2 database table, it will also read from the db1 directory.

The code is 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 start header line containing the column name:

The code is as follows:

Load data infile '/tmp/test.txt' into table test IGNORE 1 LINES;

REPLACE and IGNORE

Some input records copy the original records to the unique keyword value. The REPLACE and IGNORE keywords are used to control the operations on these input records.

If REPLACE is specified, the input row replaces the original row (in other words, the same as the original row, with the same value for a primary index or unique index ).

If you specify IGNORE, the original row copied to the input row with the unique keyword value is skipped.

If neither of the two options is specified, the running condition depends on whether the LOCAL keyword is specified. If you do not use LOCAL, an error occurs when the keyword value is repeated and the remaining text files are ignored. When LOCAL is used, the default running condition is the same as that when IGNORE is specified. this is because the server cannot stop file transmission during running.

Index impact

If you use load data infile for an empty MyISAM TABLE, all non-unique indexes will be created in an independent batch (for repair table ). When you have many indexes, this usually speeds up load data infile. In general, the load data infile speed is very fast, but in some extreme cases, you can use alter table before loading the file into the TABLE... disable keys to disable load data infile, or use alter table... enable keys re-create an index to make it faster.

Default values of FIELDS and LINES

If you do not specify the FIELDS clause, the default value is the value when you write the following statement:

The code is as follows:

Fields terminated by '\ t' enclosed by ''escaped '\\'

If you do not specify the LINES clause, the default value is the value when you write the following statement:

The code is as follows:

Lines terminated by '\ n' starting''

In other words, when reading input values, the default value causes load data infile to run as follows:

The code is as follows:

Find the line boundary at the new line.
No row prefix is skipped.
Splits rows into fields at the TAB.
Fields are not expected to be included in any quotation marks.
When a tab, New Line, or '\' is present before '\', it is understood as a text character that is part of the field value.

On the contrary, when writing output values, the default value causes SELECT... into outfile to run as follows:

The code is as follows:

Write tabs between fields.
Fields are not included in any quotation marks.
When a tab, New Line, or '\' appears in the field value, escape.
Write a new row at the end of the row.

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

Note: If you have generated a text file in Windows, you may have to use lines terminated by '\ r \ n' to read the file correctly, because Windows usually uses two characters as a line terminator. Some programs may use \ r as the line terminator when writing files. To read such a file, 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 characters before the prefix ). If a row does not contain a prefix, the entire row is skipped. Note: prefix_string will appear in the middle of a row.

Use test.txt as the file source.

The code is as follows:

Xxx "row", 1
Something xxx "row", 2

Use the following SQL to import data

The code is as follows:

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 jokes is separated by rows consisting of %, you can perform the following operations to read files containing jokes:

The code is as follows:

Load data infile '/tmp/jokes.txt' into table jokes fields terminated by ''lines terminated by '\ n % \ n' (joke );

TERMINATED, ENCLOSED, escaped field options

TERMINATED is a delimiter used to control fields. it can contain multiple characters.

Enclosed by is used to control the quotation marks of a field. it must be a single character. if OPTIONALLY is ignored, all fields are included in the enclosed by string. if OPTINALLY is specified, the enclosed by character is only used to contain values in columns with string data types (such as CHAR, BINARY, TEXT, or ENUM.

SELECT... into outfile export data, enclosed by '"', ignore OPTIONALLY

The code is as follows:

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

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

The code is as follows:

1, "a string", 100.20

Escaped by is used for escape. The fields escaped by value must be a single character.

If fields escaped by is a NULL character, no character is ESCAPED and NULL is output as NULL rather than \ N. It is not a good way to specify an empty escape character, especially if the field value of the data contains any character in the just given list, it is not recommended to do so.

If the enclosed by character appears in the field value, use the escaped by character as the prefix to escape the enclosed by character.