mysql键值存储,需要一个MySQL查询以从存储键值对的表中进行选择

I need to store few items and its properties in form of a key value pairs in the database (mySQL). I am planning to do it as following.

I'll use two tables items and item_properties.

items

itemId | itemName

-------------------

1923 | AC

1235 | Fridge

8273 | Heater

item_properties

itemId | property | value

--------------------------------

1923 | effect | cooling

1923 | consumption | efficient

1923 | type | split

1235 | effect | cooling

1235 | volume | 20 liters

8273 | effect | heating

8273 | consumption | efficient

8273 | heatMethod | coil

Now, if I have to select items whose 'effect' is 'cooling', I can do that using following query (which will give me 'AC' and 'Fridge' in result).

SELECT itemName FROM items i, item_properties p

WHERE i.itemId=p.itemId

AND (p.property = 'effect' AND p.value ='cooling');

I would like to know how write queries to select items that match multiple properties like

select all items whose 'effect' is 'cooling' AND 'consumption' is 'efficient' (which would match item 'AC').

select all items whose 'type' is 'split' OR 'heatMethod' is 'coil' OR 'consumption' is 'effecient' (which would match items 'AC' and 'Heater').

Kindly Help... Thanks in advance!!

解决方案

Here is an example query:

SELECT

itemName

FROM

items i,

JOIN

item_properties effect

ON i.itemId = effect.itemId AND effect.property = 'effect'

JOIN

item_properties consumption

ON i.itemId = consumption.itemId AND consumption.property = 'consumption'

WHERE effect.value = 'cooling' AND consumption.value = 'efficient';

I'll leave the oR query as something you can try yourself. It's simply adding more tables and using OR instead of AND in the WHERE.