How to import txt into MySQL data table?Import sql file into database tutorial

MySQLHow to import data table into txt?import sql fileMySQL database教程

MySQL import data

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

Import data using LOAD DATA

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

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

　If the LOCAL keyword is specified, it indicates that the file is read by path from the client host.If not specified, the file is read by path on the server.

You can specify column value delimiters and end-of-line markers explicitly in the LOAD DATA statement, but the default markers arePositioningcharacters and line breaks.

The syntax of the FIELDS and LINES clauses is the same for both commands.Both clauses are optional, but if both are specified, 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 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';

By default, LOAD DATA inserts data in the order of the columns in the data file. If the columns in the data file are inconsistent with the columns in the inserted table, you need to specify the order of the columns.

For example, the column order in the data file is a,b,c, but the column order in the inserted table is b,c,a, 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 to the LOAD DATA INFILEQL statement. Most options of mysqlimport correspond directly to the LOAD DATA INFILE clause.

To import data into the mytbl data table from the file dump.txt, the following command can be used:

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

The mysqlimport command can specify options to set the specified format. The format of the command statement is as follows:

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

Use the --columns option in the mysqlimport statement to set the order of the columns:

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

Introduction to common options of mysqlimport

The commonly used options of the mysqlimport command are -v to display the version (version), -p to prompt for a password, and so on.