MySQL和MyBatis常用的动态SQL函数

MySQL与MyBatis动态SQL实战

最新推荐文章于 2025-11-23 13:27:25 发布

原创最新推荐文章于 2025-11-23 13:27:25 发布 · 786 阅读

20 ·

CC 4.0 BY-SA版权

文章标签：

#java

Java 专栏收录该内容

16 篇文章

订阅专栏

ModelEngine·创作计划征文活动 10w+人浏览 1.4k人参与

📚 一、MySQL 内置“动态”函数大全（SELECT 中灵活处理）

✅ 这些函数让你在 不写存储过程 的情况下实现“逻辑动态”。

1. 空值处理（NULL Safe）

函数	说明	示例
`IFNULL(expr1, expr2)`	若 `expr1` 为 NULL，返回 `expr2`	`SELECT IFNULL(sales, 0) FROM t;`
`COALESCE(val1, val2, ...)`	返回第一个非 NULL 值	`SELECT COALESCE(phone, email, 'N/A');`
`NULLIF(expr1, expr2)`	若相等返回 NULL，否则返回 `expr1`	`SELECT num / NULLIF(total, 0);`
`ISNULL(expr)`	判断是否为 NULL（返回 1/0）	`SELECT ISNULL(name);`

💡 防除零错误必备：
SELECT recycle / NULLIF(num, 0) AS rate FROM report;

2. 条件判断

函数	说明	示例
`IF(condition, true_val, false_val)`	简单三元判断	`SELECT IF(status=1, '启用', '禁用');`
`CASE WHEN ... THEN ... ELSE ... END`	多分支判断	见下方

🔹 `CASE WHEN` 完整示例：

SELECT 
    variety,
    sales,
    CASE 
        WHEN sales >= 1000 THEN '热销'
        WHEN sales >= 500  THEN '正常'
        WHEN sales > 0     THEN '滞销'
        WHEN sales IS NULL THEN '无数据'
        ELSE '异常'
    END AS level,
    CASE status
        WHEN 1 THEN '启用'
        WHEN 0 THEN '停用'
        ELSE '未知'
    END AS status_text
FROM product_report;

3. 字符串动态处理

函数	说明	示例
`CONCAT(str1, str2, ...)`	拼接，任一为 NULL 则结果为 NULL	`CONCAT('姓名:', name)`
`CONCAT_WS(sep, str1, str2, ...)`	用分隔符拼接，自动跳过 NULL	`CONCAT_WS(',', prov, city, dist)`
`IFNULL(CONCAT(...), '')`	安全拼接	`IFNULL(CONCAT(first, last), '')`
`REPLACE(str, old, new)`	替换	`REPLACE(phone, '-', '')`
`TRIM(str)` / `LTRIM()` / `RTRIM()`	去空格	`TRIM(username)`

✅ 地址拼接最佳实践：

SELECT CONCAT_WS(' ', province, city, district) AS full_address FROM user;
-- 结果：河北省 石家庄市 裕华区 （district 为 NULL 时自动省略）

4. 数值与聚合动态控制

场景	写法
聚合空值转 0	`COALESCE(SUM(sales), 0)`
条件求和	`SUM(IF(status=1, amount, 0))`
条件计数	`COUNT(IF(type='A', 1, NULL))` 或 `SUM(type='A')`
百分比（安全）	`ROUND( SUM(ok) / NULLIF(SUM(total), 0) * 100, 2 )`

🔹 示例：按状态统计

SELECT 
    dept_id,
    SUM(IF(status = 'SUCCESS', 1, 0)) AS success_count,
    SUM(IF(status = 'FAIL', 1, 0)) AS fail_count,
    COUNT(*) AS total,
    ROUND(
        SUM(status = 'SUCCESS') / NULLIF(COUNT(*), 0) * 100, 
        2
    ) AS success_rate
FROM task GROUP BY dept_id;

5. 日期动态处理

需求	MySQL 写法
当前月第一天	`DATE_FORMAT(NOW(), '%Y-%m-01')`
当前月最后一天	`LAST_DAY(NOW())`
本月数据	`WHERE DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')`
近7天	`WHERE create_time >= CURDATE() - INTERVAL 7 DAY`
动态季度	`QUARTER(create_time)`

