如何编写支持可选绑定参数的SELECT语句适配搜索API多条件查询

你当前的SQL存在核心逻辑错误：条件判断方向写反了。现有写法是判断表字段是否为NULL，会把字段值为NULL的合法数据错误排除，且未传参时无法实现跳过过滤的效果——正确逻辑应该是判断传入的绑定参数是否为NULL，参数未传（为NULL）时该条件直接恒真、不参与过滤，参数有值时才做字段匹配。

修正后的SQL写法 #

所有可选过滤条件统一调整为「参数为NULL则跳过，否则匹配字段」的逻辑，固定过滤条件（比如d.is_purged = false）保持不变：

select d.is_purged, d.is_reorg, ds.dlr_nm, ds.city, c.first_nm, c.middle_nm, 
c.last_nm, c.is_mdd, ds.state, lds.display_name, c.is_wrn, d.crt_ts, d.upd_ts 
from deal d 
left join candidate c on d.candidate_id = c.id 
left join lkup_deal_status lds on d.status = lds.status 
left join dealership ds on d.id = ds.deal_id 
where d.is_purged = false
  and (:firstName IS NULL OR c.first_nm = :firstName)
  and (:dealershipName IS NULL OR ds.dlr_nm = :dealershipName)
  and (:city IS NULL OR ds.city = :city)
  and (:middleName IS NULL OR c.middle_nm = :middleName)
  and (:lastName IS NULL OR c.last_nm = :lastName)
  and (:state IS NULL OR ds.state = :state)
  and (:status IS NULL OR lds.display_name = :status)

补充注意事项 #

• 要求API层逻辑统一：用户未传入的参数，绑定到SQL时要设为NULL，不要传空字符串；如果业务需要支持空字符串作为查询条件，额外加参数长度判断即可
• 如果需要支持模糊搜索，把等值匹配改成LIKE语法就行，比如名字条件改成:firstName IS NULL OR c.first_nm LIKE CONCAT('%', :firstName, '%')
• 高并发、大数据量场景下，更推荐在ORM层用动态SQL能力拼接语句（比如MyBatis的<if>标签、JPA的Specification），只把实际传入的参数条件拼到WHERE子句里，能减少数据库查询优化器的判断成本，命中更精准的执行计划，性能比全量条件写在SQL里更好。

举个MyBatis动态SQL的简单示例片段：

<where>
  d.is_purged = false
  <if test="firstName != null">
    AND c.first_nm = #{firstName}
  </if>
  <if test="lastName != null">
    AND c.last_nm = #{lastName}
  </if>
  <!-- 其余参数同理加if判断 -->
</where>

内容的提问来源于stack exchange，提问作者Natalie Trinh