Specifying the Datafile Location

10.3.1 Problem

You're not sure how to tell LOAD DATA where to look for your datafile, particularly if it's located in another directory.

10.3.2 Solution

It's a matter of knowing the rules that determine where MySQL looks for the file.

10.3.3 Discussion

When you issue a LOAD DATA statement, the MySQL server normally assumes the datafile is located on the server host. However, you may not be able to load data that way:

Fortunately, if you have MySQL 3.22.15 or later, you can load local files that are located on the client host by 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.[2]

[2] As of MySQL 3.23.49, use of the LOCAL keyword may 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.

If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using the following rules:

Database directories are located directly under the data directory, so these two statements are equivalent if the current 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, MySQL looks for the file on the client host, and 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: Access denied for user: 'cbuser@localhost' (Using password: YES)

That Access denied message can be confusing, given that 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. What the error message means is that the MySQL tried to open mytbl.txt on the server host and could not access it.

mysqlimport uses the same rules for finding files as LOAD DATA. By default, it assumes the datafile is located on the server host. To use a local file, specify the --local (or -L) option on the command line.

LOAD DATA assumes the table is located in the current database unless you specify the database name explicitly. mysqlimport always requires a database argument:

% mysqlimport --local cookbook mytbl.txt

If you want to use LOAD DATA to load a file into a database other than the current one, you can 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 datafile name and the table name. Specifically, it uses the last component of the filename to determine the table name. For example, mysqlimport would interpret mytbl.txt, mytbl.dat, /tmp/mytbl.txt, /u/paul/data/mytbl.csv, and D:projectsmytbl.txt all as files containing data for the mytbl table.

Naming Datafiles Under Windows

Windows systems use as the pathname separator in filenames. That's a bit of a problem, because MySQL interprets backslash as the escape character in string values. To specify a Windows pathname, either use doubled backslashes, or use forward slashes instead. These two statements show two ways of referring to the same Windows file:

mysql> LOAD DATA LOCAL INFILE 'D:\projects\mydata.txt' INTO mytbl;

mysql> LOAD DATA LOCAL INFILE 'D:/projects/mydata.txt' INTO mytbl;

Категории