SQL基础能力-SQL中的LIKE语句

这是我参与8月更文挑战的第7天，活动详情查看： 8月更文挑战

继上一篇写了where语句以后，就很有一种继续写like的冲动。首先也是打算顺着where继续往下细讲，另外也是因为like查询作为模糊匹配查询在SQL也大量存在。然而它实在是让人又爱有怕，稍一不注意可能就会导致数据库慢查的出现。这里主要和大家讲一下like的具体使用以及导致慢查的原因。

在SQL中，like语句一般出现在where条件中使用。少部分在case when 语句中会出现，这种情况比较少见。

谈到like语句，通配符是不得不提。当前在SQL中主要有两种通配符，分别是

通配符	含义
%	代表0个或更多字符
_	代表1个字符

在实际的查询过程中，通过与like语句结合，通配符会代表相应个数的字符进行模糊匹配。例如，如果想查找名字以“CHEN”开头的所有数据，可以使用这个where子句:

SELECT CASE WHEN y.name like 'CHEN%' THEN 1 else 0 END FROM (SELECT 'chenqi' as `name`)y
使用LIKE关键字而不是=符号，因为我们使用的是部分匹配。在字符串中，我们以字符“CHEN”开始，然后是百分号“%”。这意味着要返回一个记录，第一个名字必须等于“CHEN”，然后是任意数量的字符。值得注意的是，MySQL不区分大小写。而spark与flink对大小写都非常敏感。
spark执行结果
flink执行结果
使用'%'
'%'代表0或者更多的字符，可以用于在搜索关键字的前面或者后面，如形如下列的情况
%chen%       代表查询中间为'chen'的记录
chen%       代表查询开头为'chen'的记录
%chen       代表查询结尾为'chen'的记录
msyql下三种查询结果：
spark下三种查询结果：
flink 下三种查询结果：
可以看到，对于‘%’的使用还是比较通用的，在三中引擎下的表现都一样，我们再来看'_'
使用'_'
相对于'%', '_' 减少了可匹配的字符数，这样也就在某种意义上更加精确，比如我明确知道某个条件的开头是'CH'并且长度为4位，则可以用'_'补全。如
SELECT CASE WHEN y.name like 'ch__' THEN 1 else 0 END FROM (SELECT 'chen' as `name`)y
而此时用如下语句就无法查询出想要的结果
SELECT CASE WHEN y.name like 'ch_' THEN 1 else 0 END FROM (SELECT 'chen' as `name`)y
'_'的用法相对'%'拥有更多的可选择性。
flink 中使用
spark 中使用
使用'_'与'%'
除了单独使用以外，他们还可以组合使用，一下仅选择个案进行演示
saprk
flink
NOT LIKE
这里稍微提一下，就是对like的动作取反。不进行深入的案例展示。
使用通配符带来的问题
使用通配符在提升了模糊查询的便利的同时，也带来了一个糟糕的问题，也就是使用不恰当导致的全表查询。当然这里讨论的是mysql。大数据计算引擎没有此方面的担忧。
like导致索引失效的原因：
LIKE过滤器在树遍历过程中只能使用第一个通配符之前的字符。其余字符只是过滤谓词，不会缩小扫描索引范围。因此，单个模糊匹配表达式可以包含两种谓词类型：
(1) 第一个通配符之前的部分作为访问谓词；
(2) 将其他字符作为过滤谓词。
第一个通配符之前的前缀越有选择性，扫描的索引范围就越小。这反过来又使索引查找速度更快。如下图使用三种不同的LIKE表达式说明了这种关系。三者都选择同一行，但扫描的索引范围，因此性能是非常不同的。
各种LIKE搜索
第一个表达式在通配符之前有两个字符。它们将扫描的索引范围限制为 18 行。其中只有一个匹配整个LIKE表达式——其他 17 个被提取但被丢弃。第二个表达式有一个更长的前缀，将扫描的索引范围缩小到两行。使用这个表达式，数据库只读取一个与结果无关的额外行。最后一个表达式根本没有过滤谓词：数据库只读取与整个LIKE表达式匹配的条目。LIKE以通配符开头的表达式。这样的LIKE表达式不能用作访问谓词。如果没有其他条件提供访问谓词，则数据库必须扫描整个表。
LIKE特殊语法匹配
在mysql、spark中存在更为多样的like模糊匹配方式。
比如支持正则匹配的模式，具有更加灵活的模板配置
spark中进行like正则匹配主要用到的关键是使用RLIKE or REGEXP。还是使用上面的案例进行
msyql
spark
在flink中无法支持
定义转义字符
定义转义字符，使用关键字ESCAPE，需要和like绑定使用，如
既然使用like可能导致性能问题，那么在case when中使用，会有同样问题吗？要解答这个问题，大家可以自行去看第一章《SQL执行顺序》，相信看完以后你心里就有底了。
like 语句中，MySQL对匹配字符大小写不敏感，而spark与flink则非常敏感。
在MySQL库中进行查询，需要避免使用通配符开头的查询模板，否则会导致索引失效。
正则匹配语法在flink中不适用。
参考文献：
use-the-index-luke.com/sql/where-c…