MySQL中唯一索引和主键的区别

MySQL中唯一索引和主键的区别

一、概述

1.1 基本概念

在MySQL数据库中，唯一索引（UNIQUE INDEX）和主键（PRIMARY KEY）都是用于保证数据唯一性的重要机制，但它们在功能、特性和使用场景上存在显著差异。

1.2 核心差异

二、主键详解

2.1 主键特性

基本定义

-- 主键的基本特性
-- 1. 唯一性：主键值在表中必须唯一
-- 2. 非空性：主键字段不能为NULL
-- 3. 唯一性：每个表只能有一个主键
-- 4. 聚集索引：主键自动创建聚集索引

-- 创建带主键的表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESCRIBE users;

-- 查看索引信息
SHOW INDEX FROM users;

主键约束

-- 主键约束示例
CREATE TABLE products (
    product_id INT,
    product_code VARCHAR(20),
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    
    -- 单字段主键
    PRIMARY KEY (product_id)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    
    -- 复合主键
    PRIMARY KEY (order_id, product_id)
);

-- 查看主键信息
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'mydb' 
AND CONSTRAINT_NAME = 'PRIMARY';

2.2 主键的自动递增

AUTO_INCREMENT特性

-- 自动递增主键
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据（不指定id）
INSERT INTO categories (name, description) VALUES 
('电子产品', '各类电子产品'),
('服装鞋帽', '时尚服装和鞋帽'),
('家居用品', '家居生活用品');

-- 查看插入的数据
SELECT * FROM categories;

-- 查看AUTO_INCREMENT当前值
SHOW TABLE STATUS LIKE 'categories';

-- 重置AUTO_INCREMENT
ALTER TABLE categories AUTO_INCREMENT = 1;

自增主键的注意事项

-- 自增主键的间隙问题
CREATE TABLE test_auto_increment (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 插入数据
INSERT INTO test_auto_increment (name) VALUES ('A'), ('B'), ('C');

-- 删除中间记录
DELETE FROM test_auto_increment WHERE id = 2;

-- 插入新记录（会使用id=4，而不是2）
INSERT INTO test_auto_increment (name) VALUES ('D');

-- 查看结果
SELECT * FROM test_auto_increment;

-- 手动指定自增值
INSERT INTO test_auto_increment (id, name) VALUES (2, 'E');

2.3 主键的聚集索引

聚集索引特性

-- 主键自动创建聚集索引
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

-- 查看索引信息
SHOW INDEX FROM employees;

-- 分析查询执行计划
EXPLAIN SELECT * FROM employees WHERE employee_id = 1;

-- 查看表的存储信息
SELECT 
    table_name,
    engine,
    table_rows,
    avg_row_length,
    data_length,
    index_length
FROM information_schema.tables 
WHERE table_schema = 'mydb' 
AND table_name = 'employees';

三、唯一索引详解

3.1 唯一索引特性

基本定义

-- 唯一索引的基本特性
-- 1. 唯一性：索引字段值必须唯一
-- 2. 可空性：允许NULL值（除非明确约束）
-- 3. 数量限制：可以有多个唯一索引
-- 4. 非聚集索引：创建非聚集索引

-- 创建带唯一索引的表
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_code VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看索引信息
SHOW INDEX FROM customers;

-- 另一种创建唯一索引的方式
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(20),
    sku VARCHAR(30),
    name VARCHAR(100),
    
    UNIQUE KEY uk_product_code (product_code),
    UNIQUE KEY uk_sku (sku)
);

唯一索引约束

-- 唯一索引约束示例
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    
    -- 创建唯一索引
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone)
);

-- 测试唯一性约束
INSERT INTO users (username, email, phone) VALUES 
('john_doe', 'john@example.com', '1234567890');

-- 尝试插入重复值（会失败）
INSERT INTO users (username, email, phone) VALUES 
('john_doe', 'jane@example.com', '0987654321');