MySQL LOAD Data INFILE Fast Import

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.