目录

1.为什么先读懂关系

2. 关联关系：从 ER 图到 DDL

3. 关联查询：JOIN 全景

4. 子查询与派生表

5. 视图：虚拟表的艺术

6. 内置函数：字符串、日期、数值、JSON

7. 自定义函数：CREATE FUNCTION

8. 性能与最佳实践

9. 常见错误与调试

结语

---

1.为什么先读懂关系

MySQL 作为关系型数据库，其灵魂是“关系”二字。表与表之间的逻辑依赖决定了如何建索引、如何写 JOIN、如何拆表，也决定了视图与函数的设计。理解关系，才能写出既正确又高效的 SQL。

2. 关联关系：从 ER 图到 DDL

2.1 一对一（1:1）
场景：users 与 user_profiles，主键共享。
DDL：
ALTER TABLE user_profiles
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE ON UPDATE CASCADE;
特点：外键也是主键或唯一键；查询时可直接 JOIN 也可合并为一张宽表。

2.2 一对多（1:N）
场景：departments 与 employees。
employees 表含 department_id 外键。
范式：第二范式要求非主属性完全依赖主键，避免把部门名称冗余到 employees。

2.3 多对多（M:N）
场景：students 与 courses。
需要中间表 enrollments(student_id, course_id, score, PRIMARY KEY(student_id, course_id))。
外键双方均指向主表；级联删除需谨慎，通常保留历史成绩。

2.4 自关联
场景：员工的上级也是员工。
employees(id, manager_id, …)
查询下属：
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

3. 关联查询：JOIN 全景

3.1 七种 JOIN 速查表
A) INNER JOIN：返回交集。
B) LEFT [OUTER] JOIN：以左表为主，右表缺值补 NULL。
C) RIGHT JOIN：反之。
D) FULL JOIN：MySQL 不原生支持，可用 UNION 模拟。
E) CROSS JOIN：笛卡尔积，常见于生成日期维度。
F) SELF JOIN：同表别名自连。
G) NATURAL JOIN：按同名同类型列等值连接，容易踩坑，不推荐。

3.2 经典三表联查
示例：查“购买了商品且订单金额 > 100 的用户及其最近一次登录时间”。

SELECT u.id, u.username, MAX(l.login_time) AS last_login
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items i ON o.id = i.order_id
LEFT JOIN user_logins l ON u.id = l.user_id
WHERE o.total_amount > 100
GROUP BY u.id, u.username;

3.3 USING 与 ON 的区别
• ON 通用，可写复杂条件。
• USING(col) 简写，当两表列名完全一致；SELECT 中可直接用 USING 列名而无需表前缀。
陷阱：USING 会合并输出列，可能导致与 ORM 映射冲突。

3.4 外连接过滤条件放 WHERE 与 ON 差异
LEFT JOIN products p ON …
WHERE p.status = 'active'
会把未匹配到的 products 行过滤掉，结果退化为 INNER JOIN。
正确做法是把过滤条件放到 ON 子句：
LEFT JOIN products p ON p.id = oi.product_id AND p.status = 'active';

3.5 JOIN 算法
• Nested Loop：小表驱动大表，可用索引减少扫描行。
• Block Nested Loop：利用 join buffer。查看 EXPLAIN 的 Extra：Using join buffer。
• Hash Join（MySQL 8.0.18+）：等值连接大表性能提升，需 optimizer hint 或 optimizer_switch。
EXPLAIN FORMAT=JSON 可查看 join_execution 选择。

4. 子查询与派生表

4.1 标量子查询

SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS total_orders
FROM users u;

4.2 IN / EXISTS / ANY
• IN：适合子查询结果集小。
• EXISTS：相关子查询，通常用半连接优化。
• = ANY (…) 等价于 IN，写法更灵活，可用 > ANY(SELECT …)。

4.3 派生表（Derived Table）

SELECT dept_name, avg_sal
FROM (SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id) AS t
JOIN departments d ON t.department_id = d.id;

4.4 公共表表达式 CTE（8.0+）
WITH cte AS (…) SELECT … FROM cte …
支持递归：查层级菜单、树状结构。

5. 视图：虚拟表的艺术

5.1 创建

CREATE OR REPLACE VIEW v_user_order_stats AS
SELECT u.id, u.username, COUNT(o.id) AS order_cnt, SUM(o.total_amount) AS total_amt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

5.2 更新限制
• 单表视图可 UPDATE/DELETE/INSERT，但需满足：无 GROUP BY、DISTINCT、JOIN、子查询。
• 复杂视图可通过 INSTEAD OF 触发器（MySQL 暂不支持）或拆分为存储过程实现写操作。

