mysql 数据可变_MySQL可变商品数据模型的设计

筛选时,查找"同时包含"多个特定配置条件的商品集合,可以这样写:

SELECT goods_id FROM (

SELECT goods_id FROM attr WHERE attr_name = '内存' AND attr_value = '8GB'

UNION ALL

SELECT goods_id FROM attr WHERE attr_name = '存储' AND attr_value = '256GB'

) AS tmp

GROUP BY goods_id HAVING count(*) = 2;

+----------+

| goods_id |

+----------+

| 2 |

+----------+

表结构如下:

CREATE TABLE `attr` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`goods_id` int(10) unsigned NOT NULL,

`attr_name` varchar(255) NOT NULL DEFAULT '',

`attr_value` varchar(255) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

UNIQUE KEY goods_id_attr_name (`goods_id`, `attr_name`),

KEY attr_name_attr_value (`attr_name`, `attr_value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(1,'内存','4GB');

INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(2,'内存','8GB');

INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(1,'存储','256GB');

INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(2,'存储','256GB');

+----+----------+-----------+------------+

| id | goods_id | attr_name | attr_value |

+----+----------+-----------+------------+

| 1 | 1 | 内存 | 4GB |

| 2 | 2 | 内存 | 8GB |

| 3 | 1 | 存储 | 256GB |

| 4 | 2 | 存储 | 256GB |

+----+----------+-----------+------------+