时间戳

首页 > 解决方案 > 使用 SELECT 查询检查具有单个值的多个列

sql - 使用 SELECT 查询检查具有单个值的多个列

问题描述

我有一桌水果。现在在 UI 中，我为搜索条件提供了一个字段。使用这个单一的搜索条件，我想在 Fruits Table 的多个列中进行搜索。

考虑 Fruits 表包含列 ID、Desc、Price、Quant、Stock。这里 Price,Quant 是整数，Stock 是一个 varchar。

我尝试了以下返回结果的查询，但我担心性能。

假设用户在 UI 提供的字段中输入 2 并点击搜索，则查询将如下所示

select ID, Desc, Price, Quant, Stock 
from Fruits 
where Price = '2' 
or Quant = '2' 
or stock = '2'

这是搜索同一个表的多个列的正确方法吗？还会对性能有什么影响吗？

标签： sqlspring-mvc

解决方案

首先，您要确保类型兼容。这些值很可能是数字，所以去掉引号：

select ID, Desc, Price, Quant, Stock 
from Fruits f
where Price = 2 or Quant = 2 or stock = 2;

这可以更简单地写成：

select ID, Desc, Price, Quant, Stock 
from Fruits f
where 2 in (Price, Quant, Stock);

但这无助于表现。

在大多数数据库中，您的查询将需要全表扫描——尽管有些数据库支持一种称为跳过扫描的特定类型的索引扫描，它可以提供帮助。

我能想到解决这个问题的唯一方法是在每一列上都有一个单独的索引：

create index idx_fruits_price on fruits(price);
create index idx_fruits_quant on fruits(quant, price);
create index idx_fruits_stock on fruits(stock, quant, price);

（你会明白为什么额外的列是有帮助的。）

然后使用union all：

select ID, Desc, Price, Quant, Stock 
from Fruits f
where Price = 2
union all
select ID, Desc, Price, Quant, Stock 
from Fruits f
where quant = 2 and price <> 2
union all
select ID, Desc, Price, Quant, Stock 
from Fruits f
where stock = 2 and price <> 2 and stock <> 2;

每个子查询都可以使用其中一个索引。由于不等式，结果是互斥的——假设列值不是null。如果null允许 s，则可以调整逻辑来处理它。

推荐阅读