Load_data _infile and select INTO outfile description

1.SELECT ... into outfile reads a text file that is stored in a data row from a table.

The 2.LOAD DATA infile statement is used to read rows from a text file at high speed and to mount a table.

3. Grammar rules
A) SELECT * into outfile '/root/outfile11.txt ' fields terminated by ', ' enclosed by ' "' escaped by ' \ ' lines terminated b Y ' n ' from table
(1) fields terminated by indicates what symbols are delimited between the data columns read, such as Field1, Field2, field3,....

(2) Enclosed by means that this string is added to the end and end of each column. For example: (Enclosed by ' "") each listed as "Field1", "Field2", "field3",
This is true whether it is an integer or a string type. All errors occurred while importing back to the reshaping.

(3) The function of escaped by escaped by is this.
* Escaped by can be followed by a string, but it takes only the first character.
* If you find a column with the same character in the string. Then add a same character after this character (invalid for integer)
* Special case, if the field is null exported data column will also add this character
For example:
Db_table:
ID Name
1 ABC
2 AaB
3 <NULL>
Set escaped by ' a '
The results of the export are:
ID Name
1 AABC
2 Aaab
3 an
\ n represents <NULL> in export data
This feature is designed to address the existence and null columns of the "\" character in the database and the characters that appear in the Enclosed,fields terminated.
Special handling of empty strings

For example:
Db_table:
ID Name
1 ABC
2 AaB
3 <NULL>
If you set escaped by '
The results of the export are:
ID Name
1 AABC
2 Aaaab
3 NULL
This way, it doesn't convert to \ n, directly converts <NULL> into a NULL string

(4) Lines terminated by means how to separate the next line after each row ends.

For example: lines terminated by ' \ n ' each line separated by a carriage return

Note: If it is windows, you need to use ' \ r \ n ' to represent a carriage return separator


(5) The default is FIELDS terminated by ' \ t ' enclosed by ' escaped by ' \ \ LINES terminated by ' \ n ' starting by '

(6) Here I did not use starting by ' this representation, ignoring some characters at the beginning of the field, reading the data.

For example:
Starting by ' ASDF '
DB in fields Asdfgrwew read text is: GRWEW


b) Load data infile '/root/outfile32.txt ' into table test fields terminated by ', ' enclosed by ' "' escaped by ' \ ' lines te rminated by ' \ n '

(1) fields terminated by with the "," number as the column of the separator. Put each column in the database.

(2) Enclosed by removes a character between each column, such as "field1", after the database is imported: field1

(3) escaped by if a column is found to have the same character in the string. Remove one such character (invalid for integer)

(4) Lines terminated by means to read the next line in this form

(5) The default is FIELDS terminated by ' \ t ' enclosed by ' escaped by ' \ \ LINES terminated by ' \ n ' starting by '


4.load date infile with SELECT. Into outfile is the best format to match exactly. Otherwise there will be a lot of unexpected things happen.