mysql - 如何向链接表添加和填充序列列

Question

假设我有一个如下表:

create table filetype_filestatus (
  id             integer(11) not null auto_increment,
  file_type_id   integer(11) not null,
  file_status_id integer(11) not null,
)

我想像这样添加一个序列列:

alter table filetype_filestatus add column sequence integer(11) not null;
alter table filetype_filestatus add unique key idx1 (file_type_id, file_status_id, sequence);

现在我想添加列，这很简单，并用一些满足唯一键的默认值填充它。

sequence 列是为了让用户任意排序特定file_type 的file_status 的显示。我不太关心初始订单，因为它可以在应用程序中修改。

理想情况下我会得到类似这样的结果:

FileType FileStatus Sequence
   1        1          1
   1        2          2
   1        3          3
   2        2          1
   2        2          2

我能想到的最好的是这样的:

update filetype_filestatus set sequence = file_type_id * 1000 + file_status_id;

有没有更好的方法？

Answer 1

嗯，我相信这应该可行:

UPDATE filetype_filestatus as a 
       SET sequence = (SELECT COALESCE(MAX(b.sequence), 0)  
                       FROM filetype_filestatus as b
                       WHERE b.file_type_id = a.file_type_id) + 1

WHERE sequence = 0

我建议将新列添加到表中，运行 alter table 语句(并获取默认值 0)，运行更新语句，然后 添加约束(好吧，无论如何你都必须这样做)。任何被触摸的东西都会更新为大于 0 的 sequence，因此这也可以安全地运行多次。

---

编辑:

正如@Dems 指出的那样，子查询在更新之前运行，因此上述内容实际上不适用于此目的。它确实适用于单行插入(在这里根本没有帮助)。

---

编辑:

啊，你有一个 id 列，这工作得很好(是的，我首先测试了这个)。

UPDATE filetype_filestatus as a
       SET sequence = (SELECT COALESCE(COUNT(*), 0)
                       FROM filetype_filestatus as b
                       WHERE b.file_type_id = a.file_type_id
                       AND b.id < a.id) + 1
WHERE sequence = 0

虽然不知道性能影响。