LOAD DATA INFILE
Syntax
1074
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
For security reasons, when reading text files located on the server, the files must either reside in the
database directory or be readable by all. Also, to use
LOAD DATA INFILE
on server files, you must
have the
FILE
[577]
privilege. See
Section 6.2.1, “Privileges Provided by MySQL”
. For non-
LOCAL
load
operations, if the
secure_file_priv
[490]
system variable is set to a nonempty directory name, the
file to be loaded must be located in that directory.
Using
LOCAL
is a bit slower than letting the server access the files directly, because the contents of the
file must be sent over the connection by the client to the server. On the other hand, you do not need
the
FILE
[577]
privilege to load local files.
LOCAL
also affects error handling:
• With
LOAD DATA INFILE
, data-interpretation and duplicate-key errors terminate the operation.
• With
LOAD DATA LOCAL INFILE
, data-interpretation and duplicate-key errors become warnings
and the operation continues because the server has no way to stop transmission of the file in the
middle of the operation. For duplicate-key errors, this is the same as if
IGNORE
is specified.
IGNORE
is explained further later in this section.
The
REPLACE
and
IGNORE
keywords control handling of input rows that duplicate existing rows on
unique key values:
• If you specify
REPLACE
, input rows replace existing rows. In other words, rows that have the same
value for a primary key or unique index as an existing row. See
Section 13.2.7, “
REPLACE
Syntax”
.
• If you specify
IGNORE
, input rows that duplicate an existing row on a unique key value are skipped.
• If you do not specify either option, the behavior depends on whether the
LOCAL
keyword is specified.
Without
LOCAL
, an error occurs when a duplicate key value is found, and the rest of the text file is
ignored. With
LOCAL
, the default behavior is the same as if
IGNORE
is specified; this is because the
server has no way to stop transmission of the file in the middle of the operation.
To ignore foreign key constraints during the load operation, issue a
SET foreign_key_checks = 0
statement before executing
LOAD DATA
.
If you use
LOAD DATA INFILE
on an empty
MyISAM
table, all nonunique indexes are created in a
separate batch (as for
REPAIR TABLE
). Normally, this makes
LOAD DATA INFILE
much faster when
you have many indexes. In some extreme cases, you can create the indexes even faster by turning
them off with
ALTER TABLE ... DISABLE KEYS
before loading the file into the table and using
ALTER TABLE ... ENABLE KEYS
to re-create the indexes after loading the file. See
Section 8.3.2.1,
“Speed of
INSERT
Statements”
.
For both the
LOAD DATA INFILE
and
SELECT ... INTO OUTFILE
statements, the syntax of the
FIELDS
and
LINES
clauses is the same. Both clauses are optional, but
FIELDS
must precede
LINES
if both are specified.
If you specify a
FIELDS
clause, each of its subclauses (
TERMINATED BY
,
[OPTIONALLY] ENCLOSED
BY
, and
ESCAPED BY
) is also optional, except that you must specify at least one of them.
If you specify no
FIELDS
or
LINES
clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
(Backslash is the MySQL escape character within strings in SQL statements, so to specify a literal
backslash, you must specify two backslashes for the value to be interpreted as a single backslash. The
escape sequences
'\t'
and
'\n'
specify tab and newline characters, respectively.)
In other words, the defaults cause
LOAD DATA INFILE
to act as follows when reading input:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...