前言
做后端开发，大家日常使用最多的就是 SELECT、INSERT、UPDATE、DELETE 基础语法，以及 CTE、窗口函数这类热门特性。但 MySQL 内置了大量冷门但实用性拉满的语法，合理使用可以大幅简化业务代码、减少 Java 层逻辑、提升 SQL 简洁度。
本篇聚焦数据写入、表操作、分组统计、内置函数四大方向，分享 10 个生产环境高频可用的冷门语法。
一、数据写入相关语法
1. INSERT ... SET 写法
常规 INSERT 需要严格匹配字段顺序，字段较多时极易出错。INSERT ... SET 采用键值对形式，语义和 UPDATE 一致，可读性更强。
示例
sql
-- 传统写法（字段多易错位）
INSERT INTO `sys_user` (id, name, age, phone)
VALUES (1, '张三', 28, '13800138000');
-- SET 写法（推荐，字段一一对应）
INSERT INTO `sys_user`
SET id = 1, name = '张三', age = 28, phone = '13800138000';
适用场景
字段数量多的表插入、配置表数据录入、临时数据写入。
2. INSERT IGNORE 冲突忽略插入
当主键 / 唯一索引发生冲突时，普通 INSERT 会直接报错。INSERT IGNORE 会自动跳过冲突数据，不抛出异常。
示例
sql
-- id 为主键，重复数据直接跳过
INSERT IGNORE INTO `sys_user` (id, name)
VALUES (1, '张三'), (2, '李四'), (1, '张三');
注意事项
仅针对主键、唯一索引冲突生效；慎用在核心业务表，避免数据无声丢失。
适用场景
批量数据导入、日志表写入、幂等性批量插入。
3. INSERT ... ON DUPLICATE KEY UPDATE 存在则更新
经典的不存在则插入，存在则更新语法，一条 SQL 实现「查 + 插 + 改」，无需 Java 层判断数据是否存在，是幂等写入神器。
示例
sql
-- id 为唯一键，冲突时更新姓名和年龄
INSERT INTO `sys_user` (id, name, age)
VALUES (1, '张三', 28)
ON DUPLICATE KEY UPDATE name = '张三', age = 29;
适用场景
用户信息更新、配置项维护、定时同步数据、接口幂等处理。
二、表结构快捷操作
4. CREATE TABLE ... LIKE 复制表结构
快速复刻一张表的完整结构、索引、约束，不会同步表数据。区别于 CREATE TABLE ... AS SELECT，该语法保留主键、索引、字段注释，是复制表的最优方案。
示例
sql
-- 仅复制表结构、索引、约束，无数据
CREATE TABLE `sys_user_bak` LIKE `sys_user`;
适用场景
数据备份、分表创建、临时测试表快速搭建。
三、分组与统计增强语法
5. GROUP_CONCAT 行转字符串
分组场景下，将一组内的多条数据拼接为单个字符串，替代 Java 循环拼接，数据库层一步完成行转列。
示例
sql
-- 按部门分组，拼接部门下所有用户名
SELECT
dept_id,
GROUP_CONCAT(name SEPARATOR '、') AS user_names
FROM `sys_user`
GROUP BY dept_id;
SEPARATOR：指定拼接分隔符，默认逗号,
注意事项
默认有长度限制，可通过 group_concat_max_len 调整。
适用场景
一对多数据查询、分类标签拼接、报表数据展示。
6. GROUP BY ... WITH ROLLUP 自动合计行
在分组结果末尾自动生成汇总行，无需额外写聚合 SQL，报表统计利器。
示例
sql
-- 按部门统计人数，最后追加全表总人数
SELECT dept_id, COUNT(*) AS user_count
FROM `sys_user`
GROUP BY dept_id WITH ROLLUP;
效果说明
汇总行的分组字段会显示为 NULL，聚合值为整体合计。
适用场景：各类统计报表、台账汇总、数据看板。
四、实用内置函数
7. FIND_IN_SET 集合内检索
专门用于查询逗号分隔字符串集合中的元素，适配老项目中用单个字段存储多 ID / 多标签的场景，替代 LIKE 模糊查询，效率更高。
示例
sql
-- 判断 1 是否在 '1,2,3' 集合中，返回下标（匹配到返回>0，未匹配返回0）
SELECT FIND_IN_SET('1', '1,2,3,4');
-- 业务查询示例
SELECT * FROM `article` WHERE FIND_IN_SET('技术', tag_list);
适用场景
老系统标签查询、多 ID 筛选、逗号分隔字段检索。
8. PERIOD_DIFF 快速计算月份差
专门计算年月格式 (YYYYMM) 的差值，相比 TIMESTAMPDIFF 写法更简洁，无需处理完整日期。
示例
sql
-- 计算 2026年05月 与 2025年01月 的月份差
SELECT PERIOD_DIFF(202605, 202501); -- 结果：16
适用场景
账期计算、会员时长统计、月度周期核算。
9. BIT_COUNT 统计二进制位数
统计数字二进制中 1 的个数，常用于位运算权限系统、状态位组合统计。
示例
sql
-- 二进制 1011，包含3个1，结果返回3
SELECT BIT_COUNT(0b1011);
适用场景
权限位判断、多状态组合统计、开关状态计算。
五、全文检索
10. MATCH ... AGAINST 全文索引查询
传统 LIKE %关键词% 无法走索引，大数据量下极慢。MySQL 全文索引搭配 MATCH AGAINST，实现高效文本检索。
使用步骤
- 为字段创建全文索引
sql
ALTER TABLE `article` ADD FULLTEXT INDEX idx_article_title (title);
- 全文检索查询
sql
SELECT * FROM `article`
WHERE MATCH(title) AGAINST ('MySQL 语法');
适用场景
短文本搜索、文章标题检索、商品名称检索。
总结
本篇整理的语法主要围绕数据写入、表操作、分组统计、内置函数，核心价值是：
- 简化 Java 代码逻辑，把部分业务逻辑下沉到 SQL；
- 规避传统写法的漏洞（如字段错位、索引失效）；
- 适配报表、批量导入、老系统兼容等特殊场景。
合理运用这些语法，能让你的 SQL 更简洁、执行效率更高。
