MySQL 约束与内置函数详解：后端开发必备，从入门到实战避坑

前言：为什么后端开发必须懂约束与函数？

在日常的 Java 后端 CRUD 开发中，我们频繁地与数据库打交道。一张设计规范的数据库表，不仅是数据的容器，更是业务规则与数据一致性的第一道防线。你是否遇到过以下场景？

• 场景1：用户注册时，手机号重复插入，导致业务逻辑混乱。
• 场景2：订单金额字段被程序意外写入 NULL，对账时出现诡异错误。
• 场景3：需要统计“本月每个用户的订单总金额”，写出的 SQL 冗长且低效。
• 场景4：线上查询突然变慢，排查后发现是 WHERE 条件中对字段使用了函数，导致索引失效。

这些问题，本质上都与 MySQL 的约束（Constraints）和内置函数（Functions） 密切相关。约束定义了数据的“法律”，保证存入的数据是合法、一致的；而函数则是处理数据的“工具”，能让我们用更简洁、高效的方式操作和计算数据。

本文将系统性地详解 MySQL 的五大约束和常用内置函数，结合大量可直接运行的 SQL 示例，并深入探讨生产环境中的设计规范、性能陷阱及面试高频考点。目标是让你不仅会用，更懂其背后的原理与取舍。

第一部分：MySQL 五大约束详解

约束是施加在表列上的规则，用于限制表中数据的完整性。主要有以下五种：

1. 主键约束 (PRIMARY KEY)

作用：唯一标识表中的每一行记录。一张表只能有一个主键，且主键列不能包含 NULL 值。

语法与示例：

— 创建表时定义主键（单列主键）
CREATE TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` VARCHAR(50) NOT NULL COMMENT '用户名',
PRIMARY KEY (`id`) — 定义 id 列为主键
) ENGINE=InnoDB COMMENT='用户表';

— 创建表时定义联合主键（多列组合唯一标识一行）
CREATE TABLE `user_role` (
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
`role_id` INT NOT NULL COMMENT '角色ID',
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `role_id`) — user_id 和 role_id 共同组成主键
) ENGINE=InnoDB COMMENT='用户角色关联表';

底层特性与使用场景：

• 聚簇索引：InnoDB 表中，主键即是聚簇索引，数据行按主键顺序存储。因此主键应选择递增、有序、长度短的列（如自增 BIGINT），避免随机主键（如 UUID）导致页分裂，影响插入性能。
• 业务主键 vs 代理主键：
  • id BIGINT AUTO_INCREMENT（代理主键）：无业务意义，单纯用于标识行。推荐使用，简单高效。
  • 身份证号、订单号（业务主键）：具有业务含义。需确保其唯一且不变，否则修改成本极高。
• 生产环境取舍：强烈建议使用 BIGINT UNSIGNED AUTO_INCREMENT 作为主键。除非有极强的理由（如分库分表全局唯一需求），否则不要使用 UUID 或业务字段。

2. 唯一约束 (UNIQUE)

作用：确保列（或列组合）中的每行数据都是唯一的，允许 NULL 值（但只能有一个 NULL，具体取决于数据库模式）。

语法与示例：

— 创建表时定义唯一约束
CREATE TABLE `user` (
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`email` VARCHAR(100) NOT NULL COMMENT '邮箱',
`phone` VARCHAR(20) COMMENT '手机号',
UNIQUE KEY `uk_email` (`email`), — 为 email 列创建唯一约束，并命名索引为 uk_email
UNIQUE KEY `uk_phone` (`phone`) — phone 列也唯一
) ENGINE=InnoDB;

— 修改表，添加唯一约束
ALTER TABLE `user` ADD UNIQUE KEY `uk_username` (`username`);

与主键的差异：

使用场景：防止业务关键数据重复，如用户邮箱、手机号、身份证号等。

3. 非空约束 (NOT NULL)

作用：强制列不能接受 NULL 值。这是最简单也最重要的数据完整性约束。

语法与示例：

CREATE TABLE `order` (
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`order_no` VARCHAR(32) NOT NULL COMMENT '订单号，业务关键字段不能为空',
`amount` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '订单金额，默认0',
`user_id` BIGINT NOT NULL COMMENT '用户ID，关联字段不能为空',
`remark` VARCHAR(500) COMMENT '备注，此字段允许为空'
) ENGINE=InnoDB;

生产环境规范：核心业务字段（如金额、状态、外键关联ID）必须定义为 NOT NULL。NULL 值在查询、计算（如 SUM）时容易引发意料之外的行为，增加程序复杂度。

4. 默认值约束 (DEFAULT)

作用：当插入数据未指定该列值时，自动填充一个预定义的值。

语法与示例：

CREATE TABLE `article` (
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(200) NOT NULL,
`content` TEXT NOT NULL,
`view_count` INT NOT NULL DEFAULT 0 COMMENT '浏览量，默认为0',
`is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标志：0-未删除，1-已删除',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，自动插入当前时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间，自动更新'
) ENGINE=InnoDB;

