MySQL Select row where column contains elements from a list

Question

I'm currently working on a website with multible categories which are containing videos. My problem: i have multible categories per video but i dont know how to search for it:

Select * from table where category (contains the exact item)

I already thought of the LIKE operator, but that would cause problems when there are multible categories containing the same words (E.g.: 'cats_playing_with_balls', 'dogs_playing_with_balls').

Answer 1

Fix your data model! Don't store multiple values in a single column. Instead, you should have a separate table to store the relationship between videos and categories.

create table videos (
    video_id int primary key auto_increment,
    ...
);

create table categories (
    category_id int primary key auto_increment,
    name varchar(50),
    ...
);

create table video_categories (
    video_id int references videos(video_id),
    category_id int references categories(category_id),
    primary key (video_id, category_id)
);

Then your query can be efficiently expresssed as:

select v.*
from videos v
where exists (
    select 1
    from video_categories vc
    inner join categories c on c.category_id = vc.category_id
    where vc.video_id = v.video_id and c.name = 'my category'
)

---

For your current design, assuming that the categories are consistently stored as a list of comma-separated values, you can use find_in_set():

select * from videos where find_in_set('my category', categories);