8/12/2018 F.16.
mysqlimport - MySQL, Fifth Edition
MySQL, Fi h Edition
PREV NEXT
⏮ ⏭
F.15. mysqldump F.17. mysqlshow
🔎
F.16. MYSQLIMPORT
The mysqlimport client program is a bulk loader for reading the
contents of text files into existing tables. It presents a command-line
interface to the LOAD DATA SQL statement, and is an efficient way to
enter rows into tables:
mysqlimport [options] db_name file_name ...
The db_name argument specifies the database that contains the tables
into which to load data. The tables to load are determined from the
filename arguments. For each filename, any extension from the first
period in the name is stripped off and the remaining basename is used as
the name of the table into which the file should be loaded. For example,
mysqlimport loads the contents of a file named president.txt into
the president table.
mysqlimport reads data files only. It is not intended for reading SQL-
format dump files produced by mysqldump. Use mysql for that.
F.16.1. Standard Options Supported by mysqlimport
Click here to view code image
--bind-address --default-character-set
--character-sets-dir --help
--compress --host
--debug --password
--debug-check --pipe
--debug-info --plugin-dir
--default-auth --port
For general descriptions of these options, see Section F.2.1, “Standard
MySQL Program Options.” mysqlimport also supports the standard SSL
options listed in Section F.2.1.1, “Standard SSL Options.”
F.16.2. Options Specific to mysqlimport
The following options control how mysqlimport processes input files.
Section F.16.3, “Data Format Options for mysqlimport,” describes
options used to indicate the format of the data in the input files.
• --columns=col_list, -C col_list
Specify the list of columns in the table to which columns in the data file
correspond. mysqlimport loads values in input rows into the named
columns, and sets other columns to their default values. col_list lists
one or more column names separated by commas.
• --delete, -d (boolean)
Empty each table before loading any data into it.
• --force, -f (boolean)
https://www.safaribooksonline.com/library/view/mysql-fifth-edition/9780133038552/app06lev1sec16.html 1/3
8/12/2018 F.16. mysqlimport - MySQL, Fifth Edition
Continue loading rows even if errors occur.
• --ignore, -i
When an input row contains a value for a unique key that already exists in
the table, keep the existing row and discard the input row. The --ignore
and --replace options are mutually exclusive.
• --ignore-lines=n
Ignore the first n lines of the data file. This can be used to skip an initial
row of column labels, for example.
• --local, -L (boolean)
By default, mysqlimport lets the server read the data file, which means
that the file must be located on the server host and that you must have the
FILE privilege. Specifying the --local option tells mysqlimport to
read the data file itself and send it to the server. This is slower but works
when you’re running mysqlimport on a different machine than the
server host, as well as on the server host even if you don’t have the FILE
privilege.
This option is ineffective if the server has been configured to prohibit use
of LOAD DATA LOCAL.
• --lock-tables, -l (boolean)
Lock each table before loading data into it.
• --low-priority (boolean)
Use the LOW_PRIORITY scheduling modifier on statements generated to
load data into the table.
• --replace, -r (boolean)
When an input row contains a value for a unique key that already exists in
the table, replace the existing row with the input row. The --ignore and
--replace options are mutually exclusive.
• --use-threads=n
Use n threads to load files in parallel.
F.16.3. Data Format Options for mysqlimport
By default, mysqlimport assumes that data files contain newline-
terminated lines consisting of tab-separated values. The expected format
may be altered using the following options. You might need to enclose the
option value in appropriate quoting characters. These options are
analogous to the data format options for the LOAD DATA statement. See
the entry for LOAD DATA in Appendix E, “SQL Syntax Reference.”
• --fields-enclosed-by=char
Specify that column values are enclosed within the given character,
usually a quote character. By default, values are assumed not to be
enclosed by any character. This option precludes the use of --fields-
optionally-enclosed-by.
• --fields-escaped-by=char
Specify the escape character used to escape special characters. The default
is no escape character.
• --fields-optionally-enclosed-by=char
Specify that column values may be enclosed within the given character,
usually a quote character. This option precludes the use of --fields-
enclosed-by.
• --fields-terminated-by=str
Specify the character or characters that separate column values. By
default, values are assumed to be separated by tab characters.
• --lines-terminated-by=str
Specify the character or characters that terminate input lines. By default,
lines are assumed to be terminated by newline characters.
https://www.safaribooksonline.com/library/view/mysql-fifth-edition/9780133038552/app06lev1sec16.html 2/3
8/12/2018 F.16. mysqlimport - MySQL, Fifth Edition
PREV NEXT
Recommended
⏮ / Playlists / History / Topics / Tutorials / Settings / Get the App / Sign Out ⏭
F.15. mysqldump F.17. mysqlshow
© 2018 Safari. Terms of Service / Privacy Policy
https://www.safaribooksonline.com/library/view/mysql-fifth-edition/9780133038552/app06lev1sec16.html 3/3
