MySQL UNIQUE约束详解：如何防止重复数据（附示例）

1. 介绍

在数据库设计中，保持数据唯一性极其重要。如果相同的数据被多次注册，可能导致系统故障并引发数据不一致。MySQL 提供了名为 UNIQUE 约束的功能来防止此类问题。

例如，用户注册 Web 服务时，如果同一电子邮件地址被多次注册，可能会出现登录错误。为防止这种情况，可以使用 UNIQUE 约束来确保特定列中的值保持唯一。

本文将清晰地解释 MySQL 的 UNIQUE 约束——从基础用法到高级示例。我们还会覆盖重要注意事项和常见问答，帮助你在实际项目中立即应用这些知识。

2. UNIQUE 约束的基础

什么是 UNIQUE 约束？

MySQL 中的 UNIQUE 约束用于防止特定列或列组合出现重复值。通过应用此约束，你可以确保同一值不会被插入多次。

UNIQUE 与 PRIMARY KEY 的区别

MySQL 还提供了 PRIMARY KEY 约束，它在多个方面与 UNIQUE 约束不同。

1. PRIMARY KEY 始终为 NOT NULL PRIMARY KEY 不仅保证唯一性，还禁止 NULL 值。而 UNIQUE 约束允许 NULL 值。
2. 每个表只能有一个 PRIMARY KEY 一个表只能拥有一个 PRIMARY KEY，而同一表中可以定义多个 UNIQUE 约束。

UNIQUE 约束的常见使用场景

UNIQUE 约束常用于以下情形：

• 电子邮件地址或用户名 当每个用户必须拥有唯一的电子邮件地址或用户名时。
• 产品编号或订单编号 当产品 ID 或订单 ID 必须保持不重复时。
• 复合条件 当需要保证两列或多列组合的唯一性时。

3. 如何使用 UNIQUE 约束

在创建表时定义

在 MySQL 中，你可以在创建新表时直接定义 UNIQUE 约束。

示例：对单列应用

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    PRIMARY KEY (id)
);

在此示例中，email 列被添加了 UNIQUE 约束。尝试多次插入相同的电子邮件地址将导致错误。

示例：对多列（复合 UNIQUE 约束）应用

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    product_id INT,
    user_id INT,
    UNIQUE (product_id, user_id),
    PRIMARY KEY (order_id)
);

此示例确保 product_id 与 user_id 的组合唯一。当你想防止同一用户多次订购同一产品时，这非常有用。

向已有表添加约束

你也可以向已有表添加 UNIQUE 约束。

示例：向单列添加约束

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

示例：添加复合约束

ALTER TABLE orders
ADD CONSTRAINT unique_product_user UNIQUE (product_id, user_id);

如何删除约束

要删除已有约束，请使用以下 SQL 语句：

删除单列约束

ALTER TABLE users
DROP INDEX unique_email;

删除复合约束

ALTER TABLE orders
DROP INDEX unique_product_user;

4. UNIQUE 约束的重要注意事项

处理 NULL 值

当对允许 NULL 的列应用 UNIQUE 约束时，会有特殊规则。即使同一列中存在多个 NULL 值，MySQL 也不会报错。这是因为 MySQL 将 NULL 视为“与任何值都不相等”。

示例：

CREATE TABLE test_table (
    id INT AUTO_INCREMENT,
    column1 INT UNIQUE,
    PRIMARY KEY (id)
);

现在，插入以下数据：

INSERT INTO test_table (column1) VALUES (NULL);
INSERT INTO test_table (column1) VALUES (NULL);

不会出现错误。这是因为 NULL 值被视为不等于任何其他值，包括另一个 NULL。

Performance Impact

当您定义 UNIQUE 约束时，MySQL 会在内部自动创建索引。该索引有助于搜索和排序数据，但在插入或更新大量数据时可能会降低性能。

• Note 1: 当一次性插入大量数据时，需要检查 UNIQUE 约束，这可能会减慢处理速度。
• Note 2: 为了保持最佳性能，建议在数据量变得过大之前重建索引。

Handling Errors When They Occur

如果发生 UNIQUE 约束冲突，MySQL 会返回错误代码 “1062”。该错误在尝试插入重复数据时触发。

Example:

INSERT INTO users (email) VALUES ('test@example.com');
INSERT INTO users (email) VALUES ('test@example.com');

第二次插入将导致错误。

Solutions:

1. 在插入之前检查数据是否已存在。

   SELECT COUNT(*) FROM users WHERE email = 'test@example.com';
   

2. 使用 ON DUPLICATE KEY UPDATE 在检测到重复时更新数据。

   INSERT INTO users (email) VALUES ('test@example.com')
   ON DUPLICATE KEY UPDATE email = 'test@example.com';
   

5. Frequently Asked Questions (FAQ)

Q1: What is the difference between UNIQUE and PRIMARY KEY?

PRIMARY KEY 保证唯一性且不允许 NULL 值。每个表只能定义一个 PRIMARY KEY。相比之下，UNIQUE 约束可以在多个列上定义，并且允许 NULL 值。

Q2: What happens if you apply a UNIQUE constraint to a column that allows NULL values?

可以插入多个 NULL 值。这是因为 MySQL 将 NULL 值视为不可比较的。

Q3: When should a composite UNIQUE constraint be used?

当您希望在一组列的组合上保证唯一性时使用，例如确保 product_id 与 user_id 的组合唯一。这有助于防止用户多次订购同一产品。

Q4: Can you add a UNIQUE constraint if duplicate data already exists?

不能。如果已有重复数据，您无法添加 UNIQUE 约束。必须先删除或纠正这些重复记录，然后才能添加约束。

Q5: Does removing a UNIQUE constraint also remove its index?

是的。删除 UNIQUE 约束时，相关的索引也会被删除。

6. Summary

MySQL 的 UNIQUE 约束是确保数据唯一性的极其有用的特性。通过了解它与 PRIMARY KEY 的区别以及 NULL 值的处理方式，您可以显著提升数据库设计的质量。

正确使用 UNIQUE 约束对于维护数据库完整性尤为重要。请使用本文阐述的方法和注意事项来增强您的数据库设计与实现。

在下一篇文章中，我们将探讨如何在 MySQL 中有效使用索引，并讨论其他约束，如 FOREIGN KEY 和 CHECK。敬请期待！