MySQL LOAD Data INFILE Fast Import

SELECT into OUTFILE

>HelpSelect; Name:'SELECT'Description:syntax:SELECT    [All | DISTINCT | Distinctrow]      [high_priority]      [max_statement_time = N]      [Straight_join]      [Sql_small_result] [Sql_big_result] [Sql_buffer_result]      [Sql_cache | Sql_no_cache] [sql_calc_found_rows]select_expr[, select_expr ...]    [From table_references [PARTITION partition_list]    [WHERE where_condition]    [GROUP by {col_name | expr | position} [ASC | DESC], ...[With ROLLUP]]    [Having where_condition]    [ORDER by {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[Offset,]Row_count|row_count offset (offset}] [PROCEDURE procedure_name (argument_list)]    [into OUTFILE ' file_name ' [CHARACTER SET charset_name]export_options|  intoDumpFile'file_name'      |  intoVar_name[, Var_name]]    [For UPDATE | LOCK in SHARE MODE]]

Example:

> Select *  frome;+------+-------+-------+|Id|FName|LName|+------+-------+-------+| 1669 |Jim|Smith||  337 |Mary|Jones|| 2005 |Linda|Black|+------+-------+-------+> Select *  fromE intoOutFile "/Data/Mysql/E.sql ";
or > select * into outfile '/data/mysql/e.sql ' from e;# cat E.sql1669Jim Smith337Mary Jones2005Linda Black

As you can see, the result of Select...into outfile contains only the table data, which is Tab delimited by default, or you can specify a delimiter:

> Select *  from  into outfile "/data/mysql/by','  ; # cat  e.sql    1669, Jim,smith337, Mary,jones2005 , Linda,black

Note: outfile '/path/file ', in the path need to have MySQL permissions, or will be error:

> Select *  fromT intoOutFile "/Root/Backup/Mysql/T.sql "; ERROR1(HY000): Can't create/write to file'/Root/Backup/Mysql/T.sql'(Errcode:13-permission denied)

LOAD DATA INFILE

The LOAD DATA INFILE statement reads rows from a text file into a table at very high speed.

LOADDATA[low_priority | CONCURRENT] [LOCAL]INFILE'file_name'[REPLACE | IGNORE] into TABLETbl_name[PARTITION (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,...)][SET col_name = expr,...]

Example:

> Delete from E;
> LoadData InFile "/Data/Mysql/E.sql " into TableE Fields terminated by ','; Query OK,3Rows Affected (0.01sec) Records:3Deleted:0Skipped:0Warnings:0> Select *  frome;+------+-------+-------+|Id|FName|LName|+------+-------+-------+| 1669 |Jim|Smith||  337 |Mary|Jones|| 2005 |Linda|Black|+------+-------+-------+

Because the delimiter we specified earlier is ', ', load data also specifies the delimiter, otherwise it will be an error:

> load Data infile "/data/mysql/intotable1265 (01000for column'ID'1

If the data is enclosed by some kind of symbol, you need to specify ' fields enclosed by ':

# cat E.sql " 1669 "  " Jim ""   Smith "

> Load Data infile "/data/mysql/e.sql" into table E;
ERROR 1366 (HY000): Incorrect integer value: ' 1669 ' ' for column ' id ' at row 1

> Load Data infile "/data/mysql/e.sql" into the table e fields enclosed by ' ";
Query OK, 1 row affected (0.01 sec)
Records:1 deleted:0 skipped:0 warnings:0

As shown above, the data is enclosed in double quotes, and if executed directly, an error is made.

There are also some formatting limitations, such as LINES TERMINATED by ' string ', which specifies file line breaks, such as ' \ n '.

MySQL LOAD Data INFILE Fast Import