MySQL 数据库核心概念总结目录约束（Constraint）外键（Foreign Key）索引（Index）触

一、约束（Constraint）

概念

约束是数据规则，限制数据"能不能进表"。插入/更新时立即生效，失败直接报错。

基础表示例

CREATE TABLE user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  email VARCHAR(100),
  age INT,
  created_at DATETIME
);

约束类型

1. 主键约束（PRIMARY KEY）

id BIGINT PRIMARY KEY

特点：

唯一且不能为空
一张表只能有一个
自动创建索引

使用场景：

每张表必须有主键（强烈建议）

2. 唯一约束（UNIQUE）

email VARCHAR(100) UNIQUE

效果：

INSERT INTO user(email) VALUES ('a@test.com'); -- ✅ 成功
INSERT INTO user(email) VALUES ('a@test.com'); -- ❌ 重复，报错

使用场景：

用户名、邮箱、手机号等唯一性字段

3. 非空约束（NOT NULL）

username VARCHAR(50) NOT NULL

使用场景：

必填字段（用户名、密码等）

4. 默认值（DEFAULT）

age INT DEFAULT 18

使用场景：

状态字段、默认配置

5. 检查约束（CHECK）【MySQL 8+】

age INT CHECK (age >= 0)

使用场景：

年龄不能为负、金额 ≥ 0 等范围限制

约束关键字速记表

关键字	类型
PRIMARY KEY	约束
UNIQUE	约束
NOT NULL	约束
DEFAULT	约束
CHECK	约束
FOREIGN KEY	约束

二、外键（Foreign Key）

概念

外键是表和表之间的关系约束，确保引用完整性。

示例：用户 & 订单

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,
  FOREIGN KEY (user_id) REFERENCES user(id)
);

效果：

INSERT INTO orders(user_id) VALUES (999); -- ❌ 如果 user 表中不存在 id=999，报错

级联行为（重点）

FOREIGN KEY (user_id) REFERENCES user(id)
ON DELETE CASCADE
ON UPDATE CASCADE

行为	含义
CASCADE	跟着删 / 跟着改
SET NULL	设为 NULL
RESTRICT	不允许操作

使用场景：

强一致性系统
财务 / 订单关系清晰

⚠️ 注意事项：

很多互联网项目不用外键（性能考虑、分库分表困难）
通常用代码控制关系

三、索引（Index）

概念

索引是为了"查得快"，不限制数据对错，只影响查询速度。

重要： 索引 ≠ 约束

索引类型

1. 普通索引（INDEX / KEY）

CREATE INDEX idx_username ON user(username);

使用场景：

WHERE username = ?
LIKE 'abc%'（前缀匹配）

2. 唯一索引（UNIQUE INDEX）

CREATE UNIQUE INDEX idx_email ON user(email);

特点： 既是索引，也是约束

3. 联合索引（最重要）

CREATE INDEX idx_user_age ON user(username, age);

能用到索引的情况：

WHERE username = 'a'
WHERE username = 'a' AND age = 18

❌ 不能用到索引：

WHERE age = 18（没有从最左列开始）

使用场景：

多条件查询
排序 + 查询

4. 全文索引（FULLTEXT）

CREATE FULLTEXT INDEX idx_content ON article(content);

使用场景：

搜索（不是精确匹配）

索引关键字速记表

关键字	类型
INDEX	索引
KEY	索引（同 INDEX）
UNIQUE INDEX	索引 + 约束
FULLTEXT	索引
SPATIAL	索引

四、触发器（Trigger）

概念

触发器在 INSERT / UPDATE / DELETE 前后自动执行，用于"偷偷帮你干事"。

示例：自动写日志

CREATE TRIGGER before_user_insert
BEFORE INSERT ON user
FOR EACH ROW
SET NEW.created_at = NOW();

执行效果：

INSERT INTO user(username) VALUES ('tom');
-- created_at 自动填充为当前时间

使用场景：

自动填时间
写审计日志
同步统计表

⚠️ 缺点：

隐式逻辑，排查困难
大厂慎用

触发器关键字

关键字	含义
CREATE TRIGGER	触发器
BEFORE / AFTER	时机
INSERT / UPDATE / DELETE	事件
NEW / OLD	新旧数据

五、分区（Partition）

概念

分区是将一张表在物理上拆成多块，但逻辑上还是一张表。

示例：按时间分区

