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

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);

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

序列列允许用户任意排序特定文件类型的文件状态的显示。我不太关心初始订单，因为可以在应用程序中对其进行修改。

理想情况下，我最终会得到这样的结果：

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），运行 update 语句，然后 添加约束（好吧，无论如何你都必须这样做）。任何被触及的内容都会更新为大于 0 的序列，因此也可以安全地运行多次。

---

编辑：

正如@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

但不知道对性能的影响。

Answer 2

如果您需要的只是“一些符合 idx1 的值”，为什么不直接复制 id 字段呢？毕竟，它是独一无二的...

UPDATE
  filetype_filestatus
SET
  sequence = id;

编辑

如何根据所提出问题的操作更改来获取顺序值。

ROW_NUMBER() 在 MySQL 中不可用，而且我的理解是，您也不能使用源查询中正在更新的表。

create temporary table temp_filetype_filestatus (
  id             integer(11) not null auto_increment,
  file_type_id   integer(11) not null,
  file_status_id integer(11) not null,
  PRIMARY KEY (file_type_id, file_status_id)
)

INSERT INTO temp_filetype_filestatus (
  file_type_id,
  file_status_id
)
SELECT
  file_type_id,
  file_status_id
FROM
  filetype_filestatus
ORDER BY
  file_type_id,
  file_status_id

-- Update Option 1
------------------
UPDATE
  filetype_filestatus
SET
  sequence
  =
  (SELECT id FROM temp_filetype_filestatus
   WHERE file_type_id   = filetype_filestatus.file_type_id
     AND file_status_id = filetype_filestatus.file_status_id)
  -
  (SELECT id FROM temp_filetype_filestatus
   WHERE file_type_id   = filetype_filestatus.file_type_id
   ORDER BY file_status_id ASC LIMIT 1)
  +
  1

-- Update Option 2
------------------
UPDATE
  filetype_filestatus
SET
  sequence
  =
  (SELECT COUNT(*) FROM temp_filetype_filestatus
   WHERE file_type_id    = filetype_filestatus.file_type_id
     AND file_status_id <= filetype_filestatus.file_status_id)