需求背景:
用户输入一串标题或者一串诉求内容,通过检索用户输入的内容,判断是否属于某标签下的诉求内容,通过这些标签来对用户的诉求内容进行分派。
1.数据处理阶段:
只需要将满足内容的标签数据查询出来即可;
哪些标签数据在用户输入的内容中都存在,则返回;
标签内容如下展示:
其中 contain_content 字段,就是存储的标签,目的是需要判断标签中逗号分割的内容在用户输入的内容中都存在(少一个都不行)。
实现思路:
将 contain_content 根据逗号进行分割,分割成多行的数据;然后将分割后的数据与输入的内容进行匹配即可。
想法虽好,可是实行起来难啊。
难点1:mysql 切割后的数据如何展示为多行?mysql似乎没有这个函数
难点2:切割后的数据如何对输入的内容进行完全匹配,即切割后的多行值在输入内容中都存在。
第一步:
分割字符串,原数据长这样
目标数据:
这里需要运用到mysql字符串切割函数substring_index。
这里先奉上切割后的完整代码
SELECT
a.id
,substring_index(substring_index(a.contain_content, ',', b.help_temp_id), ',', - 1) AS content
FROM
(
-- 需求需要一条优先级最高的数据,查询出来的数据如上图
select
*
from
wo_complaint_auto_assign_config
order by
priority_level
DESC
LIMIT 1
) a
INNER JOIN
help_temp b
ON b.help_temp_id <= (length(a.contain_content) - length(REPLACE(a.contain_content, ',', ''))+1)
有时间研究的话,往下看
help_temp 是一个自增序列的表,列是1-1000的id值,可以随着需求不断增加
第一层切割:
substring_index(a.contain_content, ',', b.help_temp_id)
随着help_temp_id增加,截取的字符串依次增。
“a”、“a,b”、“a,b,c”
第二层切割:
substring_index(str, ',' , -1)
获取第一层切割的字符串,按分隔符分割之后的最后一个字符串
substring_index(substring_index(a.contain_content, ',', b.help_temp_id), ',', - 1)
这个最后输出的结果:
"a" "b" "c"
第三步:
与 help_temp 表关联
判断:
length(a.contain_content) - length(REPLACE(a.contain_content, ',', ''))+1
这个存在是将数据分割成多少份?
原始长度 - 去掉分隔符“,”之后的长度 = 分隔符数量
分隔符数量 + 1 = 被分割的数量
这样第一阶段字符串的分割就完成了
第二阶段:
判断用户输入的内容是否都包含切割后的数据。
SELECT
COUNT(*) AS total_count
,SUM(IF(LOCATE(values_column, target_string) > 0, 1, 0)) AS matching_count
FROM
your_table;
在切割完成后的数据中统计。
如果 total_count 等于 matching_count,则说明所有的值都存在于目标字符串中。
如果 total_count 小于 matching_count,则说明有些值不在目标字符串中。
实现代码如下:
用户输入的标题和内容,分别用参数 titile 和 common 标识,只要有其中一个参数全包含就返回;
select
distinct
id
from
temp_table
where
-- ======判断切割完成的列值是否都存在于,需要检查的字符串中,=======
-- 如果 total 等于 contain,则说明所有的值都存在于目标字符串中。
-- 如果 total 小于 contain,则说明有些值不在目标字符串中。
(SELECT COUNT(1) as total FROM temp_table) = (select SUM(IF(LOCATE(content, title) > 0, 1, 0)) as contain from temp_table)
or
(SELECT COUNT(1) as total FROM temp_table) = (select SUM(IF(LOCATE(content, common) > 0, 1, 0)) as contain from temp_table)
这样就获取到满足条件的id,通过这个id再去关联获取满足条件的数据即可
最后奉上完整代码
-- 建表DDL
CREATE TABLE `wo_complaint_auto_assign_config` (
`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'ID',
`contain_content` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '包含内容',
`priority_level` int DEFAULT NULL COMMENT '优先级',
`create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
)
-- 插入数据
INSERT INTO `wo_complaint_auto_assign_config` (`id`, `contain_content`, `priority_level`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES ('1693931782696927233', 'ETC,呼和浩特', 1, 'admin', '2023-08-22 18:23:03', 'admin', '2023-08-23 09:26:29');
-- ==========================以上是数据提供,可不用提供数据====================================
-- 创建一个自增序列 暂定为1000
with help_temp as
(
SELECT
ones.n + tens.n * 10 + hundreds.n * 100 + thousands.n * 1000 + 1 AS help_temp_id
FROM
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) ones
CROSS JOIN
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens
CROSS JOIN
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) hundreds
CROSS JOIN
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) thousands
WHERE
ones.n + tens.n * 10 + hundreds.n * 100 + thousands.n * 1000 < 1000
),
-- =============================一列切割为多行==================================
temp_table as
(
SELECT
a.id
,substring_index(substring_index(a.contain_content, ',', b.help_temp_id), ',', - 1) AS content
FROM
(
select
*
from
wo_complaint_auto_assign_config
order by
priority_level
DESC
LIMIT 1
) a
INNER JOIN
help_temp b
ON b.help_temp_id <= (length(a.contain_content) - length(REPLACE(a.contain_content, ',', ''))+1)
)
select
T1.id
,T2.contain_content
,T2.user_id
,T2.depart_id
,T2.priority_level
from
(
select
distinct
id
from
temp_table
where
-- ======判断切割完成的列值是否都存在于,需要检查的字符串中,=======
-- 如果 total 等于 contain,则说明所有的值都存在于目标字符串中。
-- 如果 total 小于 contain,则说明有些值不在目标字符串中。
(SELECT COUNT(1) as total FROM temp_table) = (select SUM(IF(LOCATE(content, 'ETC呼和浩') > 0, 1, 0)) as contain from temp_table)
or
(SELECT COUNT(1) as total FROM temp_table) = (select SUM(IF(LOCATE(content, 'ETC呼和浩特') > 0, 1, 0)) as contain from temp_table)
) T1
join
wo_complaint_auto_assign_config T2
ON T1.id = T2.id