Mysqloutfileinfile import and export data (append mysqldump) _ MySQL

Mysqloutfileinfile import and export data (append mysqldump) mysqldump

BitsCN.com

Mysql outfile infile import and export data (append mysqldump)

Export

[Plain]

SELECT * into outfile '/tmp/jason.txt 'Fields TERMINATED by',' FROM test. jason;

Or

[Plain]

SELECT * into outfile '/tmp/jason1.txt' fields terminated by ', 'optionally enclosed by' "'lines terminated by '/N' FROM test. jason1;

Output:

"00:06:56", "100000198108800", "EXPORT_INFO", "BUY_ITEM_INNER", 100, "11", "2 ",

"00:06:59", "1068029027", "EXPORT_INFO", "BUY_ITEM_INNER", "11", "2", 70

"00:08:27", "100000198108800", "EXPORT_INFO", "BUY_ITEM_INNER", "11", "2", 20

Import

[Plain]

Load data infile '/tmp/jason.txt' into table aa. jason fields terminated ',';

Or

[Plain]

Load data infile '/tmp/jason1.txt' into table aa. jason1 fields terminated by ', 'optionally enclosed by' "'lines terminated by '/N ';

Summary:

Fields terminated by ',' delimiter between FIELDS

Optionally enclosed by '"' does not apply a numeric value when fields are surrounded.

Lines terminated by '/N' line break

Mysqldump also has the same function

[Plain]

Mysqldump-uroot-p-T/tmp test fi -- fields-enclosed-by =/"-- fields-terminated-by ="/t"

Fi is the name of the exported file.

Test is the name of the database to be exported.

-T/tmp is the location of the exported Directory

-- Fields-enclosed-by =/"indicates that every data is enclosed in double quotation marks.

-- Fields-terminated-by = "/t" indicates that each data is separated by a tab.

Here are some parameters for mysqldump.

-- Lock-all-tables,-x submits a request to lock all tables before starting data export to ensure data consistency. this is a global read lock, the -- single-transaction and -- lock-tables options are automatically disabled. after the backup is complete, the session is disconnected and automatically unlocked.

-- Lock-tables,-l is similar to -- lock-all-tables, but it refers to locking the current data table to be exported, rather than locking the entire database table all at once, this option is only applicable to MyISAM tables. if it is an innodb table, you can use the -- single-transaction option.

-- Single-transaction InnoDB table during backup, the option -- single-transaction is usually enabled to ensure backup consistency. In fact, its working principle is to set the isolation level of this session to repeatable read, to ensure that the data submitted by other sessions is not displayed during this session (dump. It is only applicable to transaction tables, such as Innodb or BDB. to export a large table, use the -- quick option.

-- No-create-info,-t only exports data, without adding the create table statement

-- No-data,-d: only the table structure is exported without exporting any table data.

BitsCN.com