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

2021-12-19 00:00:00 sql select mysql

我有以下表格:

过滤器

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 未找到任何值.

The problem is that the field u_ids is a text so the "in select" is returning something like "1, 2, 3" (with the semicolon) so the in does not find any value.

是否有任何选项可以进行转换或将字符串更改为数组?

Is there any option to make a casting or something to change the string to an array?

推荐答案

最好是规范化你的模式，不要以逗号分隔列表的形式存储关系，而是为此创建一个连接表来维护一个 m:m 用户和过滤器之间的多对多关系，创建一个新表作为 user_filters 列过滤器 id 和用户 id，并在每一行中为每个用户保存一个关联，并像您当前的架构关系一样进行过滤对于有很多用户的过滤器 1 (1, '1, 2, 3') 将变成

Its better to normalize your schema do not store relations in form of comma separated list instead create a junction table for this like to maintain a m:m many to many relation between users and filters,create a new table as user_filters with columns filter id and user id and in each row save one association per user and filter like in your current schema relation for filter 1 with many users (1, '1, 2, 3') will become like 

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

<小时>

示例架构将是这样的

Sample schema will be like this 

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 as 轻松查询，以下查询可以使用索引进行优化

For above schema you can easily query with join as, below query can be optimized using indexes 

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

示例演示

<小时>

如果您无法更改您的架构并想坚持使用当前的架构，您可以按如下方式查询，但与上述查询相比，此架构无法优化

Sample Demo

If you are not able to alter your schema and want to stick with the current one you can query as below but this one cannot be optimized enough as compare to above query

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

相关文章