MySQL's Load data infile_mysql

The LOAD DATA infile statement reads from a text file into a table at a high speed. If you specify a local keyword, read the file from the client host. If local is not specified, the file must be located on the server. (Local is available in MySQL3.22.6 or later versions.) ）

For security reasons, when reading a text file located on a server, the file must be in the database directory or readable by everyone. In addition, in order to use the load DATA INFILE on the server files, you must have file permissions on the server host.

The first time with the load data infile, thinking that just write the INSERT statement into a file, and then use the load data infile the file into the database OK, and then generated a content similar to the same n INSERT INTO table_name (' ID ', ' Name ', ' content ') VALUES (1, "A", "abc"), (2, "B", "ABC"), (3, "C", "abc"); Then execute the LOAD DATA INFILE ' file_name.sql ' into TABLE table_name in PHP; find that there is always an error, really dizzy, do not know how, had to go to the detailed read MySQL application manual:

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 wrote it:
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 wrote it:
LINES terminated by ' \ n '
In other words, when the default value results in reading input, the LOAD DATA infile behaves as follows:
Finding line boundaries at line breaks
To break a row into a field at a locator
Do not expect fields to be encapsulated by any quote character
A locator, line break, or "\" that starts with "\" is interpreted as a partial literal character of a field value

To know that the contents of my SQL are not written according to the default settings of the load data infile, for example, by default, the fields in each INSERT statement are separated by tabs and the content is not encapsulated in any quotation marks (that is, enclosed), but I am separated by commas and enclosed in double quotes. No wonder there is an error.
As a result, the SQL execution statement is written as mysql_query (' Load data local infile ' file_name.sql ' to table ' table_name ' FIELDS terminated by "," enclosed by \ ' "\" escaped by "\\\" LINES terminated by "\ n" Starting by "");
This would be a successful execution, successfully inserted a lot of records into the specified data table, but found a problem, the inserted data is messy, the field and the content to be inserted does not meet my requirements, and there are a lot of the field name to insert into the field inside, alas, Had to go back and read a MySQL in the load data infile usage, finally get to understand, the original file_name.sql inside just need to write the content in a certain format, not the entire SQL execution statements are written in, really stupid! ^_^
So, change the content into
1, "A", "ABC"
2, "B", "ABC"
3, "C", "ABC"
and the contents of the data table and the field in both quantity and order should be strictly corresponding
On the local server (I used the window host) on the test, OK, the operation is successful!
Then upload the program to the network server (Linux host), a implementation, prompt:
Can ' t get stat of ... ' (errcode:13)
At first I thought it was a question of SQL file permissions or MySQL root user rights, later think wrong, MySQL root users are superuser, there must be permissions, the problem is in the SQL file permissions, and then the SQL permissions to 777, after the implementation of the operation or not.
Online search for a moment, there is said to put the file in the/var/lib/mysql on the line, a try, indeed, but I can not in the PHP Web page to put the SQL file generation into the/var/lib/mysql, the mind, finally in the Internet to find a solution:

Using local will be slower than having the server direct access to the file because the contents of the file must be transferred from the client host to the server host. On the other hand, you do not need file permissions to load local files.
You can also use the Mysqlimport utility to load a data file, which operates by sending a load-data infile command to the server. The--local option allows Mysqlimport to read data from the client host. If the client and the server support the compression protocol, you can specify that--compress will achieve better performance on slower networks.

In fact, the method is very simple, that is, the load data infile written in the load data local infile OK.

here is the content supplement:

LOAD DATA [Low_priority | CONCURRENT] [local] INFILE ' File_name.txt '
[REPLACE | IGNORE]
Into TABLE tbl_name
[FIELDS
[Terminated by ' string ']
[[optionally] enclosed by ' char ']
[Escaped by ' char ']
]
[LINES
[Starting by ' string ']
[Terminated by ' string ']
]
[IGNORE number LINES]
[(Col_name_or_user_var,...)]
[SET col_name = expr,...]]

The LOAD DATA INFILE statement reads rows from a text file to a table at a very high speed. The file name must be a literal string.

