=Start=

缘由：

以前知道SQL中的 like 和 rlike 是有区别的，差别主要在于前者只支持 百分号(%)——匹配任意数量的任意字符，和下划线(_)——匹配一个任意字符 作为特殊字符，后者支持正则匹配——功能更强大，但速度一般也较慢。所以我一般是简单的、希望速度快些的情况下用like做模糊匹配，其它场景用rlike实现。
但是近期在分析日志的时候发现Hive SQL中的 like 和 rlike 除了在功能上有区别之外，过滤生成的结果也有差异，比较奇怪，在此记录一下，方便后面参考。

正文：

参考解答：

相关背景：
当前有一个清洗好的SQL查询日志，其中除了完整的查询语句字段 query 之外，还有通过内容扫描提取出的查询表名 table_name 和该表被标记有敏感数据的列名 col_name ，我们在分析的时候除了会判断是否对敏感表做查询之外，还需要判断是否查询了敏感列（以及所有列），否则仅仅只针对敏感表的查询操作可能没那么敏感（对于统计类操作会在另外的一个分析维度去做）。

-- 先说结论，用 rlike/regexp 来进行内容匹配，没事别用 like 做内容匹配判断了，结果可能不符合预期

,if(query rlike concat('\\s{0,},\\s{0,}',col_name),1,0) as comma_left
,if(query rlike concat(col_name,'\\s{0,},\\s{0,}'),1,0) as comma_right
,if((lower(query) like '%select *%' or query rlike ',\\s{0,}\\*'),1,0) as select_all

-- where 条件中，静态的可以用 like 来过滤，动态生成的还是需要用 rlike 来过滤（避免漏过）；
and query rlike concat(',',col_name) -- 建议用-rlike-可以匹配到98条记录
and query like concat('%,',col_name,'%') -- 不建议-like-可以匹配到50条记录

and query like '%hello%' -- 这里hello的内容是固定的，就可以使用like进行匹配
and query like concat('%,','hello','%') -- 和上面的功能等价

格式 A like B ，其中A是字符串，B是表达式，表示能否用B这个表达式去完全匹配A这个字符串的内容，换句话说能否用B这个表达式去表示A的全部内容，注意这个和rlike是有区别的。返回的结果是True/False。

B只能使用简单匹配符号和%，””表示任意单个字符，字符”%”表示任意数量的字符。

like的匹配是按字符逐一匹配的，使用B从A的第一个字符开始匹配，所以即使有一个字符不同都不行。

如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B的正则语法，则为TRUE；否则为FALSE。尤其注意NULL值的匹配，返回的结果不是FALSE和TRUE，而是null，其实除了is null/is not null，其他的关系运算符只要碰到null值出现，结果都是返回NULL，而不是TRUE/FALSE。

select
'abcde' like 'abc' -- false
,'abcde' like 'abcde' -- true
,'abcde' like 'abc%' -- true
,'abcde' like 'abcde%' -- true
,'abcde' like '%abcde%' -- true
;

select
null like '%'
,NULL like '%'
,'abc' like null
,'abc' like NULL
,'abcde' like '%abcde%'
/*
_c0    _c1 _c2 _c3 _c4 
                true
*/


select
'abc' like 'abcde' -- false
,'abc' like 'abc%' -- true
,'abc' like 'abcde%' -- false
,'abc' like '%abc%' -- true
;

/*
分析目标是找出查询SQL中包含敏感列关键字的查询行为
query - 完整查询语句字段
col_name - 敏感列名字段
*/
SELECT
  col_name,
  if(regexp_replace(query,'\\s{0,},\\s{0,}',',') like concat('%',col_name,'%'),1,0) as col_in,
  if(regexp_replace(query,'\\s{0,},\\s{0,}',',') like concat('%,',col_name,'%'),1,0) as comma_left,
  if(regexp_replace(query,'\\s{0,},\\s{0,}',',') like concat('%',col_name,',%'),1,0) as comma_right,

  if(regexp_replace(query,'\\s{0,},\\s{0,}',',') rlike col_name,1,0) as col_in2, --实际情况符合预期
  if(regexp_replace(query,'\\s{0,},\\s{0,}',',') rlike concat(',',col_name),1,0) as comma_left2, --实际情况符合预期
  if(regexp_replace(query,'\\s{0,},\\s{0,}',',') rlike concat(col_name,','),1,0) as comma_right2, --实际情况符合预期

  regexp_replace(query,'\\s{0,},\\s{0,}',',') as query1,
  query
FROM
  hive_db.query_log
WHERE
  p_date BETWEEN '20231015' and '20231015'
-- and regexp_replace(query,'\\s{0,},\\s{0,}',',') like concat('%,',col_name,'%') -- like-可以匹配到50条记录
and regexp_replace(query,'\\s{0,},\\s{0,}',',') rlike concat(',',col_name) -- rlike-可以匹配到98条记录
LIMIT 100

参考链接：

Hive中rlike,like,not like，regexp区别与使用详解
https://blog.csdn.net/qq_26442553/article/details/79452221

[知识讲解篇-20]Hive的几种like详解
https://zhuanlan.zhihu.com/p/337885986

HIVE常用正则函数(like、rlike、regexp、regexp_replace、regexp_extract)
https://www.jianshu.com/p/3bcc06b1294b

hive like 语法
https://juejin.cn/s/hive%20like%20%E8%AF%AD%E6%B3%95

=END=