mysql - 如何在行版本化MySQL表(SCD类型2)中设置主键、自增和索引？

Question

对于一个项目，我需要在 MySQL 数据库中实现行版本控制。在阅读了可能的解决方案后，我选择使用一个表并添加 start_date 和 end_date 列，以指定该版本/行何时处于事件状态。

对于同一行的多个版本，id 列是相同的。因此它在表中不再是唯一的。现在我不确定如何设置主键(和其他索引)，同时保持 id 列的自动增量事件。

我看到两个选项。第一个是使 id 只是一个索引，如下所示:

CREATE TABLE `thing` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  … other_columns …,
  `start_date` datetime NOT NULL,
  `end_date` datetime DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

另一种是将 id 和 start_date 设为主键，如下所示:

CREATE TABLE `thing` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  … other_columns …,
  `start_date` datetime NOT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

什么是最佳选择，尤其是在考虑性能时？

---

例如，数据如下所示:

id | some_column | start_date | end_date
---|-------------|------------|-----------
1  | Jonathn     | 2013-01-01 | 2013-01-02
1  | Jonathan    | 2013-01-02 | NULL
2  | James       | 2013-02-01 | NULL

在这种情况下，我添加了“Jonathn”，但稍后将其更改为“Jonathan”(同时保持相同的 ID)。稍后添加另一行(“James”)。

Answer 1

您的第一个示例 CREATE TABLE 语句没有键*，您的第二个示例有。我认为你的第二个例子就是你想要的:

CREATE TABLE `thing` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  … other_columns …,
  `start_date` datetime NOT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

您可能希望也可能不希望 id 成为一个自动递增的列，但这主要是一个功能问题(您希望能够生成递增的值还是自己插入)而不是逻辑设计或性能。

*不幸的是，MySQL 使用关键字KEY 来指定索引。必须使用 UNIQUE KEY 或 PRIMARY KEY 语法指定键。