在 apache 中的条件点燃 sql 的情况下处理空值参数

Question

我不知道如何为以下查询命中索引：

select * from Cache 
where accountId = 'demo' 
and (? is null or country = ?) 
and (? is null or county = ?) 
and (? is null or province = ?) 
and (? is null or city = ?) 
and (? is null or locality = ?) 
order by createdAt desc

我有一个关于cols的有序组索引(accountId，国家，国家，省，城市，地方，createdAt(desc((。如果我删除 isnull，它正在工作。也许有另一种方法可以在 sql 中重写它以能够解释空参数？

Answer 1

您可以使用OR expansion重写它：

select * from Cache 
where accountId = 'demo' and ? is null
union all
select * from Cache 
where accountId = 'demo' and city = ?