业务场景：

• view_count DEFAULT 0：数值型字段初始状态。
• is_deleted DEFAULT 0：逻辑删除标志位。
• created_at DEFAULT CURRENT_TIMESTAMP：强烈推荐，由数据库自动维护创建时间，比程序写入更可靠。
• updated_at DEFAULT … ON UPDATE …：自动记录最后修改时间。

5. 外键约束 (FOREIGN KEY)

作用：保证一个表中的数据匹配另一个表中数据的参照完整性。子表（从表）的外键列值必须在主表（父表）的主键列中存在。

语法与示例：

— 父表
CREATE TABLE `department` (
`dept_id` INT PRIMARY KEY AUTO_INCREMENT,
`dept_name` VARCHAR(50) NOT NULL
);

— 子表，建立外键约束
CREATE TABLE `employee` (
`emp_id` INT PRIMARY KEY AUTO_INCREMENT,
`emp_name` VARCHAR(50) NOT NULL,
`dept_id` INT,
CONSTRAINT `fk_emp_dept` — 外键约束命名
FOREIGN KEY (`dept_id`) — 本表的 dept_id 列为外键
REFERENCES `department` (`dept_id`) — 引用 department 表的 dept_id 列
ON DELETE SET NULL — 当父表记录被删除，子表对应外键设为 NULL
ON UPDATE CASCADE — 当父表主键更新，子表外键级联更新
);

外键动作详解：

• ON DELETE:
  • RESTRICT（默认）：如果子表有匹配记录，禁止删除父表记录。
  • CASCADE：级联删除子表匹配记录。（危险！容易误删大量数据）
  • SET NULL：将子表外键列设为 NULL（要求该列允许 NULL）。
  • NO ACTION：同 RESTRICT。
• ON UPDATE:
  • CASCADE：级联更新子表外键值。（常用，用于主键值变更场景）

外键的优缺点与生产环境取舍：

• 优点：
• 数据一致性：数据库层面保证关联数据有效，避免“孤儿记录”。
• 级联操作：简化应用层代码。
• 缺点（也是很多项目禁用外键的原因）：
• 性能开销：每次 INSERT/UPDATE/DELETE 子表或父表时，都需要检查外键约束，在高并发场景下可能成为瓶颈。
• 死锁风险：外键检查可能增加死锁概率。
• 分库分表困难：在分布式数据库架构中，外键难以实现和维护。
• 数据迁移与恢复复杂：导入导出数据时需要特别注意顺序。
• 生产建议：
  • 中小型单体应用：可以使用外键来保证强一致性，简化开发。
  • 高并发、分布式、微服务架构：通常禁用数据库外键，将数据一致性保证上移到应用层（通过事务、业务逻辑校验）或使用最终一致性方案。

第二部分：MySQL 内置函数分类与实战

MySQL 提供了丰富的内置函数，用于数据计算、转换和聚合。合理使用函数能极大提升开发效率。

1. 字符串函数

处理文本数据。

— CONCAT: 连接字符串
SELECT CONCAT('Hello', ' ', 'World') AS greeting; — 结果: Hello World
— 业务场景：拼接用户全名 SELECT CONCAT(last_name, first_name) AS full_name FROM users;

— SUBSTRING / SUBSTR: 截取子串
SELECT SUBSTRING('MySQL Tutorial', 7, 5) AS sub; — 从第7个字符开始截取5个，结果: Tutor

— REPLACE: 替换字符串
SELECT REPLACE('foo bar foo', 'foo', 'hello') AS replaced; — 结果: hello bar hello

— TRIM / LTRIM / RTRIM: 去除空格
SELECT TRIM(' MySQL ') AS trimmed; — 结果: 'MySQL'

— UPPER / LOWER: 大小写转换
SELECT UPPER('mysql') AS upper_case; — 结果: MYSQL

— LENGTH / CHAR_LENGTH: 长度（字节/字符）
SELECT LENGTH('中国') AS byte_len, CHAR_LENGTH('中国') AS char_len; — 结果: 6, 2 (UTF-8编码)

2. 数值函数

进行数学运算。

— ROUND: 四舍五入
SELECT ROUND(123.4567, 2) AS rounded; — 结果: 123.46
— 业务场景：计算订单平均金额 SELECT ROUND(AVG(amount), 2) FROM orders;

