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