如何将MySQL字段值更新为当前最大值加1的查询语句？

摘要：在MySQL中，我们在执行update或者insert语句时，可以借助select语句更新一个字段，使其值更新为该字段的最大值加1。   在《MySQL中自增长序列(@i:=@i+1)的用处及用法》中，介绍了如何在select语句中生成递增序列，《MySQL 把查询结果更新或者插入到新表》介绍了把查询到的多条记录复制到另一张表中，均没有介绍如何更新数据库字段的值为当前最大值加指定步长。所以在本文中，楼兰胡杨将带着大家了解如何更新某个字段的值为当前最大值加指定步长1，步长也可以是其它满足诉求的值。 创建测试表   使用以下 MySQL 语句创建测试表test并写入三条测试数据： CREATE TABLE test ( `id` bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', incr_id INT NOT NULL COMMENT '递增序列', creator varchar(30) NOT NULL COMMENT '创建者' ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '测试表'; INSERT INTO test(incr_id,creator) VALUES (FLOOR(1 + RAND() * 100),"张三"); INSERT INTO test(incr_id,creator) VALUES (FLOOR(1 + RAND() * 100),"张三丰"); INSERT INTO test(incr_id,creator) VALUES (FLOOR(1 + RAND() * 100),"楼兰胡杨");   表中incr_id用于演示如何更新它的值为其当前最大值加1。当然，令其如主键ID一样支持AUTO_INCREMENT时，可以实现自增长，实现方案非常简单，本文不再赘述。这里主要讲述如何通过update select和insert select实现字段值递增的策略。 update select实现字段递增赋值   首先使用聚合函数max来计算最大值，然后将其加1。以下SQL将返回test表中incr_id字段的最大值并且加1： SELECT MAX(incr_id)+1 FROM test;   运行此命令后，将返回一个包含最大值加1的单独的列。接下来，楼兰胡杨将此用作update语句的源值，以下实现方案基于嵌套子查询完成： UPDATE test SET incr_id = ( SELECT max_incr FROM ( SELECT MAX(incr_id) + 1 AS max_incr FROM test ) AS tmp ) WHERE id = 3;   在这个更新DML中，我们在子查询语句中使用聚合函数max计算字段最大值，然后对其最大值加1。与前面的例子一样，WHERE子句用于指定要更新的记录。这个方案因为耗内存、效率低而不适用于批量更新场景，但变量模拟自增方案适用于批量更新或复杂逻辑控制，实现脚本如下： -- 自定义变量 SET @max_incr = (SELECT MAX(incr_id) + 1 FROM test); -- 使用变量更新字段 UPDATE test SET incr_id = @max_incr WHERE id = 3; insert select实现字段递增赋值   在insert操作中实现字段递增赋值时，也是首先使用聚合函数max来计算最大值，然后将其加1。先介绍一下insert select语法糖： INSERT INTO target_table (column1, column2, ...) SELECT value1, value2, ... FROM source_table_a a JOIN source_table_b b ON a.id = b.a_id WHERE condition_clause; target_table：用于插入数据的目标表。 source_table_a 和 source_table_b：源表，从中选择数据用于插入目标表。可以从多个表中选择数据并插入到目标表中，可以一个表。 value1, value2, …：被插入到目标表的值，既可以是从源表中选择的列，也可以是返回常量的表达式，更可以是一个常量。在编写SELECT语句的时候，可以使用MySQL支持的全部语法。