MySQL插入时使用max()+1设置值的问题

在MySQL中，有时需要插入一条新记录并为其中的某个字段设置一个唯一的值。其中一种常见的做法是使用该字段当前最大值加1来设置这个值。本文将介绍在使用该方法时可能会遇到的问题，并提供解决方法。

阅读更多：MySQL 教程

问题描述

假设有一个表foo，其中有一个自增的id字段，我们想插入一条记录并手动指定id的值，代码可能如下：

INSERT INTO foo (id, name) VALUES ((SELECT MAX(id)+1 FROM foo), 'bar');

在执行此代码后，可能会遇到以下问题：

当表中无记录时，MAX(id)将返回NULL并不能参与计算。因此，此查询将返回值为1而不是期望的2。
在高并发环境下，多个客户端同时执行此代码时，它们可能会同时读取同一个MAX(id)的值并将其加1。结果，这些查询将插入相同的id值，导致违反唯一性约束（如果存在）。

解决该问题的一种方法是使用MySQL的自增特性：

INSERT INTO foo (name) VALUES ('bar');

这将在id列上自动分配下一个可用值。

如果必须手动设置id值，则可以使用MySQL的SELECT FOR UPDATE语法来在读取MAX(id)的过程中锁定表，从而避免并发问题：

BEGIN;
SELECT MAX(id)+1 INTO @next_id FROM foo FOR UPDATE;
INSERT INTO foo (id, name) VALUES (@next_id, 'bar');
COMMIT;

在这个例子中，我们使用了MySQL的事务来保证整个过程是原子的。在SELECT语句中使用FOR UPDATE将锁定表，从而防止其他客户端同时读取表并插入相同的id值。

如果必须手动设置MySQL表中的自增字段值，请使用SELECT FOR UPDATE来避免并发问题。如果可能，最好仅让MySQL自动分配值。