Mysql high-speed import and export of large-capacity TXT text _ MySQL

Mysql high-speed import and export of large TXT text bitsCN.com

Mysql high-speed import and export of large TXT text

First, let's talk about how to import TXT files to mysql.

The load data infile statement of loaddata and MySQL is used to quickly read rows from a text file and LOAD a table. The load data infile statement reads a table from a text file at a high speed.

Load data parameter configuration:

SQL code

Load data [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt (file path) '[REPLACE | IGNORE]

Into table tbl_name

[FIELDS

[Terminated by '/t']

[OPTIONALLY] enclosed by '']

[Escaped by '//']

[Lines terminated by '/N']

[IGNORE number LINES]

[(Col_name,...)]

If you specify the LOCAL keyword, read the file from the client's LOCAL host. If LOCAL is not specified, the file must be located on the server (remote ). (LOCAL is available in MySQL 3.22.6 or a later version .) If you specify the keyword LOW_PRIORITY, the execution of the load data statement is postponed until no other customers read the table. The REPLACE and IGNORE keywords control repeated processing of existing unique key records. If you specify REPLACE, the new row replaces the existing row with the same unique key value.

If you specify IGNORE, skip the input of duplicate rows of existing rows with a unique key. If you do not specify any option, an error occurs when the duplicate key is found and the remaining part of the text file is ignored. If you use the LOCAL keyword to load data from a LOCAL file, the server cannot stop file transmission during the operation. Therefore, the default behavior is as if IGNORE was specified. Load data infile is the inverse operation of SELECT... into outfile. SELECT syntax. To write DATA from a database to a file, SELECT... into outfile is used. to read the file back to the database, load data infile is used. The syntax of the FIELDS and LINES clauses of the two commands is the same. The two clauses are optional, but if two clauses are specified, FIELDS must be before LINES.

If you specify a FIELDS clause, each of its clauses (terminated by, [OPTIONALLY] enclosed by and escaped by) is optional, except that you must specify at least one of them.

If you do not specify a FIELDS clause, the default value is the same as if you write this statement:

Fields terminated by '/t' enclosed by ''escaped '//'

If you do not specify a LINES clause, the default value is the same as if you write this statement:

Lines terminated by '/N'

In other words, when the default value causes reading input, load data infile performs as follows:

Search for line boundary at line breaks

Split the row into fields at the location operator

Do not expect fields to be enclosed by any quotation marks

The delimiters, linefeeds, or "/" starting with "/" are part of the literal characters of the field value.

Conversely, the default value causes SELECT... into outfile to behave as follows when writing data to the output:

Write a locator between fields

Enclose fields without any quotation marks

Use "/" to escape the location, line break, or "/" character that appears in the field

Line feed at the end of a row

Note: To write fields escaped by '//', you must specify two backslash values for the value read as a single backslash.

The IGNORE number LINES option can be used to IGNORE the header of a column name at the beginning of the file:

Mysql> load data infile "/tmp/file_name" into table test IGNORE 1 LINES;

I have created a demo table:

SQL code

Mysql> use DB_cmd_demo;

Mysql> explain insertdemo;

+ ------- + ------------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + ------------- + ------ + ----- + --------- + ------- +

| Code | varchar (25) | YES | NULL |

| Demo | varchar (15) | NO |

+ ------- + ------------- + ------ + ----- + --------- + ------- +

2 rows in set (0.01 sec)

The File_AA.txt text format is as follows:

XML/HTML code

AAVTUALOJJKEC

AAHRFTXIBLGYG

AAOAQVLJROSBT

AACNKRKBZEDPE

SQL code

Mysql> load data local infile 'd:/File_AA.txt 'into table insertdemo (demo );

Query OK, 4 rows affected (0.00 sec)

Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

The generation is as follows:

SQL code

Mysql> select * from insertdemo;

+ ------ + ---------------- +

| Code | demo |

+ ------ + ---------------- +

| NULL | AACNKRKBZEDPE |

| ULL | AAOAQVLJROSBT

| ULL | AAHRFTXIBLGYG

| ULL | AAVTUALOJJKEC

+ ------ + ---------------- +

4 rows in set (0.00 sec)

Let's talk about how mysql exports data to TXT.

This is a demo on the internet. I will post it here. In fact, the two usage methods are mainly to understand the usage of each parameter.

SQL code

-- Export data as a text file

SELECT demo, code

Into outfile 'd:/DM_HY_EXPORT.TXT'

Fields terminated by ', 'optionally enclosed ''''

Lines terminated by '/N'

FROM insertdemo;

Fields terminated by ',' data FIELDS are separated BY commas

Optionally enclosed by ''' data of each field is enclosed by single quotation marks (note the expression of single quotation marks)

Lines terminated by '/N' each data end with'/N' as a line break.

BitsCN.com