MySQL InnoDB: 非主キーの自動インクリメント

MySQL の InnoDB ストレージ エンジンでは、通常、主キーは自動インクリメントされます。ただし、主キー以外のキーも自動でインクリメントすることは可能です。

問題ステートメント

データベース スキーマには、「book_comments」と「book_comments_votes」という 2 つのテーブルが含まれています。 「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 の主キー: これにより、テーブル サイズが大幅に増加します。

ただし、推奨される解決策は、非主キーに一意のインデックスを作成し、代替手段の欠点を回避することです。このアプローチは、パフォーマンスやデータ ストレージの効率を犠牲にすることなく、シンプルさと整合性の両方を提供します。