database - MYSQL match id with column keywords and show results -

i have db hundreds of cakes , different keywords each cake. in example want achieve first match cakes have @ least 2 similar keywords-- in example cakes 1 , 4. (not sure how table here, have listed ids 1-4 , column called ingredients shows 3 ingredients each id.) , want show them. sounds easy enough, have been unsuccessful far. appreciated.

table name:desserts

id ingredients
1-- fudge, caramel, coconut-- 2. vanilla, hazelnut, coconut-- 3. vanilla, chocolate, fudge-- 4. fudge, caramel, vanilla--

basically, want show cakes have 2 or more of same ingredients. not sure how query.

that horrible database design , correct answer change it. ideally should have separate tables cakes , ingredients, third relate cake ids ingredient ids.

but assuming cakes have 3 ingredients, can, though absolutely should not, this:

select distinct id (select id, substring_index(ingredients,',',1) ingredient cakes      union      select id, substring_index(substring_index(ingredients,',',-2),',',1) ingredient cakes      union      select id, substring_index(ingredients,',',-1) ingredient cakes) group ingredient having count(*) >= 2 

it uses sub-select create 3 tables of ingredients, 1 each comma separate position, combined union. once have information case of grouping cakes ingredients , filtering out ingredients have not been used more once (the having clause). leaves list of cake ids contain non-unique ingredients.

it horrid solution , not work if have cakes 4 or more ingredients, though can add more union statements account more possibly ingredients if there no way of changing database design.