MySQL Tens data import and export

Recently requested by the company, in doing database import export. The amount of data per day is about 350W. After a lot of testing, I feel the following method is better

Data export :SELECT into ... OUTFILE

Basic syntax:

SELECT

[IntoOUTFILE ' file_name '

[CHARACTER SET charset_name]

Export_options

| into dumpfile ' file_name '

| Into var_name [, Var_name]]

The statement is divided into two parts. The first half is a normal SELECT statement that queries the required data through this SELECT statement, and the latter part exports the data. Where the target file parameter indicates which file the query is exported to, and the option parameter is an optional parameter, the possible values are:

• • Field TERMINATED by ' string ': Sets the delimiter between the fields for the string, which can be single or multiple characters. The default value is "\ t".
  • Fields enclosed by ' character ': sets the character to enclose the value of a field, only for a single character. By default, no symbols are used.
  • field optionally enclosed by ' character ': set characters to enclose character fields such as char, varchar, and text. By default, no symbols are used.
  • Fields escaped by ' character ': Sets the escape character, only for a single character. The default value is "\".
  • LINES starting by ' string ': Sets the character at the beginning of each line of data, which can be single or multiple characters. By default, no characters are used.
  • LINES TERMINATED by ' string ': Sets the character at the end of each line of data, which can be single or multiple characters. The default value is "\ n". Fields and lines two clauses are optional, but if two clauses are specified, fields must be in front of the lines.

Example :

SELECT * into OUTFILE ' e:/data.txt ' fields TERMINATED by ', ' optionally enclosed by ' ' \ LINES TERMINATED by ' \ n ' from DL.D Atas

Export results:

　　　　　　　

data import :load the data infile

Basic syntax: Load data [low_priority] [local] infile ' file_name txt ' [replace | ignore]
into table Tbl_name
[Fields
[Terminated by ' t ']
[optionally] enclosed by ']
[escaped by ']]
[Lines terminated by ' n ']
[Ignore number lines]
[(Col_name,)]

The load Data infile statement reads from a text file into a table at a high speed. Before using this command, the MYSQLD process (service) must already be running. For security reasons, when reading a text file located on the server, the file must be in the database directory or read by everyone. Also, in order to use the load data infile on the files on the server, you must have file permissions on the server host.

1 If you specify the keyword low_priority, then MySQL will wait until no one else reads the table before inserting the data. You can use the following command:

Load data low_priority infile "/home/mark/data sql" into table Orders;

2 If the local keyword is specified, the file is read from the client host. If local is not specified, the file must be located on the server.

3 Replace and ignore keyword controls duplicate processing of existing unique key records. If you specify replace, the new row replaces the existing rows that have the same unique key value. If you specify ignore, skip the input of the duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored. For example: Load data low_priority infile "/home/mark/data SQL" replace into table Orders;

4 separators

(1) The fields keyword specifies the segmentation format of the file segment, and if this keyword is used, the MySQL profiler wants to see at least one of the following options:
Terminated by delimiter: What characters are used as separators
Enclosed by field character
Escaped by escape character terminated by describes the delimiter of the field, which by default is the tab character (\ t)
Enclosed by describes the enclosed character of a field.
Escaped by describes the escape character. The default is a backslash (backslash:\)

Example :

Load Data low_priority local infile "e:/data.txt" replace into table datas fields terminated by ', ' enclosed by ' "' escape D by ' "' lines terminated by ' \ n ';

Import and export of MySQL tens data