mysql 手动递增 ids？

Question

我有一个表，其中包含项目（id、名称等），并且我希望某种数据库方案能够拥有该项目的多个副本，同时仍然能够增加 id。将有一个名为 startdate 或 date_from_which_this_entry_should_be_used 的字段。

我想到了两种实现此目的的方法：

1. 拥有一个仅包含 ids（主键、自动增量）的表，并连接到包含所有项目信息的表。

   优点：

   • 简单易懂
   • 在我之后的人很难感到困惑

   缺点：

   • 由于该系统已投入使用，因此需要更多调试和编码
   • 拥有一个只有单个字段的表似乎很奇怪

2. 拥有一个使用子表的表选择获取最大值 (+1) 以应用于新项目。

   优点：

   • 单表
   • 仅进行细微的代码调整（但也许并没有那么不同）

   缺点：

   • 容易出错（手动递增、不允许删除或最大值可能已关闭）

提前致谢！

Answer 1

您应该创建一个名为 item_ids 的表或其他表来生成 id 值。没关系，这只有一个列。

CREATE TABLE item_ids (
  item_id INT AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

您甚至不需要向其提交任何数据。您只需使用它来生成 id 值：

START TRANSACTION;
INSERT INTO item_ids DEFAULT VALUES;
SET @id = LAST_INSERT_ID();
ROLLBACK;

所以现在您有一个并发安全的方法来创建新的 id。

然后，您为 items 表创建一个复合主键。为此，您必须使用 MyISAM。

CREATE TABLE items (
  item_id INT,
  seq_id INT AUTO_INCREMENT,
  name VARCHAR(20),
  etc VARCHAR(20),
  PRIMARY KEY (item_id, seq_id)
) ENGINE=MyISAM;

MyISAM 支持复合主键中的自动增量列，对于每个新的 item_id，该列将从值 1 开始。* 它还使用 MAX(item_id)+1，因此如果删除最后一个，其值将被重新分配。这与 AUTO_INCRMENT 的其他用途不同，在其他用途​​中，已删除的值不会重新使用。

无论插入新项目，还是插入现有项目的新副本，都使用类似的 INSERT：

INSERT INTO items (item_id, name, etc) VALUES (@id, 'Stephane', 'etc');

@id 参数要么是现有项目的值，要么是自动生成的值。您从 item_ids 表中获取的生成值。

* InnoDB 仅支持主键或唯一键的第一列自动递增，并且不会从其他列的每个不同值开始计数。