我的sql水平一般,仅是一个笔记。无法保存是最优解。仅供参考。
场景
有一张消息表,其中有一个收信人字段中把多个用户以,
分隔保存信息。我需要根据userid来查询信息。为了方便理解,我减化一下表结构。我的表结构如下:
message_id | receiver | sender | message | create_time |
1 | user1,user2 | system | hello | 2022-06-30 16:30:12 |
2 | user1,user3 | system | world | 2022-06-30 16:31:19 |
3 | user3,user4,user5 | user1 | java | 2022-06-30 16:31:22 |
4 | user7,user8 | user2 | neovim | 2022-06-30 16:31:53 |
5 | itkey,lxyoucan | system | itkey | 2022-06-30 16:32:17 |
6 | user,user11 | good | very | 2022-06-30 16:38:18 |
表结构
create table t_message
(
message_id bigint auto_increment comment '消息ID'
primary key,
receiver varchar(4000) null comment '收件人',
sender varchar(200) not null comment '发件人',
message varchar(4000) not null comment '消息内容',
create_time datetime null comment '创建时间'
)
comment '消息中心';
数据
INSERT INTO t_message (message_id, receiver, sender, message, create_time) VALUES (1, 'user1,user2', 'system', 'hello', '2022-06-30 16:30:12');
INSERT INTO t_message (message_id, receiver, sender, message, create_time) VALUES (2, 'user1,user3', 'system', 'world', '2022-06-30 16:31:19');
INSERT INTO t_message (message_id, receiver, sender, message, create_time) VALUES (3, 'user3,user4,user5', 'user1', 'java', '2022-06-30 16:31:22');
INSERT INTO t_message (message_id, receiver, sender, message, create_time) VALUES (4, 'user7,user8', 'user2', 'neovim', '2022-06-30 16:31:53');
INSERT INTO t_message (message_id, receiver, sender, message, create_time) VALUES (5, 'itkey,lxyoucan', 'system', 'itkey', '2022-06-30 16:32:17');
INSERT INTO t_message (message_id, receiver, sender, message, create_time) VALUES (6, 'user,user11', 'good', 'very', '2022-06-30 16:38:18');
实践
[错误的示范]like模糊匹配
首先我第一个思路想到的是使用like语句进行模糊查询。看似简单易用,但是会有bug。
比如查询user3
:
select * from t_message where receiver like '%user3%';
message_id | receiver | sender | message | create_time |
2 | user1,user3 | system | world | 2022-06-30 16:31:19 |
3 | user3,user4,user5 | user1 | java | 2022-06-30 16:31:22 |
没有任何问题,但如果我要查询user
用户的数据呢?
select * from t_message where receiver like '%user%';
message_id | receiver | sender | message | create_time |
1 | user1,user2 | system | hello | 2022-06-30 16:30:12 |
2 | user1,user3 | system | world | 2022-06-30 16:31:19 |
3 | user3,user4,user5 | user1 | java | 2022-06-30 16:31:22 |
4 | user7,user8 | user2 | neovim | 2022-06-30 16:31:53 |
6 | user,user11 | good | very | 2022-06-30 16:38:18 |
发现此时查询的数据就不准备了,userid越是短的,查的越不精确。
可行的办法
首先说明,可能不是最好的解决办法
help_topic
开始之前我们先简答的了解一下help_topic
,主要是用来把一行变成多行的。
select substring_index(substring_index('82,83,84,85,86', ',', help_topic_id + 1), ',', -1) as Id
from mysql.help_topic
where help_topic_id < (length('82,83,84,85,86') - length(replace('82,83,84,85,86', ',', '')) + 1);
执行结果:
Id |
82 |
83 |
84 |
85 |
86 |
解析
乍一看比较绕,我尽量写详细一些。
substring_index
的作用:取得目标字符串左侧第n个分割符左侧的部分,n为负时返回右侧第n个的右部分。help_topic
是数据库mysql
的一个表,该表提供查询帮助主题给定关键字的详细内容(详细帮助信息)
表字段含义:
-
help_topic_id
:帮助主题详细信息在表记录中对应的ID -
name
:帮助主题给定的关键字名称。 -
help_category_id
:帮助主题类别ID,与help_category表中的help_category_id字段值相等。 -
description
:帮助主题的详细信息(这里就是我们通常查询帮助信息真正想看的内容,例如:告诉我们某某语句如何使用的语法与注意事项等)。 -
example
:帮助主题的示例信息(告诉我们语句如何使用的示例)。 -
url
:该帮助主题对应在MySQL官方在线手册中的URL链接地址。
ps:这条sql语句其实跟help_topic表没有什么关系,仅仅是借用了help_topic表的help_topic_id的值为(0,1,2,…),因此在本数据库中建立一个仅含ID字段的表进行关联,同样可以实现转多行效果
where 查询条件部分
(length('82,83,84,85,86') - length(replace('82,83,84,85,86', ',', '')) + 1)
看着很长,其实就是为了计算,
分隔的部分信息数量。也就是,
的数量+1。其实最终目标也只是得到了一个0,1,2,3,4的列表。有这样信息,就可以用来截取字符串了。
可以把这个sql一段一段执行,直至完全理解。
最终实现
SELECT
message_id,
substring_index( substring_index( msg.receiver, ',', topic.help_topic_id + 1 ), ',',- 1 ) AS receiver,
sender,
message,
create_time
FROM
t_message msg
JOIN mysql.help_topic topic ON topic.help_topic_id < ( length( msg.receiver ) - length( REPLACE ( msg.receiver, ',', '' ) ) + 1 );
查询结果如下:
message_id | receiver | sender | message | create_time |
1 | user1 | system | hello | 2022-06-30 16:30:12 |
1 | user2 | system | hello | 2022-06-30 16:30:12 |
2 | user1 | system | world | 2022-06-30 16:31:19 |
2 | user3 | system | world | 2022-06-30 16:31:19 |
3 | user3 | user1 | java | 2022-06-30 16:31:22 |
3 | user4 | user1 | java | 2022-06-30 16:31:22 |
3 | user5 | user1 | java | 2022-06-30 16:31:22 |
4 | user7 | user2 | neovim | 2022-06-30 16:31:53 |
4 | user8 | user2 | neovim | 2022-06-30 16:31:53 |
5 | itkey | system | itkey | 2022-06-30 16:32:17 |
5 | lxyoucan | system | itkey | 2022-06-30 16:32:17 |
6 | user | good | very | 2022-06-30 16:38:18 |
6 | user11 | good | very | 2022-06-30 16:38:18 |
得到这个表以后,查询就比较简单了。我比较担心的是,性能可能不会特别的高。
这时候我们还按user来查询,写法如下:
select * from (
SELECT message_id,
substring_index(substring_index(msg.receiver, ',', topic.help_topic_id + 1), ',',
- 1) AS receiver,
sender,
message,
create_time
FROM t_message msg
JOIN mysql.help_topic topic ON topic.help_topic_id <
(length(msg.receiver) - length(REPLACE(msg.receiver, ',', '')) + 1)
) as tmsg where tmsg.receiver='user';
message_id | receiver | sender | message | create_time |
6 | user | good | very | 2022-06-30 16:38:18 |
这次的查询结果就是正确的了。
减少子查询的使用,可以写成如下:
SELECT
message_id,
sender,
message,
create_time
FROM
t_message msg
JOIN mysql.help_topic topic ON topic.help_topic_id < ( length( msg.receiver ) - length( REPLACE ( msg.receiver, ',', '' ) ) + 1 )
where substring_index( substring_index( msg.receiver, ',', topic.help_topic_id + 1 ), ',',- 1 ) = 'user';
查询结果:
message_id | sender | message | create_time |
6 | good | very | 2022-06-30 16:38:18 |
总结
最终方案的准确性应该是没有问题了,但是性能方面还是有优化的空间的。