— CEIL / FLOOR: 向上/向下取整
SELECT CEIL(123.45), FLOOR(123.45); — 结果: 124, 123

— ABS: 绝对值
SELECT ABS(–100); — 结果: 100

— MOD: 取模
SELECT MOD(10, 3); — 结果: 1

— RAND: 生成随机数 (0~1)
SELECT RAND(); — 每次结果不同
— 业务场景：随机抽取一条记录 SELECT * FROM articles ORDER BY RAND() LIMIT 1; (**注意：大数据集性能极差**)

3. 日期与时间函数

处理日期和时间。

— NOW() / CURDATE() / CURTIME(): 当前日期时间
SELECT NOW(), CURDATE(), CURTIME(); — 如: 2023-10-27 14:30:00, 2023-10-27, 14:30:00

— DATE_FORMAT: 格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted; — 结果: 2023-10-27 14:30:00
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y') AS en_date; — 结果: Friday, October 27, 2023

— DATE_ADD / DATE_SUB: 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week; — 加7天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month; — 减1个月
— 业务场景：查询最近30天的订单 SELECT * FROM orders WHERE order_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

— DATEDIFF: 计算日期差（天数）
SELECT DATEDIFF('2023-10-31', '2023-10-27') AS day_diff; — 结果: 4

— YEAR / MONTH / DAY: 提取日期部分
SELECT YEAR(NOW()) AS curr_year, MONTH(NOW()) AS curr_month, DAY(NOW()) AS curr_day;

4. 聚合函数

对一组值执行计算，返回单个值。常与 GROUP BY 子句一起使用。

— 示例数据表 sales
— | region | product | amount |
— | East | A | 100 |
— | East | B | 200 |
— | West | A | 150 |

— COUNT: 计数
SELECT COUNT(*) AS total_rows FROM sales; — 统计行数，结果: 3
SELECT COUNT(DISTINCT region) AS region_count FROM sales; — 统计不重复的区域数，结果: 2

— SUM: 求和
SELECT SUM(amount) AS total_amount FROM sales; — 结果: 450

— AVG: 平均值
SELECT AVG(amount) AS avg_amount FROM sales; — 结果: 150

— MAX / MIN: 最大/最小值
SELECT MAX(amount) AS max_amount, MIN(amount) AS min_amount FROM sales; — 结果: 200, 100

— GROUP BY 分组聚合
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;
— 结果:
— | region | region_total |
— | East | 300 |
— | West | 150 |

— **WHERE 与 HAVING 的区别（面试高频）**
— WHERE: 在分组 **前** 过滤行，不能使用聚合函数。
— HAVING: 在分组 **后** 过滤分组结果，可以使用聚合函数。
SELECT region, SUM(amount) AS region_total
FROM sales
— WHERE product = 'A' — 先过滤出 product A 的记录，再分组求和
GROUP BY region
HAVING SUM(amount) > 100; — 分组后，只显示总和大于100的分组

5. 流程控制函数

实现条件逻辑。

— IF: 三元运算符
SELECT IF(amount > 100, 'High', 'Low') AS amount_level FROM orders;
— 业务场景：标记订单金额等级

— CASE WHEN: 多条件判断（更强大）
SELECT
order_id,
amount,
CASE
WHEN amount >= 500 THEN 'A'
WHEN amount >= 200 THEN 'B'
ELSE 'C'
END AS amount_grade
FROM orders;
— 业务场景：用户等级划分、状态映射等

— IFNULL / COALESCE: 处理 NULL 值
SELECT IFNULL(NULL, 'default_value'); — 结果: default_value
SELECT COALESCE(NULL, NULL, 'first_non_null'); — 结果: first_non_null
— COALESCE 返回参数列表中第一个非 NULL 值，比 IFNULL 更通用。

6. 自定义函数 (UDF)

当内置函数不满足需求时，可以创建自定义函数。

— 示例：创建一个函数，计算折扣后价格
DELIMITER //
CREATE FUNCTION calculate_discount_price(original_price DECIMAL(10,2), discount_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE final_price DECIMAL(10,2);
SET final_price = original_price * (1 – discount_rate);
RETURN final_price;
END //
DELIMITER ;

— 使用自定义函数
SELECT product_name, price, calculate_discount_price(price, 0.2) AS discounted_price FROM products;

注意：UDF 虽然灵活，但可能影响性能，且增加数据库维护复杂度。简单的计算尽量在应用层完成。

第三部分：约束与函数结合实战

实战1：联合主键 + 日期函数自动填充

— 日志表：用 (user_id, log_date) 作为联合主键，确保用户每天只有一条汇总日志
CREATE TABLE `user_daily_log` (
`user_id` BIGINT NOT NULL COMMENT '用户ID',
`log_date` DATE NOT NULL COMMENT '日志日期',
`pv_count` INT DEFAULT 0 COMMENT '页面浏览量',
`uv_count` INT DEFAULT 0 COMMENT '独立访客',
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `log_date`), — 联合主键
KEY `idx_log_date` (`log_date`)
) ENGINE=InnoDB;

