LOAD DATA INFILE
Syntax
1072
VALUES(col_name)
[968]
in the
UPDATE
clause refers to the value of
col_name
that would be
inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row
inserts. The
VALUES()
[968]
function is meaningful only in
INSERT ... UPDATE
statements and
returns
NULL
otherwise. Example:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
That statement is identical to the following two statements:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;
If a table contains an
AUTO_INCREMENT
column and
INSERT ... UPDATE
inserts a row, the
LAST_INSERT_ID()
[961]
function returns the
AUTO_INCREMENT
value. If the statement updates
a row instead,
LAST_INSERT_ID()
[961]
is not meaningful. However, you can work around this by
using
LAST_INSERT_ID(expr)
[961]
. Suppose that
id
is the
AUTO_INCREMENT
column. To make
LAST_INSERT_ID()
[961]
meaningful for updates, insert rows as follows:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
The
DELAYED
option is ignored when you use
ON DUPLICATE KEY UPDATE
.
13.2.6.
LOAD DATA INFILE
Syntax
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '
file_name
'
[REPLACE | IGNORE]
INTO TABLE
tbl_name
[CHARACTER SET
charset_name
]
[{FIELDS | COLUMNS}
[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 into a table at a very high speed.
LOAD DATA INFILE
is the complement of
SELECT ... INTO OUTFILE
. (See
Section 13.2.8.1,
“
SELECT ... INTO
Syntax”
.) To write data from a table to a file, use
SELECT ... INTO OUTFILE
.
To read the file back into a table, use
LOAD DATA INFILE
. The syntax of the
FIELDS
and
LINES
clauses is the same for both statements. Both clauses are optional, but
FIELDS
must precede
LINES
if
both are specified.
You can also load data files by using the
mysqlimport
utility; it operates by sending a
LOAD DATA
INFILE
statement to the server. The
--local
[311]
option causes
mysqlimport
to read data files
from the client host. You can specify the
--compress
[310]
option to get better performance over slow
networks if the client and server support the compressed protocol. See
Section 4.5.5, “
mysqlimport
— A Data Import Program”
.
For more information about the efficiency of
INSERT
versus
LOAD DATA INFILE
and speeding up
LOAD DATA INFILE
, see
Section 8.3.2.1, “Speed of
INSERT
Statements”
.
The file name must be given as a literal string. On Windows, specify backslashes in
path names as forward slashes or doubled backslashes. As of MySQL 5.0.19, the
character_set_filesystem
[442]
system variable controls the interpretation of the file name.
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 ...
