Mysql Innodb:自动增量非主键 [英] Mysql Innodb: Autoincrement non-Primary Key

问题描述

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

Is it possible to auto-increment a non-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.

• 进行commnet_id PK并通过book_id, timestamp, user_id上的唯一索引强制执行完整性.在这种情况下，我将创建一个附加索引.
• 保留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:

进行comment_id PK，并通过对book_id，timestamp和user_id的唯一索引来增强完整性."

"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.

这篇关于Mysql Innodb:自动增量非主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！