MySQL查询字段实现字符串分割split功能的示例代码
作者:唯空城 发布时间:2024-01-22 03:18: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存储过程,有返回参数
来源:https://blog.csdn.net/qq_37634156/article/details/122429089
猜你喜欢
- 1、二者的区别apply(): 非异步(子进程不是同时执行的),堵塞主进程。它的非异步体现在:一个一个按顺序执行子进程, 子进程不
- 如何加点盐(salt)?为了加强MD5的安全性,从而加入了新的算法部分即加盐值,加盐值是随机生成的一组字符串,可以包括随机的大小写字母、数字
- 思考:测试用例执行后,如何生成一个直观漂亮的测试报告呢?分析:1.unittest单元测试框架本身带有一个textTestRunner类,可
- 一、用属性替代 getter 或 setter 方法以下代码中包含手动实现的 getter(get_ohms) 和 setter(set_o
- 前言本文主要介绍的是关于Django验证码生成与使用的相关内容,分享出来供大家参考学习,下面来一起看看详细的介绍:方法如下:1、基于PIL生
- 本文实例为大家分享了opencv实现答题卡识别的具体代码,供大家参考,具体内容如下"""识别答题卡"
- docx2txt的Github地址docx2txt是基于python的从docx文件中提取文本和图片的库。代码是从python-docx中获
- 前不久微信上线了拍一拍功能,刚推出就被有才的网友玩坏了。还有更多没有节操的拍法这里就不展示了。但拍一拍属于弱提示,只有在聊天界面才能感受到。
- 本文实例讲述了Python使用matplotlib绘制正弦和余弦曲线的方法。分享给大家供大家参考,具体如下:一 介绍关键词:绘图库官网:ht
- 前言字典是Python中最强大的数据类型之一,本文将给大家详细介绍关于Python合并两个字典(dict)的相关内容,分享出来供大家参考学习
- 除了C/C++以外,我也接触过不少流行的语言,PHP、java、javascript、python,其中python可以说是操作起来最方便,
- 问题你想对在Unix系统上面运行的程序设置内存或CPU的使用限制。解决方案resource 模块能同时执行这两个任务。例如,要限制CPU时间
- 大家还是直接看代码吧~netG = Generator()print('# generator parameters:',
- 近期遇到一个需求,就是用PySide2做出一个GUI,并且要将后台使用Matplotlib绘制的图显示在界面上。自己琢磨了蛮久,网上也搜了不
- 框架概念框架和web服务器关系·静态资源:不是经常变化的资源、往往是固定不变的资源·动态资源:经常变化的资源·模板文件:提供了一个显示的模板
- 1. 使用 in 和 not inin 和 not in 在 Python 中是很常用的关键字,我们将它们归类为 成员运算符。使用这两个成员
- 本文实例讲述了Python实现的井字棋(Tic Tac Toe)游戏。分享给大家供大家参考,具体如下:说明用python实现了井字棋,整个框
- IIf 函数 根据表达式的值,来返回两部分中的其中一个。语法IIf(expr, truepart, fal
- 本文实例讲述了Python多进程机制。分享给大家供大家参考。具体如下:在以前只是接触过PYTHON的多线程机制,今天搜了一下多进程,相关文章
- 1、通过将函数存储在独立的文件中,可隐藏程序代码的细节,将重点放在程序的高层逻辑上。2、能让你在众多不同的程序中重用函数。将函数存储在独立文