$ Linux Command

Databases

mysqlimport

Import data files into MySQL/MariaDB tables.

mysqlmariadbimportdatacsv

Additional Notes

mysqlimport imports text files into MySQL or MariaDB database tables. It provides a command-line interface to the LOAD DATA INFILE SQL statement. The filename (without extension) is used as the target table name.

It is useful for bulk loading data from CSV, TSV, or delimited text files generated by other systems or exports. It is significantly faster than inserting rows one at a time.

Syntax

mysqlimport [options] database text-file...

Parameters

  • database: The name of the target database.
  • text-file: One or more data files to import. The base filename (without extension) is the table name.

Common Options

  • -u user, --user=user: MySQL user to connect as.
  • -p, --password: Prompt for password.
  • -h host, --host=host: Connect to the specified host.
  • -P port, --port=port: Port number.
  • -L, --local: Read input files from the client host (default is server).
  • -d, --delete: Empty the table before importing.
  • -r, --replace: Replace existing rows that have the same primary/unique key.
  • -i, --ignore: Ignore rows with duplicate primary/unique keys.
  • -f, --force: Continue even if errors occur.
  • --columns=col1,col2,...: Specify which columns to import.
  • --fields-terminated-by=str: Field delimiter (default is tab).
  • --fields-enclosed-by=char: Character that encloses fields (e.g., ").
  • --fields-optionally-enclosed-by=char: Optional enclosure character.
  • --fields-escaped-by=char: Escape character (default is \).
  • --lines-terminated-by=str: Line ending (default is \n).
  • --lines-starting-by=str: Line starting marker.
  • -v, --verbose: Verbose output.

Examples

mysqlimport -u root -p mydb data.csv

Import data.csv into the data table in mydb.

mysqlimport -u root -p --local --fields-terminated-by=',' mydb users.csv

Import a CSV file with comma-delimited fields.

mysqlimport -u root -p --delete mydb products.txt

Empty the products table before importing.

mysqlimport -u root -p --replace --fields-enclosed-by='"' mydb export.csv

Replace existing rows and handle quoted fields.

mysqlimport -u root -p --columns=id,name,email mydb contacts.txt

Import only specific columns from a tab-delimited file.

Practical Notes

  • The input filename determines the target table: customers.csv imports into the customers table.
  • For CSV files, use --fields-terminated-by=',' --fields-enclosed-by='"'.
  • The user must have INSERT and DELETE (with --delete) privileges on the target table.
  • For very large imports, consider using mysql directly with LOAD DATA LOCAL INFILE for more control.
  • Ensure character encoding matches between the file and the table (use --default-character-set=utf8mb4 if needed).