如何实现过滤NULL及纯空格值的SARGABLE SQL查询？

嘿，这个问题问到点子上了——既要过滤掉NULL、空字符串和纯空格值，又得保证查询是SARGABLE（能用到列上的索引），确实得在简洁性和性能之间找平衡。咱们一步步拆解：

首先得明确：你原来的语句where isnull(ltrim(rtrim(ean_code)), N'') <> N''之所以不适合，是因为LTRIM/RTRIM函数包裹了EAN_CODE列，这会让数据库无法直接使用该列上的索引，只能做全表扫描或索引扫描，性能拉胯。

你拟写的语句where EAN_CODE is not null and EAN_CODE NOT IN ('', ' ', ' ', ' ', ' ', ' ')是SARGABLE的（直接对列做常量比较，索引能用），但缺点很明显：手动列举空格长度太麻烦，还容易漏掉更长的纯空格字符串（比如6个、7个空格的情况）。

给你几个更优的方案，按需选择：

方案1：平衡性能与简洁性的近似SARGABLE写法 #

如果EAN_CODE列上有索引，咱们可以先用SARGABLE条件快速过滤掉大部分无效数据，再对剩下的小部分数据做函数处理——实际性能几乎不受影响，写法还简洁：

WHERE EAN_CODE IS NOT NULL 
  AND EAN_CODE <> ''
  AND LTRIM(RTRIM(EAN_CODE)) <> ''

前两个条件会利用索引快速排除NULL和空字符串，剩下的行数量通常不会太多，最后一步的LTRIM/RTRIM开销可以忽略不计，比你手动列举一堆空格要靠谱得多。

方案2：严格SARGABLE（无函数，全索引利用） #

如果你必须保证100%用索引、不能对列用任何函数，那只能基于常量比较来写，但可以优化列举方式：先预估业务中EAN_CODE的最大长度，然后列举到对应长度的空格。比如如果业务里这个字段最长是15位，就写成：

WHERE EAN_CODE IS NOT NULL 
  AND EAN_CODE NOT IN ('', ' ', '  ', '   ', '    ', '     ', '      ', '       ', '        ', '         ', '          ', '           ', '            ', '             ', '              ')

虽然看起来有点丑，但确实是严格SARGABLE的，能完全利用索引。如果嫌手动写麻烦，可以用脚本生成这些空格字符串。

方案3：一劳永逸的表达式/计算列索引 #

如果你有权限修改表结构，这绝对是最优解：创建一个基于清理后值的计算列（或表达式索引），后续所有查询都能复用，既简洁又高效。

举几个主流数据库的例子：

SQL Server #

-- 添加持久化计算列
ALTER TABLE YourTable ADD CleanEAN AS LTRIM(RTRIM(EAN_CODE)) PERSISTED;
-- 给计算列建索引
CREATE INDEX IX_YourTable_CleanEAN ON YourTable(CleanEAN);

查询时直接用：

WHERE CleanEAN <> ''

MySQL #

-- 添加存储型虚拟列
ALTER TABLE YourTable ADD CleanEAN VARCHAR(255) GENERATED ALWAYS AS (TRIM(EAN_CODE)) STORED;
-- 建索引
CREATE INDEX IX_YourTable_CleanEAN ON YourTable(CleanEAN);

查询写法同上。

PostgreSQL #

-- 创建表达式索引（无需修改表结构）
CREATE INDEX IX_YourTable_CleanEAN ON YourTable(TRIM(EAN_CODE));

查询时用：

WHERE TRIM(EAN_CODE) <> ''

数据库会自动使用这个表达式索引，实现SARGABLE的效果。

总结 #

• 不能改表结构？优先选方案1，兼顾性能和简洁性；
• 必须严格SARGABLE且不能用函数？选方案2，根据业务字段长度列举足够的空格；
• 能改表结构？直接冲方案3，一劳永逸解决所有类似查询的性能问题。

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