在 MySQL 数据库开发与设计中，主键、外键、索引是保障数据完整性和查询性能的基础，CHECK 约束用于精细化数据校验，存储过程和触发器则能实现 SQL 逻辑的封装与自动化执行。

前置准备：创建测试库与表

首先创建测试数据库和数据表，后续所有案例均基于这两张表：

-- 创建数据库
create database jx character set utf8;
use jx;

-- 创建用户表 users
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- 创建订单表 orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    user_id INT,
    owner VARCHAR(50) NOT NULL,
    level INT NOT NULL,
    age INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查看表结构
desc users;
desc orders;

一、主键（Primary Key）

主键是表的唯一标识，是关系型数据库的核心基础。

核心特性

1. 唯一标识单条记录，无重复值
2. 一张表只能有一个主键
3. 支持单列 / 多列组合主键
4. 自动强制为NOT NULL

核心作用

• 唯一标识数据，精准定位记录
• 保障数据唯一性，避免脏数据
• 自带索引，大幅提升主键查询速度
• 作为外键关联的基础，建立表间关系

常用语法

-- 1. 创建表时定义主键
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- 2. 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 3. 新增主键
ALTER TABLE users ADD PRIMARY KEY (id);

二、外键（Foreign Key）

外键用于关联两张表，强制数据的参照完整性（子表数据必须在父表中存在）。

核心特性

1. 子表外键列 = 父表主键列
2. 防止产生孤立、无效的关联数据
3. 支持级联更新 / 删除

核心作用

• 维护多表数据一致性
• 规范表与表之间的关联关系
• 避免非法数据插入

常用语法

-- 1. 创建表时定义外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 2. 删除外键（需先查看外键名）
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

-- 3. 新增外键（自定义约束名，推荐）
ALTER TABLE orders ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

语法解析

• ADD CONSTRAINT：添加约束并自定义名称
• FOREIGN KEY (user_id)：指定子表外键列
• REFERENCES users(id)：指定关联的父表 + 主键

---

三、索引（Index）

索引是提升查询速度的核心工具，本质是优化数据查找结构的目录。

核心特性

• 快速定位数据，减少全表扫描
• 分为唯一索引 / 普通索引
• 索引会占用存储空间，写入数据时会有性能损耗

索引分类（高频实用版）

1. 按数据结构分类

• B+tree 索引：MySQL 默认索引，支持范围查询 / 排序，适用绝大多数场景
• Hash 索引：仅支持等值查询，速度极快，Memory 引擎适用
• Fulltext 索引：全文检索，用于文本模糊搜索

2. 按物理存储分类

• 聚簇索引：数据和索引存在一起，主键默认就是聚簇索引，一张表仅一个
• 二级索引：辅助索引，叶子节点存储主键值，需回表查询

3. 按字段特性分类

• 主键索引：主键自动生成，唯一 + 非空
• 普通索引：最基础索引，允许重复值
• 前缀索引：对字符串前 N 个字符建索引，节省空间

4. 按字段个数分类

• 单列索引：单个字段建索引
• 联合索引：多个字段组合建索引，遵循最左匹配原则

常用语法

-- 1. 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 2. 创建联合索引
CREATE INDEX orders_index ON orders(owner, level);

-- 3. 查看索引
show index from users;

-- 4. 删除索引
DROP INDEX idx_username ON users;

-- 5. 强制使用索引
select * from users force index (idx_username) where username='tom';

执行计划分析（EXPLAIN）

使用EXPLAIN追踪索引是否生效，是 SQL 优化必备工具：

explain select id from users;

关键字段解析

• type：查询性能（最优→最差：const > ref > range > index > ALL）
• key：实际使用的索引
• rows：扫描的行数（越小越好）
• Extra：额外优化信息（Using index代表索引高效命中）

---

四、CHECK 约束

MySQL 8.0.16+ 正式支持CHECK约束，用于自定义数据校验规则，在数据库层面拦截非法数据。

核心特性

• 自定义列数据规则（数值范围、字符串格式等）
• 插入 / 更新数据时自动校验

核心作用

• 数据合法性验证
• 统一业务规则，避免应用层重复校验
• 保障数据质量

常用语法

-- 1. 给用户名添加格式约束（字母开头，长度≥3）
ALTER TABLE users ADD CONSTRAINT chk_username_format 
CHECK (username REGEXP '^[a-zA-Z][a-zA-Z0-9_]{2,}$');

-- 2. 查看约束
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME='users'\G;

-- 3. 删除约束
ALTER TABLE users DROP CONSTRAINT chk_username_format;

实战应用场景

-- 限制年龄 18-60 岁
CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18 AND age <= 60)
);

-- 限制订单金额必须大于0
ALTER TABLE orders ADD CHECK (total_amount > 0);

五、存储过程（Stored Procedure）

存储过程是预先编译并存储在数据库中的 SQL 集合，可重复调用，封装业务逻辑。

核心优点

1. 预编译，执行速度快
2. 减少网络传输，仅传调用命令
3. 统一业务逻辑，便于维护
4. 提升数据安全性

常用语法

-- 1. 创建存储过程（查询所有用户）
DELIMITER //  -- 临时修改语句结束符
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM jx.users;
END //
DELIMITER ;  -- 恢复结束符

-- 2. 带参数的存储过程（根据ID查询用户）
DELIMITER //
CREATE PROCEDURE get_user_info(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- 3. 调用存储过程
CALL GetAllUsers();
CALL get_user_info(1);

-- 4. 查看存储过程
SHOW PROCEDURE status where Db='jx'\G;

-- 5. 删除存储过程
DROP PROCEDURE IF EXISTS GetAllUsers;

适用场景

• 复杂多表查询 / 数据统计
• 固定业务逻辑封装（如订单结算）
• 定时数据处理任务

---

六、触发器（Trigger）

触发器是与表绑定的自动执行的存储过程，在INSERT/UPDATE/DELETE时自动触发。

核心特性

• 自动触发，无需手动调用
• 分为BEFORE（执行前）和AFTER（执行后）
• 可使用NEW（新数据）、OLD（旧数据）操作数据

核心概念

1. 触发时间
   • BEFORE：操作前执行，用于数据校验 / 预处理
   • AFTER：操作后执行，用于日志记录 / 关联操作
2. NEW/OLD
   • NEW：代表插入 / 更新后的新数据
   • OLD：代表更新 / 删除前的旧数据

常用语法

-- 1. 插入订单前，自动设置订单日期
ALTER TABLE orders ADD COLUMN order_date DATE;

DELIMITER //
CREATE TRIGGER set_order_date_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.order_date = CURDATE();
END //
DELIMITER ;

-- 2. 删除用户后，自动删除关联订单
DELIMITER //
CREATE TRIGGER delete_order_after_user
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    DELETE FROM orders WHERE user_id = OLD.id;
END //
DELIMITER ;

-- 3. 查看触发器
show triggers\G;

-- 4. 删除触发器
DROP TRIGGER set_order_date_before_insert;

总结

本文覆盖了 MySQL 最核心的六大知识点，总结如下：

1. 主键 / 外键：保障数据唯一性和表关联完整性
2. 索引：优化查询速度，EXPLAIN是索引优化神器
3. CHECK 约束：数据库层自定义数据校验规则
4. 存储过程：封装 SQL 逻辑，复用性强、性能高
5. 触发器：自动化执行 SQL，适配数据联动场景