我的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

解析

乍一看比较绕,我尽量写详细一些。

  1. substring_index的作用:取得目标字符串左侧第n个分割符左侧的部分,n为负时返回右侧第n个的右部分。
  2. 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

总结

最终方案的准确性应该是没有问题了,但是性能方面还是有优化的空间的。