揭秘MySQL高手常用的高级SQL技巧 (一)

前言

在日常的开发工作中，我们每天都在和SELECT、INSERT、UPDATE、DELETE打交道。但你是否想过，除了这些基本操作，MySQL还隐藏着许多强大而精妙的功能？真正的MySQL高手，其价值不仅在于能写出正确的SQL，更在于能写出高效、优雅、可读性强的SQL。

本系列文章 《MySQL数据库奇技淫巧》 将为你揭示那些深藏不露的高级技巧。今天，作为系列的第一篇，我们将从5个最常用、最能立竿见影的技巧开始，助你开启MySQL的进阶之路。

技巧 1: INSERT ... ON DUPLICATE KEY UPDATE - 优雅地处理数据冲突

背景与痛点:

一个非常常见的场景：记录用户的积分。如果用户是第一次获得积分，需要INSERT一条新记录；如果用户已经有积分记录，则需要UPDATE现有记录。

很多人的第一反应是“先查后改”：

1. SELECT COUNT(*) FROM user_scores WHERE user_id = ?;
2. 如果结果为0，执行 INSERT INTO user_scores (user_id, score) VALUES (?, ?);
3. 如果结果大于0，执行 UPDATE user_scores SET score = score + ? WHERE user_id = ?;

这种模式存在两个致命问题：

• 性能低下: 需要两次数据库交互（一次SELECT，一次INSERT/UPDATE）。
• 并发问题: 在高并发环境下，两个线程可能同时执行SELECT并都发现记录不存在，然后都尝试INSERT，导致其中一个因为主键或唯一键冲突而失败。即使加了事务，也可能导致死锁。

奇技淫巧:

MySQL提供了 INSERT ... ON DUPLICATE KEY UPDATE 语句，可以原子性地解决这个问题。

原理:

这条语句在执行INSERT时，如果因为PRIMARY KEY或UNIQUE索引冲突而导致插入失败，它不会报错，而是会转而执行UPDATE子句中的逻辑。

举例:

假设我们有一个用户积分表 user_scores：

CREATE TABLE user_scores (
    user_id INT PRIMARY KEY,
    score INT NOT NULL,
    last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

现在，我们要为 user_id = 101 的用户增加 50 分。

INSERT INTO user_scores (user_id, score)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE score = score + 50;

• 如果user_id = 101的记录不存在: MySQL会执行 INSERT INTO user_scores (user_id, score) VALUES (101, 50);
• 如果user_id = 101的记录已存在: MySQL会执行 UPDATE user_scores SET score = score + 50 WHERE user_id = 101;

进阶细节:

在UPDATE子句中，你可以使用 VALUES(column_name) 来引用你试图插入的值。

-- 假设我们要将分数更新为插入的值，而不是累加
INSERT INTO user_scores (user_id, score)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE score = VALUES(score); 
-- VALUES(score) 在这里就是 50

这个技巧将两次数据库操作合并为一次，不仅代码更简洁，而且是原子操作，完美解决了并发问题，是MySQL高手处理此类场景的首选方案。

技巧 2: GROUP_CONCAT - 行转列的字符串聚合神器

背景与痛点:

假设你有两张表：文章表(articles)和标签表(tags)，一个文章可以有多个标签。现在，你需要查询每篇文章及其对应的所有标签，并将标签以逗号分隔的字符串形式展示。

常规的JOIN查询会得到这样的结果：

article_id | title      | tag_name
------------------------------------
1          | MySQL入门  | 数据库
1          | MySQL入门  | SQL
2          | Java教程   | 编程

你需要的是这样的结果：

article_id | title      | tags
-----------------------------------------
1          | MySQL入门  | 数据库,SQL
2          | Java教程   | 编程

在应用程序层面处理这个转换当然可以，但这意味着你需要传输冗余的数据（重复的文章标题），并在代码中编写循环逻辑来拼接字符串，这不够优雅，也增加了网络开销。

奇技淫巧:

使用聚合函数 GROUP_CONCAT()。

原理:

GROUP_CONCAT() 是一个聚合函数，它会将分组（GROUP BY）内指定列的非NULL值连接成一个单一的字符串。

举例:

SELECT
    a.id AS article_id,
    a.title,
    GROUP_CONCAT(t.tag_name) AS tags
FROM articles a
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
GROUP BY a.id, a.title;

进阶细节:

GROUP_CONCAT 功能非常强大，它还有几个可选参数：

• DISTINCT: 去除重复的标签名。
• ORDER BY: 在拼接前对标签名进行排序。
• SEPARATOR: 指定自定义的分隔符，默认为逗号,。

-- 去重、按字母排序、并用'|'分隔
GROUP_CONCAT(DISTINCT t.tag_name ORDER BY t.tag_name SEPARATOR ' | ')

重要提醒: GROUP_CONCAT 有一个最大长度限制，由系统变量 group_concat_max_len 控制（默认为1024字节）。如果拼接后的字符串超过这个长度，结果会被截断。你可以通过SET命令调整它的大小：SET SESSION group_concat_max_len = 102400;

技巧 3: EXPLAIN - SQL性能优化的第一道门

背景与痛点:

"我的SQL为什么这么慢？" 这是每个开发者都会遇到的问题。在优化一个慢查询时，最忌讳的就是凭感觉猜测。你需要一个科学的工具来告诉你，MySQL到底是如何执行你的SQL语句的。

奇技淫巧:

EXPLAIN 就是这个工具。它不会真正执行你的SQL，而是分析并返回MySQL优化器为这条SQL选择的执行计划。

原理:

执行计划（Execution Plan）是数据库内部执行查询的路径图。通过它，你可以知道：

• 查询涉及了哪些表。
• 表的连接顺序是怎样的。
• 是否使用了索引，以及具体使用了哪个索引。
• 预估需要扫描多少行数据。
• 是否存在文件排序（filesort）等耗性能的操作。

举例:

在你写的任何SELECT语句前加上EXPLAIN关键字：

EXPLAIN SELECT * FROM users WHERE age > 30;

你会得到一个表格形式的输出，其中有几个列至关重要：

• type: 这是最重要的列，表示连接类型，直接反映了查询的效率。性能从高到低依次是：
  system > const > eq_ref > ref > range > index > ALL
  目标: 至少要达到range级别，最好是ref或eq_ref。ALL代表全表扫描，是性能杀手，必须优化。

graph TD
subgraph 高性能
A(system) --> B(const) --> C(eq_ref) --> D(ref)
end
subgraph 中等性能
E(range) --> F(index)
end
subgraph 低性能
G(ALL)
end
D --> E
F --> G

style A fill:#c8e6c9,stroke:#5a925e,color:#000
style B fill:#c8e6c9,stroke:#5a925e,color:#000
style C fill:#c8e6c9,stroke:#5a925e,color:#000
style D fill:#c8e6c9,stroke:#5a925e,color:#000
style E fill:#fff9c4,stroke:#a38f00,color:#000
style F fill:#fff9c4,stroke:#a38f00,color:#000
style G fill:#ffcdd2,stroke:#b71c1c,color:#000

• key: 实际使用的索引。如果为NULL，则表示没有使用索引。
• rows: 预估需要读取的行数。这个值越小越好。
• Extra: 额外信息，包含很多重要线索。
  • Using index: 覆盖索引，性能极好。
  • Using where: 表明在存储引擎层过滤后，服务层还需要再次进行WHERE条件过滤。
  • Using temporary: 使用了临时表，通常意味着性能不佳。
  • Using filesort: 在内存或磁盘上进行了排序，是常见的性能瓶颈。

掌握EXPLAIN是从SQL新手到高手的必经之路。在写完任何一个复杂查询后，随手EXPLAIN一下，是专业DBA和高级开发者的肌肉记忆。

技巧 4: CASE 表达式与聚合函数联用 - 在SQL中实现逻辑判断

背景与痛点:

需要在一个查询中完成多种条件的统计。例如，统计一个订单表中“已付款”、“待发货”、“已完成”状态的订单数量。

常规做法是执行三次查询：

SELECT COUNT(*) FROM orders WHERE status = 'paid';
SELECT COUNT(*) FROM orders WHERE status = 'shipping';
SELECT COUNT(*) FROM orders WHERE status = 'completed';

这同样是多次数据库交互，效率低下。

奇技淫巧:

将 CASE 表达式和聚合函数（如 COUNT, SUM）结合起来，实现条件聚合。

原理:

CASE 表达式允许你在SQL中嵌入IF-THEN-ELSE逻辑。当它与聚合函数结合时，可以对每一行数据进行判断，然后只对满足条件的行进行聚合计算。

举例:

一次性统计所有状态的订单数：

SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 ELSE NULL END) AS paid_count,
    COUNT(CASE WHEN status = 'shipping' THEN 1 END) AS shipping_count, -- ELSE NULL 可以省略
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS completed_amount_sum
FROM orders;

