MySql INSERT MAX()+ 1的问题 [英] Problem with MySql INSERT MAX()+1
问题描述
我有一个包含许多用户的表.在该表中,我有一个名为user_id(INT)的列,我想为每个人分别增加. user_id必须从1开始
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
我准备了一个简单的示例:
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...
INSERT INTO users(user_id, name) SELECT(SELECT MAX(user_id)+1 from users where
name='Bob'), 'Bob';
如果Bob不存在(第一项),则user_id设置为0(零).这就是问题.我需要user_id从1而不是0开始.
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.
推荐答案
您可以使用以下内容:
INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';
但是这样的查询会导致竞争状况.在运行之前,请确保您正在事务中并锁定了用户表.否则,您可能会得到两个具有相同编号的Bobs.
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.
这篇关于MySql INSERT MAX()+ 1的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!