MySQL find value in a separated hyphen list

Question

I have a user id that I would like to find if this id existed in a separated list with hyphen, using a bind method:

I have 2 issues:

1- The correct SQL query,

2- How to bind the value.

Table structure:

mysql> SELECT dash_cat_id, dash_users_cats FROM dash_cats;
+-------------+-----------------+
| dash_cat_id | dash_users_cats |
+-------------+-----------------+
|           1 | 2               |
|           2 | 1,2             |
|           3 | 1               |
|           4 | 2               |
|           5 | 1,2,3           |
|           6 | 1,2,3           |
|           7 | 1,2,3           |
|           8 | 1,2,3           |
+-------------+-----------------+
8 rows in set (0.00 sec)

$userID = 2; // this might be changed to 1 OR 3, it depends of the user

Then I would like to retrieve all dash_users_cats that the userID is 2 as an example;

I used this query, but it seems to be wrong:

 mysql> SELECT dash_cat_id, REPLACE(dash_users_cats, '-', ',') as dashRep from dash_cats WHERE FIND_IN_SET(2, dash_users_cats);
 +-------------+---------+
 | dash_cat_id | dashRep |
 +-------------+---------+
 |           1 | 2       |
 |           4 | 2       |
 +-------------+---------+
 2 rows in set (0.00 sec)

EDIT:

I changed the hyphen with the comma

Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). — sticky bit, Jun 20 '21 at 12:28
You might have misunderstood... Do **not** use strings with separated lists **at all**, regardless of the separator character, comma or dash or whatever... — sticky bit, Jun 20 '21 at 12:34
Read this: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and decide. — forpas, Jun 20 '21 at 12:38
There are no arrays in MySQL. And even if there were: No, don't use it. Use a linking table. — sticky bit, Jun 20 '21 at 12:50

score 2 · Answer 1 · answered Jun 20 '21 at 14:35

In a relational database, there are no "lists" or "arrays". Use a set of rows, one value per row in the given column.

mysql> SELECT dash_cat_id, dash_users_cats FROM dash_cats;
+-------------+-----------------+
| dash_cat_id | dash_user_id    |
+-------------+-----------------+
|           1 | 2               |
|           2 | 1               |
|           2 | 2               |
|           3 | 1               |
|           4 | 2               |
|           5 | 1               |
|           5 | 2               |
|           5 | 3               |
|           6 | 1               |
|           6 | 2               |
|           6 | 3               |
|           7 | 1               |
|           7 | 2               |
|           7 | 3               |
|           8 | 1               |
|           8 | 2               |
|           8 | 3               |
+-------------+-----------------+

Now you can search for a specific user easily:

SELECT * FROM dash_cats WHERE dash_user_id = 2;

score 1 · Accepted Answer · answered Jun 20 '21 at 16:22

You should normalize your tables by storing single user_cat value against each user_id. Refer to @Bill Karwin's answer. However, you can still make your current solution work by replacing comma with hyphen in the WHERE clause,

SELECT dash_cat_id,
REPLACE(dash_users_cats, '-', ',') as dashRep
FROM dash_cats
WHERE FIND_IN_SET(2, REPLACE(dash_users_cats, '-', ','))

MySQL find value in a separated hyphen list

2 Answers2

Linked