MySQL 4 methods to import data

1. Import mysql command

The mysql command import syntax is:

mysql -u username -p password < database data to be imported (runoob.sql)

Examples:

# mysql -uroot -p123456 < runoob.sql

The above command will import the entire backed up database runoob.sql.

2. Import with source command

To import a database using the source command, you need to log in to the database terminal first:

mysql> create database abc; # Create a databasemysql> use abc; # Use the created databasemysql> set names utf8; # Set the encodingmysql> source /home/abc/abc.sql # Import the backup database

3. Import data using LOAD DATA

MySQL provides the LOAD DATA INFILE statement to insert data. The following example reads the dump.txt file from the current directory and inserts 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 from the client host according to the path. If not specified, the file is read from the server at path.

You can explicitly specify column value delimiters and end-of-row markers in the LOAD DATA statement, but the default markers are tabs and newlines.

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 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';

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 table being inserted, 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 insert 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);

4. Import data using mysqlimport

The mysqlimport client provides a command-line interface to the LOAD DATA INFILEQL statement. Most options for mysqlimport correspond directly to the LOAD DATA INFILE clause.

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

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

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

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
  --lines-terminated-by="\r\n" mytbl 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 \
  mytbl dump.txt
password *****

Introduction to common options of mysqlimport

The above is the detailed content of the method of importing data into MySQL. For more information about importing data into MySQL, please pay attention to other related articles on 123WORDPRESS.COM!