🔹 示例：按月份汇总

SELECT 
    DATE_FORMAT(report_date, '%Y-%m') AS month,
    SUM(sales) AS total_sales
FROM report 
WHERE report_date >= '2025-01-01'
GROUP BY DATE_FORMAT(report_date, '%Y-%m')
ORDER BY month;

🧩 二、MyBatis 动态 SQL 完整版（XML 写法）

✅ 用于 Java 应用层动态构建 WHERE、SET、ORDER BY 等

1. 基础结构：`<where>` + `<if>`

<select id="selectReport" resultType="ReportVO">
  SELECT 
    zv.variety,
    COALESCE(SUM(zrb.recycle), 0) AS recycle,
    COALESCE(SUM(zrb.num), 0) AS num,
    COALESCE(SUM(zrb.sales), 0) AS sales
  FROM zszy_variety zv
  LEFT JOIN zszy_report_body zrb ON zv.id = zrb.variety_id
  LEFT JOIN zszy_report_head zrh ON zrb.head_id = zrh.id
  <where>
    <!-- 区域权限过滤 -->
    <if test="userId != null and userId != ''">
      AND zrh.enter_name IN (
        SELECT enter_name FROM ar_sys_user 
        WHERE user_type = 'ENTER' 
          AND region_id LIKE CONCAT(
            (SELECT region_id FROM ar_sys_user WHERE id = #{userId}), '%'
          )
      )
    </if>
    
    <!-- 时间范围 -->
    <if test="beginTime != null and beginTime != ''">
      AND zrh.report_date >= #{beginTime}
    </if>
    <if test="endTime != null and endTime != ''">
      AND zrh.report_date &lt;= #{endTime}
    </if>
    
    <!-- 排除品种 -->
    AND zv.id NOT IN (18, 20, 21)
  </where>
  GROUP BY zv.id, zv.variety
  ORDER BY zv.id
</select>

⚠️ 注意：<where> 会自动去掉首个 AND，避免语法错误。

2. 更新语句：`<set>` + `<if>`

<update id="updateUser">
  UPDATE sys_user
  <set>
    <if test="username != null and username != ''">username = #{username},</if>
    <if test="email != null and email != ''">email = #{email},</if>
    <if test="status != null">status = #{status},</if>
    update_time = NOW()
  </set>
  WHERE id = #{id}
</update>

✅ <set> 自动去掉末尾逗号。

3. 批量操作：`<foreach>`

🔸 IN 查询

<select id="selectByIds" resultType="User">
  SELECT * FROM sys_user 
  WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

🔸 批量插入

<insert id="batchInsert">
  INSERT INTO report (variety_id, sales, create_time) VALUES
  <foreach collection="list" item="item" separator=",">
    (#{item.varietyId}, #{item.sales}, NOW())
  </foreach>
</insert>

🔸 批量更新（CASE WHEN）

<update id="batchUpdateStatus">
  UPDATE sys_user 
  SET status = CASE id
    <foreach collection="userList" item="user">
      WHEN #{user.id} THEN #{user.status}
    </foreach>
  END
  WHERE id IN
  <foreach collection="userList" item="user" open="(" separator="," close=")">
    #{user.id}
  </foreach>
</update>

4. 动态排序（防注入！）

❗ 不能直接拼接用户输入的排序字段！

✅ 安全做法：白名单校验（Java 层）

public List<Report> getReport(String orderField, String orderDir) {
    // 白名单校验
    if (!Arrays.asList("variety", "sales", "num").contains(orderField)) {
        orderField = "variety";
    }
    if (!"ASC".equalsIgnoreCase(orderDir) && !"DESC".equalsIgnoreCase(orderDir)) {
        orderDir = "ASC";
    }
    return reportMapper.selectWithOrder(orderField, orderDir);
}

XML 中使用 `${}`（仅限可信字段）

<select id="selectWithOrder" resultType="Report">
  SELECT variety, sales FROM report
  ORDER BY ${orderField} ${orderDir}
</select>

⚠️ ${} 是直接拼接，必须确保参数来自白名单！

5. 复杂条件：`<choose>`, `<when>`, `<otherwise>`

<select id="searchUsers" resultType="User">
  SELECT * FROM sys_user
  <where>
    <choose>
      <when test="keyword != null and keyword != ''">
        AND (username LIKE CONCAT('%', #{keyword}, '%') 
             OR phone LIKE CONCAT('%', #{keyword}, '%'))
      </when>
      <when test="deptId != null">
        AND dept_id = #{deptId}
      </when>
      <otherwise>
        AND status = 1
      </otherwise>
    </choose>
  </where>
</select>

🔐 三、安全与性能最佳实践

问题	解决方案
SQL 注入	用 `#{}`（预编译），不用 `${}`；动态排序必须白名单校验
N+1 查询	避免在循环中查 DB；用 JOIN 或批量查询
索引失效	避免在字段上用函数：`WHERE DATE(create_time) = '2025-10-01'` → 改为范围查询
空值处理	聚合用 `COALESCE(SUM(...), 0)`，不要依赖应用层判空
大表分页	用 `WHERE id > last_id LIMIT 100` 替代 `OFFSET`

📌 四、完整示例：带权限、时间、分页的报表查询

Java Mapper 接口

List<ReportVO> selectReportPage(@Param("userId") Long userId,
                                @Param("beginTime") String beginTime,
                                @Param("endTime") String endTime,
                                @Param("pageNum") int pageNum,
                                @Param("pageSize") int pageSize);

MyBatis XML

<select id="selectReportPage" resultType="ReportVO">
  SELECT 
    zv.variety,
    COALESCE(SUM(zrb.recycle), 0) AS recycle,
    COALESCE(SUM(zrb.num), 0) AS num,
    COALESCE(SUM(zrb.sales), 0) AS sales
  FROM zszy_variety zv
  LEFT JOIN zszy_report_body zrb ON zv.id = zrb.variety_id
  LEFT JOIN zszy_report_head zrh ON zrb.head_id = zrh.id
  <where>
    <if test="userId != null">
      AND zrh.enter_name IN (
        SELECT enter_name FROM ar_sys_user 
        WHERE user_type = 'ENTER' 
          AND region_id LIKE CONCAT(
            (SELECT region_id FROM ar_sys_user WHERE id = #{userId}), '%'
          )
      )
    </if>
    <if test="beginTime != null and beginTime != ''">
      AND zrh.report_date >= #{beginTime}
    </if>
    <if test="endTime != null and endTime != ''">
      AND zrh.report_date &lt;= #{endTime}
    </if>
    AND zv.id NOT IN (18, 20, 21)
  </where>
  GROUP BY zv.id, zv.variety
  ORDER BY zv.id
  LIMIT #{pageSize} OFFSET #{pageSize} * (#{pageNum} - 1)
</select>

✅ 总结：动态 SQL 使用原则

简单逻辑 → MySQL 函数（IFNULL, CASE, COALESCE）
复杂条件 → MyBatis <if>, <choose>
批量操作 → <foreach>
动态排序 → 白名单 + ${}（慎用）
永远不用字符串拼接 SQL！
聚合结果必须 COALESCE(..., 0) 防 NULL

💡 记住：99% 的动态需求，MyBatis 的 <where> + <if> 就够了，无需存储过程！

✅ 一、空值处理函数（最常用）

1. `IFNULL(expr1, expr2)`

如果 expr1 为 NULL，返回 expr2；否则返回 expr1

SELECT IFNULL(sales, 0) AS sales FROM report;
-- 销量为空时显示 0

2. `COALESCE(value1, value2, ..., valueN)`

返回第一个 非 NULL 的值

SELECT COALESCE(phone, email, '无联系方式') AS contact FROM user;

3. `NULLIF(expr1, expr2)`

如果 expr1 = expr2，返回 NULL；否则返回 expr1

-- 避免除零错误
SELECT num / NULLIF(total, 0) AS avg_price FROM product;

✅ 二、条件判断函数

1. `IF(condition, true_value, false_value)`

SELECT 
    name,
    IF(status = 1, '启用', '禁用') AS status_text
FROM sys_user;

2. `CASE WHEN`（更强大）

SELECT 
    variety,
    CASE 
        WHEN sales > 1000 THEN '热销'
        WHEN sales > 500  THEN '一般'
        WHEN sales IS NULL THEN '无数据'
        ELSE '滞销'
    END AS level
FROM product_report;

✅ 三、字符串动态拼接

1. `CONCAT(str1, str2, ...)`

任意一个为 NULL，结果就是 NULL

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM user;

2. `CONCAT_WS(separator, str1, str2, ...)`

自动跳过 NULL，用分隔符连接

SELECT CONCAT_WS(',', province, city, district) AS address FROM user;
-- 结果：河北省,石家庄市 （即使 district 为 NULL）

✅ 四、真正的“动态 SQL”：存储过程中拼接 SQL

适用于：表名、字段名、排序字段等 无法用参数绑定 的场景。

示例：根据参数动态查询不同列

DELIMITER $$

CREATE PROCEDURE GetReport(
    IN sort_column VARCHAR(50),
    IN sort_dir VARCHAR(4) -- 'ASC' or 'DESC'
)
BEGIN
    SET @sql = CONCAT(
        'SELECT variety, sales FROM zszy_report ',
        'ORDER BY ', sort_column, ' ', sort_dir
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- 调用
CALL GetReport('sales', 'DESC');

⚠️ 注意：这种方式有 SQL 注入风险！务必对 sort_column 做白名单校验。

✅ 五、结合 MyBatis 的动态 SQL（Java 开发常用）

虽然你问的是 MySQL，但实际开发中动态 SQL 多在 ORM 层处理。例如 MyBatis：

<select id="selectReport" resultType="Report">
  SELECT 
    zv.variety,
    COALESCE(SUM(zrb.sales), 0) AS sales
  FROM zszy_variety zv
  LEFT JOIN zszy_report_body zrb ON zv.id = zrb.variety_id
  LEFT JOIN zszy_report_head zrh ON zrb.head_id = zrh.id
  <where>
    <if test="userId != null">
      AND zrh.enter_name IN (
        SELECT enter_name FROM ar_sys_user 
        WHERE user_type = 'ENTER' 
          AND region_id LIKE CONCAT(
            (SELECT region_id FROM ar_sys_user WHERE id = #{userId}), '%'
          )
      )
    </if>
    <if test="beginTime != null">
      AND zrh.report_date >= #{beginTime}
    </if>
    <if test="endTime != null">
      AND zrh.report_date <= #{endTime}
    </if>
    AND zv.id NOT IN (18, 20, 21)
  </where>
  GROUP BY zv.id, zv.variety
  ORDER BY zv.id
</select>

✅ 这才是企业级开发中“动态 SQL”的主流用法！

✅ 六、实用技巧汇总

需求	写法
空值转 0	`IFNULL(num, 0)` 或 `COALESCE(num, 0)`
多条件判断	`CASE WHEN ... END`
安全除法	`num / NULLIF(denominator, 0)`
拼接地址（跳过 NULL）	`CONCAT_WS(',', province, city, district)`
动态排序（存储过程）	`CONCAT('ORDER BY ', col)` + `PREPARE`
防止 WHERE 1=1	使用 `<where>` 标签（MyBatis）

❌ 常见误区

在 WHERE 中直接写 IFNULL(column, 0) = 0
→ 会导致索引失效！应改写为：
```
WHERE column = 0 OR column IS NULL
```
用 CONCAT 拼接条件防注入
→ 存储过程中必须校验输入，不能直接拼接用户输入！

✅ 总结

场景	推荐方案
空值处理	`IFNULL`, `COALESCE`
条件分支	`IF`, `CASE WHEN`
字符串拼接	`CONCAT_WS`（自动跳过 NULL）
真正动态（表名/字段名）	存储过程 + `PREPARE`（慎用）
应用层动态查询	MyBatis `<if>`, `<where>`（首选）