MySQL query batch insert batch update optimization details introduction

When I was studying mysql recently, I encountered the problem of low efficiency of mysql batch insertion and batch update. I have been using sqlserver before, and the efficiency of mysql itself is quite good. Here I will record the efficiency improvement methods without comparing the time. , the efficiency of actual test results has been greatly improved.

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 optimization, scenario It is to determine whether the examcode exists

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

The query result 1 means it exists, and the result null means it does not exist

Batch insertion optimization, scenario batch insertion of student information

INSERT INTO `b_student` (`examcode`,`stucode`,`name`) VALUES('10001','10001','张三'),('10002','10002','李四');

Batch update optimization, scenario batch update Student information

Batch insertion here introduces two methods (the table is required to have a primary key), and there are other methods.

1. The replace into method updates a certain column or columns based on the primary key. Note: This method will clear the columns except id and name.

replace into b_student (id,name) values (1,'张三丰'),(2,'李思思');

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

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

The above two methods are very efficient in batch updates. Choose according to the actual situation.

The above is the detailed introduction of MySQL query, batch insertion, batch update and optimization. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!