MySQL Import data infile usage
Basic Syntax: load data [low_priority] [local] infile ' file_name txt ' [replace | ignore]
into table Tbl_name
[Fields
[Terminated by ' t ']
[optionally] enclosed by ']
[escaped by ']]
[Lines terminated by ' n ']
[Ignore number lines]
[(Col_name,)]
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 infileon 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 low_priority infile "/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 low_priority infile "/home/mark/data SQL" replace into table Orders; 4 separators (1) The fields keyword specifies the segmentation format of 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 character is used as the delimiter
Enclosed by Fields Enclose characters
escaped by Escape character terminated byThe delimiter that describes 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:\)
For example: Load data infile "/home/mark/orders txt" replace into table Orders fields terminated by ', ' enclosed by ' "; (2) lines keyword specifies the separation of each record defaults think ' \ n ' is a line break If all two fields are specified, that field must precede lines . If you do not specify The fields keyword default value is the same as if you wrote this: 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:lines terminated by ' \ n ' For example:load Data infile "/jiaoben/load.txt" replace into table test fields terminated by ', ' lines termin Ated by '/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 were upgrading from an Access database to a MySQL database The following example shows how to import data into a specified column (field):
load Data infile "/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 as " MyFile txt " The given file is read from the database directory of the current database.
Note: null values in the field are denoted by \ n
MySQL Import data infile usage
