Mysql Innodb：自增非主键

Mysql Innodb: Autoincrement non-Primary Key

提问by ProfileTwist

Is it possible to auto-increment a non-Primary Key?

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

Table "book_comments"

表“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)

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

此表上将没有其他索引。但是，我想让comment_id列自动递增，以便我可以轻松创建另一个表：

Table "book_comments_votes"

表“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.

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

Question:

题：

Is it possible to auto-increment a non-Primary Key - as in, auto-increment the comment_idcolumn in table "book_comments"?

是否可以自动增加非主键 - 例如，自动增加comment_id表“book_comments”中的列？

Alternatives, Discussion:

替代方案，讨论：

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

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

• 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_voteswith the entire PK. This would more than triple the size of the table.

• 使 commnet_id PK 并通过 上的唯一索引强制执行完整性book_id, timestamp, user_id。在这种情况下，我会创建一个额外的索引。
• 保留 PK 并将其中的 comment_id 替换book_comments_votes为整个 PK。这将使桌子的大小增加三倍以上。

Suggestions? Thoughts?

建议？想法？

回答by Danack

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'
);

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

将为“testInc”插入具有自动递增值的行。然而，这是一件非常愚蠢的事情。

You already said the right way to do it:

您已经说过正确的方法：

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

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

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.

这正是你应该做的。它不仅为您提供了一个合适的主键，供您将来查询时使用，而且还满足最小惊讶原则。

回答by Gowtham Vakani

You've existing table. If you already gave a primary key to comment_idand the only purpose is to set auto_increment. You can drop the primary key to that column. MySQL allows a column with any key property can access auto_increment. So better try an index or unique keyto comment_idlike below.

你有现有的表。如果你已经给了一个主键comment_id并且唯一的目的是设置 auto_increment。您可以将主键删除到该列。MySQL 允许具有任何键属性的列都可以访问 auto_increment。所以，最好尝试索引或unique key以comment_id低于喜欢。

1.Remove Primary Key

1.删​​除主键

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

ALTER TABLE `book_comments` DROP PRIMARY KEY ;

1. Add AUTO_INCREMENTwith UNIQUE_KEYproperty.

2. 添加AUTO_INCREMENT与UNIQUE_KEY属性。

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

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

回答by loebe

As of MySQL 5.5, it seems to be possible without an index orprimary key an INT field to be provided with autoincrement.

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