文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

问题背景

查询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 个分隔符之后的所有字符。

举例:

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

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 THEN
		    LEAVE out_loop;
		END IF;
		
		# 遍历字段值,拆分后进行判断,存在则赋值1,不存在则赋值0
		SET @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 WHERE
				help_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 THEN
			LEAVE out_loop;
		END IF;
		
	END LOOP out_loop;
	close table_loop;
END

测试:

后续MyBatis调用存储过程的返回值进行业务判断见

MyBatis调用MySQL存储过程,有返回参数 

到此这篇关于MySQL查询字段实现字符串分割split功能的示例代码的文章就介绍到这了,更多相关MySQL字符串分割内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