MySQL Load data infile import

We often import data! MySQL has an efficient import method, which is the load data infile below to see the case description

Basic syntax:

 load data [low_priority] [local] infile  "   " [replace | Ignore]into Table tbl_name[fields[terminated by   " t   "  " ][escaped by  "  \ ']]  [lines terminated By " n  Span style= "color: #800000;" > '  

The load Data infile statement reads from a text file into a table at a high speed. Before using this command, the MYSQLD process (service) must already be running. For security reasons, when reading a text file located on the server, the file must be in the database directory or read by everyone. Also, in order to use the load data infile on the files on the server, you must have file permissions on the server host. 1 If you specify the keyword low_priority, then MySQL will wait until no one else reads the table before inserting the data. You can use the following command:

Load data  "/home/mark/data sql" into table Orders;

2 If the local keyword is specified, the file is read from the client host. If local is not specified, the file must be located on the server. 3 Replace and ignore keyword controls duplicate processing of existing unique key records. If you specify replace, the new row replaces the existing rows that have the same unique key value. If you specify ignore, skip the input of the 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. For example:

Load data  "/home/mark/data sql" replace into table Orders;

4 Separator (1) The fields keyword specifies the split format for the file segment, and if this keyword is used, the MySQL profiler wants to see at least one of the following options:
Terminated by delimiter: What characters are used as separators
Enclosed by field character
Escaped by escape character terminated by describes the delimiter of the field, which by default is the tab character (\ t)
Enclosed by describes the enclosed character of a field.
Escaped by describes the escape character. The default is a backslash (backslash:\) such as:

" /home/mark/orders txt " replace into table Orders fields terminated by','" ' ;

(2) The lines keyword specifies that each record is delimited defaults that ' \ n ' is a newline character if all two fields are specified, the field must precede lines. If you do not specify the fields keyword default value with the same as if you write: field terminated by ' \ t ' enclosed by ' "escaped by ' \ \ ' If you do not specify a lines clause, the default value is the same as if you write: Li NES terminated by ' \ n ' For example:

" /jiaoben/load.txt " ' , ' ' / N ';

5 Load Data infile can import files into the database by the specified columns. When we want to import part of the data, we need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs. For example, when we upgrade from an Access database to a MySQL database, the following example shows how to import data into a specified column (field):

" /home/order txt " into table Orders (Order_number, order_date, customer_id);

6 when looking for files on a server host, the server uses the following rules:
(1) If an absolute pathname is given, the server uses that path name.
(2) If a relative pathname with one or more predecessor parts is given, the server searches for files relative to the server's data directory.
(3) If you give a file name that does not have a predecessor, the server looks for the file in the database directory of the current database.
For example:/MyFile txt "The file is read from the server's data directory, and a file given as" MyFile txt "is read from the database directory of the current database. Note: null values in the field are \ n representsTransfer from http://hunan.iteye.com/blog/752606

MySQL Load data infile import