|< Day Day Up >|
9.1 The LOAD DATA INFILE Statement
LOAD DATA INFILE provides an alternative to INSERT for adding new records to a table. With INSERT, you specify data values directly in the INSERT statement. LOAD DATA INFILE reads the values from a separate datafile.
The simplest form of the LOAD DATA INFILE statement specifies only the name of the datafile and the table into which to load the file:
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
The filename is given as a string and must be quoted. MySQL assumes, unless told otherwise, that the file is located on the server host, that it has the default file format (tab-delimited and newline-terminated lines), and that each input line contains a value for each column in the table. However, LOAD DATA INFILE has clauses that give you control over each of those aspects of data-loading operations and more:
The syntax for LOAD DATA INFILE is as follows, where optional parts of the statement are indicated by square brackets:
LOAD DATA [LOCAL] INFILE 'file_name' [IGNORE | REPLACE] INTO TABLE table_name format_specifiers [IGNORE n LINES] [(column_list)]
9.1.1 Specifying the Datafile Location
The rules for interpreting the filename are somewhat different for the server host and the client host.
18.104.22.168 Specifying the Location of Files on the Server Host
Suppose that the server's data directory is /var/mysql/data, the database directory for the test database is /var/mysql/data/test, and the file data.txt is located in that database directory. Using the filename interpretation rules just given, it's possible to refer to the data.txt file three different ways in a LOAD DATA INFILE statement:
22.214.171.124 Specifying the Location of Files on the Client Host
Suppose that there's a datafile named data.txt located in the /var/tmp directory on the client host and you invoke the mysql program while located in that directory. You can load the file into a table t using either of these two statements:
LOAD DATA LOCAL INFILE '/var/tmp/data.txt' INTO TABLE t; LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t;
The first statement names the file using its full pathname. The second names the file relative to the current directory. If you invoke the mysql program in the /var directory instead, you can still load the file using the same full pathname. However, the relative pathname to the file is different than when running the program in the /var/tmp directory:
LOAD DATA LOCAL INFILE 'tmp/data.txt' INTO TABLE t;
126.96.36.199 Specifying Filenames on Windows
On Windows, the pathname separator character is \, but MySQL treats the backslash as the escape character in strings. To deal with this issue, write separators in Windows pathnames either as / or as \\. To load a file named C:\mydata\data.txt, specify the filename as shown in either of the following statements:
LOAD DATA INFILE 'C:/mydata/data.txt' INTO TABLE t; LOAD DATA INFILE 'C:\\mydata\\data.txt' INTO TABLE t;
9.1.2 Loading Specific Table Columns
By default, LOAD DATA INFILE assumes that data values in input lines are present in the same order as the columns in the table. If the datafile contains more columns than the table, MySQL ignores the excess data values. If the datafile contains too few columns, each missing column is set to its default value in the table. (This is the same way MySQL handles columns that aren't named in an INSERT statement.)
If input lines don't contain values for every table column, or the data values are not in the same order as table columns, you can add a comma-separated list of column names within parentheses at the end of the LOAD DATA INFILE statement. This tells MySQL how columns in the table correspond to successive columns in the datafile. A list of columns is useful in two ways:
9.1.3 Skipping Datafile Lines
To ignore the initial part of the datafile, use the IGNORE n LINES clause, where n is the number of input lines to skip. This clause is commonly used when a file begins with a row of column names rather than data values. For example, to skip the first input line, a statement might be written like this:
LOAD DATA INFILE 'data.txt' INTO TABLE t IGNORE 1 LINES;
9.1.4 LOAD DATA INFILE and Duplicate Records
When you add new records to a table with an INSERT or REPLACE statement, you can control how to handle new records containing values that duplicate unique key values already present in the table. You can allow an error to occur, ignore the new records, or replace the old records with the new ones. LOAD DATA INFILE affords the same types of control over duplicate records by means of two modifier keywords. However, its duplicate-handling behavior differs slightly depending on whether the datafile is on the server host or the client host, so you must take the datafile location into account.
IGNORE and REPLACE are mutually exclusive. You can specify one or the other, but not both.
For datafiles located on the client host, duplicate unique key handling is similar, except that the default is to ignore records that contain duplicate keys rather than to terminate with an error. That is, the default is as though the IGNORE modifier were specified. The reason for this is that the client/server protocol doesn't allow transfer of the datafile from the client host to the server to be interrupted after it has started, so there's no convenient way to abort the operation in the middle.
9.1.5 Interpreting the Result of a LOAD DATA INFILE Statement
As LOAD DATA INFILE executes, it keeps track of the number of records processed and the number of data conversions that occur. Then it returns to the client an information string in the following format (the counts in each field will vary per LOAD DATA INFILE operation):
Records: 174 Deleted: 0 Skipped: 3 Warnings: 14
The fields have the following meanings:
9.1.6 Privileges Needed for LOAD DATA INFILE
LOAD DATA INFILE requires that you have the INSERT privilege for the table into which you want to load data, as well as the DELETE privilege if you specify the REPLACE modifier. For a file located on the client host, you must have read access to the file, but no additional MySQL privileges are required. However, if the datafile is located on the server host, the server itself must read the file. In that case, you must also have the FILE privilege. Because FILE is an administrative privilege, it's likely that to use LOAD DATA INFILE without LOCAL, you'll need to connect to the server as an administrative user such as root.
9.1.7 Efficiency of LOAD DATA INFILE
LOAD DATA INFILE is very efficient for a datafile that is located on the server host. The MySQL server reads the file directly, so the data values need not cross the network from the client to the server. But even for a datafile located locally on the client host, LOAD DATA INFILE is more efficient than INSERT statements because there's less overhead for parsing data values and because the rows are loaded in a single operation. (Some of the efficiency of loading multiple rows at once can be obtained with INSERT if you use its multiple-row syntax, but LOAD DATA INFILE is still more efficient.)
|< Day Day Up >|