MySQL中如何为多列指定唯一约束

技术背景

在数据库设计中，有时需要确保表中多列的组合值是唯一的。例如，在一个投票表中，需要保证user、email和address这三列的组合值在表中是唯一的，以避免重复投票或重复记录。MySQL 提供了多种方法来实现多列的唯一约束。

实现步骤

创建表时指定唯一约束

在创建表时，可以直接指定多列的唯一约束。以下是一个示例：

CREATE TABLE `votes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user` varchar(255),
    `email` varchar(255),
    `address` varchar(255),
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_index` (`user`, `email`, `address`)
);

在这个示例中，UNIQUE KEY 用于指定 user、email 和 address 三列的组合值必须是唯一的。

修改现有表添加唯一约束

如果表已经存在，可以使用 ALTER TABLE 语句来添加唯一约束：

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

使用 MySQL Workbench 添加唯一约束

1. 打开 MySQL Workbench，选择要修改的表。
2. 切换到 Indexes 选项卡。
3. 选择 UNIQUE 作为索引类型。
4. 为索引命名，并勾选 Index Columns 部分中需要添加唯一约束的列。

使用 phpMyAdmin 添加唯一约束

1. 导航到目标表的结构页面。
2. 为其中一列添加唯一索引。
3. 展开结构页面底部的 Indexes 列表，找到刚添加的唯一索引。
4. 点击编辑图标，在弹出的对话框中添加其他需要的列。

核心代码

创建表时指定唯一约束

CREATE TABLE `content_html` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_box_elements` int(11) DEFAULT NULL,
    `id_router` int(11) DEFAULT NULL,
    `content` mediumtext COLLATE utf8_czech_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `my_uniq_id` (`id_box_elements`, `id_router`)
);

修改现有表添加唯一约束

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

最佳实践

• 命名规范：为唯一约束命名时，建议使用有意义的名称，以便于识别和管理。例如，可以使用表名和列名的组合，如 votes_user_email_address_unique。
• 处理 NULL 值：MySQL 将 NULL 值视为唯一值，因此在多列唯一索引中，如果某些列允许为 NULL，则可能会出现多个包含 NULL 值的行。如果需要避免这种情况，可以考虑使用默认值或其他方法来处理 NULL。

常见问题

多列唯一索引中包含 NULL 值

MySQL 目前将 NULL 视为唯一值，因此在多列唯一索引中，如果某些列包含 NULL，则可能会出现多个包含 NULL 值的行。这是 MySQL 的一个已知行为，目前被标记为 “will not fix”。如果需要避免这种情况，可以考虑使用复合主键或其他方法来处理。

ON DUPLICATE KEY 是否适用于复合键

ON DUPLICATE KEY 可以与复合键一起使用，但需要对语法进行一些调整。具体示例可以参考 相关问题。