MYSQL - compare arrays with comma separated string

Question

I have a database where I store posts with ids for tags, categories and types.

For example I have a database table for posts like this:

ID	NAME	TAGS	CATEGORIES	TYPE
1	News	12,13,4	1,4,22	20,21
2	Text	12,4	1,3,5	15
3	Docs	12,13,4	4,8,22	13,20

Now I want to make a mysql query to get all entries with the following ids:

TAGS (12 or 4) & CATEGORIES (1 or 4) & TYPE (20)

The query should return the posts 1 and 3 (News and Docs)

How can I get the right results?

Does this answer your question? [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) — user3783243, Aug 30 '22 at 22:14
Hi, I can´t change the database structure anymore. Is there a way to achieve my query anyways? — sinan_u, Aug 30 '22 at 22:20
There is https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set but you should really talk with dev team about the schema, this won't scale well. — user3783243, Aug 30 '22 at 22:22
how should the IDs be stored? There would be several combinations per post? — sinan_u, Aug 30 '22 at 22:25

Akina · Answer 1 · 2022-08-31T09:00:39.503

Now I want to make a mysql query to get all entries with the following ids:

TAGS (12 or 4) & CATEGORIES (1 or 4) & TYPE (20)

SELECT *
FROM posts 
WHERE JSON_OVERLAPS(CONCAT('[', tags, ']'), '[12,4]')
  AND JSON_OVERLAPS(CONCAT('[', categories, ']'), '[1,4]')
  AND FIND_IN_SET(20, type);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=708f22757b340e1a822f9f65c03642c1

MySQL version 8.0.17 or above needed.

We have MySQL 5.7. so this will not work :( – sinan_u

SELECT DISTINCT
       posts.*
FROM posts
JOIN ( SELECT 12 tag
       UNION ALL 
       SELECT 4 ) tags ON FIND_IN_SET(tags.tag, posts.tags)
JOIN ( SELECT 1 category
       UNION ALL 
       SELECT 4 ) categories ON FIND_IN_SET(categories.category, posts.categories)
JOIN ( SELECT 20 type ) types ON FIND_IN_SET(types.type, posts.type)

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3715ee5a1656fd555bf67181ddd75d5f

Hi Akina, the DB is MySQL 5.7. so this will not work :( – sinan_u Aug 31 '22 at 08:53 — sinan_u, Aug 31 '22 at 08:53

MYSQL - compare arrays with comma separated string

1 Answers1