mysql - 搜索多种颜色的数据库设计

Question

问题:如何使用此数据库设计搜索最多五种颜色？如果不可能，有更好的解决方案吗？

这是我构建的颜色搜索，它非常适合一种颜色，但我试图允许某人搜索最多 1-5 种颜色，理想情况下它会找到具有全部或部分颜色的媒体颜色。

查询输入以十六进制代码开始，然后转换为 RGB。我目前最多存储 5 种颜色(将来可能最多存储 8 种)。

CREATE TABLE `media_has_colors_unique_index` (
  `media_id` int(9) unsigned NOT NULL,
  `red` int(3) unsigned NOT NULL,
  `green` int(3) unsigned NOT NULL,
  `blue` int(3) unsigned NOT NULL,
  `hex` varchar(6) NOT NULL DEFAULT '',
  `percentage` double unsigned NOT NULL,
  `sequence` int(11) unsigned NOT NULL,
  PRIMARY KEY (`media_id`,`red`,`green`,`blue`),
  KEY `index_on_hex` (`hex`),
  KEY `index_on_percentage` (`percentage`),
  KEY `index_on_timestamp` (`sequence`),
  KEY `index_on_media_id` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以下是我在包含大约一百万条记录的临时数据库上运行的查询示例:

SELECT media_id, hex, percentage
FROM media_has_colors_unique_index
WHERE 
((red BETWEEN 41 AND 50) AND (green BETWEEN 166 AND 203) AND (blue BETWEEN 216 AND 264)) 
    AND 
((red BETWEEN 137 AND 168) AND (green BETWEEN 215 AND 262) AND (blue BETWEEN 223 AND 272))
GROUP BY media_id
HAVING percentage > 0.3
LIMIT 100;

查询中读取 BETWEEN 41 AND 50 的部分是通过基于百分比计算匹配阈值，为 PHP 中的每个颜色 channel 动态创建的:MIN = ceil($rgb[0 ] * .9) 和 MAX = ceil($rgb[0] * 1.1)。基本上在任一方向上都是 10%，这允许一系列颜色匹配在视觉上仍然与原始颜色搜索相似。

问题:如何使用此数据库设计搜索最多五种颜色？如果不可能，有更好的解决方案吗？

Answer 1

find media that have all or some of those colors

您的示例查询返回的媒体颜色既在第一个 3D 范围内，又在第二个 3D 范围内。不是具有第一范围内的颜色的介质和具有第二范围内的颜色的介质。后者是颜色在第一范围内或在第二范围内的介质。如果您想要这些媒体，请使用 OR 而不是 AND。