You’re not
sure how to tell
LOAD
DATA
where to look for your datafile,
particularly if it’s located in another directory.
You can load files that are located on the server host or on the
client host from which you issue the LOAD
DATA
statement. By default, the MySQL server assumes that the datafile is
located on the server host. However, that might not be appropriate in
all cases:
If you access the MySQL server from a remote client host and have no means of transferring your file to the server host (such as a login account there), you won’t be able to put the file on the server.
Even if you have a login account on the server host, your
MySQL account must be enabled with the FILE
privilege, and the file to be
loaded must be either world-readable or located in the data
directory for the default database. Most MySQL users do not have
the FILE
privilege (because it
enables them to do dangerous things), and you might not want to
make the file world readable (for security reasons) or be able to
put it in the database directory.
Fortunately, you can load local files that are located on the
client host using LOAD
DATA
LOCAL
rather than LOAD
DATA
. The only permission you need to import
a local file is the ability to read the file yourself. One caveat is
that the LOCAL
keyword might be
disabled by default. You may be able to turn it on using the
--local-infile
option for mysql. If that doesn’t work, your server has
been configured not to allow LOAD
DATA
LOCAL
at all. (Many of the examples in this
chapter assume that LOCAL
can be
used. If that’s not true for your system, you’ll need to adapt these
examples. Omit LOCAL
from the
statement, make sure that the file is located on the MySQL server
host, and specify its pathname using the following rules. For example,
specify the full pathname.)
If the
LOCAL
keyword is not
present in the LOAD
DATA
statement, the MySQL server reads the
datafile. It looks for the file on the server host using the following
rules:
An absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.
A relative pathname is interpreted two ways, depending on whether it has a single component or multiple components. For a single-component filename such as mytbl.txt, MySQL looks for the file in the database directory for the default database. (The statement will fail if you have not selected a default database.) For a multiple-component filename such as xyz/mytbl.txt, MySQL looks for the file beginning in the MySQL data directory. That is, it expects to find mytbl.txt in a directory named xyz.
Database directories are located directly under the server’s
data directory, so these two statements are equivalent if the default
database is cookbook
:
mysql>LOAD DATA INFILE 'mytbl.txt' INTO TABLE mytbl;
mysql>LOAD DATA INFILE 'cookbook/mytbl.txt' INTO TABLE mytbl;
If the LOCAL
keyword is
specified in the LOAD
DATA
statement, your client program looks
for the file on the client host and sends its contents to the server.
The client interprets the pathname the same way your command
interpreter does:
If your file is located on the client host, but you forget to indicate that it’s local, you’ll get an error.
mysql>LOAD DATA 'mytbl.txt' INTO TABLE mytbl;
ERROR 1045 (28000): Access denied for user: 'user_name
@host_name
' (Using password: YES)
That Access
denied
message can be confusing: if you’re
able to connect to the server and issue the LOAD
DATA
statement, it would seem that you’ve already gained access to MySQL,
right? The meaning of the error message is that the MySQL tried to
open mytbl.txt on the server host
and could not access it.
If your MySQL server runs on the host from which you issue the
LOAD
DATA
statement, “remote” and
“local” refer to the same host. But the rules just
discussed for locating datafiles still apply. Without LOCAL
, the server reads the datafile. With
LOCAL
, the client program reads the
file and sends its contents to the server.
mysqlimport uses the same rules for finding files as LOAD
DATA
. By default, it assumes that the
datafile is located on the server host. To indicate that the file is
local to the client host, specify the--local
(or -L
)
option on the command line.
LOAD
DATA
assumes that the table is located in
the default database unless you specify the database name explicitly.
mysqlimport always requires a
database argument:
%mysqlimport --local cookbook mytbl.txt
To use LOAD
DATA
to load a file into a specific database
rather than the default database, qualify the table name with the
database name. The following statement does this, indicating that the
mytbl
table is located in the
other_db
database:
mysql>LOAD DATA LOCAL 'mytbl.txt' INTO TABLE other_db.mytbl;
LOAD
DATA
assumes no relationship between the
name of the datafile and the name of the table into which you’re
loading the file’s contents. mysqlimport assumes a fixed relationship
between the data-file name and the table name. Specifically, it uses
the last component of the filename to determine the table name. For
example, mysqlimport interprets
mytbl,mytbl.txt, mytbl.dat, /tmp/mytbl.txt, /u/paul/data/mytbl.csv, and C:projectsmytbl.txt
all as files containing data for the mytbl
table.
3.145.35.247