Batch data import techniques in MySQL

As the amount of data continues to increase, how to efficiently import large amounts of data into the MySQL database has become an important issue that data managers need to pay attention to. In practical applications, MySQL provides a variety of batch data import techniques. By rationally using these techniques, the efficiency and accuracy of data import can be greatly improved. This article will introduce common batch data import techniques in MySQL.

1. Use the LOAD DATA command

LOAD DATA is a command in MySQL used to import data in batches. This command can read data directly from a text or CSV format file and insert the data into the specified table. The LOAD DATA command supports multiple data formats, including CSV, text, XML, etc., and also provides a variety of import methods, which can be imported according to field name matching, line number limit, byte limit, etc. The following is an example of using the LOAD DATA command to import data in batches:

LOAD DATA LOCAL INFILE '/home/user/data.csv' INTO TABLE table1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '
';

The above command means to insert the data in the local file /home/user/data.csv into the table table1. The delimiter of each field is a comma, and each row of data uses a newline. character is the delimiter.

2. Use the INSERT INTO SELECT command

The INSERT INTO SELECT command is another way to import data in batches. This command can read data from one table and insert data into another table. The INSERT INTO SELECT command can customize the fields to be inserted and the filter conditions, which is very flexible. The following is an example of batch importing data using the INSERT INTO SELECT command:

INSERT INTO table2 (col1, col2, col3 ) SELECT col1, col2, col3 FROM table1 WHERE col4=1;

The above command means to insert the col1, col2, col3 field values ​​of the record whose col4 is equal to 1 in table1 into the corresponding columns of table2.

3. Import data in batches

When importing large amounts of data, you can use the batch import method to divide the data into multiple small files and import them into the database respectively. This is to prevent serious degradation of database performance caused by importing too much data at one time. For example, 10 million records can be divided into ten small files, and one million records can be imported at a time, which can effectively reduce the pressure on the database.

4. Optimize the index mechanism during import

When importing data, MySQL will automatically update the index in the table, which may cause the import speed to slow down. If you do not need to update the index during import, you can close the index before importing and reopen the index after the data import is completed. Here is an example of turning off the index and importing the data:

ALTER TABLE table1 DISABLE KEYS;
LOAD DATA LOCAL INFILE '/home/user/data.csv' INTO TABLE table1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '
';
ALTER TABLE table1 ENABLE KEYS;

The above command means to close the table before importing data The index of table1 will be reopened after the data import is completed. This method can effectively improve the efficiency of data import.

In short, there are many techniques for importing data in MySQL, and each technique has its applicable occasions and precautions. By rationally applying these techniques, the efficiency and accuracy of data import can be greatly improved.

The above is the detailed content of Batch data import techniques in MySQL. For more information, please follow other related articles on the PHP Chinese website!