mysql - 如何为每个现有记录创建新记录并将现有记录引用到新创建的记录？

Question

这是我当前的数据库架构:

类别表:

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | category1  |
| 2          | category2  |
+------------+------------+

它是通过以下方式创建的:

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3889 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

我需要通过以下方式为每个现有类别记录创建 super 类别记录:

• 添加新的自引用不可为空列 parent_id(应引用 id)
• 对于新记录( super 类别)parent_id 应设置为其自身记录的 id
• 对于现有记录(类别)，parent_id 应设置为新创建记录的 id。

预期结果:

+------------+------------+------------+
| id         | name       | parent_id  |   
+------------+------------+------------+
| 1          | category1  | 3          |
| 2          | category2  | 4          |
| 3          | category1  | 3          |
| 4          | category2  | 4          |
+------------+------------+------------+

我遇到困难的部分是为现有记录设置指向新记录的 id 的 parent_id 。
有什么想法吗？

Answer 1

需要进行自连接:

update category p
inner join category c
on c.name=p.name
set c.parent_id=p.id
where c.id<>p.id