For more information on the efficiency of insert and load data INFILE and the speed of the load data INFILE, refer to the accelerated INSERT statement in the Administrator's Manual.

The system variable character_set_database indicates that the character set is used to interpret the information in the file. The set NAMES and setting character_set_client do not affect the interpretation of the input.

Users can also use the Import utility to load data files, which are implemented by sending a load data INFILE command to the server. The--local option enables import to read data files from the client host. If the client and server support the compression protocol, the user can specify the--compress option to achieve better performance on a slower network. Refer to the Import-Data import program in the Administrator's Manual.
If the user specifies a keyword low_priority,load the execution of the DATA statement will be deferred until no other client is reading the table.

If a gssys table satisfies a condition that is inserted at the same time (that is, the table has a free block in the middle) and you specify concurrent for the Gssys table, the other threads will retrieve the data from the table when load data is executing. This option also slightly affects the performance of load DATA, even if no other threads are using the table at the same time.

If the local keyword is specified, it interprets the connected client:
• If local is specified, the client component on the client host reads the file and sends it to the server. You can give the full path of the file to determine its exact location. If a relative path is given, the file name is relative to the directory where the client component started.
• If no local is specified, the file is located on the server's host and is read directly by the server.
When you locate a file from a server host, the server uses the following rules:
• If the full path is given, the server uses the path name.
• If a relative path of one or more predecessor components is given, the server searches for files relative to the server's data directory.
• If a filename is given without a predecessor, the server searches for files from the database directory of the current database.
Attention:

These rules mean that a file given by '/gsfile.txt ' is read from the server's data directory, whereas a file given ' Gsfile.txt ' is read from the current database's data directory. For example, the following load DATA statement reads the file ' Data.txt ' from the DB1 database directory, because the DB1 is the current database, and even if the statement explicitly loads the file into the table in the DB2 database, it reads from the DB1 directory:

sqlcli> use DB1;
sqlcli> LOAD DATA INFILE ' data.txt ' into TABLE db2.gs_table;

Attention:

When you specify a Windows path name, you use a slash instead of a backslash. If you want to use a backslash, you must write double.
For security reasons, when reading a text file located on a server, the file must be in the database directory or read by all users. That is, when you execute load DATA INFILE on a file on a server, the user must obtain file permissions.
Refer to the permissions provided by the Gbase in the administrator's Manual.

The local speed is slightly slower than the server's direct access to the file because the contents of the file must be routed through the client to server connection. On the other hand, you do not need to obtain file permissions for local files.

Local can work only if both the server and the client are allowed. For example, if kernel is started, the local cannot work--local-infile=0. Refer to the security issue of the load DATA Local in the administrator's Manual.

The REPLACE and ignore keywords handle input records that duplicate existing primary key values.

If replace is specified, the input line replaces the existing row (that is, rows with the same primary index value as the existing row). Refer to the Replace syntax.

If ignore is specified, the input rows that duplicate the existing row primary key values are skipped. If you do not specify either, the action behavior depends on whether the local keyword is specified. If no local is specified, if a duplicate key value is found, an error is generated and the remainder of the text file is ignored. If local is specified, the default action behavior is the same as specified ignore, because the server has no way to terminate the transfer of the file during the operation.

If you want the foreign KEY constraint to be ignored in the mount operation, you can execute the SET FOREIGN_KEY_CHECKS=0 statement before executing load DATA.
If the user uses load DATA INFILE on an empty Gssys table, all non unique indexes are created in batches (like repair). This can usually make the load DATA INFILE faster when there are many indexes. Normally it's very fast, but there are extreme situations where the user can use alter TABLE before loading the file. DISABLE KEYS to close them and use alter TABLE after loading files ... ENABLE the KEYS to rebuild the index, thereby speeding up index creation. Refer to the accelerated INSERT statement in the Administrator's Manual.

