Load data (SQL), loaddatasql
For insert operations on database tables, we will write programs to execute insert SQL statements, and insert less data. If there is more data. The execution efficiency may be unsatisfactory. The load data statement is used to read data from a text file and load data into a table at a high speed, which is much more efficient than the former. Refer to http://hunan.iteye.com/blog/752606 https://dev.mysql.com/doc/refman/5.6/en/load-data.html and mysql Chinese Document 5.1 to sort out the following, syntax load data [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file _ name' [REPLACE | IGNORE] into table tbl_name [PARTITION (partition_name [, partition_name]...)] [character set charset_name] [{FIELDS | COLUMNS} [terminated by 'string'] [[OPTIONALLY] enclosed by 'Char '] [escaped by 'Char'] [LINES [STARTING BY 'string'] [terminated by 'string'] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var [, col_name_or_user_var]...)] [SET col_name = {expr | DEFAULT}, [, col_name = {expr | DEFAULT}]...]
Parse [LOW_PRIORITY | CONCURRENT] [LOCAL]
- If LOW_PRIORITY is specified, the execution of the load data statement is delayed until no other client reads data from the table.
- If CONCURRENT is specified, if a myisam Table meets the conditions for Simultaneous Insertion (the table has idle blocks in the middle), when load data is being executed, other threads will obtain data from the table again, this option slightly affects the performance of loda data even if no other threads are using this table at the same time.
- If LOCAL is specified, it is considered to be related to the connected client. The file is read from the client and sent to the server.
Note: If the file is on the server where the database is located, the file must be in the database directory or be fully readable (with file Permission) [REPLACE | IGNORE] with a unique key (or primary key) the REPLACE and IGNORE keywords are used to control the operations of these input records.
- If REPLACE is specified, the input row replaces the original row in case of a primary key or a unique key.
- If IGNORE is specified, the input row is skipped in case of a primary key or a unique key.
Note: If the two options are not specified, the two solutions are used. If local is specified, the operation is the same as ignore when a conflict occurs. If local is not specified, an error occurs when a conflict occurs, and the subsequent text is ignored [PARTITION (partition_name [, partition_name]...)] unknown. When [character set charset_name] is added, the connection encoding is specified and parsed, character set utf8 [{FIELDS | COLUMNS} [terminated by 'string'] [[OPTIONALLY] enclosed by 'Char '] [escaped by 'Char '] FIELDS, then (terminated by, [OPTIONALLY] enclosed by and escaped by) specify at least one of them and you can set multiple
- Terminated by xxx specifies the delimiter between attributes
- OPTIONALLY unknown, used and supplemented
- Enclosed by xxx specifies the attribute value's open and closed tag
- Escaped by xxx specifies the Escape Character prefix
NOTE: If FIELDS is not specified, the default value is fields terminated by '\ t' enclosed by ''escaped '\\'. The attribute is not open or closed, and the delimiter between attribute values is a tab, escape Character Prefix: Use the backslash [LINES [starting by 'string'] [terminated by 'string']. If LINES are specified, (starting by, terminated) specify at least one of them. You can set multiple
- Starting by xxx If the row to be read has a common prefix and is read from the beginning of the prefix, you can set it. If a row does not contain a common prefix, the row is skipped.
- Terminated by xxx line terminator
NOTE: If LINES are not specified, the default value is lines terminated by '\ n' starting ''. In general, the line break is used as the row Terminator, and there is no common prefix LINES and FIELDS. If both are specified, FIELDS needs to be placed before LINES [IGNORE number {LINES | ROWS}] to ignore rows at the beginning of your price inquiry, for example, you can use IGNORE 1 LINES to skip a start header line containing the column name [(col_name_or_user_var [, col_name_or_user_var]...)] specify the list of attributes to be inserted, such as (name, age) [SET col_name = {expr | DEFAULT}, [, col_name = {expr | DEFAULT}]...] SET can be used to specify the value of an attribute load data infile xxx into table xxx (column1, column2) when setting load data for values other than source input files) set column3 = CURRENT_TIMESTAMP; assign values to user variables for the content in the input file, and then assign values to a property load data infile xxx into table xxx (column1, column2, @ var, column4) set column3 = @ var/100; instance creation test table create table 'test' ('id' int (11) not null AUTO_INCREMENT, 'name' varchar (255) not null default '', 'age' int (11) not null default '0', 'addtime' datetime not null, 'timestamp' timestamp not null default '2017-00-00 00:00:00 'on update CURRENT_TIMESTAMP, primary key ('id') ENGINE = InnoDB default charset = utf8; create the data file 'hehe ''12''' pe ter''12'' sta \ 'rof''12'' ali Guiyang ce ''12'' te \ st'' 12 ''y \ a \ n''12'' lv \ t China p''12'' p \ 'vl '12'' ya \ Guizhou n ''14'' ya \ n Guizhou n''14'' ya \ Guizhou n''14'' ya \ Guizhou n''14'' l \ vp '13' p \ tvl '18' console connection mysql execute load dataload data infile 'd: /data.txt 'into table test character set utf8 fields terminated by '\ t' enclosed by "'" escaped by '\ 'Lines terminated by' \ n' (NAME, AGE) set ADDTIME = CURRENT_TIMESTAMP;
Execution result
