Shulou(Shulou.com)06/01 Report--

MySQL exports and imports data

The database export can be used to copy the database to another server. You can transfer the database to a server running on another host, which is the most typical data export task. You can also transfer data to different servers running on the same host. You can do this if you are testing the server against a new version of MySQL and want to use the actual data from the production server. Data can also be loaded into external applications, and data exports can be used to transfer data from one RDBMS to another RDBMS.

The two most common ways to complete export and import operations are:

Z. use SELECT... INTO OUTFILE exports data to a file

Using the LOAD DATA INFILE statement to import data from a file

1.1. Export data using SELECT...INTO OUTFILE

You can use the INTO OUTFILE clause on the SELECT statement to write the result set directly to the file. To use SELECT in this way, place the INTO OUTFILE clause before the FROM clause.

The file name indicates the location of the output file. MySQL writes the file to the specified path on the server host. The output file has the following characteristics: the file will be written to the server host rather than sent to the client over the network. The file cannot already exist. The server will write a new file on the server host.

To run SELECT... INTO OUTFILE statement, you must connect to the server using an account with FILE privileges. MySQL creates files with the following permissions: the account running the MySQL process will have files that are readable to all users.

The file contains a corresponding line for each line selected by the statement. By default, column values are separated by tabs, and lines terminate at newline characters.

Syntax:

SELECT... INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

[export_options]

Export_options:

[{FIELDS | COLUMNS}]

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

1) data file format specifier

SELECT...INTO OUTFILE uses the default data file format TSV, where column values are separated by tabs and records are terminated by line breaks. To use SELECT...INTO OUTFILE to write files that use different delimiters or terminators, use the FIELDS and LINES clauses to specify the output format.

The angular FIELDS clause specifies how the columns are displayed.

L TERMINATED BY specifies the field delimiter, which is a tab by default.

L ENCLOSED BY specifies how to trap column values. The default setting is not to use quotation marks (that is, the default is an empty string).

L ESCAPED BY indicates the escape character to use when representing non-printed characters such as line breaks or tabs. The default escape character is the backslash (\) character.

The angular LINES TERMINATED BY clause specifies the line delimiter, which is the newline character by default.

MySQL uses backslashes to escape special characters, so characters such as newlines and tabs must be represented as "\ n" and "\ t", respectively. Similarly, to represent a backslash character, it must be escaped as follows: "\".

2) escape characters

The command line Terminator includes newline and carriage return / newline pairs. The default newline Terminator is common in Linux systems, while carriage return / newline pairs are common in Windows systems.

ESCAPED BY

The ESCAPED BY clause only controls the output of values in the data file; it does not change the way MySQL interprets special characters in statements. For example, if you specify that the data file escape character is "@" by writing ESCAPED BY'@', it does not mean that you must use "@" to escape other special characters in the statement. You must use the MySQL escape character (backslash:\) to escape the special characters in the statement, using syntax such as LINES TERMINATED BY'\ r\ n'(instead of LINES TERMINATED BY'@ ringing n').

Escape character meaning

\ n NULL

\ 0 NULL (zero) bytes

\ b backspace

\ nWrap

\ r enter

\ s space

\ t Tab character

\ 'single quotation marks

\ "double quotation marks

\\ backslash

All of the above escape characters can be used alone or in longer strings, with the exception of\ N, which is used as a NULL only when it appears alone.

3) examples of usage

Mysql > select * into outfile't 1.tsv 'from T1

ERROR 1290 (HY000): The MySQL server is running with the-- secure-file-priv option so it cannot execute this statement

Mysql > show variables like 'secure%'

+-+

| | Variable_name | Value |

+-+

| | secure_auth | ON |

| | secure_file_priv | / var/lib/mysql-files/ |

+-+

2 rows in set (0.05sec)

Mysql > select * into outfile'/ var/lib/mysql-files/t1.tsv' from T1

Query OK, 7 rows affected (0.01sec)

Note: if secure_file_priv is configured, the export file must be exported to this directory, otherwise an error ERROR 1290 will be reported.

[root] # cat / var/lib/mysql-files/t1.tsv

100 a

200 a

300 a

Mysql > select * into outfile'/ var/lib/mysql-files/t1a.tsv' FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'LINES TERMINATED BY'\ n'

-> from T1

Query OK, 7 rows affected (0.00 sec)

[root] # cat t1a.tsv

100, "a"

200, "a"

300, "a"

[root] #

1.2. Import data using LOAD DATA INFILE

The LOAD DATA INFILE statement reads the values from the data file into the table. LOAD DATA INFILE is SELECT. Reverse operation of INTO OUTFILE. If the data file you are importing contains table data separated by tabs or commas, use the LOAD DATA INFILE command. The most important features of such files are:

N-column value delimiter

N-line delimiter

N the character used to enclose the value (for example: quotation marks)

Whether the column name is specified in the n file

N is there a header indicating which table rows to skip before import

N the location of the file in the file system

N whether appropriate permissions are required to access files

The order of n columns

