MySQL - 唯一索引

MySQL - 唯一索引

数据库索引是特殊的查找表，数据库搜索引擎可以使用它们来加快数据检索。虽然标准索引主要用于性能，但 唯一索引 (Unique Index) 具有双重目的：它提高了查询性能，并通过确保索引列（或列组合）中的所有值都是唯一的来强制数据完整性。

此约束是维护准确可靠数据的基础。例如，您可以使用唯一索引来确保没有两个用户可以使用相同的电子邮件地址注册，或者每个产品都具有独特的 SKU (库存单位)。

理解唯一索引

唯一索引可以在表的一个或多个列上创建。一旦就位，数据库将拒绝任何会在索引列中创建重复值的 INSERT（插入）或 UPDATE（更新）操作。

关键属性

• 唯一性： 对于单列索引，该列中的每个值都必须是唯一的。对于多列（复合）索引，指定列中的值 组合 必须是唯一的。
• NULL 值： 唯一索引允许存在多个 NULL 值。这是因为 NULL 被视为未知值，并且两个 NULL 不被认为是彼此相等的。这与 PRIMARY KEY（主键）不同，主键是隐式唯一的，并且根本不允许 NULL 值。
• 性能： 像任何索引一样，唯一索引显著加快了在索引列上进行过滤的查询（WHERE email = '...'）。但是，它会给数据修改操作（INSERT、UPDATE）增加少量开销，因为数据库必须检查唯一性。

语法

您可以在创建表时添加唯一索引，或将其添加到现有表。

-- Method 1: Using CREATE UNIQUE INDEX on an existing table
CREATE UNIQUE INDEX index_name ON table_name (column1, column2...);

-- Method 2: Using ALTER TABLE (often preferred for clarity)
ALTER TABLE table_name ADD UNIQUE KEY index_name (column1, column2...);

-- Method 3: Defining it within CREATE TABLE
CREATE TABLE table_name (
    column1 DATATYPE,
    column2 DATATYPE,
    ...,
    UNIQUE KEY index_name (column1)
);

实际示例

创建单列唯一索引

让我们创建一个 USERS 表并确保每个 EMAIL 都是唯一的。

CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(50) NOT NULL,
    EMAIL VARCHAR(100) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Now, add a unique index to the EMAIL column
ALTER TABLE USERS ADD UNIQUE KEY `uk_email` (EMAIL);

uk_email 名称是唯一键的常见命名约定。

尝试插入重复值

首先，让我们插入一条有效记录。

INSERT INTO USERS (USERNAME, EMAIL) VALUES ('Alice', 'alice@example.com');
-- Query OK, 1 row affected

现在，让我们尝试插入另一个具有相同电子邮件的用户。

INSERT INTO USERS (USERNAME, EMAIL) VALUES ('Alice B.', 'alice@example.com');

MySQL 将拒绝此操作并返回错误，从而保护您的数据完整性。

ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'users.uk_email'

创建复合唯一索引

想象一个系统，用户可以对不同项目进行投票，但每个项目只能投票一次。我们需要确保 USER_ID 和 ITEM_ID 的组合是唯一的。

CREATE TABLE VOTES (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USER_ID INT NOT NULL,
    ITEM_ID INT NOT NULL,
    VOTE_VALUE INT NOT NULL, -- e.g., 1 for upvote, -1 for downvote
    -- Composite unique index to prevent a user from voting twice on the same item
    UNIQUE KEY `uk_user_item` (USER_ID, ITEM_ID)
);

有了这个结构：

• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (1, 101, 1); — 成功
• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (2, 101, 1); — 成功 (不同用户)
• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (1, 102, 1); — 成功 (不同项目)
• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (1, 101, -1); — 失败 (重复的 USER_ID 和 ITEM_ID 对)

管理和查看索引

您可以列出表上的所有索引以验证它们的存在和属性。

SHOW INDEX FROM USERS;

输出将是一个表格，显示每个索引的详细信息。要检查的关键列是 Key_name（索引的名称）、Column_name（索引列）和 Non_unique（唯一索引和主键的此值为 0）。

使用客户端程序创建唯一索引

添加索引是常见的数据库迁移任务。这些任务应通过脚本编写并进行版本控制。以下是使用 JDBC (Java Database Connectivity) 在现代 Java 应用程序中执行此操作的方法。

示例：Java 与 try-with-resources

Java




import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UniqueIndexManager {

    // Use constants for connection details
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        // SQL to create the table
        String createTableSql = "CREATE TABLE IF NOT EXISTS EMPLOYEES (" +
                                " ID INT AUTO_INCREMENT PRIMARY KEY," +
                                " EMPLOYEE_ID VARCHAR(20) NOT NULL," +
                                " NAME VARCHAR(100)" +
                                ");";

        // SQL to add the unique index
        String addIndexSql = "ALTER TABLE EMPLOYEES ADD UNIQUE KEY uk_employee_id (EMPLOYEE_ID)";

        // The try-with-resources statement ensures that each resource is closed at the end of the statement
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            System.out.println("Successfully connected to the database...");

            // Create table if it doesn't exist
            stmt.execute(createTableSql);
            System.out.println("Table 'EMPLOYEES' is ready.");

            // Add the unique index
            stmt.executeUpdate(addIndexSql);
            System.out.println("Unique index 'uk_employee_id' created successfully on 'EMPLOYEES' table.");

        } catch (SQLException e) {
            // A more specific error check
            if (e.getSQLState().equals("42000") && e.getMessage().contains("Duplicate key name")) {
                System.out.println("Index already exists. No action taken.");
            } else {
                System.err.println("An SQL error occurred:");
                e.printStackTrace();
            }
        } catch (Exception e) {
            System.err.println("A general error occurred:");
            e.printStackTrace();
        }
    }
}


Output
The likely output on the first run:

Connected successfully...
Table 'EMPLOYEES' is ready.
Unique index 'uk_employee_id' created successfully on 'EMPLOYEES' table.

On a subsequent run:

Connected successfully...
Table 'EMPLOYEES' is ready.
Index already exists. No action taken.