LOAD DATA INFILE Syntax--官方

For security reasons,when reading text files located on the server,the files must either reside in the database directory or be readable by all. Also,to use??on server files,you must have the??privilege. See?. For non-?load operations,if the?system variable is set to a nonempty directory name,the file to be loaded must be located in that directory.

Using??is a bit slower than letting the server access the files directly,because the contents of the file must be sent over the connection by the client to the server. On the other hand,you do not need the??privilege to load local files.

?also affects error handling:

With?,data-interpretation and duplicate-key errors terminate the operation.


With?,data-interpretation and duplicate-key errors become warnings and the operation continues because the server has no way to stop transmission of the file in the middle of the operation. For duplicate-key errors,this is the same as if??is specified.??is explained further later in this section.




The??and??keywords control handling of input rows that duplicate existing rows on unique key values:

If you specify?,input rows replace existing rows. In other words,rows that have the same value for a primary key or unique index as an existing row. See??Syntax”.


If you specify?,input rows that duplicate an existing row on a unique key value are skipped.


If you do not specify either option,the behavior depends on whether the??keyword is specified. Without,an error occurs when a duplicate key value is found,and the rest of the text file is ignored. With?,the default behavior is the same as if??is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.




To ignore foreign key constraints during the load operation,issue a??statement before executing?.

If you use??on an empty??table,all nonunique indexes are created in a separate batch (as for?). Normally,this makes??much faster when you have many indexes. In some extreme cases,you can create the indexes even faster by turning them off with??before loading the file into the table and using??to re-create the indexes after loading the file. See??Statements”.

For both the??and??statements,the syntax of the??and?clauses is the same. Both clauses are optional,but??must precede??if both are specified.

If you specify a??clause,each of its subclauses (,?,and) is also optional,except that you must specify at least one of them.

If you specify no??or??clause,the defaults are the same as if you had written this:

(Backslash is the MySQL escape character within strings in SQL statements,so to specify a literal backslash,you must specify two backslashes for the value to be interpreted as a single backslash. The escape sequences?and??specify tab and newline characters,respectively.)

In other words,the defaults cause??to act as follows when reading input:
Look for line boundaries at newlines.


Do not skip over any line prefix.


Break lines into fields at tabs.


Do not expect fields to be enclosed within any quoting characters.


Interpret characters preceded by the escape character?”?as escape sequences. For example,?”,?”,and”?signify tab,newline,and backslash,respectively. See the discussion of??later for the full list of escape sequences.




Conversely,the defaults cause??to act as follows when writing output:
Write tabs between fields.


Do not enclose fields within any quoting characters.


Use?”?to escape instances of tab,or?”?that occur within field values.


Write newlines at the ends of lines.





If you have generated the text file on a Windows system,you might have to use?to read the file properly,because Windows programs typically use two characters as a line terminator. Some programs,such as?WordPad,might use??as a line terminator when writing files. To read such files,use?.


If all the lines you want to read in have a common prefix that you want to ignore,you can use?prefix_string'?to skip over the prefix,?and anything before it. If a line does not include the prefix,the entire line is skipped. Suppose that you issue the following statement:
If the data file looks like this:
The resulting rows will be??and?. The third row in the file is skipped because it does not contain the prefix.

The?number?LINES?option can be used to ignore lines at the start of the file. For example,you can use?to skip over an initial header line containing column names:

When you use??in tandem with??to write data from a database into a file and then read the file back into the database later,the field- and line-handling options for both statements must match. Otherwise,??will not interpret the contents of the file properly. Suppose that you use??to write a file with fields delimited by commas:
To read the comma-delimited file back in,the correct statement would be:
If instead you tried to read in the file with the statement shown following,it wouldn't work because it instructs??to look for tabs between fields:
The likely result is that each input line would be interpreted as a single field.

?can be used to read files obtained from external sources. For example,many programs can export data in comma-separated values (CSV) format,such that lines have fields separated by commas and enclosed within double quotation marks,with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs,the statement shown here illustrates the field- and line-handling options you would use to load the file:
tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY 'rn'
  IGNORE 1 LINES;

If the input values are not necessarily enclosed within quotation marks,use??before the?keywords.

Any of the field- or line-handling options can specify an empty string (). If not empty,the??and??values must be a single character. The?,?,and??values can be more than one character. For example,to write lines that are terminated by carriage return/linefeed pairs,or to read a file containing such lines,specify a??clause.

To read a file containing jokes that are separated by lines consisting of?,you can do this

?controls quoting of fields. For output (),if you omit the word?,all fields are enclosed by the??character. An example of such output (using a comma as the field delimiter) is shown here:

If you specify?,the??character is used only to enclose values from columns that have a string data type (such as?,?,?,or?):

Note that occurrences of the??character within a field value are escaped by prefixing them with the?character. Also note that if you specify an empty??value,it is possible to inadvertently generate output that cannot be read properly by?. For example,the preceding output just shown would appear as follows if the escape character is empty. Observe that the second field in the fourth line contains a comma following the quote,which (erroneously) appears to terminate the field:

For input,the??character,if present,is stripped from the ends of field values. (This is true regardless of whether??is specified;??has no effect on input interpretation.) Occurrences of the?character preceded by the??character are interpreted as part of the current field value.

If the field begins with the??character,instances of that character are recognized as terminating a field value only if followed by the field or line??sequence. To avoid ambiguity,occurrences of the?character within a field value can be doubled and are interpreted as a single instance of the character. For example,if??is specified,quotation marks are handled as shown here:

 The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

?controls how to read or write special characters:

For input,if the??character is not empty,occurrences of that character are stripped and the following character is taken literally as part of a field value. Some two-character sequences that are exceptions,where the first character is the escape character. These sequences are shown in the following table (using?”?for the escape character). The rules for??handling are described later in this section.