5.3 物化视图（MySQL 无原生）
可用触发器 + 日志表、或 MySQL Event Scheduler + INSERT INTO SELECT 实现定期刷新。

5.4 安全与权限
GRANT SELECT ON mydb.v_user_order_stats TO 'report_user'@'%';
隐藏基表敏感列，实现列级安全。

5.5 WITH CHECK OPTION

CREATE VIEW v_active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

当通过视图插入 status='inactive' 会被拒绝。

6. 内置函数：字符串、日期、数值、JSON

6.1 字符串
CONCAT_WS(sep, str1, str2)
SUBSTRING_INDEX(str, delim, count) 取邮箱域名：SUBSTRING_INDEX(email, '@', -1)
REGEXP_REPLACE(col, pattern, repl) 8.0+。

6.2 日期
NOW(), CURRENT_TIMESTAMP(6) 支持微秒。
DATE_FORMAT(order_time, '%Y-%m') 分组月报。
TIMESTAMPDIFF(SECOND, start, end) 计算耗时。
生成连续日期序列：
WITH RECURSIVE t AS (SELECT '2025-01-01' AS dt
UNION ALL
SELECT dt + INTERVAL 1 DAY FROM t WHERE dt < '2025-01-31')
SELECT dt FROM t;

6.3 数值
ROUND(val, 2) 四舍五入。
FORMAT(val, 2) 千位分隔。
RAND() 随机抽样。

6.4 JSON（5.7+ 原生、8.0 增强）
存储：JSON 类型。
查询：
SELECT JSON_EXTRACT(data, '.user.name′)ASusern​ameFROMlogsWHEREJSONC​ONTAINS(data,′1′,′.flags');

更新：
UPDATE users
SET profile = JSON_SET(profile, '$.age', 30)
WHERE id = 1;

虚拟列索引：
ALTER TABLE users
ADD COLUMN age INT AS (JSON_EXTRACT(profile, '$.age')) STORED,
ADD INDEX idx_age(age);

7. 自定义函数：CREATE FUNCTION

7.1 标量函数

DELIMITER //
CREATE FUNCTION fn_discount(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN price * (1 - rate/100);
END //
DELIMITER ;
SELECT fn_discount(199.00, 10); -- 179.10

7.2 表值函数（MySQL 无官方实现，可用存储过程 + 临时表模拟）。
7.3 安全性
log_bin_trust_function_creators = 1 允许无 SUPER 创建。
函数内禁止访问表可能导致复制不一致，需声明 READS SQL DATA / MODIFIES SQL DATA。

8. 性能与最佳实践

8.1 索引策略
• 关联列必须有索引：外键、JOIN 条件。
• 覆盖索引：Extra = Using index。
• 复合索引左前缀：INDEX(a,b,c) 可服务 (a), (a,b), (a,b,c)。
• ORDER BY + LIMIT 走索引避免 filesort。

8.2 JOIN 优化
• 小表驱动大表，STRAIGHT_JOIN 强制顺序。
• 避免 SELECT *，减少回表。
• 大表分页：先通过覆盖索引子查询定位 id 范围，再 JOIN 回原表。

8.3 EXPLAIN 关键字段
select_type、type（ALL/index/range/ref/eq_ref/const）、possible_keys、key_len、rows、filtered、Extra。

8.4 函数对索引的影响
WHERE DATE(create_time) = '2025-08-18' 无法使用索引；应改为范围：
WHERE create_time >= '2025-08-18 00:00:00' AND create_time < '2025-08-19 00:00:00'。

9. 常见错误与调试

• 1052 Column ‘id’ in field list is ambiguous：使用表别名限定。
• 1064 You have an error in your SQL syntax：检查关键字拼写、版本差异。
• 1449 The user specified as a definer does not exist：视图或存储过程的 DEFINER 失效，需重建或赋权。
• 删除有外键的行报错 1451：级联策略未设置或 NO ACTION。
• 子查询返回多行：标量子查询应保证单行单列。
开启 general_log 或慢查询日志，结合 pt-query-digest 分析。

结语

关联查询、关联关系、视图与函数是 MySQL 日常开发的四大基石。理解关系模型，写出优雅的 JOIN；善用视图封装复杂逻辑、隔离权限；掌握内置函数与自定义函数，提升开发效率；再配合索引与优化技巧，才能真正让 MySQL 在千万级乃至亿级数据场景下游刃有余。愿本文成为你进阶路上的路标，祝编码愉快！