MySQL BULK INSERT data

MySQL BULK INSERT data

1. For MyISAM types of tables, you can quickly import large amounts of data in the following ways.

ALTER TABLE tblname DISABLE KEYS;

Loading the data

ALTER TABLE tblname ENABLE KEYS;

These two commands are used to open or close an update for a non-unique index of the MyISAM table. When importing large amounts of data into a non-empty MyISAM table, you can increase the efficiency of the import by setting these two commands. For importing large amounts of data into an empty MyISAM table, the default is to import the data first and then create the index, so you don't have to set it up.

2. For InnoDB types of tables, this approach does not improve the efficiency of importing data. For InnoDB types of tables, there are several ways we can improve the efficiency of the import:

A. Because tables of the InnoDB type are saved in the order of the primary key, the imported data is arranged in the order of the primary key, which effectively improves the efficiency of the imported data. If the InnoDB table does not have a primary key, an internal column is created by default as the primary key, so if you can create a primary key for the table, you can use this advantage to improve the efficiency of importing data.

B. Perform set unique_checks=0 before importing data, turn off uniqueness Check, execute set unique_checks=1 after import, restore uniqueness check, can improve efficiency of import.

C. If the app uses autocommit, it is recommended to execute set autocommit=0 before import, turn off autocommit, and then execute after import.

Reference: http://cdbit.com/read/insert-large-data-to-mysql.html