mysqlimport

The mysqlimport client program is a bulk loader for reading the contents of text files into existing tables. It functions as a command-line interface to the LOAD DATA SQL statement, and is an efficient way to enter rows into tables.

mysqlimport reads data files only. It is not intended for reading SQL-format dump files produced by mysqldump. Use mysql to read such files instead.

Usage

mysqlimport [options] db_name file_name ...

The db_name argument specifies the database that contains the tables into which you want 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 will load the contents of a file named president.txt into the president table.

Standard Options Supported by mysqlimport

--character-sets-dir      --password                --socket
--compress                --pipe                    --user
--debug                   --port                    --verbose
--default-character-set   --protocol                --version
--help                    --shared-memory-base-name
--host                    --silent

mysqlimport also supports the standard SSL options.

Options Specific to mysqlimport

The following options control how mysqlimport processes input files. The next section, "Data Format Options for mysqlimport," describes options that may be used to indicate the format of the data in the input files.

• --columns=col_list

  List the columns in the table to which columns in the data file correspond. Values in input rows will be loaded into the named columns, and other columns will be set to their default values. col_list is a list of one or more column names separated by commas.

• --delete, -d (boolean)

  Empty each table before loading any data into it.

• --force, -f (boolean)

  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 disallow 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 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.

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.

• --fields-enclosed-by=char

  Specifies 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

  Specifies the escape character used to escape special characters. The default is no escape character.

• --fields-optionally-enclosed-by=char

  Specifies 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

  Specifies the character or characters that separate column values. By default, values are assumed to be separated by tab characters.

• --lines-terminated-by=str

  Specifies the character or characters that terminate input lines. By default, lines are assumed to be terminated by newline characters.