如何在具有3次类似操作符的SQL查询中编写一次“替换”

Question

这是我的sql查询。我不想写三遍“替换”。我如何优化它？

select * from table1 where col1='blah' AND 
(
replace(replace(col2,'_',' '),'-',' ') LIKE ? OR 
replace(replace(col2,'_',' '),'-',' ') LIKE ? OR 
replace(replace(col2,'_',' '),'-',' ') LIKE ?
)

Answer 1

在MySQL中，即使不进行任何聚合，也可以在HAVING子句中使用列别名：

select *, replace(replace(col2,'_',' '),'-',' ') as col2_replace
from table1
where col1='blah'
having col2_replace like ?
    or col2_replace like ?

Answer 2

您可以使用子查询：

SELECT *
FROM (
  select *, replace(replace(col2,'_',' '),'-',' ') AS r
  from table1 
  where col1='blah' 
) s
WHERE r LIKE ? OR r LIKE ? OR r LIKE ?

或LATERAL

select *
from table1
  ,LATERAL(SELECT replace(replace(col2,'_',' '),'-',' ')  AS r) s
where col1='blah' 
  and (s.r LIKE ? OR s.r LIKE ? OR s.r LIKE ?)

db<>fiddle demo

我更喜欢第二种方法，因为没有必要引入外部查询。此特性在8.0.14版本中添加。

相关信息：

• PostgreSQL: using a calculated column in the same query
• CROSS/OUTER APPLY in MySQL

Answer 3

MySQL有一种将子查询具体化的倾向--这不仅会导致读取和写入临时表的开销，而且还会影响更复杂查询中索引的使用。

以下是三个不需要子查询的替代解决方案。

如果?不包含通配符，那么最简单的方法是：

 replace(replace(col2, '_', ' '), '-', ' ') in (?, ?, ?)

如果是这样，则更改逻辑以使用单个正则表达式模式：

 replace(replace(col2, '_', ' '), '-', ' ') regexp ?

您还可以显式地调整查询中的模式：

 replace(replace(col2, '_', ' '), '-', ' ') regexp
    concat('(',
           replace(replace(?, '_', '.'), '%', '.*'), ')|(',
           replace(replace(?, '_', '.'), '%', '.*'), ')|(',
           replace(replace(?, '_', '.'), '%', '.*'), ')'
          )