MySQL imports data

May 15, 2021 MySQL

4. Introduction to common options for mysqlimport

MySQL imports data

There are two simple ways to import MySQL-exported data in MySQL.

Import data using LOAD DATA

The LOAD DATA INFILE statement is provided in MySQL to insert data. T he following example reads the file dump file from the .txt directory and inserts the data from the file into the mytbl table of the current database.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

If you specify a LOCAL keyword, you read the file by path from the customer host. If not specified, the file is read by path on the server.

You can clearly point out the separator and end-of-line markers for column values in the LOAD DATA statement, but the default tags are locators and line breaks.

The fields and LINES clauses of the two commands have the same syntax. Both clauses are optional, but if both are specified at the same time, the FIELDS clause must appear before the LINES clause.

If the user specifies a FIELDS clause, its clauses (TERMINATED BY, (OPTIONALLY) ENCLOSED BY, and ESCAPED BY) are also optional, however, the user must specify at least one of them.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA inserts data by default in the order of the columns in the data file, and if the columns in the data file do not match the columns in the inserted table, you need to specify the order of the columns.

For example, the order of the columns in the data file is a, b, c, but the order of the columns inserted into the table is b, c, a, then the data import syntax is as follows:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

Import data using mysqlimport

The mysqlimport client provides a command line interface for LOAD DATA INFILEQL statements. M ost of mysqlimport's options correspond directly to the LOAD DATA INFILE clause.

To import data .txt mytbl data table from the file dump, you can use the following commands:

$ mysqlimport -u root -p --local database_name dump.txt
password *****

The mysqlimport command can specify the option to format the specified command statement as follows:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

The --columns option is used in mysqlimport statements to set the order of columns:

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

Introduction to common options for mysqlimport

Options	Function
-d or --delete	Delete all information from the data sheet before the new data is imported into the data sheet
-f or --force	Whether or not an error is encountered, mysqlimport forces the data to continue to be inserted
-i or --ignore	Mysqlimport skips or ignores rows that have the same unique keyword, and the data in the import file is ignored.
-l or -lock-tables	The table is locked before the data is inserted, which prevents the user's queries and updates from being affected when you update the database.
-r or -replace	This option does the opposite of the -i option, which overrides records in the table that have the same unique keyword.
--fields-enclosed- by= char	Specify what is enclosed when you record data in a text file, and in many cases the data is enclosed in double quotes. By default, the data is not surrounded by characters.
--fields-terminated- by=char	Specifies a separator between the values of individual data, which is a period in a period-separated file. Y ou can use this option to specify separators between data. The default separator is a grid jumper (Tab)
--lines-terminated- by=str	This option specifies a separate string or character of data between lines in a text file. B y default, mysqlimport has newline as the line separator. You can choose to replace a single character with a string: a new line or a carriage return.

The usual options for mysqlimport commands are -v display version, -p prompt for password, and so on.

MySQL imports data

Table of contents