mysql逗号分隔关键字,MySQL ::从逗号分隔的字符串中选择

I have the following tables:

filters

id | u_ids

1 | 1, 2, 3

2 | 5, 6

users

id | name

1 | Tom

2 | Tim

3 | Sue

4 | Bruce

5 | Ann

6 | George

And I want to run the following select

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

I would like to receive

id | name

1 | Tom

2 | Tim

3 | Sue

But I receive nothing.

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?

解决方案

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')

;

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

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