Whether the number of columns in the n file and the table match

Syntax:

LOAD DATA

[LOW_PRIORITY | CONCURRENT] [LOCAL]

INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[PARTITION (partition_name [, 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)

[, col_name_or_user_var]...)]

[SET col_name= {expr | DEFAULT}]

[, col_name= {expr | DEFAULT}].]

Example:

LOAD DATA INFILE'/ tmp/City.txt' FIELDS TERMINATED BY', 'INTO TABLE City

1) skip or convert input data

Ignore data file lines

To ignore the beginning of the data file, you can use the IGNORE n LINES clause, where n is an integer indicating the number of input lines to ignore. Use this clause when the file starts with a column name row instead of a data value row.

Mysql > LOAD DATA INFILE'/ tmp/City.txt'

-> INTO TABLE City IGNORE 2 LINES

Ignore or convert column values

You can provide user variables in the column list and the optional SET clause, which has a syntax similar to the SET clause in the UPDATE statement. Before inserting the data values read from the file into the table, LOAD DATA INFILE converts them to process the values contained in the user variables. To assign input data columns to user variables instead of table columns, provide the name of the user variable in the form of a column list. If you assign a column to an unused user variable in an SET expression, the statement ignores the value in that column and does not insert it into the table.

LOAD DATA INFILE'/ tmp/City.txt'

INTO TABLE City (@ skip, @ Name,CountryCode, @ District, Population)

SET name=CONCAT (@ Name,'', @ District)

Specify user variables (instead of column names) in the list of statement columns, which ignores the values of unused variables in the SET expression by converting column values using the SET clause (optional).

2) duplicate record

When you use an INSERT or REPLACE statement to add a new row to a table, you can control how the statement handles rows that already have keys in the table. You can allow the statement to generate errors, you can use the IGNORE clause to discard the row, or you can use the ON DUPLICATE KEY UPDATE clause to modify an existing row.

LOAD DATA INFILE provides the same level of control over duplicate rows, that is, by using the two modifier keywords IGNORE (discard the row containing the duplicate key) and REPLACE (replace with the version containing the same key in the file); however, its duplicate handling behavior varies slightly depending on whether the data file is on the server host or on the client host, so when using LOAD DATA INFILE, you must consider the location of the data file.

3) load files from the server host

When loading files located on the server host, LOAD DATA INFILE handles lines that contain duplicate unique keys as follows:

By default, duplicate key violations caused by input records will result in an error; the rest of the data file will not be loaded. The processed records before this point will be loaded into the table.

If you provide the IGNORE keyword after the file name, the new record that caused the duplicate key violation will be ignored and the statement will not generate an error. LOAD DATA INFILE processes the entire file, loads all records that do not contain duplicate keys, and discards the remaining records.

If you provide the REPLACE keyword after the file name, the new record that caused the duplicate key violation will replace any existing record in the table that contains the duplicate key value. LOAD DATA INFILE processes the entire file and loads all the records in the file into the table.

4) load files from the client host

When loading files from the client host, LOAD DATA INFILE ignores records that contain duplicate keys by default. That is, the default behavior is the same as when you specify the IGNORE option. This is because the client / server protocol does not allow you to interrupt the transfer of data files from the client host to the server after the transfer starts, so it is not convenient to abort the operation during the operation.

5) examples of usage

Mysql > use test

Mysql > CREATE TABLE `t1` (

-> `f1` int (11) DEFAULT NULL

-> `f2` varchar (20) DEFAULT NULL

->) ENGINE=InnoDB DEFAULT CHARSET=latin1

-> / *! 50100 PARTITION BY HASH (F1)

-> PARTITIONS 4 * /

Query OK, 0 rows affected (0.17 sec)

Import default format t1.tsv files

Mysql > LOAD DATA local INFILE'/ var/lib/mysql-files/t1.tsv' IGNORE INTO TABLE T1

Query OK, 7 rows affected (0.01sec)

Records: 7 Deleted: 0 Skipped: 0 Warnings: 0

Mysql >

Mysql >

Mysql > select * from T1

+-+ +

| | F1 | f2 | |

+-+ +

| | 100 | a |

| | 200 | a |

| | 300 | a |

| | 400 | a |

| | 1 | a |

| | 101 | a |

| | 111 | b | |

+-+ +

7 rows in set (0.00 sec)

Import t1.tsv files in the specified format

Mysql > LOAD DATA local INFILE'/ var/lib/mysql-files/t1a.tsv' IGNORE INTO TABLE T1

-> FIELDS TERMINATED BY', 'ENCLOSED BY' "'LINES TERMINATED BY'\ n 'ignore 1 lines

Query OK, 6 rows affected (0.00 sec)

Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

Mysql >

Example:

LOAD DATA local INFILE'/ Users/xxx/Downloads/loaddata.txt' IGNORE INTO TABLE testLoadData

FIELDS TERMINATED BY', 'ENCLOSED BY' "'LINES TERMINATED BY'\ n 'ignore 1 lines (username, age, description)

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.