使用多列作为mysql的唯一标识符 [英] Use multiple columns as unique identifier for mysql

问题描述

我有一个包含以下各列的mysql表:

I have a mysql table with the following columns:

group_id
game_id
user_id
message
last_update

我要使其不存在任何两行，其中行x的group_id的值等于行y的group_id的值并且行x的user_id的值也等于行x的user_id的值y行.

I want to make it so that no two rows can exist where the value of group_id for row x is equal to the value of group_id for row y AND the value of user_id for row x is also equal to the value of user_id for row y.

例如，假设我插入以下值:

So, for example, let's say I insert the following following values:

group_id = 783
game_id = 34
user_id = 29237
message = none
last_update = 11233452

以上数据，即使是mysql查询试图插入它，如果已经存在具有group_id和user_id相同组合的行，也不应创建新行.有没有办法做到这一点?基本上，我试图使两列协同工作，就像唯一索引一样.

The above data, even if a mysql query tries to insert it, should not create a new row if a row already exists with the same combination of group_id and user_id. Is there a way to do this? Basically, I'm trying to get two columns to work together kind of like an unique index.

推荐答案

是的，MySQL提供了这样做的功能.

Yes, MySQL provides the ability to do this.

ALTER TABLE MyTable
ADD UNIQUE KEY `my_unique_key` (`group_id`, `user_id`)

我不知道您正在使用此表做什么，但听起来这个唯一键可能是该表主键的最佳选择.主键也自动是唯一键.如果您决定将其设置为主键，请执行以下操作:

I don't know what you're using this table for, but it sounds like this unique key might be a strong candidate for primary key of the table. A primary key is automatically a unique key as well. If you decide to make it the primary key, then do the following instead:

ALTER TABLE MyTable
ADD PRIMARY KEY (`group_id`, `user_id`)

(如果您收到一条错误消息，提示已经存在主键，请发出ALTER TABLE MyTable DROP PRIMARY KEY，然后重复上述命令.)

(If you receive an error message that there is already a primary key, then issue ALTER TABLE MyTable DROP PRIMARY KEY and then repeat the above command.)

修改:针对用户评论

对于唯一键覆盖的列，不能有多个行具有相同的非NULL值.因此，例如在group_id = 0 AND user_id = 5中不能有两行. 0是一个值.但是，如果使两列中的一列或两列都为空，则可以包含多行，这些行直到NULL的位置都相同.因此，您可能有两行(或更多行)，其中group_id IS NULL AND user_id = 1234.

You cannot have multiple rows with identical non-NULL values for the columns covered by the unique key. So you cannot have two rows where group_id = 0 AND user_id = 5, for example. 0 is a value. But if you make one or both of the columns nullable, you can have multiple rows that are identical up to positioning of NULLs. So you could have two (or more) rows where group_id IS NULL AND user_id = 1234.

Proviso:以上对于两个常用的MySQL存储引擎(MyISAM和InnoDB)都是正确的. MySQL确实有存储引擎，其中NULL被视为唯一值，但是您可能没有使用它们.

Proviso: The above is true for both commonly-used MySQL storage engines (MyISAM and InnoDB). MySQL does have storage engines in which NULL is regarded as a unique value, but you are probably not using them.

如果将一列或两列设置为可空，则唯一键不能为主键-主键必须位于NOT NULL的列上.

If you make one or both columns nullable, then your unique key cannot be the primary key - a primary key has to be on columns that are NOT NULL.

让我们假设您已将此键作为主键，并且现在要在group_id列中允许NULL.我不知道什么是数据类型group_id.我假定它当前为INT UNSIGNED NOT NULL，但如果不是，则必须修改以下内容.您将不再能够将此键用作主键.这是您可以运行以进行所需更改的命令:

Let's suppose you had made this key your primary key and you now want to allow NULL in the group_id column. I don't know what datatype group_id is at the moment; I'll assume it's currently INT UNSIGNED NOT NULL, but you will have to modify the below if it's not this. You would no longer be able to use this key as your primary key. Here is a command you could run to make the desired changes:

ALTER TABLE MyTable
    DROP PRIMARY KEY,
    MODIFY group_id INT UNSIGNED,
    ADD UNIQUE KEY `my_unique_key_with_nulls` (`group_id`, `user`)

这篇关于使用多列作为mysql的唯一标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！