Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the import and export method of MySQL table data". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

LOAD DATA INFILE

The LOAD DATA INFILE statement can quickly import text records into a table, SELECT. INTO OUTFILE can import data from a table into a file, and the syntax of the FIELDS and LINES clauses is the same.

The user who executes the statement needs to have FILE permission, and the mysql user can read the data file.

Mysqlimport can also import the text file into the table, and mysqlimport also calls the LOAD DATA INFILE statement to the server, and the-- local option reads the client file.

The LOAD DATA INFILE syntax is as follows:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_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,...]

LOCAL

When local is specified, the client sends read files to the server operating system's temporary directory (non-MySQL temporary directory), which will cause execution to fail if the space is insufficient. If local is not specified, it is read from the server, and the relative path is the path relative to data_dir.

LOAD DATA INFILE: when the data is illegal or the data unique index conflict will cause execution failure. LOAD DATA LOCAL INFILE: because the server cannot terminate the sending of client files, when the data is illegal or the data unique index conflicts, it will be warned to ignore the error to continue, like IGNORE.

REPLACE

A data unique index conflict will replace the existing record

IGNORE

Data unique index conflicts will be ignored. If neither IGNORE nor REPLACE is specified, it depends on the LOCAL option. If local is not specified, the file data after the data is duplicated will not be executed, and if local is specified, conflicts will be ignored to continue execution.

IGNORE number LINES

Specify to skip the first few lines of the file

LOAD DATA INFILE usage:

Default load data infile format (do not specify FIELDS,LINES option) FIELDS TERMINATED BY'\ t 'ENCLOSED BY' 'ESCAPED BY'\\ 'LINES TERMINATED BY'\ n'STARTING BY''Import csv file LOAD DATA INFILE' data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY', 'ENCLOSED BY' "'LINES TERMINATED BY'\ r\ n' IGNORE 1 LINES; Import the file LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY','(column2,column3) containing the specified column The import file sets the value of the specified column LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY','(column1,column2) set column3 = CURRENT_TIMESTAMP;;SELECT... INTO OUTFILE

SELECT... INTO OUTFILE writes the query results to the server, so you need to have FILE permission and write permission to the specified path, and the specified file cannot be an existing file on the server.

The client can generate the specified result file using mysql-e "SELECT..." > file_name.

SELECT... The syntax of FIELDS and LINES clauses of INTO OUTFILE is the same as that of LOAD DATA INFILE.

The OPTION option means the following:

FIELDS TERMINATED BY 'value'

Sets the delimited characters between fields, which can be single or multiple characters. The default is the tab'\ t'.

FIELDS [OPTIONALLY] ENCLOSED BY 'value'

Set the enclosing character of the field, which can only be a single character.

FIELDS ESCAPED BY 'value'

Sets the escape character, which can only be a single character. The default value is'\'.

LINES STARTING BY 'value'

Sets the character at the beginning of each line of data, which can be single or multiple characters. No characters are used by default.

LINES TERMINATED BY 'value'

Sets the character at the end of each line of data, which can be a single or more characters. The default value is'\ ncarriage return windows newline character is'\ r\ n'.

Export a file in csv format: SELECT aformab INTO OUTFILE'/ tmp/result.txt' FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'LINES TERMINATED BY'\ n' FROM test_table

Mysqldump-T path dbname tbl_name can also generate a text file, a tbl_name.sql table definition file and a tbl_name.txt file table data file in the path directory, and you can specify the fields and lines options to specify the format of the generated data file.

This is the end of the introduction of "the method of importing and exporting MySQL data". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.