mysql select data from a multivalue attribute

Question

How can I run a select query on a multivalue attribute? Does mysql have a function do select certain data from a multivalue field? Much help is appreciated. Here's a sample of the problem:

Table

userid      groups
-------------------
  2          2,3,5
  4          1
  9          2,5,10

datatype is char(250) for groups

I want to do a query to select all userids that belong to group 5, in this example it would be userid 2 and 9. So:

userid
------
  2
  9

Any way to go about it with a mysql query? or with php/mysql?

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

In case the groups datatype is SET

You can use

SELECT * FROM users WHERE FIND_IN_SET('5', groups);

UPDATE

In case of char or varchar. You can use this

SELECT * FROM users
WHERE 
     groups LIKE '5,%'
     OR groups LIKE '%,5'
     OR groups LIKE '%,5,%'
     OR groups = '5'

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 11 '12 at 00:52

Starx

77,474
47
185
261

the datatype is `char(250)` sorry :( – user962449 Mar 11 '12 at 00:57
@user962449, if you have to select the records, as per one item only then its better you use `SET` rather than `char(25)` – Starx Mar 11 '12 at 00:58

score 0 · Answer 2 · answered Mar 11 '12 at 01:00

This is ugly, but if you want to do it completely in MySQL, you could do:

SELECT *
FROM users
WHERE groups LIKE '%,5,%'
OR groups = '5'
OR groups LIKE '%,5'
OR groups LIKE '5,%'

You'd be better off having a separate table with one group per row and a reference back to the user. You could do a join with a much simpler condition with that schema.

SELECT users.*
FROM users
INNER JOIN users_groups ON (users.user_id = users_groups.group_id)
WHERE users_groups.group_id = 5

mysql select data from a multivalue attribute

2 Answers2

UPDATE