INSERT INTO一个带有UNIQUE INDEX的表，用于两列

I have a simple tag_map table as

我有一个简单的tag_map表

CREATE TABLE tag_map
(
tag_map_id mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
post_id mediumint(7) unsigned REFERENCES posts(post_id),
tag_id mediumint(7) unsigned REFERENCES tags(tag_id),
UNIQUE INDEX (post_id,tag_id),
PRIMARY KEY(tag_map_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

I added UNIQUE INDEX to avoid duplicate pairs of a tag associated to a post. Now when I try to add new enteries as

我添加了UNIQUE INDEX以避免与帖子关联的重复标记对。现在当我尝试添加新的肠子时

INSERT IGNORE INTO (post_id,tag_id) VALUES (post1_id,tag1_id), (post1_id, tag2_id),...

I will receive an error

我会收到一个错误

ERROR 1062 (23000): Duplicate entry '16777215' for key 'PRIMARY'

but when I SELECT WHERE tag_map_id='16777215'; this belongs to a different tag and post.

但是当我选择WHERE时,tag_map_id ='16777215';这属于不同的标签和帖子。

Where did I wrong?

我哪里错了?

3 个解决方案

#1

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

Your PK's mediumint max value of 16777215 has been reached.

您的PK的中等最大值已达到16777215。

Alter to int or above

改为int或以上

#2

tag_map_id is the primary key for the table and can't be duplicated. Check SELECT MAX(tag_map_id) FROM tag_map; and the value of AUTO_INCREMENT in SHOW CREATE TABLE tag_map;. The second value should be greater than the first. Sometimes these can get out of sync.

tag_map_id是表的主键,不能重复。检查SELECT MAX(tag_map_id)FROM tag_map;和SHOW CREATE TABLE中的AUTO_INCREMENT值tag_map;。第二个值应大于第一个值。有时这些可能会失去同步。

If the AUTO_INCREMENT value is less than the maximum tag_map_id, just do ALTER TABLEtag_mapAUTO_INCREMENT=x;where x is one greater than MAX(tag_map_id).

如果AUTO_INCREMENT值小于最大tag_map_id,则执行ALTER TABLEtag_mapAUTO_INCREMENT = x;其中x是大于MAX的一个(tag_map_id)。

#3

Your tag_map_id is declared as the table PRIMARY KEY. You may never, ever, ever have a duplicate primary key. It doesn't matter which tag and post the second row belongs to: it has the same primary key.

您的tag_map_id被声明为表PRIMARY KEY。您可能永远不会有任何重复的主键。第二行所属的标签和帖子无关紧要:它具有相同的主键。

You will need to alter your schema if you want two items with the same tag_map_id, but it's more likely you've made a logic error in what you are trying to accomplish.

如果你想要两个具有相同tag_map_id的项目,你将需要改变你的模式,但是你更有可能在你想要完成的事情中犯了一个逻辑错误。

#1