Hướng dẫn load data infile mysql
13.2.7 LOAD DATA Statement
The The
mysqlimport utility provides another way to load data files; it operates by sending a For information about the efficiency of
Input File Character Set The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes. The server interprets the file name using the character set indicated by the
By default, the server interprets the file contents using the character set indicated by the
Note It is not possible to load data files that use the Input File Location These rules determine the
The non-
Note The server also uses the non- Security Requirements For a non-
For a Duplicate-Key and Error Handling The
The If none of In addition to affecting duplicate-key handling as just described,
Field and Line Handling For both the If you specify a If you specify no
Backslash is the MySQL escape character within strings in SQL statements. Thus, to specify a literal backslash, you must specify two backslashes for the value to be interpreted as a single backslash. The escape sequences In other words, the defaults
cause
Conversely, the defaults cause
Note For a text file generated on a Windows system, proper file reading might require If all the input lines have a common prefix that you want to ignore, you can use
If the data file looks like this:
The resulting rows are The
When you use
To read the comma-delimited file, the correct statement is:
If instead you tried to read the file with the statement shown following, it would not work because it instructs
The likely result is that each input line would be interpreted as a single field.
If the input values are not necessarily enclosed within quotation marks, use Any of the field- or line-handling options can specify an empty string ( To read a file containing jokes that are separated by lines consisting of
If you specify
Occurrences of the
For input, the If the field begins with the
In certain cases, field- and line-handling options interact:
Handling of
An attempt to load Some cases are not supported by
Column List Specification The following example loads all columns of the
By default, when no column list is provided at the end of the
You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns. Input Preprocessing Each instance of
User variables in the
The
You can also discard an input value by assigning it to a user variable and not assigning the variable to any table column:
Use of the column/variable list and
Column Value Assignment
To process an input line, Interpretation of field values and assignment to table columns depends on these factors:
Those factors combine to produce restrictive or nonrestrictive data interpretation by
Restrictive data interpretation uses these rules:
By contrast, nonrestrictive data interpretation uses these rules:
For
Partitioned Table Support Concurrency Considerations With the With the Replication Considerations Miscellaneous Topics On Unix, if you
need
Here you must run the command that generates the data to be loaded and the mysql commands either on separate terminals, or run the data generation process in the background (as shown in the preceding example). If you do not do this, the pipe blocks until data is read by the mysql process. |