我需要一个更快的方法来选择一个MYSQL函数内的逗号字符串值

e0uiprwp  于 2023-10-15  发布在  Mysql
关注(0)|答案(2)|浏览(112)

我需要找到一个比下面两个工作解决方案更快的解决方案。
如果我将一个逗号分隔的字符串传递给一个函数,我需要最有效的方法来查看一个字段是否与该字符串中的值匹配。对于下面的例子,comma_string是函数的文本参数,它可以等于这样的字符串2,56,34,98,23
目前最快的解决方案是:

set _json_set = concat("[", comma_string, "]");
select * from `table` where `ID` in (select * from json_table(_json_set, "$[*]" columns(`c` int path "$")) as `jt`);

一个慢得多的解决方案是使用as find_in_set,但是find_in_set没有正确使用索引,因此它更慢:

select * from `table` where find_in_set(`ID`, comma_string);

上面的例子工作得很好,当然下面也很好,但据我所知,这是不可能的,因为变量/参数不能是集合/数组,使用术语in是快速的,因为它正确地使用了索引。上面的解决方案只比下面的概念慢,因为json表的构建:

select * from `table` where `ID` in (comma_string_set);

有没有比最好的方法更有效的方法(效率是速度)。请记住,这是一个函数。

**编辑:**归功于Paul Spiegel,但我稍微改变了他的答案,这是我第一个方法的迭代,并获得了轻微的速度增加。它更快的原因是,作为一项规则,连接比子查询快,并且它摆脱了一个选择,即子查询。所以挑战是,有人能让它更快,并在下面改进吗?:

select * from `table`
join json_table(_json_set, "$[*]" columns(`c` int path "$"))`jt` on `c`=`table`.`ID`;
ix0qys7i

ix0qys7i1#

使用JOIN而不是IN-子查询可能会更快:

set _json_set = concat("[", comma_string, "]");
select `t`.* 
from json_table(_json_set, "$[*]" columns(`c` int path "$")) as `jt`
join `table` `t` on `t`.`ID` = `jt`.`c`;

注意:如果comma_string中存在重复项,则查询可能返回重复项。

kmbjn2e3

kmbjn2e32#

你可以做你想做的事情并让它使用索引的唯一方法是使用动态SQL语句。

SET @sql = concat('select * from mytable where ID in (', comma_string_set, ')');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

您有责任保证comma_string_set是安全的,不会导致SQL注入漏洞。也就是说,它必须只包含一个以逗号分隔的id值列表。
不能在触发器或存储函数中使用此方法,但可以在存储过程中使用它(除非从触发器或存储函数调用该过程)。
如果必须在存储函数中执行此操作,则需要后退一步,重新考虑如何设计应用程序。

相关问题