LOAD DATA INFILE 'text_file_name.text' [REPLACE |
IGNORE] INTO TABLE table_name [FIELDS
[TERMINATED BY ',' [OPTIONALLY] ENCLOSED BY '"'
ESCAPED BY '\\' ]] [LINES TERMINATED BY '\n'] [(Field1,
Field2...)]
This is used to read rows from a text file, which must be located on the
server, at a very high speed. The server-client protocol doesn't yet
support files over a connection. If you only have the file on the
client, use rcp or ftp to copy it, possibly compressed,
to the server before using LOAD DATA INFILE. All paths to the text
file are relative to the database directory.
To write data to a text file, use the SELECT ... INTO OUTFILE
'interval.txt' fields terminated by ',' enclosed by '"' escaped by '\\'
lines terminated by '\n' FROM ... syntax.
Normally you don't have to specify any of the text file type options. The
default is a compact text file with columns separated with tab
characters and all rows end with a newline. Tabs, newlines and \
inside fields are prefixed with a \. NULLs are read and written as
\N.
FIELDS TERMINATED BY has the default value of \t.
FIELDS [OPTIONALLY] ENCLOSED BY has the default value of ".
FIELDS ESCAPED BY has the default value of '\\'.
LINES TERMINATED BY has the default value of '\n'.
FIELDS TERMINATED BY and LINES TERMINATED BY may be more
than one character.
If LINES TERMINATED BY is an empty string and FIELDS
TERMINATED BY is non-empty then lines are also terminated with
FIELDS TERMINATED BY.
If FIELDS TERMINATED BY and FIELDS ENCLOSED BY both are
empty strings (") then this gives a fixed row format ("not delimited"
import format). With a fixed row size NULL values are output as a
blank string.
If you specify OPTIONALLY in ENCLOSED BY, then only
strings are enclosed in ENCLOSED BY by the SELECT ... INTO
statement.
Duplicated ENCLOSED BY chars are removed from strings that start
with ENCLOSED BY. For example: With ENCLOSED BY '"':
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss
If ESCAPED BY is not empty then the following characters will be
prefixed with the escape character: ESCAPED BY, ASCII 0,
and the first character in any of FIELDS TERMINATED BY,
FIELDS ENCLOSED BY and LINES TERMINATED BY.
If FIELDS ENCLOSED BY is not empty then NULL is read as a
NULL value. If FIELDS ESCAPED BY is not empty then
\N is also read as a NULL value.
If REPLACE is used, then the new row will replace all rows which
have the same unique index. If IGNORE is used, the row will then
be skipped if a record already exists with an identical unique key.
If none of the above options are used an error will be issued. The
rest of the text file will be ignored if one gets a duplicate index error.
Some possible cases that are not supported by LOAD DATA:
FIELDS TERMINATED BY and FIELDS ENCLOSED
BY both are empty) and BLOB columns.
FIELDS ESCAPED BY is empty and the data contains LINES
TERMINATED BY or FIELDS ENCLOSED BY followed by FIELDS
TERMINATED BY.
All rows are read into the table. If a row has too few columns, the rest
of the columns are set to default values. TIMESTAMP columns are only
set to the current time if there is a NULL value for the column or if
the TIMESTAMP column is left out from the field list when the field
list is used (the last case only holds for the first TIMESTAMP column).
For security reasons the text file must either reside in the database
directory or be readable by all. Each user that wants to use
LOAD DATA INFILE must also have 'Y' in the 'File_priv' column
in the user privilege table!
See section How does the privilege system work?
Because LOAD DATA INFILE regards all input as strings you can't use
number values for enum or set columns as you can with
INSERT statements. All enum and set must be given as
strings!
For more information about the escaped syntax, See section Literals. How do you write strings and numbers?.
When the LOAD DATA query is done, one can get the following info
string with the C API function mysql_info().
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Warnings are incremented for each column which can't be stored without loss of precision, for each column which didn't get a value from the read text line (happens if the line is too short) and for each line which has more data than can fit into the given columns. A warning is also given for any time, date, timestamp or datetime column that is set to 0.
An example that loads all columns:
LOAD DATA INFILE 'persondata.text' INTO TABLE persondata;
See section How should I arrange my table to be as fast/small as possible?
Go to the first, previous, next, last section, table of contents.