解析:

• COUNT(column) 只计算column不为NULL的行。所以 CASE WHEN status = 'paid' THEN 1 ELSE NULL END 的写法，只有当 status 为 'paid' 时才计数。
• SUM() 会累加表达式的值。CASE WHEN status = 'completed' THEN 1 ELSE 0 END 当 status 为 'completed' 时累加1，否则累加0，从而实现了计数。
• 这个技巧还可以用于条件求和，如计算已完成订单的总金额。

这种“行转列”的统计方式，将多次查询压缩为一次，极大提升了报表和统计类查询的性能。

技巧 5: <=> (NULL-safe equal) - 最安全的等值比较

背景与痛点:

在SQL中，NULL 是一个特殊的存在，它代表“未知”。任何值与NULL进行常规比较（=, !=, <>)，结果都是NULL，而不是TRUE或FALSE。

SELECT NULL = NULL; 的结果是 NULL。

这导致在WHERE子句中，WHERE column = NULL 永远不会返回任何行。我们必须使用WHERE column IS NULL。当比较两个可能为NULL的列时，情况变得更复杂：
WHERE col_a = col_b 无法正确处理 col_a 和 col_b 都为 NULL 的情况。你需要写成冗长的：
WHERE (col_a = col_b) OR (col_a IS NULL AND col_b IS NULL)

奇技淫巧:

使用MySQL提供的NULL安全等于运算符 <=>。

原理:

<=> 的行为与=基本相同，但它对NULL进行了特殊处理：

• 如果两个操作数都是NULL，它返回 1 (TRUE)。
• 如果只有一个操作数是NULL，它返回 0 (FALSE)。

举例:

SELECT NULL <=> NULL;       -- 结果: 1 (TRUE)
SELECT 1 <=> NULL;        -- 结果: 0 (FALSE)
SELECT NULL <=> 1;        -- 结果: 0 (FALSE)

现在，之前那个复杂的WHERE子句可以被极大地简化：

-- 冗长的旧写法
-- WHERE (col_a = col_b) OR (col_a IS NULL AND col_b IS NULL)

-- 优雅、安全的新写法
WHERE col_a <=> col_b;

这个操作符在进行数据比对、查找变更数据（尤其是当旧值或新值可能为NULL时）等场景下非常有用，能让你的SQL逻辑更清晰、更健壮。