MySQL ::从逗号分隔的字符串中选择 [英] MySQL :: Select from comma separated string

问题描述

我有下表:

过滤器

filters

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 用户与过滤器之间的多对多关系，用列过滤器ID和用户ID创建新表为user_filters，并在每行中为每个用户和过滤器保存一个关联，就像您当前的过滤器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作为查询，下面的查询可以使用索引进行优化

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 

这篇关于MySQL ::从逗号分隔的字符串中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！