LOAD DATA
TheLOAD DATAstatement batch loads data into a TiDB table.
Synopsis
- LoadDataStmt
LoadDataStmt::='LOAD'
'DATA'LocalOpt'INFILE'stringLit DuplicateOpt'INTO'
“表”TableName CharsetOpt Fields Lines IgnoreLines ColumnNameOrUserVarListOptWithBrackets LoadDataSetSpecOptParameters
LocalOpt
You can specify that the imported data file is located on the client or on the server by configuring theLocalOptparameter. Currently, TiDB only supports data import from the client. Therefore, when importing data, set the value ofLocalOpttoLocal.
FieldsandLines
You can specify how to process the data format by configuring theFieldsandLinesparameters.
字段终止BY: Specifies the separating character of each data.FIELDS ENCLOSED BY: Specifies the enclosing character of each data.LINES TERMINATED BY: Specifies the line terminator, if you want to end a line with a certain character.
Take the following data format as an example:
"bob","20","street 1"\r\n "alice","33","street 1"\r\n我f you want to extractbob,20, andstreet 1, specify the separating character as',', and the enclosing character as'\"':
字段终止BY
','ENCLOSEDBY
'\"'LINES TERMINATEDBY
'\r\n'
我f you do not specify the parameters above, the imported data is processed in the following way by default:
字段终止BY
'\t'ENCLOSEDBY
''LINES TERMINATEDBY
'\n'
我GNORE number LINES
You can ignore the firstnumberlines of a file by configuring the我GNORE number LINESparameter. For example, if you configure我GNORE 1 LINES, the first line of a file is ignored.
我n addition, TiDB currently only supports parsing the syntax of theDuplicateOpt,CharsetOpt, andLoadDataSetSpecOptparameters.
例子
创建
TABLEtrips ( trip_idbigint
NOT
NULL
PRIMARYKEY AUTO_INCREMENT, durationinteger
not
null, start_date datetime, end_date datetime, start_station_numberinteger, start_stationvarchar(255), end_station_numberinteger, end_stationvarchar(255), bike_numbervarchar(255), member_typevarchar(255) );
Query OK, 0 rows affected (0.14 sec)The following example imports data usingLOAD DATA. Comma is specified as the separating character. The double quotation marks that enclose the data is ignored. The first line of the file is ignored.
我f you see the error messageERROR 1148 (42000): the used command is not allowed with this TiDB version, refer toERROR 1148 (42000): the used command is not allowed with this TiDB version.
LOAD DATA LOCAL INFILE '/mnt/evo970/data-sets/bikeshare-data/2017Q4-capitalbikeshare-tripdata.csv' INTO TABLE trips FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);
Query OK, 815264 rows affected (39.63 sec) Records: 815264 Deleted: 0 Skipped: 0 Warnings: 0LOAD DATAalso supports using hexadecimal ASCII character expressions or binary ASCII character expressions as the parameters forFIELDS ENCLOSED BYand字段终止BY. See the following example:
LOAD DATALOCAL我NFILE'/mnt/evo970/data-sets/bikeshare-data/2017Q4-capitalbikeshare-tripdata.csv'
我NTO
TABLEtrips FIELDS TERMINATEDBYx'2c'ENCLOSEDBYb'100010'LINES TERMINATEDBY
'\r\n'我GNORE1LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);我n the above example,x'2c'is the hexadecimal representation of the,character andb'100010'is the binary representation of the"character.
MySQL compatibility
- TiDB will by default commit every 20 000 rows. This behavior is similar to MySQL NDB Cluster, but not the default configuration with the InnoDB storage engine.