Mysql import data load data infile usage, mysqlinfile
Mysql import data load 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 a table from a text file at a high speed. Before using this command, the mysqld process (service) must be running. For security reasons, when reading text files on the server, the files must be in the database directory or can be read by everyone. In addition, to use load data infile for files on the server, you must have the file permission on the server host. 1. If you specify the keyword low_priority, MySQL inserts data only when no one else reads the table. 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 on the server. 3 replace and ignore keywords control repeated processing of existing unique key records. If you specify replace, the new row replaces the existing row with the same unique key value. If you specify ignore, skip the input of duplicate rows of existing rows with a unique key. If you do not specify any option, an error occurs when the duplicate key is found, and the remaining part of the text file is ignored. For example:
Load data low_priority infile "/home/mark/data SQL" replace into table Orders;The 4-separator (1) fields keyword specifies the delimiter format. If this keyword is used, the MySQL parser wants to see at least one of the following options:
Terminated by separator: Which character is used as the Separator
The enclosed by field contains the start character.
The delimiter of the description field of the escaped by Escape Character terminated by. It is a tab character (\ t) by default)
Enclosed by describes the starting characters of a field.
Escape characters described by escaped. The default value is the backslash (backslash :\)
For example, load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by ', 'enabledby '"';(2) The lines keyword specifies that the delimiter of each record is '\ n' by default, that is, the line break. If both fields are specified, fields must be before lines. If you do not specify the default value of the fields keyword, it is the same as if you write the following: fields terminated by '\ t' enclosed by ''' escaped by' \ 'if you do not specify a line clause, the default value is the same as if you write this statement: lines terminated by '\ n' for example: load data infile "/jiaoben/load.txt" replace into table test fields terminated by', 'Lines terminated by '/N '; 5 load data infile: You can import files to the database by specified columns. When we want to import part of the data, we need to add some columns (columns/fields) to the MySQL database to meet some additional needs. For example, the following example shows how to import data to a specified field when upgrading from an Access database to a MySQL database:
Load data infile "/home/Order txt" into table Orders (Order_Number, Order_Date, Customer_ID );6 when searching for files on the server host, the server uses the following rules:
(1) If an absolute path name is provided, the server uses this path name.
(2) If a relative path name of one or more front parts is given, the server searches for files in the data directory of the server relative to the server.
(3) If a file name without a front-end component is provided, the server searches for a file in the database directory of the current database.
For example, the file in/myfile txt is read from the server's data directory, while the file in myfile txt is read from the database directory of the current database.
Note: The null value in the field is represented by \ N.