The LOAD DATA INFILE is a select ... The reverse operation of into outfile. Refer to the SELECT syntax. Use SELECT ... into OutFile writes data from a database to a file. Use the load DATA INFILE to read files to the database. The syntax of the fields and lines clauses of the two commands is the same. Two clauses are optional, but if two are specified at the same time, the FIELDS clause must appear before the lines clause.
If the user specifies a fields clause, its clauses (terminated by, [optionally] enclosed by and escaped by) are optional, but the user must specify at least one of them.
If the user does not specify a fields clause, the default is to use the following statement:
FIELDS terminated by ' \ t ' enclosed by ' escaped by ' \ '
If the user does not specify a lines clause, the default is to use the following statement:
LINES terminated by ' \ n ' starting by '
In other words, when the input is read, the default load DATA INFILE behaves as follows:
• Find the boundary of a line at a newline character.
• Do not omit any line prefixes.
• Separates rows into fields at tabs.
• Do not think the field is encapsulated by any quotation mark character.
• A literal character that is interpreted as a field value by a locator, line break, or ' \ ' that begins with "\".
Conversely, when the output is written, the default value causes the Select ... into outfile performance is as follows:
• Insert a tab between field values.
• Encapsulate fields without any quoted characters.
• Use "\" to escape the occurrences of a locator, newline, or ' \ ' character that appears in a field value.
• Add line breaks at the end of the row.
Attention:

In order to write fields escaped by ' \ \, the user must specify two backslashes, which will be read as a backslash.
Attention:

If you are a text file for a Windows system, you may have to use lines terminated by ' \ r \ n ' to read the file because the Windows system features two characters as the line terminator. In some programs, you might use \ r as a line terminator, such as WordPad, when writing a file. When reading this type of file, you need to use lines terminated by ' \ R '.
If all the rows to be read have prefixes that the user would like to ignore, you can use lines starting by ' prefix_string ' to skip the prefix (and any content that precedes it). If a row has no previous prefix, the entire row is skipped. Note that prefix_string may be in the middle of the line!
For example:
sqlcli> LOAD DATA INFILE '/tmp/test.txt '
-> into the TABLE test LINES starting by "XXX";

Use it to read files that contain the following:

xxx "Row", 1
something xxx "Row", 2
You can get the data ("row", 1) and ("Row", 2).

IGNORE number LINES This option can be used to ignore lines at the beginning of a file. For example, you can use ignore 1 LINES to skip the first line that contains a column name:
sqlcli> LOAD DATA INFILE '/tmp/test.txt '
-> into TABLE test IGNORE 1 LINES;
When a user uses a select ... into outfile and load data INFILE writes data from one database to a file and then reads it into the database from the file, the field and row handling options for the two commands must match. Otherwise, the LOAD DATA INFILE will not interpret the file contents correctly. Suppose the user uses Select ... into OutFile writes data to a file in a comma-delimited field:
Sqlcli> SELECT * into outfile ' data.txt '
-> FIELDS terminated by ', '
-> from Table2;
In order to read back a comma-delimited file, the correct statement should be:
sqlcli> LOAD DATA INFILE ' data.txt ' into TABLE table2
-> FIELDS terminated by ', ';
If the user attempts to read the file with the statement shown below, it will not work because the command load DATA INFILE The field value with the locator character:
sqlcli> LOAD DATA INFILE ' data.txt ' into TABLE table2
-> FIELDS terminated by ' t ';
The possible result is that each input row is interpreted as a separate field.
The LOAD DATA INFILE can also be used to read files obtained by external sources. For example, files in DBASE format, fields are separated by commas and surrounded by double quotes. If the line in the file is terminated with a newline character, the following is a description of the field and row processing options that the user will use to load the file:
sqlcli> LOAD DATA INFILE ' data.txt ' into TABLE tbl_name
-> FIELDS terminated by ', ' enclosed by ' '
-> LINES terminated by ' \ n ';

Any field and row handling options can specify an empty string ('). If not empty, the FIELDS [optionally] enclosed by and FIELDS escaped by value must be a single character. FIELDS terminated by and lines terminated by value can exceed one character. For example, to write a line that is terminated by a carriage return line break, or to read a file that contains such a line, you should specify a lines terminated by ' \ r \ n ' clause.
FIELDS [optionally] enclosed by reference to the control field. For the output (SELECT ...). into outfile), if the user omits the word optionally, all fields are surrounded by the enclosed by character. Examples of such an output file (with a comma as a field delimiter) are as follows:
"1", "a string", "100.20"
"2", "a string containing a, comma", "102.20"
"3", "a string containing a \" quote "," 102.20 "
"4", "a string containing a \", Quote and comma ", 102.20"

