业务背景

今天接到项目提出的一个技术难题，需求大概是这样的。
数据库中有两列，都是以英文逗号（分隔符）隔开的字符串，需要在sql文中判断出第二列是否包含第一个中按分隔符拆分成数组中的任意元素。
┐(‘～`；)┌

┐(‘～`；)┌

┐(‘～`；)┌

解决方案

怎么会有这么奇葩的需求，想了好久，貌似直接通过语法写不出来，上最后王牌：自定义函数。

/*
    校验v2（以指定符号分隔的字符串）中是否包含v1（以指定符号分隔的字符串）中项。
    eg： v1='1,2,3',v2='4,5,6' 不包含，返回0
        v1='1,2,5',v2='4,5,6'  包含，返回3
    author : kindey.s
    date   : 2020-03-04
 */
CREATE FUNCTION v2_if_include_v1_item (v1 VARCHAR(4000),v2 VARCHAR(4000)) RETURNS int(11)
    begin
        declare sc VARCHAR(1); -- 分隔符
        declare item VARCHAR(4000); -- 当前验证项
        declare leaveItem VARCHAR(4000); -- 剩余需要验证项
        declare pos int; -- 当前第一个分隔符位置
        declare existIndex int; -- 包含的位置，如果是0，说明不包含
        -- v1为空，直接返回不包含
        if length(v1) = 0 then
            return 0;
        end if;
        -- v2为空，直接返回不包含
        if length(v2) = 0 then
            return 0;
        end if;
        -- 分隔符暂时写死为英文逗号，以后可以升级为传入参数
        set sc = ',';
        -- 初始，v1全部需要校验
        set leaveItem = v1;
        -- 初始，v1中的第一个分隔符位置
        set pos = instr(leaveItem,sc);
        -- 如果v1中的第一个分隔符位置为0，说明v1中只有一个元素，直接判断v2中是否包含v1
        if pos = 0 then
            set existIndex = instr(v2,v1);
            if existIndex !=0 then
                return existIndex;
            end if;
        end if;
        -- 循环v1中的所有元素，逐个判断，发现存在返回v2中的index
        while length(leaveItem) > 0
        do

            IF pos = 0 THEN -- 最后一个元素
                set existIndex = instr(v2,leaveItem);
                set leaveItem = '';
                if existIndex != 0 then
                    return existIndex;
                end if;
            ELSE
                -- 截取第一个原型
                set item = substr(leaveItem,1,pos-1);
                -- 排除一个元素后作为剩余判断量
                set leaveItem = substr(leaveItem,pos+1,length(leaveItem) - pos);
                -- 获取新第一个分隔符位置
                set pos = instr(leaveItem,sc);
                -- 判断元素是否在v2中存在，返回0说明不存
                set existIndex = instr(v2,item);
                if existIndex != 0 then
                    return existIndex;
                end if;
            END IF;
        end while;
        return 0;
    end;

如上，费劲巴拉（mysql语法不熟，o(╥﹏╥)o）搞了快一个小时。总算搞出来了，测试了一下，基本满足业务需求。

后续思考

虽然问题解决了，作为一个写了10年代码的老程序猿，不能这么没有追求啊。只是实现了怎么行，性能怎么样呢。上测试。

测试环境

硬件

项目	指标
CPU	8 core
内存	16G
硬盘	500G SSD固态

软件

• win10
• mysql5.7

准备数据

CREATE TABLE t_test3  (
  c1 varchar(255) NULL,
  c2 varchar(255) NULL
);

INSERT INTO `t_test3`(`c1`, `c2`) VALUES ('1,2,3', '4,5,6,7');
INSERT INTO `t_test3`(`c1`, `c2`) VALUES ('1,3,5', '4,5,6,7');

insert t_test3
SELECT * from t_test3;

SELECT COUNT(1) from t_test3;

EXPLAIN
SELECT * FROM t_test3 where v2_if_include_v1_item(c1,c2) > 0;

测试结果

记录数	执行时间
1024	29ms
4096	0.86s
8192	1.7s
16384	3.4s

结果总结

记录数量在1000条以下表现良好，超过5000条就不太理想了，超过10000条基本就是灾难。
过程中尝试使用函数索引进行优化，也因为mysql函数索引的众多限制而失败了。

alter table t_test3 add column c_index int(11) GENERATED ALWAYS AS (v2_if_include_v1_item(c1,c2));

报错信息如下：

报错信息

原因是：创建generated column(包括virtual generated column 和stored generated column)时不能使用非确定性的（不可重复的）函数。

虽然最后满足了业务的要求，但是这个性能...，想了好久也没有想到更好的方案。
希望哪位大神能点建议，拜谢！！！