CREATE TABLE log (
  id BIGINT,
  created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

使用场景：

日志表
订单表（超大数据量）
历史数据归档

⚠️ 注意事项：

分区字段必须出现在主键里
不等于分表（对应用透明）

六、字段级自动行为

1. 时间字段自动行为（重点）

完整示例

created_at DATETIME 
  NOT NULL 
  DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP

组成部分：

部分	类型	作用
NOT NULL	约束	不允许为空
DEFAULT CURRENT_TIMESTAMP	默认值（函数）	插入时自动填当前时间
ON UPDATE CURRENT_TIMESTAMP	自动更新行为	更新行时自动改时间

⚠️ 重要说明：

不是触发器，是 MySQL 字段内建的自动时间机制
只有 CURRENT_TIMESTAMP 可以在 DEFAULT 中使用（NOW()、UUID()、RAND() 等都不支持）

生效时机

1️⃣ INSERT 时：

INSERT INTO user(username) VALUES ('tom');
-- created_at 自动 = 当前时间

2️⃣ UPDATE 时（重点）：

UPDATE user SET username = 'jerry' WHERE id = 1;
-- created_at 也会被自动更新（哪怕你没改这个字段）

✅ 标准设计（推荐）

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP

字段	行为
created_at	只在插入时赋值
updated_at	插入 + 每次更新

❌ 常见错误：

-- 错误：创建时间被更新了（语义错误）
created_at DATETIME 
  DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP

2. AUTO_INCREMENT（自增行为）

id BIGINT AUTO_INCREMENT

特点：

插入时自动增长
必须是 KEY
一表只能一个

3. GENERATED COLUMN（计算字段）

虚拟列（不占空间）

total_price DECIMAL(10,2)
  GENERATED ALWAYS AS (price * count) VIRTUAL

存储列（占空间）

total_price DECIMAL(10,2)
  GENERATED ALWAYS AS (price * count) STORED

注意： 只有 STORED 类型可以加索引

4. ENUM / SET（半隐藏类型）

status ENUM('active','disabled','deleted') DEFAULT 'active'

⚠️ 不推荐：

可读性差
修改麻烦
大厂基本不用（推荐 TINYINT）

5. 其他字段级能力

COMMENT（字段元信息）

status TINYINT COMMENT '1正常 0禁用'

特点： 不影响数据，但极其重要

VISIBLE / INVISIBLE（MySQL 8.0+）

password VARCHAR(255) INVISIBLE

效果： SELECT * 不返回该字段

字符规则

username VARCHAR(50)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci

作用： 控制是否区分大小写、排序规则

字段级能力分类速查表

类别	关键字
约束	NOT NULL, UNIQUE
默认值	DEFAULT
自动时间	DEFAULT CURRENT_TIMESTAMP
自动更新时间	ON UPDATE CURRENT_TIMESTAMP
自增	AUTO_INCREMENT
计算字段	GENERATED ALWAYS AS
外键行为	ON DELETE / ON UPDATE
可见性	VISIBLE / INVISIBLE
元信息	COMMENT
字符规则	CHARSET / COLLATE

七、快速参考表

功能对比总表

功能	解决什么问题	什么时候用
约束	数据对不对	保证数据质量
外键	表关系	强一致性系统
索引	查得快	高频查询
触发器	自动操作	数据自动维护
分区	表太大	上千万 / 上亿数据

记忆口诀

约束管对错，索引管快慢
外键管关系，触发器管自动
分区管体量，不管逻辑

关键字分类速记

约束关键字

PRIMARY KEY
UNIQUE
NOT NULL
DEFAULT
CHECK
FOREIGN KEY

索引关键字

INDEX / KEY
UNIQUE INDEX
FULLTEXT
SPATIAL

触发器关键字

CREATE TRIGGER
BEFORE / AFTER
INSERT / UPDATE / DELETE
NEW / OLD

八、实际应用建议

user 表推荐设计（✅）

CREATE TABLE user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP
);

不建议使用（❌）

ENUM（推荐用 TINYINT）
触发器（隐式逻辑，排查困难）
created_at 使用 ON UPDATE（语义错误）
复杂 GENERATED COLUMN（除非必要）

补充说明

DEFAULT 函数限制

在 DEFAULT 子句中，只有 CURRENT_TIMESTAMP 可以使用，其他函数都不支持：

函数	是否支持
CURRENT_TIMESTAMP	✅
NOW()	❌
UUID()	❌
RAND()	❌

外键级联行为详解

CASCADE：主表删除/更新时，从表跟着删除/更新
SET NULL：主表删除/更新时，从表相关字段设为 NULL
RESTRICT：主表删除/更新时，如果从表有引用，不允许操作

联合索引最左前缀原则

联合索引 (username, age) 能用到索引的查询：

✅ WHERE username = ?
✅ WHERE username = ? AND age = ?
❌ WHERE age = ?（没有从最左列开始）

分区 vs 分表

分区：逻辑上一张表，物理上多块，对应用透明
分表：逻辑上多张表，应用需要路由

总结

约束：保证数据正确性，插入/更新时立即检查
外键：维护表间关系，但性能敏感场景慎用
索引：提升查询速度，注意联合索引的最左前缀原则
触发器：自动执行逻辑，但会增加排查难度
分区：处理大数据量，对应用透明
字段级自动行为：合理使用可简化代码，注意语义正确性

核心原则：

约束管对错，索引管快慢
外键管关系，触发器管自动
分区管体量，不管逻辑