MySQL - 唯一键

MySQL - UNIQUE 约束

MySQL 中的 UNIQUE 约束是一条规则，它确保列或一组列中的所有值都是互不相同的。它是强制数据完整性、防止重复记录以及确保特定字段可以唯一标识表中行的基本工具。

当您应用 UNIQUE 约束时，MySQL 会自动在指定的列上创建 UNIQUE 索引。这不仅强制了唯一性，还有助于加快基于这些列进行筛选的查询。

`UNIQUE` 与 `PRIMARY KEY`：主要区别

虽然这两种约束都强制唯一性，但它们有重要的区别：

特性	主键	UNIQUE 约束
NULL 值	不允许 `NULL` 值。主键列必须始终有值。	允许存在多个 `NULL` 值。`NULL` 被认为与其他 `NULL` 值不相等。
每表数量	一个表只能有一个 `PRIMARY KEY`（主键）。	一个表可以有多个 `UNIQUE` 约束。
聚簇索引	在 InnoDB 存储引擎中，`PRIMARY KEY` 也是聚簇索引，它决定了行的物理存储顺序。	创建一个辅助的非聚簇索引。
主要目的	作为表中行的主要、确定性标识符。	确保辅助键或替代键（例如，电子邮件地址、产品 SKU）的唯一性。

在新表上创建 `UNIQUE` 约束

您可以使用 CREATE TABLE 语句在创建表时定义 UNIQUE 约束。

语法

-- Method 1: Inline definition
CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Method 2: Table-level constraint definition (better for named constraints)
CREATE TABLE Products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(20) NOT NULL,
    product_name VARCHAR(255),
    CONSTRAINT uc_product_sku UNIQUE (sku)
);

命名您的约束（例如 uc_product_sku）是一种最佳实践，因为这使得后续识别和删除它变得容易得多。

处理 `UNIQUE` 约束冲突

如果您尝试 INSERT 或 UPDATE 一行，其中 UNIQUE 列的值已存在，MySQL 将拒绝该操作并返回错误。

-- Let's insert a user
INSERT INTO Users (username, email) VALUES ('john.doe', 'john.doe@example.com');

-- Now, let's try to insert another user with the same email
INSERT INTO Users (username, email) VALUES ('johndoe2', 'john.doe@example.com');

第二个 INSERT 语句将失败并返回类似以下内容的错误消息：

ERROR 1062 (23000): Duplicate entry 'john.doe@example.com' for key 'users.email'

您的应用程序代码必须准备好优雅地处理此错误。

向现有表添加 `UNIQUE` 约束

您可以使用 ALTER TABLE 语句向已存在的表添加 UNIQUE 约束。如果该列已包含重复值，此操作将失败。

语法

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

示例

假设我们有一个 Employees 表，并希望确保 employee_id 是唯一的。

ALTER TABLE Employees
ADD CONSTRAINT uc_employee_id UNIQUE (employee_id);

创建复合 `UNIQUE` 约束

UNIQUE 约束也可以应用于多个列。这确保了这些列中值的组合是唯一的，尽管单个值可以重复。

一个常见的例子是防止学生多次注册同一门课程。

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    grade CHAR(1),
    CONSTRAINT uc_student_course UNIQUE (student_id, course_id)
);

在此表中，student_id 为 1 的学生可以注册 course_id 为 101 的课程，student_id 为 2 的学生也可以注册 course_id 为 101 的课程。但是，student_id 为 1 的学生不能第二次注册 course_id 为 101 的课程。

删除 `UNIQUE` 约束

要删除 UNIQUE 约束，请使用 ALTER TABLE ... DROP CONSTRAINT 语句。这就是为什么命名约束非常有帮助的原因。

ALTER TABLE Products DROP CONSTRAINT uc_product_sku;

如果您没有为约束命名，MySQL 会自动分配一个名称（通常与列名相同）。您可以使用 SHOW CREATE TABLE Products; 找到该名称，然后使用该名称删除它。

使用客户端程序创建 `UNIQUE` 约束

这里是使用不同语言创建带有 UNIQUE 约束的表并处理潜在重复条目错误的示例。

Node.js (mysql2)
Python (mysql-connector-python)
Java (JDBC)
PHP (mysqli)


This Node.js script attempts to insert a user and catches the specific error for duplicate entries.

```javascript
// 依赖：npm install mysql2
const mysql = require('mysql2/promise');

async function addUser(username, email) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost', user: 'root', password: 'your_password', database: 'your_database'
    });

    const sql = 'INSERT INTO Users (username, email) VALUES (?, ?)';
    await connection.execute(sql, [username, email]);
    console.log(`User '${username}' added successfully.`);

  } catch (error) {
    // MySQL 重复条目错误码是 1062
    if (error.code === 'ER_DUP_ENTRY') {
        console.error(`Error: A user with that username or email already exists.`);
    } else {
        console.error(`An unexpected error occurred: ${error.message}`);
    }
  } finally {
    if (connection) await connection.end();
  }
}

// 第一次调用会成功，第二次调用会因自定义错误消息而失败
addUser('jane.doe', 'jane.doe@example.com');
// addUser('jane.doe', 'another.email@example.com'); // 这也会失败

Python’s mysql.connector has specific error codes you can check for.

# 依赖：pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def add_user(username, email):
    try:
        with mysql.connector.connect(user='root', password='your_password', database='your_database') as conn:
            with conn.cursor() as cursor:
                sql = 'INSERT INTO Users (username, email) VALUES (%s, %s)'
                cursor.execute(sql, (username, email))
                conn.commit()
                print(f"User '{username}' added successfully.")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_DUP_ENTRY:
            print(f"Error: A user with username '{username}' or email '{email}' already exists.")
        else:
            print(f"Database error: {err}")

# 示例用法
add_user('peter.jones', 'peter.jones@example.com')
add_user('another.user', 'peter.jones@example.com') # 这将触发错误

In Java, you catch SQLException and check the vendor-specific error code.

import java.sql.*;

public class UniqueConstraintExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "root";
        String pass = "your_password";
        String sql = "INSERT INTO Users (username, email) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, "susan.adams");
            pstmt.setString(2, "s.adams@example.com");
            pstmt.executeUpdate();
            System.out.println("User 'susan.adams' added successfully.");

        } catch (SQLException e) {
            // MySQL 重复条目错误码是 1062
            if (e.getErrorCode() == 1062) {
                System.err.println("Error: A user with that username or email already exists.");
            } else {
                e.printStackTrace();
            }
        }
    }
}

In PHP, you can check the errno property of the mysqli object after a failed query.

<?php
$mysqli = new mysqli('localhost', 'root', 'your_password', 'your_database');
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$username = 'test_user';
$email = 'test@example.com';

$sql = "INSERT INTO Users (username, email) VALUES (?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ss", $username, $email);

if ($stmt->execute()) {
    echo "User '$username' added successfully.";
} else {
    // MySQL 重复条目错误码是 1062
    if ($mysqli->errno == 1062) {
        echo "Error: Username or email already exists.";
    } else {
        echo "An error occurred: " . $mysqli->error;
    }
}

$stmt->close();
$mysqli->close();
?>

MySQL - 唯一键

MySQL - UNIQUE 约束

UNIQUE 与 PRIMARY KEY：主要区别

在新表上创建 UNIQUE 约束

语法

处理 UNIQUE 约束冲突

向现有表添加 UNIQUE 约束

语法

示例

创建复合 UNIQUE 约束

删除 UNIQUE 约束

使用客户端程序创建 UNIQUE 约束