MySQL ::从逗号分隔的字符串中选择

小编典典

MySQL ::从逗号分隔的字符串中选择

sql

我有以下表格：

过滤器

id | u_ids
1  | 1, 2, 3
2  | 5, 6

使用者

id | name
1  | Tom
2  | Tim
3  | Sue
4  | Bruce
5  | Ann
6  | George

我想运行以下选择

select * from users where id in (select u_ids from filters where id =1);

我想收到

id | name
1  | Tom
2  | Tim
3  | Sue

但是我什么也没收到。

问题在于该字段u_ids是文本，因此“ in select”返回的内容类似于“ 1、2、3”（带有分号），因此 in 找不到任何值。

是否可以进行强制转换或将字符串更改为数组的选项？

阅读 198

2021-05-05

共1个答案

小编典典

最好规范化您的架构，不要以逗号分隔列表的形式存储关系，而是为此创建一个联结表，以维护用户和过滤器之间的 m：m
多对多关系，并创建一个新表，user_filters其中包括过滤器ID和用户列id并在每行中为每个用户保存一个关联，并且过滤器就像您当前架构关系中的过滤器1中有很多用户的(1,'1, 2, 3') 情况一样

filter id user id
    (1, '1'),
    (1, '2'),
    (1, '3'),

示例架构将如下所示

CREATE TABLE user_filters
    (`fid` int, `u_id` varchar(50))
;

INSERT INTO user_filters
    (`fid`, `u_id`)
VALUES
    (1, '1'),
    (1, '2'),
    (1, '3'),
    (2, '5'),
    (2, '5')
;

CREATE TABLE filters
    (`id` int, `title` varchar(50))
;

INSERT INTO filters
    (`id`, `title`)
VALUES
    (1, 'test'),
    (2, 'test 1')
;


CREATE TABLE users
    (`id` int, `name` varchar(6))
;

INSERT INTO users
    (`id`, `name`)
VALUES
    (1, 'Tom'),
    (2, 'Tim'),
    (3, 'Sue'),
    (4, 'Bruce'),
    (5, 'Ann'),
    (6, 'George')
;

对于上面的模式，您可以使用join轻松查询，下面的查询可以使用索引进行优化

select u.* 
from users u
join user_filters uf on(uf.u_id = u.id)
 where uf.fid =1

样本演示

如果您无法更改自己的架构并希望坚持使用当前架构，则可以按以下方式查询，但与上述查询相比，该架构无法充分优化

select u.* 
from users u
join filters f on(find_in_set(u.id,replace(`u_ids`,' ','')) > 0)
 where f.id =1

样本演示

数据库规范化

2021-05-05