Load syntax
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 ,...) ]
Load data infileStatements are used to read rows from a text file and load a table. The file name must be a text string.
ByCharacter_set_databaseThe character set indicated by system variables is used to interpret information in the file.Set namesAndCharacter_set_clientDoes not affect the description of the input.
Typical examples
LoadData local infile'Data.txt' Into TableTbl_name fields terminatedBy ','Optionally enclosedBy '"'Lines terminatedBy '\ N'
If you only want to load some columns of a table, you should specify a column list:
LoadData local infile'Persondata.txt' Into TablePersondata (col1, col2 );
File Path
IfLocalIs considered to be related to the connected client:
- IfLocalThe file is 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.
- IfLocalIf 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:
- If an absolute path name is specified, the server uses this path name.
- If a relative path name with one or more pilot components is given, the server searches for files relative to the server data directory.
- 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./Myfile.txtIs read from the server data directoryMyfile.txtThe same files will be read from the default database directory.
Load data from the client using the absolute path
LoadData local infile'/Import/data.txt' Into TableDb2.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.
UseDb1;LoadData infile'Data.txt' Into TableDb2.my _ table;
IgnoreNumberLines options
IgnoreNumberThe 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:
LoadData infile'/Tmp/test.txt'Into TableTest ignore1Lines;
Replace and ignore
Some input records copy the original records to the unique keyword value.ReplaceAndIgnoreKeyword is used to control the operation of these input records.
If you specifyReplaceThe input row replaces the original row (in other words, the same as the original row, for a primary index or a unique index with the same value ).
If you specifyIgnoreThe original row is skipped when it is copied to the input row with the unique keyword value.
If neither of the two options is specifiedLocalWhether the keyword is specified. Not usedLocalWhen the keyword value is repeated, an error occurs and the remaining text files are ignored. UseLocalDefault running conditions andIgnoreThe specified time is the same. This is because the server cannot stop file transfer during running.
Index impact
If you do notMyISAMTable usageLoad data infile, All non-unique indexes will be created in an independent batch (Repair table). When you have many indexes, this usually causesLoad data infileGreatly speed up. Generally,Load data infileBut in some extreme cases, you can useAlter table... disable keysCloseLoad data infileOr useAlter table... enable keysRe-create an index to make it faster.
Default values of fields and lines
If you do not specifyFieldsThe default value is the value when you write the following statement:
Fields terminatedBy '\ T'EnclosedBy ''EscapedBy '\\'
If you do not specifyLinesThe default value is the value when you write the following statement:
Lines terminatedBy '\ N'StartingBy ''
In other words, when reading the input value, the default value will makeLoad data infileRun 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 will makeSelect... into OUTFILERun 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: WriteFields escaped'\\', You must specify two backslashes for the value to be read as a single SLR slash.
Note:If you already haveWindowsA text file is generated in the system. You may have to useLines terminated'\ R \ n'To read the file correctly, becauseWindowsProgramGenerally, two characters are used as the line terminator. Some programsWhen writing a file, you may use\ RAs the line terminator. To read such a file, useLines terminated'\ 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_stringIt appears in the middle of a row.
Use test.txt as the file source.
XXX "row ",1Something XXX "row ",2
Use the following SQL to import data
LoadData infile'/Tmp/test.txt' Into TableTest lines startingBy"XXX ";
LastAnd only get data("Row", 1)And("Row", 2).
Terminated lines options
IfJokesBy%Separate the composed rows,To read the includeJokesYou can perform the following operations:
LoadData infile'/Tmp/jokes.txt' Into TableJokes fields terminatedBy ''Lines terminatedBy '\ 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,Must be a single character,If you ignore the wordOptionally, All fields are included inEnclosedString,If you specifyOptinally, ThenEnclosedThe character is only used to contain data types with strings (for exampleChar, binary, textOrEnum.
Select... into OUTFILE export data,Enclosed by '"', ignore optionally
"1"," A string ","100.20"
Select... into OUTFILE to export data, enclosed by '"', specify optionally
1, "A string ",100.20
Escaped by is used for escape. The fields escaped by value must be a single character.
IfFields escapedIf the character is null, no character is escaped andNullActedNullOutput, instead\ 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 it appears in the field valueEnclosedBy usingEscapedCharacter as the prefixEnclosedEscape characters.
From http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data