MySQL InnoDB:自增非主键

2021-11-20 00:00:00 mysql innodb primary-key

是否可以自动递增非主键?

表book_comments"

Table "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 列自动递增，以便我可以轻松创建另一个表:

There will be no other indexes on this table. However, I would like to make the comment_id column autoincrement so that I can easily create another table:

表book_comments_votes"

Table "book_comments_votes"

comment_id  (medium_int)
user_id     (medium_int)

Primary key -> (comment_id, user_id)

用户只能为每条图书评论投票一次.此表通过主键强制执行此规则.

Users would be able to vote only once per book comment. This table enforces this rule by the primary key.

是否可以自动递增 非主 键 - 例如，自动递增表book_comments"中的 comment_id 列?

Is it possible to auto-increment a non-primary key - as in, auto-increment the comment_id column in table "book_comments"?

为了简单起见，我想这样做，如上所述.替代方案并不乐观.

I would like to do this for simplicity as explained above. The alternatives are not promising.

  • 通过 book_id、timestamp、user_id 上的唯一索引制作 commnet_id PK 并强制执行完整性.在这种情况下，我会创建一个额外的索引.
  • 保留 PK 并将 book_comments_votes 中的 comment_id 替换为整个 PK.这将使桌子的大小增加三倍以上.
  • Make the commnet_id PK and enforce integrity through a unique index on book_id, timestamp, user_id. In this case, I would create an additional index.
  • Keep the PK and replace the comment_id in the book_comments_votes with the entire PK. This would more than triple the size of the table.

建议?想法?

推荐答案

是的，你可以.您只需将该列设为索引即可.

Yes you can. You just need to make that column be an index.

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"插入具有自动递增值的行.然而，这是一件非常愚蠢的事情.

Will insert rows with auto-incrementing values for 'testInc'. However this is a really dumb thing to do.

你已经说过正确的方法了:

You already said the right way to do it:

通过 book_id、timestamp、user_id 上的唯一索引使 comment_id PK 并强制执行完整性."

"Make the comment_id PK and enforce integrity through a unique index on book_id, timestamp, user_id."

这正是你应该做的.它不仅为您提供将来查询所需的表的正确主键，还满足 最小惊讶原则.

That's exactly the way that you should be doing it. Not only does it provide you with a proper primary key key for the table which you will need for future queries, it also satisfies the principle of least astonishment.

相关文章