给定一个名为item
的表,其中包含"主"记录,例如:
item_id item_type item_name item_description
1 clothing T-Shirt Get your t-shirt here
2 clothing Polo Shirt Another kind of short-sleeve shirt
3 computer Macbook 2016 Macbook Pro with retina
和另一个称为 custom_fields
的表,我认为将被称为EAV结构(实体 - 属性值):
id item_id attribute value
1 1 size large
2 1 color purple
3 2 size medium
4 2 color green
5 3 memory 16GB
6 3 hard drive 512GB SSD
编码查询的最佳方法是什么,该查询将包含所有指定/配置的属性,该查询也将支持 ORDER BY
?
事实:
1.数据结构无法改变。
2.属性未知(用户配置)。3.可能为给定类型的项目设置了技术上无限数量的"属性"(例如上面的size
或color
)(实际上,我无法想象超过50个)。
4.查询中未包含哪些属性(用户配置)。
假设指定的属性存在于数组中,例如:
$attributes = ['size', 'color'];
似乎有些php可以做类似:
$counter = 1;
$from = 'item AS i';
$fields = 'name, description';
$order_by = 'size';
foreach( $attributes AS $attribute ) {
$table_alias = "a{$counter}";
$fields.= ",{$table_alias}.value AS {$attribute}";
$from.= " INNER JOIN custom_fields AS {$table_alias} ON i.item_id = {$table_alias}.item_id AND {$table_alias}.attribute = '{$attribute}'";
$counter++;
}
$query = "SELECT {$fields} FROM {$from} {$where} ORDER BY {$order_by}";
将构建一个可用于包括数据在内的查询,并允许通过订单。该查询看起来像:
SELECT name, description, a1.value AS size, a2.value AS color
FROM item AS i
INNER JOIN custom_fields AS a1 ON i.item_id = a1.item_id
AND a1.attribute = 'size'
INNER JOIN custom_fields AS a2 ON i.item_id = a2.item_id
AND a2.attribute = 'color'
但是,如果有20列以上,我担心的是,此查询似乎令人讨厌(20 JOINS
),并且可能存在性能问题。
有更好的方法来构建此问题吗?是否有一些方法,例如使用临时表,可以更有用或性能?
更新:
MySQL的版本不能确保是任何特定的内容,因此应该与任何版本兼容,因此请假设版本5.0 。
您也只能使用一个**加入*这样做:
SELECT m.*
,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
,GROUP_CONCAT(IF(cf.attribute = 'memory', cf.`value`, NULL)) AS 'memory'
,GROUP_CONCAT(IF(cf.attribute = 'hard drive', cf.`value`, NULL)) AS 'hard drive'
FROM master m
LEFT JOIN custom_fields cf ON m.item_id = cf.item_id
GROUP BY m.item_id;
要生成产品(或更多)的查询,您可以使用以下方式:对于所有产品
选择m。*
select DISTINCT
CONCAT("SELECT m.*n"
, GROUP_CONCAT(",GROUP_CONCAT(IF(cf.attribute = '",attribute,"', cf.`value`, NULL)) AS '",attribute,"'" SEPARATOR 'n')
, "FROM master mnLEFT JOIN custom_fields cf ON m.item_id = cf.item_idnGROUP BY m.item_id") as myquery
FROM custom_fields
WHERE item_id in (1);
示例
mysql> select DISTINCT
-> CONCAT("SELECT m.*n"
-> , GROUP_CONCAT(",GROUP_CONCAT(IF(cf.attribute = '",attribute,"', cf.`value`, NULL)) AS '",attribute,"'" SEPARATOR 'n')
-> , "FROM master mnLEFT JOIN custom_fields cf ON m.item_id = cf.item_idnGROUP BY m.item_id") as myquery
-> FROM custom_fields;
结果
SELECT m.*
,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
,GROUP_CONCAT(IF(cf.attribute = 'memory', cf.`value`, NULL)) AS 'memory'
,GROUP_CONCAT(IF(cf.attribute = 'hard drive', cf.`value`, NULL)) AS 'hard drive'FROM master m
LEFT JOIN custom_fields cf ON m.item_id = cf.item_id
GROUP BY m.item_id
1 row in set (0,00 sec)
mysql>
并执行此查询
mysql> SELECT m.*
-> ,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
-> ,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
-> ,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
-> ,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
-> ,GROUP_CONCAT(IF(cf.attribute = 'memory', cf.`value`, NULL)) AS 'memory'
-> ,GROUP_CONCAT(IF(cf.attribute = 'hard drive', cf.`value`, NULL)) AS 'hard drive'FROM master m
-> LEFT JOIN custom_fields cf ON m.item_id = cf.item_id
-> GROUP BY m.item_id ;
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
| item_id | item_type | item_name | item_description | size | color | size | color | memory | hard drive |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
| 1 | clothing | T-Shirt | Get your t-shirt here | large | purple | large | purple | NULL | NULL |
| 2 | clothing | Polo Shirt | Another kind of short-sleeve shirt | medium | green | medium | green | NULL | NULL |
| 3 | computer | macbook | 2016 Macbook Pro with retina | NULL | NULL | NULL | NULL | 16GB | 512GB SSD |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
3 rows in set (0,00 sec)
mysql>
带有准备的语句的样品
mysql> select DISTINCT
-> CONCAT("SELECT m.*n"
-> , GROUP_CONCAT(",GROUP_CONCAT(IF(cf.attribute = '",attribute,"', cf.`value`, NULL)) AS '",attribute,"'" SEPARATOR 'n')
-> , "FROM master mnLEFT JOIN custom_fields cf ON m.item_id = cf.item_idnGROUP BY m.item_id") as myquery INTO @sql
-> FROM custom_fields;
Query OK, 1 row affected (0,00 sec)
mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0,01 sec)
Statement prepared
mysql> execute stmt;
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
| item_id | item_type | item_name | item_description | size | color | size | color | memory | hard drive |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
| 1 | clothing | T-Shirt | Get your t-shirt here | large | purple | large | purple | NULL | NULL |
| 2 | clothing | Polo Shirt | Another kind of short-sleeve shirt | medium | green | medium | green | NULL | NULL |
| 3 | computer | macbook | 2016 Macbook Pro with retina | NULL | NULL | NULL | NULL | 16GB | 512GB SSD |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
3 rows in set (0,00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0,00 sec)
mysql>
使用mysql 5.7您可以考虑将属性检索为JSON:
SELECT
i.item_id,
i.item_name,
i.item_description,
CONCAT('{',
GROUP_CONCAT(CONCAT(JSON_QUOTE(a1.attribute),
':',
JSON_QUOTE(a1.value))
SEPARATOR ','),
'}') AS att
FROM
item AS i
INNER JOIN
custom_fields AS a1 ON i.item_id = a1.item_id
INNER JOIN
custom_fields AS a2 ON i.item_id = a2.item_id
AND a2.attribute = 'size'
WHERE
a1.attribute IN ('color' , 'size')
GROUP BY 1
ORDER BY a2.value DESC
结果:
+---------+------------+------------------------------------+-----------------------------------+
| item_id | item_name | item_description | att |
+---------+------------+------------------------------------+-----------------------------------+
| 2 | Polo Shirt | Another kind of short-sleeve shirt | {"size":"medium","color":"green"} |
| 1 | T-Shirt | Get your t-shirt here | {"size":"large","color":"purple"} |
+---------+------------+------------------------------------+-----------------------------------+
假设Custom_fields具有唯一的索引(item_id,属性)。
AND a2.attribute = 'size'
是动态部分,您可以在其中定义属性为订单,以及将属性定义为获取的 a1.attribute IN ('color' , 'size')
。
它的行为与原始的多加入查询略有不同。如果缺少一个或多个属性,它仍然返回项目。