mysql - 用于选择美国多个州的模式设计，或 "all"

Question

作为一个简化示例，假设我正在销售小部件。我在全国(美国和加拿大)销售它们，但有些只能在特定地区(美国一个或多个州或加拿大省)销售。

我想要一种存储此信息的好方法，以及一种查询给定用户可用的小部件的快速方法。 “美国，50 个州和华盛顿特区。”是最常见的值，所以我不想插入 51 行。

MySQL 不支持位图索引，所以排除了。

这里有一些组合:

• 美国50 个州和哥伦比亚特区
• 美国50 个州、哥伦比亚特区、加拿大，但不包括魁北克。
• 美国48 个相邻的州和哥伦比亚特区
• 美国哥伦比亚特区，但不包括科罗拉多
• 美国、哥伦比亚特区和领土(波多黎各等)。

我的用户会为他们的州/省和国家/地区提供一个值。

您能推荐一个提供良好存储和快速匹配的模式吗？

谢谢!

Answer 1

您应该构建预定义的值集并将该集存储到项目中。使用一个值，您可以检索匹配集和匹配项。

CREATE TABLE `valuesets` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `valueset_items` (
  `valueset_id` int(11) unsigned NOT NULL,
  `value` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`valueset_id`,`value`),
  CONSTRAINT `fk_valueset_items_valueset` FOREIGN KEY (`valueset_id`) REFERENCES `valuesets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE `items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `valueset_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_items_valueset` (`valueset_id`),
  CONSTRAINT `fk_items_valueset` FOREIGN KEY (`valueset_id`) REFERENCES `valuesets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

选择所有匹配特殊值的项目

SELECT *
FROM items
WHERE 
  valueset_id IN ( SELECT valueset_id 
                   FROM valueset_items 
                   WHERE `value` = 'A' )

SQL Fiddle DEMO