MySQL WHERE 条件查询：3种 NULL 值处理方案与 MyBatis 参数安全实践

在数据库查询中，WHERE 子句是最常用的过滤条件之一。然而，当涉及到 NULL 值的处理时，许多开发者常常会遇到各种问题。本文将深入探讨 MySQL 中 NULL 值的特殊处理方式，并分享在 MyBatis 框架中如何安全地处理参数，避免常见的陷阱。

1. NULL 值的特殊性及其查询处理

NULL 在数据库中表示"未知"或"不存在"的值，它与空字符串、0 或其他任何值都不同。这种特殊性使得 NULL 值的处理需要特别注意。

1.1 为什么不能用 = NULL？

在 SQL 中，NULL 与任何值的比较（包括 NULL 本身）都会返回 UNKNOWN，而不是 TRUE 或 FALSE。这是因为 NULL 代表未知，无法确定它是否等于其他值。

-- 错误的 NULL 值查询方式
SELECT * FROM users WHERE name = NULL;  -- 不会返回任何结果

1.2 正确的 NULL 值查询方式

MySQL 提供了专门的运算符来处理 NULL 值：

-- 查询为 NULL 的记录
SELECT * FROM users WHERE name IS NULL;

-- 查询不为 NULL 的记录
SELECT * FROM users WHERE name IS NOT NULL;

1.3 NULL 值处理的三种实用方案

方案一：使用 IFNULL 函数转换

-- 将 NULL 转换为默认值后再比较
SELECT * FROM users WHERE IFNULL(name, '') = '';

方案二：使用 COALESCE 函数

-- 返回参数列表中第一个非 NULL 的值
SELECT * FROM users WHERE COALESCE(name, email, '') LIKE '%search%';

方案三：使用 <=> 安全等于运算符

-- 可以正确处理 NULL 值的等于比较
SELECT * FROM users WHERE name <=> NULL;

注意：<=> 运算符在 MySQL 中可用，但不是标准 SQL 的一部分，可能在其他数据库中不支持。

2. MyBatis 中的参数安全处理

MyBatis 作为流行的 ORM 框架，提供了两种参数占位符方式：#{} 和 ${}，它们在处理 NULL 值和防止 SQL 注入方面有显著差异。

2.1 #{} 与 ${} 的本质区别

特性	#{}	${}
处理方式	预编译，参数化查询	字符串替换
SQL 注入风险	安全	存在风险
NULL 值处理	自动转换为 JDBC 的 NULL	直接拼接为 NULL 字符串
适用场景	值参数	动态表名、列名等

2.2 安全处理 NULL 值的 MyBatis 实践

场景一：基本 NULL 值处理

<select id="findUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="name != null">
      AND name = #{name}
    </if>
  </where>
</select>

场景二：动态 NULL 值查询

