[MySQL] 一次更新多条不同的记录

朱雀 2022-07-12 02:08 235阅读 0赞

表tb\_name:

MySQL> desc tb_name;
     +--------------+--------------+------+-----+---------+----------------+
     | Field        | Type         | Null | Key | Default | Extra          |
     +--------------+--------------+------+-----+---------+----------------+
     | id           | int(11)      | NO   | PRI | NULL    | auto_increment |
     | station      | int(11)      | NO   | MUL | NULL    |                |
     | seq          | int(11)      | NO   |     | NULL    |                |
     | meaning      | varchar(40)  | NO   |     |         |                |
     | data         | float        | NO   |     | 0       |                |
     | notes        | varchar(200) | NO   |     |         |                |
     | rectime      | datetime     | NO   |     | NULL    |                |
     +--------------+--------------+------+-----+---------+----------------+
     7 rows in set (0.00 sec)
    
    mysql>

需求：同一时间update表tb\_name多次,每条记录的内容不相同

对于更新记录，update语法不支持一次更新多条记录，只能一条一条执行：

update tb_name set data = '111',rectime = '2017-01-02' where station = 'a' and seq = 1;
    update tb_name set data = '111',rectime = '2017-01-02' where station = 'a' and seq = 2;
    update tb_name set data = '111',rectime = '2017-01-02' where station = 'a' and seq = 3;

当update list非常大时，执行效率低。

解决方法：

1.给字段station，seq添加联合唯一索引  
由于on dumplicate key update只认主键或唯一索引。我的表不是根据主键id去更新内容，因此采用唯一索引列。(station，seq)构成联合唯一索引。

alter table tb_name add unique index (station,seq);

2.用insert into tb\_name () values (),(),() on duplicate key update xx=xx,xx=xx;来一次性更新多条记录

insert into tb_name (station,seq,data,rectime) values(10, 1, 0, '2017-01-17 08:17:09'), (10, 2, 0, '2017-01-17 08:17:09'),(10, 131, 0, '2017-01-17 08:17:09') on duplicate key update data=values(data),rectime=values(rectime)

on duplicate key update语法官方说明：  
[http://docs.oracle.com/cd/E17952\_01/refman-5.1-en/insert-on-duplicate.html][http_docs.oracle.com_cd_E17952_01_refman-5.1-en_insert-on-duplicate.html]  
它会先执行插入操作，碰到有主键或唯一索引的列发生冲突时，对冲突的这一行，执行update操作，更新sql语句中指定的某几列。如果所有的列都不冲突，此语法和简单的insert into语法效果一样。

on duplicate key update 语法的官方说明：  
[http://docs.oracle.com/cd/E17952\_01/refman-5.1-en/insert-on-duplicate.html][http_docs.oracle.com_cd_E17952_01_refman-5.1-en_insert-on-duplicate.html]  
replace和insert所做的工作完全相同，区别是当碰到有主键或唯一索引的列发生冲突时，对冲突的这一行，在insert前会对这行数据执行delete操作。效果是这一行中没有被指定的列会被更新成本列的默认值，如果所有的列都不冲突，此语法和简单的inset into语法效果一样。

max\_allowed\_packet参数的官方说明：  
[http://docs.oracle.com/cd/E17952\_01/refman-5.5-en/replication-features-max-allowed-packet.html][http_docs.oracle.com_cd_E17952_01_refman-5.5-en_replication-features-max-allowed-packet.html]  
mysql中没有一次更新纪录数的限制，但是有sql语句长度的限制。如果需要执行超长的sql语句，需要调整max\_allowed\_packet这个配置参数。

[http_docs.oracle.com_cd_E17952_01_refman-5.1-en_insert-on-duplicate.html]: http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-on-duplicate.html
[http_docs.oracle.com_cd_E17952_01_refman-5.5-en_replication-features-max-allowed-packet.html]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replication-features-max-allowed-packet.html