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 中的主鍵： 這會顯著增加表格大小。

但是，建議的解決方案仍然是在非主鍵上建立唯一索引，並避免替代方案的缺點。這種方法提供了簡單性和完整性，而不犧牲效能或資料儲存效率。