使用多列作为 mysql 的唯一标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3798555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use multiple columns as unique identifier for mysql
提问by user396404
I have a mysql table with the following columns:
我有一个包含以下列的 mysql 表：
group_id
game_id
user_id
message
last_update
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.
我想让它不存在两行，其中第 x 行的 group_id 值等于第 y 行的 group_id 值，并且第 x 行的 user_id 值也等于第 y 行的 user_id 值。
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
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 查询尝试插入它，如果已存在具有相同 group_id 和 user_id 组合的行，也不应该创建新行。有没有办法做到这一点？基本上，我试图让两列像唯一索引一样一起工作。
回答by Hammerite
Yes, MySQL provides the ability to do this.
是的，MySQL 提供了执行此操作的能力。
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`)
(If you receive an error message that there is already a primary key, then issue ALTER TABLE MyTable DROP PRIMARY KEYand then repeat the above command.)
（如果您收到一条错误消息，指出已经存在主键，则发出ALTER TABLE MyTable DROP PRIMARY KEY并重复上述命令。）
Edit:In response to user comment
编辑：回应用户评论
You cannot have multiple rows with identical non-NULLvalues 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 canhave 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.
NULL对于唯一键覆盖的列，您不能有多个具有相同非值的行。因此group_id = 0 AND user_id = 5，例如，您不能有两行 where 。0 是一个值。但是，如果您将一列或两列设置为可空，则可以有多个行，直到NULLs 的定位都相同。所以你可以有两行（或更多）行，其中group_id IS NULL AND user_id = 1234.
Proviso: The above is true for both commonly-used MySQL storage engines (MyISAM and InnoDB). MySQL does have storage engines in which NULLis regarded as a unique value, but you are probably not using them.
附带条件：以上对于常用的 MySQL 存储引擎（MyISAM 和 InnoDB）都适用。MySQL 确实具有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.
如果您将一列或两列设为可空，则您的唯一键不能是主键 - 主键必须位于NOT NULL.
Let's suppose you had made this key your primary key and you now want to allow NULLin the group_idcolumn. I don't know what datatype group_idis 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:
假设您已将此键设为您的主键，并且您现在希望NULL在group_id列中允许。我现在不知道什么是数据类型group_id；我假设它是 current INT UNSIGNED NOT NULL，但如果不是这个，你将不得不修改下面的内容。您将无法再使用此键作为主键。这是您可以运行以进行所需更改的命令：
ALTER TABLE MyTable
DROP PRIMARY KEY,
MODIFY group_id INT UNSIGNED,
ADD UNIQUE KEY `my_unique_key_with_nulls` (`group_id`, `user`)
回答by Nik
well for this you can create composite key of group_id and user_id using any mysql editor like phpmyadmin or sqlyog, and mark it as unique index.
为此，您可以使用任何 mysql 编辑器（如 phpmyadmin 或 sqlyog）创建 group_id 和 user_id 的复合键，并将其标记为唯一索引。
