MySQL query batch insert batch update optimization, mysql insert

MySQL query batch insert batch update optimization, mysql insert

Recently, when I was studying mysql, I encountered the problem that mysql batch insertion and batch update are not efficient. I have been using sqlserver for a long time. mysql is still very efficient, the efficiency improvement method is recorded here, so we will not compare the time, and the actual test result is much more efficient.

Create Table Structure

1 DROP TABLE IF EXISTS `b_student`;2 CREATE TABLE `b_student` (3   `id` int(11) NOT NULL AUTO_INCREMENT,4   `examcode` varchar(20) CHARACTER SET gbk NOT NULL DEFAULT '',5   `stucode` varchar(20) CHARACTER SET gbk NOT NULL DEFAULT '',6   `name` varchar(20) CHARACTER SET gbk NOT NULL DEFAULT '',7   PRIMARY KEY (`id`)8 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

Query OptimizationThe scenario is to determine whether examcode exists

SELECT 1 FROM b_student WHERE examcode='10001'  limit 1;

Query Result 1 indicates that the query exists, and result null indicates that the query does not exist.

 

Batch insert OptimizationInsert student information in batches in scenarios

Insert into 'B _ student' ('examcode', 'stucode', 'name') VALUES ('123456', '123456', 'zhang san'), ('123456 ', '123', 'lily ');

 

Batch update Optimization, Scenario batch update Student Information

Batch insert two methods are described here (the table must have a primary key), and there are other methods.

1. The replace into method updates a column or several columns based on the primary key. Note: This method clears all columns except id and name.

Replace into B _student (id, name) values (1, 'zhang Sanfeng '), (2, 'lisis ');

2. insert into... on duplicate key update method, based on the columns defined after primary key update

insert into b_student (id,stucode) values (1,'20001'),(2,'20002') on duplicate key update stucode=values(stucode);

The above two methods are highly efficient during batch update and can be selected based on actual conditions.