MySQL InnoDB：自增非主键

Question

是否可以自动增加一个非主键字段？

表 "book_comments"

book_id     medium_int
timestamp   medium_int
user_id     medium_int
vote_up     small_int
vote_down   small_int
comment     text
comment_id  medium_int

Primary key -> (book_id, timestamp, user_id)

这个表不会有其他索引。但是，我想让 comment_id 这一列自动增加，以便我可以轻松地创建另一个表：

表格“book_comments_votes”

comment_id  (medium_int)
user_id     (medium_int)

Primary key -> (comment_id, user_id)

每个书评只能投一次票。这个表通过主键来强制执行这个规则。

问题：

是否可以自动递增非主键 - 比如，在“book_comments”表中自动递增“comment_id”列？

---

备选方案，讨论。

我希望出于简单的考虑而这样做，但其他方案并不理想。

• 将“comment_id”设置为PK，并通过对“book_id”、“timestamp”和“user_id”的唯一索引来保证完整性。在这种情况下，我会创建一个额外的索引。
• 保留PK，用整个PK替换“book_comments_votes”中的“comment_id”。这将使表的大小增加三倍以上。

有建议吗？想法呢？

- ProfileTwist

---

为什么你不能简单地将 comment_id 设为主键呢？我不明白为什么你需要额外的唯一索引。可能是我没有完全理解你的问题。 - Michael Mior

使用 comment_id 作为主键不足以保证数据完整性 - 特别是针对特定书籍的其他重复用户投票。因此，需要使用复合键创建唯一索引。 - ProfileTwist

所以用户只能为每本书投票一次，而不是像您在问题中所述的那样为每个书评投票一次？ - Michael Mior

Answer 1

是的，你可以。你只需要将那一列设置为索引即可。

CREATE TABLE `test` (
  `testID` int(11) NOT NULL,
  `string` varchar(45) DEFAULT NULL,
  `testInc` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`testID`),
  KEY `testInc` (`testInc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


insert into test(
  testID,
 string
)
values (
1,
    'Hello'
);


insert into test( 
testID,
 string
)
values (
2,
    'world'
);

将为'testInc'插入自增值的行，但这是一件非常愚蠢的事情。

你已经说出了正确的做法：

"将comment_id作为主键，并通过书籍ID、时间戳和用户ID的唯一索引强制执行完整性。"

这正是你应该采取的方式。它不仅为表提供了适当的主键，这对于未来的查询是必要的，而且还满足了最小惊讶原则。

- Danack

---

“this is a really dumb thing to do” 的翻译是“这是一件非常愚蠢的事情”。这是一个强烈的表达方式。每个特点都有其使用场景。我有大量基于文本的密钥，不想在整个设计中传播它们，替代密钥正好符合要求。 - Robert

一个代理键是我建议他们使用的，而不是他们最初的愚蠢想法。 - Danack

非常感谢，你帮了大忙：）） - Bynd

这不仅是一个强硬的评论，而且是错误的评论。虽然他说拥有适当的键是一件好事，但这并不意味着由各种列组成的复杂键就是最佳解决方案。一个主整数 ID 是非常便携的，而复杂的键则不一定如此。 - John

即使它允许在任何索引上使用auto_increment，我至少会将其设置为唯一索引。一个非唯一的自增索引似乎是灾难的预谋。 - ysth

Answer 2

您已经有了一张现有的表。如果您已经为comment_id列分配了主键，而唯一的目的是设置自动增量。那么您可以删除该列的主键。MySQL允许具有任何关键属性的列访问自动递增。因此，最好尝试像下面这样对comment_id使用索引或unique key。

1. 删除主键

ALTER TABLE `book_comments` MODIFY `comment_id` INT(5) NOT NULL;

ALTER TABLE `book_comments` DROP PRIMARY KEY ;

2. 使用UNIQUE_KEY属性添加AUTO_INCREMENT。

ALTER TABLE 'brand_keywords' CHANGE COLUMN 'comment_id' 'comment_id' INT(5) NOT NULL AUTO_INCREMENT UNIQUE;

- Gowtham Vakani

Answer 3

从MySQL 5.5开始，似乎可以在没有索引或主键的情况下为INT字段提供自增功能。

- loebe

---

你能行吗？请发布你的代码。我在5.5.23和5.6.12中尝试过：> create table test ( user varchar(20) not null, id int auto_increment, primary key(user)); ERROR 1075 (42000): 不正确的表定义；只能有一个自动列，并且必须定义为键。 - phil_w

抱歉，您需要添加一个索引，这是我的错误。创建表test （user varchar（20）not null， id int auto_increment， index（id）， primary key（user）） - loebe

你能提及一下参考资料吗？ - Sajjad