在列中创建新的唯一值

Question

我有一个以下格式的（MYSQL）表；假设表的名称是 mytable:

id	姓名	组
123	名称1	1
124	名称2	2
125	名称3	1
126	名称4

id 是唯一的并且自动递增。 name 是一个唯一的字符串，group 只是一个整数

我现在想将 name4 分配给一个尚不存在的新 group，因此本例中 name4 的 group 不能是 1 或 2。

例如，结果可能是：

id	姓名	组
126	名称4	3

目前，我按 group 降序排序，只需手动插入最大数字 + 1，但我想知道是否有更好/更快的方法在列中生成新的唯一值。 group 除了是一个整数之外没有其他约束。

我正在使用 MySQL Workbench，因此我可以使用 SQL 命令以及特定于 Workbench 的选项（如果有）。

如果有任何不清楚的地方，我很乐意提供说明。

Answer 1

在 MySQL 8.0 中，您可以获得有关两个窗口函数的帮助：

• MAX，检索最大“group”值
• ROW_NUMBER，检索表中存在的每个 NULL 的增量值。

然后，您可以对这两个值求和并更新“组”字段为空的表。

WITH cte AS (
    SELECT id, name, MAX(group_) OVER() + ROW_NUMBER() OVER(PARTITION BY group_ IS NULL ORDER BY name) AS new_group
    FROM tab
)
UPDATE tab 
INNER JOIN cte
        ON tab.id = cte.id AND tab.name = cte.name
SET tab.group_ = cte.new_group
WHERE tab.group_ IS NULL;

请在此处查看演示。

---

在 MySQL 5.X 中，您可以使用一个变量，用最大的“group”值进行初始化，然后在 UPDATE 语句中增量更新。 >SET 子句。

SET @maxgroup = NULL;
SELECT MAX(group_) INTO @maxgroup FROM tab;

UPDATE tab 
SET group_ = (@maxgroup:= @maxgroup + 1)
WHERE group_ IS NULL;
ORDER BY id;

请在此处查看演示。