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:
Which table to load
The name and location of the datafile
Which columns to load
The format of the datafile
How to handle duplicate records
Whether to ignore lines at the beginning of the datafile
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)]
The following sections explain how the various parts of the statement work.
Specifying the Datafile Location
LOAD DATA INFILE can read datafiles that are located on the server host
or on the client host:
By default, MySQL assumes that the file is located on the server host.
The MySQL server reads the file directly.
If the statement begins with LOAD DATA LOCAL INFILE rather than with LOAD DATA INFILE,
the file is read from the client host on which the statement is issued. In other words
, LOCAL means local to the client host from which the statement is issued. In this case,
the client program reads the datafile and sends its contents over the network to the server.
The rules for interpreting the filename are somewhat different for the server
host and the client host.
Specifying the Location of Files on the Server Host
Without LOCAL in the LOAD DATA INFILE statement, MySQL looks for the datafile
located on the server host and interprets the pathname as follows:
If you refer to the file by its full pathname, the server looks for the file
in that exact location.
If you specify a relative name with a single component, the server looks for
the file in the database directory for the default database. (This isn't necessarily
the database that contains the table into which you're loading the file.)
If you specify a relative pathname with more than one component, the server
interprets the name relative to its data directory.
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:
You can refer to the file by its full pathname:
LOAD DATA INFILE '/var/mysql/data/test/data.txt' INTO TABLE t;
If test is the default database, you can refer to a file in the database directory
using just the final component of its pathname:
LOAD DATA INFILE 'data.txt' INTO TABLE t;
You can refer to any file in or under the server's data directory by its
pathname relative to that directory:
LOAD DATA INFILE './test/data.txt' INTO TABLE t;
Specifying the Location of Files on the Client Host
If you use LOCAL to read a datafile located on the client host, pathname
interpretation is simpler:
If you refer to the file by its full pathname, the client program looks for
the file in that exact location.
If you specify a relative pathname, the client program looks for the file relative
to its current directory. Normally, this is the directory in which you invoked the program.
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;
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;
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.
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:
Which table to load
The name and location of the datafile
Which columns to load
The format of the datafile
How to handle duplicate records
Whether to ignore lines at the beginning of the datafile
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)]
The following sections explain how the various parts of the statement work.
Specifying the Datafile Location
LOAD DATA INFILE can read datafiles that are located on the server host
or on the client host:
By default, MySQL assumes that the file is located on the server host.
The MySQL server reads the file directly.
If the statement begins with LOAD DATA LOCAL INFILE rather than with LOAD DATA INFILE,
the file is read from the client host on which the statement is issued. In other words
, LOCAL means local to the client host from which the statement is issued. In this case,
the client program reads the datafile and sends its contents over the network to the server.
The rules for interpreting the filename are somewhat different for the server
host and the client host.
Specifying the Location of Files on the Server Host
Without LOCAL in the LOAD DATA INFILE statement, MySQL looks for the datafile
located on the server host and interprets the pathname as follows:
If you refer to the file by its full pathname, the server looks for the file
in that exact location.
If you specify a relative name with a single component, the server looks for
the file in the database directory for the default database. (This isn't necessarily
the database that contains the table into which you're loading the file.)
If you specify a relative pathname with more than one component, the server
interprets the name relative to its data directory.
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:
You can refer to the file by its full pathname:
LOAD DATA INFILE '/var/mysql/data/test/data.txt' INTO TABLE t;
If test is the default database, you can refer to a file in the database directory
using just the final component of its pathname:
LOAD DATA INFILE 'data.txt' INTO TABLE t;
You can refer to any file in or under the server's data directory by its
pathname relative to that directory:
LOAD DATA INFILE './test/data.txt' INTO TABLE t;
Specifying the Location of Files on the Client Host
If you use LOCAL to read a datafile located on the client host, pathname
interpretation is simpler:
If you refer to the file by its full pathname, the client program looks for
the file in that exact location.
If you specify a relative pathname, the client program looks for the file relative
to its current directory. Normally, this is the directory in which you invoked the program.
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;
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;
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.