MySql INSERT MAX()+1 的问题

2022-01-09 00:00:00 max sql increment insert mysql

我有一个包含许多用户的表.在该表中，我有一个名为 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                   |
+--------------+-----------------------+

以下查询将执行此操作，但仅当表中已存在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';

但是这样的查询可能会导致竞争条件.确保您处于事务中并在运行用户表之前锁定它.否则你可能会得到两个号码相同的 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.

相关文章