GaussDB(DWS)迁移 - MySQL兼容 - find_in_set函数改写

函数介绍

find_in_set函数是MySQL实现集合成员查找的SQL写法，其功能如下
函数原型：FIND_IN_SET(str,strlist)
功能释义：

假如字符串str 在由N 子链组成的字符串列表strlist 中， 则返回值的范围在 1 到 N 之间 。
一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。
如果第一个参数是一个常数字符串，而第二个是type SET列，则 FIND_IN_SET() 函数被优化，使用比特计算。
如果str不在strlist 或strlist 为空字符串，则返回值为 0 。
如任意一个参数为NULL，则返回值为 NULL。
这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

示例语句：

mysql > SELECT FIND_IN_SET('b','a,b,c,d');
     -> 2
#--结果为2 , 因为b 在strlist集合中放在2的位置 并且起始数是从1开始计算起的!

语句改写

DWS目前(8.3.0)不支持类似功能的函数，但是可以通过plpgsql自定义函数进行改写，实现同样的功能。

CREATE OR REPLACE FUNCTION find_in_set(v_mem text, v_set text)
RETURNS int AS $$
DECLARE
   v_index int = 0;
   v_array text[];
   v_elem text;
BEGIN
   v_array := string_to_array(v_set, ',');
   FOREACH v_elem IN ARRAY v_array
   LOOP
      v_index := v_index+1;
      IF v_elem = v_mem THEN
         RETURN v_index;
      END IF;
   END LOOP;
   RETURN 0;
END;
$$ LANGUAGE plpgsql STRICT;

功能验证

经验证，函数结果集与MySQL保持一致。

--常规输入场景
select find_in_set('2','3,4,5'),find_in_set('2','1,2,3');
 find_in_set | find_in_set
-------------+-------------
           0 |           2
(1 row)
--空串匹配场景
select find_in_set('',',4,5,'),find_in_set('','1,2,,4');
 find_in_set | find_in_set
-------------+-------------
           1 |           3
(1 row)
--输入含null场景
select find_in_set(null,''),find_in_set('',null);
 find_in_set | find_in_set
-------------+-------------
             |
(1 row)

注意：输入空串场景，需考虑兼容模式差异，因ORA兼容模式下会将空串视作NULL。

补充知识

SQL之字符匹配函数LOCATE、POSITION、INSTR、IN、ANY、LIKE、REGEXP

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技~