MySQL查询字段实现字符串分割split功能的示例代码 MySQL查询字段实现字符串分割

问题背景
场景模拟
方法实现

SUBSTRING_INDEX
LENGTH
REPLACE

SQL解析

问题背景

查询MySQL中用逗号分隔的字段【a,b,c】是否包含【a】

场景模拟现有表【ec_logicplace】，如下图所示：

文章图片

要求判断数值【1】是否存在于表【ec_logicplace】中的【actual_place_id】中

方法实现首先将【actual_place_id】字段用逗号拆分查询出来

文章图片

通用模板为：

SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c', ',', help_topic_id + 1 ), ',',- 1 ) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH( 'a,b,c' ) - LENGTH( REPLACE ( 'a,b,c', ',', '' ) ) + 1

上述所用的关键字包含【SUBSTRING_INDEX】，【LENGTH】，【REPLACE】。

SUBSTRING_INDEX
用于字符串拆分，格式为：
SUBSTRING_INDEX(str,delim,count)

参数	含义
str	需要拆分的字符串
delim	分隔符，通过某字符进行拆分
count	当 count 为正数，取第 n 个分隔符之前的所有字符；当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

【MySQL查询字段实现字符串分割split功能的示例代码】举例：
获取第二个以“，”逗号为分隔符之前的所有字符

SUBSTRING_INDEX('a,b,c',',',2)

获取倒数第二个以“，”逗号为分隔符之前的所有字符

SUBSTRING_INDEX('a,b,c',',',-2)

LENGTH
获取字符串的长度，格式为：
LENGTH(str)

参数	含义
str	需要计算长度的字符串

举例：
获取’a,b,c‘字符串的长度

LENGTH('a,b,c')

REPLACE
替换函数，格式为：

replace(str,from_str,to_str)

参数	含义
str	需要进行替换的字符串
from_str	需要被替换的字符串
to_str	需要替换的字符串

举例：
将分隔符“，”逗号替换成“、”顿号

REPLACE('a,b,c',',','、')

SQL解析此处用的是MySQL库的help_topic 表的help_topic_id 来作为变量，因为help_topic_id 是自增的。
原理：把要拆分的字符串拆分，首先需要知道最后要被拆分成多少个字符串，也就是上述所说的count,其次是需要知道用什么来进行拆分。所以分为两个步骤来进行sql编写
step1:获取最后需被拆分成多少个字符串，用help_topic_id 来模拟遍历第n个字符串：

help_topic_id < LENGTH('a,b,c')-LENGTH(REPLACE('a,b,c',',',''))+1

step2:根据逗号进行拆分字符串，也就是SUBSTRING_INDEX函数，最后把结果赋值给num字段

SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c',',',help_topic_id+1),',',-1) AS num

需要注意的是，这里使用的是MySQL中的内置表help_topic，里面有508条数据（不同版本数据条数有差别），用户需要有对该表查询的权限，这样的话只满足分割数量少于508条的字符串，否则应该自定义辅助表，设置更大的一个递增列
当需要分割的字符串是查询出来的时候
当需要分割的字符串是查询出来的时候，可能不止一条数据，直接嵌入模板SQL会报错

文章图片

这时候可以使用存储过程来进行处理，也就是本次遇到的问题
要求判断数值【1】是否存在于表【ec_logicplace】中的【actual_place_id】中
首先创建一个存储过程，一个输入参数一个输出参数，输入参数为需要判断的值，输出参数为判断结果，我们以0，1来进行判断，0是输入参数不存在于表字段中，1是输入参数存在于表字段中。存储过程如下

CREATE DEFINER=`root`@`%` PROCEDURE `queryActualInLogic`(IN `actualPlaceId` bigint,OUT `isContain` int)BEGIN DECLARE v_column VARCHAR(100); -- 设置终止标记 declare done int default 0; # 查询出所有待判断的字段值集合 declare table_loop cursor for SELECT actual_place_id FROM ec_logicplace WHERE actual_place_id IS NOT NULL; # 捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1相当于try异常 declare continue handler for not found set done=1; open table_loop; out_loop:LOOP# 遍历字段值，一一赋值FETCH NEXT FROM table_loop into v_column; IF done = 1 THENLEAVE out_loop; END IF; # 遍历字段值，拆分后进行判断，存在则赋值1，不存在则赋值0SET @STMT = CONCAT("SELECT COALESCE(count(1),0) AS isContain into @v_count FROM (SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( '",v_column,"' , ',', help_topic_id + 1 ), ',', -1 ) AS num FROM mysql.help_topic WHEREhelp_topic_id < LENGTH( '",v_column,"' ) - LENGTH ( REPLACE ( '",v_column,"' , ',', '' ) ) + 1 ) t WHERE t.num = ",actualPlaceId,"; "); PREPARE STMT FROM @STMT; #执行语句EXECUTE STMT; deallocate prepare STMT; set isContain = @v_count; if isContain = 1 THENLEAVE out_loop; END IF; END LOOP out_loop; close table_loop; END

测试：

文章图片

文章图片

后续MyBatis调用存储过程的返回值进行业务判断见
MyBatis调用MySQL存储过程，有返回参数
到此这篇关于MySQL查询字段实现字符串分割split功能的示例代码的文章就介绍到这了,更多相关MySQL字符串分割内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！