Oracle 5.0, Справочное руководство

Страница: 1093 / 3232

LOAD DATA INFILE
Syntax
1073
The character set indicated by the
character_set_database
[442] system variable is used to
interpret the information in the file.
SET NAMES
and the setting of
character_set_client
[441] do
not affect interpretation of input. If the contents of the input file use a character set that differs from the
default, it is usually preferable to specify the character set of the file by using the
CHARACTER SET
clause, which is available as of MySQL 5.0.38. A character set of
binary
specifies “no conversion.”
LOAD DATA INFILE
interprets all fields in the file as having the same character set, regardless of the
data types of the columns into which field values are loaded. For proper interpretation of file contents,
you must ensure that it was written with the correct character set. For example, if you write a data file
with
mysqldump -T
or by issuing a
SELECT ... INTO OUTFILE
statement in
mysql
, be sure to
use a
--default-character-set
option so that output is written in the character set to be used
when the file is loaded with
LOAD DATA INFILE
.
Note that it is currently not possible to load data files that use the
ucs2
character set.
If you use
LOW_PRIORITY
, execution of the
LOAD DATA
statement is delayed until no other clients
are reading from the table. This affects only storage engines that use only table-level locking (such as
MyISAM
,
MEMORY
, and
MERGE
).
If you specify
CONCURRENT
with a
MyISAM
table that satisfies the condition for concurrent inserts (that
is, it contains no free blocks in the middle), other threads can retrieve data from the table while
LOAD
DATA
is executing. This option affects the performance of
LOAD DATA
a bit, even if no other thread is
using the table at the same time.
CONCURRENT
is not replicated. See Section 16.4.1.10, “Replication and
LOAD
Operations” , for more
information.
The
LOCAL
keyword affects expected location of the file and error handling, as described later.
LOCAL
works only if your server and your client both have been configured to permit it. For example, if
mysqld
was started with
--local-infile=0
[462] ,
LOCAL
does not work. See Section 6.1.6, “Security Issues
with
LOAD DATA LOCAL
” .
The
LOCAL
keyword affects where the file is expected to be found:
• If
LOCAL
is specified, the file is read by the client program on the client host and sent to the server.
The file can be given as a full path name to specify its exact location. If given as a relative path
name, the name is interpreted relative to the directory in which the client program was started.
When using
LOCAL
with
LOAD DATA
, a copy of the file is created in the server's temporary directory.
This is not the directory determined by the value of
tmpdir
[502] or
slave_load_tmpdir
[1465] ,
but rather the operating system's temporary directory, and is not configurable in the MySQL Server.
(Typically the system temporary directory is
/tmp
on Linux systems and
C:\WINDOWS\TEMP
on
Windows.) Lack of sufficient space for the copy in this directory can cause the
LOAD DATA LOCAL
statement to fail.
• If
LOCAL
is not specified, the file must be located on the server host and is read directly by the
server. The server uses the following rules to locate the file:
• If the file name is an absolute path name, the server uses it as given.
• If the file name is a relative path name with one or more leading components, the server searches
for the file relative to the server's data directory.
• If a file name with no leading components is given, the server looks for the file in the database
directory of the default database.
In the non-
LOCAL
case, these rules mean that a file named as
./myfile.txt
is read from the
server's data directory, whereas the file named as
myfile.txt
is read from the database directory of
the default database. For example, if
db1
is the default database, the following
LOAD DATA
statement
reads the file
data.txt
from the database directory for
db1
, even though the statement explicitly
loads the file into a table in the
db2
database: