别只会写 SELECT，这10个 MySQL 技巧太香了

非鱼牛马社
2,129 阅读9分钟

大家好，欢迎来到 非鱼牛马社，一个热爱技术的全栈开发者。最近在做一个新项目，发现很多同事还在用着最基础的 SQL 语句，写出来的代码又长又难维护。作为一个踩过无数坑的老司机，我觉得是时候分享一些实用的 MySQL 技巧了。这些技巧都是我日常工作中经常用到的，绝对能帮你提高开发效率！

写在前面

作为一名经常和 MySQL 打交道的开发者，我发现很多小伙伴还在用着最基础的 CRUD 操作。今天，我要分享一些我日常工作中经常用到的 MySQL 小技巧，这些技巧真的帮我省了不少时间，相信对你也会很有帮助！

目录

  1. 使用 JSON 类型存储灵活数据
  2. 使用 WITH ROLLUP 进行分组统计
  3. 使用 CASE WHEN 进行条件统计
  4. 使用 INSERT IGNORE 避免重复插入
  5. 使用 ON DUPLICATE KEY UPDATE 实现插入或更新
  6. 使用 FIND_IN_SET 进行集合查询
  7. 使用 GROUP_CONCAT 合并多行数据
  8. 使用 EXISTS 优化子查询
  9. 使用 ROW_NUMBER() 实现分页
  10. 使用 WITH 子句优化复杂查询

1. 使用 JSON 类型存储灵活数据

还记得上次做用户设置功能的时候，产品经理说："这个功能要支持用户自定义主题、通知设置、字体大小..." 我当时就想，如果每个设置都建一个字段，那表结构得多臃肿啊！后来发现 MySQL 的 JSON 类型简直就是救星！

-- 创建用户偏好表，使用 JSON 类型存储灵活配置
CREATE TABLE user_preferences (
    id INT PRIMARY KEY COMMENT '主键ID',
    user_id INT COMMENT '用户ID',
    preferences JSON COMMENT '用户偏好设置（JSON格式）'
) COMMENT='用户偏好表';

-- 插入测试数据
INSERT INTO user_preferences VALUES 
(1, 1, '{"theme": "dark", "notifications": true, "fontSize": 14}'),
(2, 2, '{"theme": "light", "notifications": false, "fontSize": 16}'),
(3, 3, '{"theme": "dark", "notifications": true, "fontSize": 12}');

-- 查询 JSON 数据
SELECT preferences->'$.theme' FROM user_preferences WHERE user_id = 1;

查询结果：

2. 使用 WITH ROLLUP 进行分组统计

这个技巧是我在做部门薪资报表时发现的。老板不仅要看每个部门的薪资情况，还要看总计。以前我都是写两个查询，然后用代码合并，现在用 WITH ROLLUP 一行代码就搞定了，简直不要太爽！

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY COMMENT '员工ID',
    name VARCHAR(50) COMMENT '员工姓名',
    department VARCHAR(50) COMMENT '所属部门',
    salary DECIMAL(10,2) COMMENT '薪资'
) COMMENT='员工信息表';

-- 插入测试数据
INSERT INTO employees VALUES 
(1, '张三', '技术部', 15000.00),
(2, '李四', '技术部', 18000.00),
(3, '王五', '技术部', 17000.00),
(4, '赵六', '市场部', 12000.00),
(5, '钱七', '市场部', 13000.00),
(6, '孙八', '人事部', 10000.00),
(7, '周九', '人事部', 11000.00);

-- 统计各部门员工数量和薪资总和，并计算总计
SELECT 
    department,
    COUNT(*) as employee_count,
    SUM(salary) as total_salary
FROM employees
GROUP BY department WITH ROLLUP;

查询结果：

3. 使用 CASE WHEN 进行条件统计

这个技巧在处理状态统计时特别有用。比如要统计用户活跃度，不用写多个查询，一个 CASE WHEN 就搞定！我经常用它来做数据统计，代码简洁又高效。

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT '用户ID',
    name VARCHAR(50) COMMENT '用户名',
    status VARCHAR(20) COMMENT '用户状态'
) COMMENT='用户信息表';

-- 插入测试数据
INSERT INTO users VALUES 
(1, '张三', 'active'),
(2, '李四', 'active'),
(3, '王五', 'inactive'),
(4, '赵六', 'active'),
(5, '钱七', 'inactive');

-- 统计活跃和非活跃用户数量
SELECT 
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive_users
FROM users;

查询结果：

4. 使用 INSERT IGNORE 避免重复插入

这个是我最常用的技巧之一！特别是在处理批量导入数据时，遇到重复数据直接跳过，不会报错，让程序继续运行。以前我都是先查询再插入，现在直接 INSERT IGNORE 就完事了，省心！

-- 创建用户表（如果之前没创建）
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT '用户ID',
    name VARCHAR(50) COMMENT '用户名',
    email VARCHAR(100) COMMENT '邮箱'
) COMMENT='用户信息表';

-- 插入测试数据
INSERT INTO users VALUES 
(1, '张三', 'zhangsan@example.com'),
(2, '李四', 'lisi@example.com');

-- 使用 INSERT IGNORE 插入数据，遇到重复则跳过
INSERT IGNORE INTO users (id, name, email) 
VALUES 
(1, '张三', 'zhangsan@example.com'),
(3, '王五', 'wangwu@example.com');

执行结果：

  • 如果 id=1 的记录不存在：插入成功
  • 如果 id=1 的记录已存在：跳过插入，不报错
  • id=3 的新记录会正常插入

5. 使用 ON DUPLICATE KEY UPDATE 实现插入或更新

这个功能太实用了！比如用户更新个人信息，如果用户已存在就更新，不存在就插入，一行代码搞定！以前我都是写两个 SQL，现在一个就搞定了，代码量直接减半！

-- 使用 ON DUPLICATE KEY UPDATE 实现插入或更新
INSERT INTO users (id, name, email) 
VALUES 
(1, '张三', 'zhangsan_new@example.com'),
(4, '赵六', 'zhaoliu@example.com')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email);

执行结果：

  • 如果 id=1 的记录不存在：插入新记录
  • 如果 id=1 的记录已存在：更新 name 和 email 字段
  • id=4 的新记录会正常插入

6. 使用 FIND_IN_SET 进行集合查询

有时候我们需要查询某个分类下的所有商品，如果分类 ID 是以逗号分隔的字符串，用 FIND_IN_SET 就能轻松搞定！这个技巧在处理多对多关系时特别有用。

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY COMMENT '商品ID',
    name VARCHAR(100) COMMENT '商品名称',
    category_ids VARCHAR(100) COMMENT '分类ID列表（逗号分隔）'
) COMMENT='商品信息表';

-- 插入测试数据
INSERT INTO products VALUES 
(1, '商品A', '1,2,3'),
(2, '商品B', '2,3,4'),
(3, '商品C', '1,4'),
(4, '商品D', '3,4,5'),
(5, '商品E', '1,5,6');

-- 查询指定分类下的所有商品
SELECT * FROM products 
WHERE FIND_IN_SET('1', category_ids);

查询结果：

7. 使用 GROUP_CONCAT 合并多行数据

这个功能在做报表时特别有用。比如要显示每个部门的所有员工名单，不用在代码里拼接，直接让数据库帮你搞定！我经常用它来做数据导出，效率提升了不少。

-- 使用 GROUP_CONCAT 合并部门员工名单
SELECT 
    department,
    GROUP_CONCAT(name SEPARATOR ', ') as employees
FROM employees
GROUP BY department;

查询结果：

8. 使用 EXISTS 优化子查询

这个技巧可以帮你优化查询性能。比如要找出所有包含高价商品的订单，用 EXISTS 比用 IN 子查询要快得多！我做过测试，数据量大的时候性能提升特别明显。

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY COMMENT '订单ID',
    user_id INT COMMENT '用户ID',
    created_at DATETIME COMMENT '创建时间'
) COMMENT='订单表';

-- 创建订单项表
CREATE TABLE order_items (
    id INT PRIMARY KEY COMMENT '订单项ID',
    order_id INT COMMENT '订单ID',
    price DECIMAL(10,2) COMMENT '商品价格'
) COMMENT='订单项表';

-- 插入测试数据
INSERT INTO orders VALUES 
(1, 1001, '2024-03-20 10:00:00'),
(2, 1002, '2024-03-20 11:00:00'),
(3, 1002, '2024-03-20 11:30:00'),
(4, 1003, '2024-03-20 12:00:00'),
(5, 1003, '2024-03-20 14:15:00');

INSERT INTO order_items VALUES 
(1, 1, 150.00),
(2, 1, 80.00),
(3, 2, 90.00),
(4, 3, 200.00),
(5, 4, 70.00),
(6, 5, 180.00);

-- 使用 EXISTS 查询包含高价商品的订单
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.order_id = o.id 
    AND oi.price > 100
);

查询结果：

9. 使用 ROW_NUMBER() 实现分页

做分页查询时，这个技巧特别有用。不用写复杂的子查询，直接给每行数据编号，然后按编号筛选，简单又高效！我经常用它来做分页，代码简洁多了。

-- 创建文章表
CREATE TABLE articles (
    id INT PRIMARY KEY COMMENT '文章ID',
    title VARCHAR(200) COMMENT '文章标题',
    content TEXT COMMENT '文章内容',
    created_at DATETIME COMMENT '创建时间'
) COMMENT='文章表';

-- 插入测试数据
INSERT INTO articles VALUES 
(1, '文章1', '内容1', '2024-03-20 09:00:00'),
(2, '文章2', '内容2', '2024-03-20 10:00:00'),
(3, '文章3', '内容3', '2024-03-20 11:00:00'),
(4, '文章4', '内容4', '2024-03-20 12:00:00'),
(5, '文章5', '内容5', '2024-03-20 13:00:00'),
(6, '文章6', '内容6', '2024-03-20 14:00:00'),
(7, '文章7', '内容7', '2024-03-20 15:00:00'),
(8, '文章8', '内容8', '2024-03-20 16:00:00'),
(9, '文章9', '内容9', '2024-03-20 17:00:00'),
(10, '文章10', '内容10', '2024-03-20 18:00:00');

-- 使用 ROW_NUMBER() 实现分页查询
SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) as row_num
    FROM articles
) t
WHERE row_num BETWEEN 1 AND 10;

查询结果：

10. 使用 WITH 子句优化复杂查询

这个功能让复杂的查询变得清晰易读。比如要统计用户订单情况，可以先把统计数据算出来，然后再和其他表关联，代码结构清晰多了！我经常用它来优化复杂的统计查询。

-- 使用 WITH 子句优化复杂查询
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(amount) as total_amount
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    us.order_count,
    us.total_amount
FROM users u
JOIN user_stats us ON u.id = us.user_id;

查询结果：

写在最后

这些 MySQL 技巧都是我在实际工作中经常用到的，它们真的帮我解决了很多问题。记住，好的数据库操作不仅要考虑功能实现，还要注意性能和可维护性。

如果你觉得这篇文章对你有帮助，别忘了点赞收藏！如果你也有什么好用的 MySQL 技巧，欢迎在评论区分享，让我们一起进步！

最后说一句：写代码就像写文章，要讲究技巧，但更要注重实用。希望这些技巧能帮到你！😊

avatar
文章
阅读
粉丝