MySQL 使用max()+1来为记录生成唯一ID问题

阅读更多：MySQL 教程

问题描述

在MySQL中，我们经常需要先查询出表中某个字段的最大值（比如ID），然后在新增一条记录时，将这个字段的值设置为max()+1。这样做的意义在于保证新增的记录在ID上是唯一的。

例如，有一张Student表，包含以下字段：

现在我们想新增一条记录，要求ID值是当前表中ID字段的最大值加1。我们可以使用以下SQL语句：

INSERT INTO Student(ID, NAME) VALUES((SELECT max(ID)+1 FROM Student), "Tom");

然而，这种方法存在一定的问题，以下是两个常见的问题：

问题1：并发插入时可能会出现重复记录

由于max()+1的算法是在SQL语句中完成的，所以当多个用户同时新增记录时，就有可能出现两个记录的ID相同的情况。例如，如果同时有两个用户在11:30:05这一秒钟新增了Student表的记录，而这时的max(ID)是100，则两个用户都会选择将ID设置为101。当他们的新增记录同时写入到表中时，就会出现两条ID相同的记录，造成数据错误。

问题2：并发插入时会出现死锁

当多个用户同时向数据库插入记录时，就会出现死锁的情况。如果一个用户先执行了SELECT max(ID)+1 FROM Student语句，但还没有执行INSERT INTO Student(ID, NAME) VALUES语句时，另外一个用户也执行了相同的SELECT语句，并且得到了比第一个用户更大的ID值，那么第一个用户在执行INSERT语句时会出现死锁，因为他试图往表中插入一个已经被另外一个用户插入的ID。

解决方案

为了避免上述问题，我们可以使用MySQL的AUTO_INCREMENT机制来为ID字段自动生成ID值。在建表时，我们需要为ID字段加上AUTO_INCREMENT属性，然后在插入记录时，只需设置其他字段的值，而不需要设置ID的值。

下面是使用AUTO_INCREMENT的Student表结构：

CREATE TABLE Student(
    ID int NOT NULL AUTO_INCREMENT,
    NAME varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);

在上述表结构中，ID字段被定义为AUTO_INCREMENT，代表ID字段会自增，也就是说，每插入一条新记录时，ID字段的值会自动递增1。

在插入记录时，我们只需要提供NAME字段的值就可以了：

INSERT INTO Student(NAME) VALUES('Tom');

这样，ID字段的值会自动生成，并且不会出现并发问题。

总结

使用max()+1来为记录生成唯一ID的做法看似简单，但却存在重复插入和死锁的风险。使用MySQL的AUTO_INCREMENT机制可以避免这些问题，且具有更高的效率。因此，我们应该尽量避免使用max()+1来生成唯一ID，而是使用AUTO_INCREMENT机制来自动生成ID值。