— 插入数据，利用 DATE(NOW()) 函数自动获取当前日期
INSERT INTO `user_daily_log` (`user_id`, `log_date`, `pv_count`)
VALUES (1001, DATE(NOW()), 1)
ON DUPLICATE KEY UPDATE `pv_count` = `pv_count` + 1;
— 业务场景：用户行为日统计，利用联合主键+ON DUPLICATE KEY UPDATE实现原子性累加，避免并发下重复插入。

实战2：外键约束的替代方案（应用层校验）

在生产环境中，尤其是高并发或分布式系统，我们常禁用数据库外键，转而使用应用层逻辑保证数据一致性。以下是一个典型的订单-订单项场景：

— 1. 父表：订单表（无外键约束）
CREATE TABLE `order` (
`order_id` VARCHAR(32) PRIMARY KEY COMMENT '订单号',
`user_id` BIGINT NOT NULL,
`total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0-待支付,1-已支付,2-已取消',
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;

— 2. 子表：订单项表（无外键约束，但存储了 order_id）
CREATE TABLE `order_item` (
`item_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`order_id` VARCHAR(32) NOT NULL COMMENT '关联订单号',
`product_id` BIGINT NOT NULL,
`quantity` INT NOT NULL DEFAULT 1,
`price` DECIMAL(10,2) NOT NULL,
KEY `idx_order_id` (`order_id`),
KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB;

— 3. 应用层事务示例（Java伪代码）：
— @Transactional
— public void createOrderWithItems(OrderDTO orderDTO) {
— // 1. 插入订单主表
— orderMapper.insert(order);
— // 2. 批量插入订单项（此时order_id已存在，由应用层保证）
— orderItemMapper.batchInsert(items);
— // 3. 其他业务逻辑…
— }

关键点：

• 数据一致性：通过应用层事务保证 order 和 order_item 的插入原子性。
• 查询关联：通过 JOIN 或应用层多次查询实现关联查询。
• 删除/更新：删除订单时，需在事务中先删子表 (order_item)，再删父表 (order)，或采用逻辑删除。
• 优点：避免了数据库外键的锁竞争和性能开销，更适合分库分表。
• 缺点：一致性保障责任从数据库转移到了应用代码，需要更严谨的代码设计和测试。

第四部分：高级主题与生产避坑指南

1. 函数索引与索引失效踩坑

函数索引（MySQL 8.0+）：允许对列使用函数后的结果创建索引，解决因函数导致索引失效的问题。

— 场景：经常按『年』查询用户注册时间
— 错误写法（索引失效）：
SELECT * FROM user WHERE YEAR(created_at) = 2023;

— 解决方案1（最优）：使用范围查询，可利用 created_at 上的普通索引
SELECT * FROM user WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

— 解决方案2（MySQL 8.0+）：创建函数索引
ALTER TABLE user ADD INDEX idx_created_at_year ((YEAR(created_at)));
— 注意：函数索引的列表达式必须用括号包裹
SELECT * FROM user WHERE YEAR(created_at) = 2023; — 此时可以使用 idx_created_at_year 索引

常见导致索引失效的函数/操作：

• WHERE DATE(create_time) = '2023-10-27' → 改为 WHERE create_time >= '2023-10-27' AND create_time < '2023-10-28'
• WHERE LEFT(name, 3) = 'ABC' → 考虑使用前缀索引 ALTER TABLE t ADD INDEX idx_name_prefix (name(3));
• WHERE amount + 100 > 500 → 改为 WHERE amount > 400
• WHERE CAST(id AS CHAR) = '100' → 确保类型匹配
• 隐式类型转换：WHERE varchar_column = 123（数字与字符串比较）也会导致索引失效。

2. 生产库表设计规范（约束部分）

3. 开发踩坑汇总

第五部分：后端高频面试题精讲

总结

MySQL 的约束与函数是后端开发者必须掌握的核心知识。约束是数据的“宪法”，从底层保障了数据的正确性；函数是高效的“工具”，能简化复杂的数据操作。

核心要点回顾：

希望这篇近万字的详解能帮助你建立起 MySQL 约束与函数的完整知识体系，并在日常开发与面试中游刃有余。建议收藏本文，随时查阅。