MySQL query tables using delimited string

Question

Here's my table structure:

user_id   features
------------------
1         1,2,3,4,5
2         2,4,5
3         1,2
4         3,4,5

id        feature
------------------------
1         item1
2         item2
3         item3
4         item4
5         item5

I'd like to join both tables and query eg. user id 2 and get the following result:

id_feature   feature
-----------------------------------------
2            item2 
4            item4
5            item5

I'm storing up to 500 values in "features", that's why I didn't want to create thousands of records. Would this be possible in an easy way?

You should just create a row per userid-feature combination. Even if it means thousands of rows. You can still do what you want but it will cost you performance, because you lose any indices and will have to analyze strings. — nl-x, Jun 28 '14 at 19:19
And it would be very easy, if you've got a junction table and not a comma separated list. — VMai, Jun 28 '14 at 19:22
You're going to want to read [this question and its answers](http://stackoverflow.com/q/3653462/812837). — Clockwork-Muse, Jun 29 '14 at 10:26

score 5 · Accepted Answer · edited Jun 28 '14 at 19:34

Your reasons for not having a proper junction table are insufficient. SQL is designed to support large tables and to do the processing efficiently.

That said, you can do what you want:

select f.*
from users u join
     features f
     on find_in_set(f.id, uf.features) > 0
where u.user_id = 2;

However, this query is less efficient than a proper table structure, with an appropriate UserFeatures table, that has a column for user_idand feature_id.

score 2 · Answer 2 · answered Jun 28 '14 at 19:28

You should just create a row per userid-feature combination. Even if it means thousands of rows. You can still do what you want but it will cost you performance, because you lose any indices and will have to analyze strings.

If you still want to proceed with this bad design:

select feature.* from
feature f inner join user_feature uf on
    concat('%,',f.id,',%') like concat(',',uf.filters,',')
where uf.user_id = 2

MySQL query tables using delimited string

2 Answers2