If the user specifies that the optionally,enclosed by character is used only to wrap fields such as Char,binary,text or enum that contain string types:
1, "A string", 100.20
2, "A string containing a, comma", 102.20
3, "a string containing a \" quote ", 102.20
4, "a string containing a \", Quote and comma ", 102.20
Attention:

The enclosed by character that appears in a field value and is escaped by using the escaped by character as its prefix. Also note that if the user specifies an empty escaped by value, it may produce an output file that cannot be read correctly by the load DATA INFILE. For example, if the escape character is empty, the output shown above becomes the output shown below. Notice the second field in line fourth, which contains a comma followed by a quote that looks like the end of a field:

1, "A string", 100.20
2, "A string containing a, comma", 102.20
3, "A string containing a" quote ", 102.20
4, "A string containing a", quote and comma ", 102.20
For input, the enclosed by character is stripped from the tail of the field value if it exists. (This is true regardless of whether optionally is specified, and optionally does not affect the input interpretation.) If the escaped by character exists before the enclosed by character, it is interpreted as part of the current field value.

If the field starts with a enclosed character, the character instance is considered to terminate a field value, as long as the field or row terminated by sequence is followed. To be clear, if you want to use the enclosed by character in a field, you can repeat it two times, and they will be interpreted as a single enclosed by character processing. For example, if you specify enclosed by ' ", the quotation marks are treated as follows:
"The" "Big" "Boss"-> the "big" boss
The "big" boss-> the "big" boss
The "big" "Boss->", "Big" "Boss

FIELDS escaped by controls how special characters are written or read. If the fields escaped by character is not empty, it is used as a prefix for the following output characters:

