MySQL InnoDB：自动递增非主键

在MySQL的InnoDB存储引擎中，主键通常是自动递增的。但是，也可以自动递增非主键。

问题陈述

数据库模式包括两个表：“book_comments”和“book_comments_votes”。 “book_comments”表有一个非主键列“comment_id”，需要自动递增以与“book_comments_votes”表保持一致，该表包含对“comment_id”的引用作为其主键。

解决方案

要自动递增非主键，必须将其声明为索引。这是一个例子：

CREATE TABLE `book_comments` (
  `book_id` MEDIUMINT NOT NULL,
  `timestamp` MEDIUMINT NOT NULL,
  `user_id` MEDIUMINT NOT NULL,
  `vote_up` SMALLINT,
  `vote_down` SMALLINT,
  `comment` TEXT,
  `comment_id` MEDIUMINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`book_id`, `timestamp`, `user_id`),
  KEY `comment_id` (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

替代方案和讨论

虽然在非主键上创建简单索引是最直接的解决方案，但存在一些替代方案：

• 将comment_id作为主键：这需要在（book_id，timestamp，user_id）上创建一个额外的唯一索引以保持完整性。
• 存储整个book_comments_votes 中的主键： 这会显着增加表大小。

但是，推荐的解决方案仍然是在非主键上创建唯一索引，并避免替代方案的缺点。这种方法提供了简单性和完整性，而不牺牲性能或数据存储效率。