Mysql high-speed import and export of large TXT text

Mysql high-speed import and export of large TXT text
Let's talk about how to use loaddata for mysql to import TXT www.2cto.com. MySQL's load data infile statement is used to read rows from a text file and LOAD rows into a table at a high speed. 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 '\ 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 by' \ '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: find the line boundary at the linefeed and split the line into fields at the locator. Do not expect the field to be enclosed by any quotation mark character. The location character starting with "\", line break, or "\" is interpreted as a field. some literal characters of the value are the opposite, the default value causes the SELECT... into outfile: write a positioning character between fields. Use the "\" Escape Character to escape the positioning character, line feed, or "\" character that appears in the field. Note that the line break should be written at the end of the line, to write fields escaped '\', The read value. You must specify two backslash values. The www.2cto.com 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 created a demo table: SQL code mysql> use db_demo_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) File_AA.txt the 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 generate the following: SQL code mysql> select * from insertdemo; + ------ + ---------------- + | Code | demo | + ------ + ---------------- + | NULL | role | ULL | AAOAQVLJROSBT | ULL | AAHRFTXIBLGYG | ULL | role + ------ + ---------------- + 4 rows set (0.00 sec) let's talk about how to export mysql 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 and then OK the www.2cto.com SQL code -- export data as a text file SELECT demo, code INTO OUTFILE 'd: \ DM_HY_EXPORT.TXT 'fields TERMINATED ', 'optionally ENCLOSED '''Lines terminated by '\ n' FROM insertdemo; fields terminated ', '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.