MySql INSERT MAX()+1 的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1587562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem with MySql INSERT MAX()+1
提问by Chad
I have a single table containing many users. In that table I have column called user_id (INT), which I want increment separately for each person. user_id MUST start at 1
我有一个包含许多用户的表。在那个表中，我有一个名为 user_id (INT) 的列，我想为每个人单独增加。user_id 必须从 1 开始
I've prepared a simple example:
我准备了一个简单的例子：
Showing all names
+--------------+-----------------------+
| user_id | name |
+--------------+-----------------------+
| 1 | Bob |
| 1 | Marry |
| 2 | Bob |
| 1 | John |
| 3 | Bob |
| 2 | Marry |
+--------------+-----------------------+
Showing only where name = Bob
+--------------+-----------------------+
| user_id | name |
+--------------+-----------------------+
| 1 | Bob |
| 2 | Bob |
| 3 | Bob |
+--------------+-----------------------+
The following query will do this, but it will only work if 'Bob' already exists in the table...
以下查询将执行此操作，但仅当表中已存在 'Bob' 时才有效...
INSERT INTO users(user_id, name) SELECT(SELECT MAX(user_id)+1 from users where
name='Bob'), 'Bob';
If Bob does not exist (first entry) user_id is set to 0 (zero). This is the problem. I need the user_id to start from 1 not 0.
如果 Bob 不存在（第一个条目），则 user_id 设置为 0（零）。这就是问题。我需要 user_id 从 1 而不是 0 开始。
回答by Luká? Lalinsky
You can use something like this:
你可以使用这样的东西：
INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';
But such query can lead to a race condition. Make sure you are in a transaction and you lock the users table before running it. Otherwise you might end up with two Bobs with the same number.
但是这样的查询会导致竞争条件。确保您处于事务中并且在运行它之前锁定用户表。否则，您最终可能会得到两个具有相同编号的 Bob。
回答by Greg
回答by Daan
It's been reported as a bug in MySQL.
I'm quoting Guilhem Bichot:
我引用 Guilhem Bichot 的话：
Second, here is the simplest of workarounds:
instead of using:
insert into foo(lfd) values((select max(lfd) from foo)+1)
just do
insert into foo(lfd) select (max(lfd)+1) from foo;
其次，这里是最简单的解决方法：
而不是使用：
insert into foo(lfd) values((select max(lfd) from foo)+1)
just do
insert into foo(lfd) select (max(lfd)+1)来自 foo;
回答by Daan
Don't use MAX() + 1 then. Use an auto-numbering scheme on the table.
那就不要使用 MAX() + 1 。在桌子上使用自动编号方案。
I didn't read the question properly. Use Greg's suggestion of IFNULL().
我没有正确阅读问题。使用 Greg 对 IFNULL() 的建议。