<select id="findUsers" resultType="User">
  SELECT * FROM users
  <where>
    <choose>
      <when test="includeNull == true">
        AND (name IS NULL OR name = #{name})
      </when>
      <otherwise>
        AND name = #{name}
      </otherwise>
    </choose>
  </where>
</select>

场景三：LIKE 查询中的 NULL 处理

<select id="searchUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="keyword != null">
      AND name LIKE CONCAT('%', #{keyword}, '%')
    </if>
  </where>
</select>

2.3 连续 % 通配符的处理案例

在模糊查询中，有时查询条件本身包含 % 字符，这时需要特殊处理：

// Java 代码中处理包含 % 的查询条件
public List<User> searchUsers(String keyword) {
  if (keyword != null) {
    keyword = keyword.replace("%", "\\%");
  }
  return userMapper.searchUsers(keyword);
}

<!-- MyBatis 映射文件 -->
<select id="searchUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="keyword != null">
      AND name LIKE CONCAT('%', #{keyword}, '%') ESCAPE '\\'
    </if>
  </where>
</select>

3. 高级 NULL 值处理技巧

3.1 NULL 值与索引的关系

当字段允许为 NULL 时，索引的行为会有一些特殊之处：

1. 对于 IS NULL 条件，只有当索引列包含 NULL 值时才会使用索引
2. 复合索引中，如果所有列都为 NULL，则该行不会被索引
3. 使用 WHERE col IS NOT NULL 可以利用索引

3.2 使用索引优化 NULL 值查询

-- 创建包含 NULL 值的索引
CREATE INDEX idx_name ON users(name);

-- 优化 IS NULL 查询
EXPLAIN SELECT * FROM users WHERE name IS NULL;

3.3 NULL 值与聚合函数

聚合函数对 NULL 值的处理方式各不相同：

函数	NULL 处理方式
COUNT()	不统计 NULL 值
SUM()	忽略 NULL 值
AVG()	忽略 NULL 值
MAX()	忽略 NULL 值
MIN()	忽略 NULL 值
GROUP_CONCAT()	忽略 NULL 值

4. MyBatis 参数安全的最佳实践

4.1 始终优先使用 #{}

<!-- 安全的方式 -->
<select id="findUserById" resultType="User">
  SELECT * FROM users WHERE id = #{id}
</select>

<!-- 不安全的方式 -->
<select id="findUserByIdUnsafe" resultType="User">
  SELECT * FROM users WHERE id = ${id}
</select>

4.2 必须使用 ${} 时的安全措施

当必须使用 ${} 时（如动态表名），应采取以下防护措施：

1. 使用白名单验证输入
2. 对输入进行严格的转义处理
3. 限制数据库用户的权限

// 表名白名单验证
private static final Set<String> ALLOWED_TABLES = 
    Set.of("users", "products", "orders");

public List<Map<String, Object>> queryTable(String tableName) {
  if (!ALLOWED_TABLES.contains(tableName)) {
    throw new IllegalArgumentException("Invalid table name");
  }
  return mapper.queryTable(tableName);
}

4.3 使用 SQL 注入检测工具

1. 在开发阶段使用 SQL 注入检测工具扫描代码
2. 定期进行安全审计
3. 使用 MyBatis 插件进行运行时检测

// 简单的 MyBatis 插件示例，检测 ${} 使用
@Intercepts({
  @Signature(type= StatementHandler.class, 
             method="prepare", 
             args={Connection.class, Integer.class})
})
public class SqlInjectionInterceptor implements Interceptor {
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    StatementHandler handler = (StatementHandler) invocation.getTarget();
    BoundSql boundSql = handler.getBoundSql();
    String sql = boundSql.getSql();
    
    if (sql.contains("${")) {
      log.warn("Potential SQL injection risk: " + sql);
    }
    
    return invocation.proceed();
  }
}

5. 实际案例分析与性能优化

5.1 NULL 值对查询性能的影响

案例：用户表有 100 万条记录，其中 10% 的 email 字段为 NULL

-- 查询1: 使用 IS NULL
SELECT * FROM users WHERE email IS NULL;
-- 执行时间: 120ms

-- 查询2: 使用 = ''
SELECT * FROM users WHERE email = '';
-- 执行时间: 80ms

-- 查询3: 使用 IFNULL 转换
SELECT * FROM users WHERE IFNULL(email, '') = '';
-- 执行时间: 350ms

优化建议：

1. 尽量避免在 WHERE 子句中对列使用函数
2. 考虑使用默认值代替 NULL
3. 为 NULL 值查询创建专门的索引

5.2 MyBatis 批量操作中的 NULL 处理

<!-- 批量插入时处理 NULL 值 -->
<insert id="batchInsert" parameterType="list">
  INSERT INTO users (name, email, age)
  VALUES 
  <foreach collection="list" item="user" separator=",">
    (#{user.name}, #{user.email,jdbcType=VARCHAR}, 
     #{user.age,jdbcType=INTEGER})
  </foreach>
</insert>

提示：指定 jdbcType 可以更精确地控制 NULL 值的处理方式

5.3 动态 SQL 中的复杂 NULL 处理

<!-- 复杂的 NULL 值条件组合 -->
<select id="findUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="name != null">
      AND name = #{name}
    </if>
    <if test="email != null">
      <choose>
        <when test="email == ''">
          AND (email IS NULL OR email = '')
        </when>
        <otherwise>
          AND email = #{email}
        </otherwise>
      </choose>
    </if>
    <if test="minAge != null or maxAge != null">
      AND age BETWEEN 
      <choose>
        <when test="minAge != null">#{minAge}</when>
        <otherwise>0</otherwise>
      </choose>
      AND
      <choose>
        <when test="maxAge != null">#{maxAge}</when>
        <otherwise>200</otherwise>
      </choose>
    </if>
  </where>
</select>

6. 总结与最佳实践

在实际开发中，处理 NULL 值和保证 SQL 安全是每个后端开发者的必备技能。以下是一些关键的最佳实践：

1. NULL 值处理 ：

   • 始终使用 IS NULL/IS NOT NULL 来检查 NULL 值
   • 考虑使用 COALESCE 或 IFNULL 函数简化逻辑
   • 在设计表结构时，慎重考虑是否真的需要允许 NULL

2. MyBatis 安全 ：

   • 99% 的情况下使用 #{} 预编译参数
   • 限制 ${} 的使用，仅在必要时使用并严格验证输入
   • 为可能为 NULL 的参数指定 jdbcType

3. 性能优化 ：

   • 避免在 WHERE 子句中对列使用函数
   • 为包含 NULL 值的列创建合适的索引
   • 考虑使用默认值代替 NULL 以提高查询性能

4. 代码可维护性 ：

   • 在 MyBatis 映射文件中使用清晰的注释
   • 将复杂的条件逻辑封装到方法或标签中
   • 保持 SQL 的可读性和一致性