Fields escaped by character
fields [optionally] enclosed by character.
fields terminated by and lines terminated by the first character of the by value.
ASCII 0 (ASCII ' 0 ' is actually written after the escape character, not a 0-value byte).
If the fields escaped by character is empty, no characters are escaped and the null value is still exported as null instead of \ n. Specifying an empty escape character may not be a good method, especially if the user's data field value contains any of the characters in the list just characters.
For input values, if the fields escaped by character is not a null character, the character is stripped when it appears, and the following characters are then used as part of the field value. The exception is the escaped ' 0 ' or ' N ' (for example, or \ n, at which point the escape character is ' \ '). These sequences are understood to be ASCII NUL (a 0-valued byte) and null. The rules for null processing are described in the back of this section.
For more "\" Escape syntax information, view the literal values.
In some cases, the field interacts with row handling:
• If lines terminated by is an empty string, FIELDS terminated by IS non-null, the rows end with FIELDS terminated by.
• If the fields terminated by and fields enclosed by value are empty ('), then a fixed row (no delimiter) format will be used. When used in fixed row format, delimiters are not used between fields (but the user still has a row non-terminal). Write and read column values use the display width of the column. For example, if a column is defined as int (7), the value of the column will be written with a 7-character field. For input, the column value is obtained by reading 7 characters.
LINES terminated by is still used to detach rows. If a row does not contain all the fields, the remainder of the column is set to their default values. If the user does not have a row non-terminal, the user should set it as '. In this case, the text file must contain all the fields for each row.
The fixed row format also affects the handling of null values; see below. Note that if a user is using a multibyte character set, the fixed-length format will not work.
There are many processing of NULL values, depending on the fields and lines options used by the user:
• For the default fields and lines values, the output, NULL is written \ n, when read, \ n is read as NULL (assuming the escaped by character is "\").
• If fields enclosed by is not a null value, a field containing a literal word null value is read as a null value. This differs from the word null surrounded by the fields enclosed by character. The word is read as a string ' NULL '.
• If the fields escaped by is empty, the null value is written as a word null.
• Null is written as an empty string when the fixed row format is used (when fields terminated by and fields enclosed by are null values). Note that this causes the null value in the table and the empty string to be indistinguishable when the file is written, because both are written as empty strings. Users should not use a fixed row format if they need to be able to distinguish between the files when they are read and returned.
Some situations that cannot be supported by the load DATA INFILE:
• Fixed-size record lines (FIELDS terminated by and FIELDS enclosed by are empty) and blob or text columns.
• If the user specifies that one delimiter is the same as another, or another prefix, the LOAD DATA INFILE may not interpret the input correctly. For example, the following fields clause will cause a problem:
FIELDS terminated by ' "' enclosed by '"
•
• If fields escaped by is empty, a field value contains fields enclosed by or lines terminated by followed by the value of fields terminated by, which will cause the load DATA infi LE to stop reading a field or row prematurely. This is because the load DATA INFILE does not know where the field or row value ends.
The following example loads all the columns of the Persondata table:
sqlcli> LOAD DATA INFILE ' persondata.txt ' into TABLE persondata;
By default, when a field column is not supplied after the load data INFILE statement, the load data INFILE that the input row contains all the fields in the table column. If the user wants to load some of the columns in the table, specify a field list:
sqlcli> LOAD DATA INFILE ' Persondata.txt '
-> into TABLE persondata (col1,col2,...);
If the input file's field order differs from the order of the columns in the table, the user must also specify a field list. Otherwise, Gbase does not know how to match the input fields with the columns in the table.
The list of columns can contain column names or user variables, and the SET clause is supported. This enables you to assign values to user variables with input values and transform them before assigning the results to the columns.
The user variable in the SET clause has multiple uses. The following example takes the first column of the data file directly as the value of T1.column1 and assigns the second column to a user variable that takes a division before the value of the T2.column2
LOAD DATA INFILE ' file.txt '
into TABLE t1
(Column1, @var1)
SET column2 = @var1/100;
A SET clause can provide a value that is not derived from an input file. The following statement sets Column3 to the current date and time:
LOAD DATA INFILE ' file.txt '
into TABLE t1
(Column1, Column2)
SET column3 = Current_timestamp;
By assigning an input value to a user variable, you can discard this input value and do not assign this value to a column of the table:
LOAD DATA INFILE ' file.txt '
into TABLE t1
(Column1, @dummy, Column2, @dummy, Column3);

Use column/variable lists and set clauses for restrictions:

The assignment column name in the set clause should be only to the left of the assignment operator.
• In Set assignment statements, subqueries can be used. This query returns a value that will be assigned to the column, which may be just a scalar query. You cannot use subqueries to query the tables that will be imported.
• For column/variable lists or SET clauses, rows that are ignored because they use the IGNORE clause are not processed.
• User variables cannot be used when the imported data is in row-fixed format because the user variable does not display a width.
When processing input rows, LOAD DATA divides the rows into fields, and if a column/variable list and SET clause are provided, the values are used according to it. The resulting row is inserted into the table. If this table has before inserts or after insert triggers, they are activated separately before and after the insert row.

If you enter too many fields for the record line, the extra fields are ignored and the number of warnings is increased.
If there are fewer fields in one input row, columns with no input fields will be set to the default values. The default value assignment is described in the Create TABLE syntax.

An empty field value is different from the explanation of the loss of the field value:

• For string types, the column is set to an empty string.
• For numeric types, the column is set to 0.
• For date and time types, the column is set to the "0" value for the column type. Reference date and Time type.

If you explicitly assign an empty string to a string, number, or date or time type in an INSERT or UPDATE statement, the user gets the same result as above.
The timestamp column is set to the current date and time only in two cases. In one case, when the column has a null value (that is, \ n), and the other is (for the first TIMESTAMP column only), the TIMESTAMP column is omitted from the field list when a field manifest is specified.

The LOAD DATA INFILE that all input is a string, so that for an enum or set column, the user cannot set a numeric value as an INSERT statement. All enum and set must be specified as strings!

When the load DATA INFILE query ends, it returns the information string, formatted as follows:
Records:1 deleted:0 skipped:0 warnings:0