MySQL how to use SET type to select records where a SET contains one or more items from another SET -

I have a table called 'recipes', which is listed in a SET type called 'content', all food items

Table of Recipes

ID (int)

A recipe (a small piece of special ingredients for recipes Numbers are restricted to use). Contents (text)

Content (SET)

I have another table called 'food_type' in each record for a 'name' field (eg 'fruit', 'poultry' 'Masala', 'Meat', ...) and 'Item' is a SET type that holds content related to that food type. As for the 'fruit', the SET record can be 'apple, orange, pear, banana'

food_types table

ID (p)

Item (SET)

My problem is how to create a MySQL query that sets the 'item' set in the 'Content' set From the 'recipes' table with one or more items, select all the records.

Then my question may be in English:

'Select all the recipes where there is one or more fruits in the content'

Does any MySQL A good way to do this is to ask a question:

Note: I appreciate that there are better ways to organize tables for dishes / content problems but I seek a MySQL query solution I am doing it with the SAT and the tables mentioned above The breakfast.

This is an improper use of the SET field - you can not add any food_type to the schema, the revision is always a good indicator Use an included table to represent many people that your structure is wrong: Many relationships In this situation, create a table ingredient_type which is the ingredient_id and Shows both tables with food_type_id . It also gives you permission for a variety of elements, such as a SET, but more flexible. (Component): apple orange chicken ginger potato food_type (type): fruit vegetable meat poultry spice ingredient_type (component, type) apple, fruit orange, fruit chicken, meat chicken, poultry ginger , Vegetable ginger, spice potatoes, vegetable recipe_address (recipe, ingredient): Method 1, Chicken Method 1, Potato Recipe 2, Orange Recipe 2, Ginger

With that structure, You can ask:

  Choose method (recipe) recipe_ingredient WHERE component = 'orange';  

This will show you all recipes that use oranges. A bit more complex:

Choose from the recipe (recipe) ingredient_type from recipe_ingredient recipe_ingredient.ingredient = ingredient_type.ingredient where ingredient_type.type = 'fruit';

All recipes that will be used by any component that are a fruit.

Edit: On using SETs.

You can do this by using the binary nature of SET storage. Where there is an overlap between recipe content and category members, the number of bits set in bits and (using the & amp; operator)> 0:

  Select different method from recipes. To eat eat_types (food_types.items and recipes.ingredients) at BIT_COUNT & gt; 0 where food_type Category = 'fruit'; 
 
 
 
 
 
 
 
 
 Only up to 64 items  
 Field values ​​are always set as one set in the same order 
 
 Most queries except for simple matches (not set for example 
 
 As long as the definitions are not the same, SETs can not be compared 
 
 There is no additional metadata about each item 
  
 This approach is intended for relational Use the database This textbook is the first-usual-form database material, the only purpose I can see for this kind of structure is to use it as an example of how to do it.