SQL实例之六

一个关键词对应多个站点,现有实现是:

keyword   US
keyword   UK

同一个词有多个站点,对应了表的多条数据,这个实现在根据站点提取关键词时很方便,但是在编辑和词列表时就比较麻烦(比如列表时同一个词列多条记录,编辑时也只能针对一个站点),期望是列表时相同的词只列出一个,站点信息用一个逗号分隔列出,编辑时可以编辑词的不同站点属性,为了达到这个预期,使用如上的存储结构显然不可行,但是目前程序已经按照这个格式进行读取了,那么我们需要寻找一个能够兼顾两头的方案。

实现方案是使用视图。

把站点这个属性提取出来:

//keyword表
1  keyword1   
2  keyword2

//keyword_site表
id  kid  site
1   1    US
2   1    UK
3   1    DE

这样,关键词keyword1就对应了US UK DE三个站点,而Keyword表还是仅仅保存关键词。那么如果构建最开始说的那个表结构呢(因为程序已经基于这个实现了),经过以上改造后,就可以使用视图组装一个新表了:

CREATE 
VIEW `Keywords`AS 
SELECT ks.id, k.keyword, ks.site from keyword as k LEFT JOIN keyword_site as ks ON k.id = ks.kid;

mysql_view
这个就是最终结果。这个实现兼顾了两头。

以下是具体的SQL:

-- ----------------------------
-- Table structure for keyword
-- ----------------------------
DROP TABLE IF EXISTS `keyword`;
CREATE TABLE `keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of keyword
-- ----------------------------
INSERT INTO `keyword` VALUES ('1', 'a1');
INSERT INTO `keyword` VALUES ('2', 'a2');
INSERT INTO `keyword` VALUES ('3', 'a3');
INSERT INTO `keyword` VALUES ('4', 'a4');

-- ----------------------------
-- Table structure for keyword_site
-- ----------------------------
DROP TABLE IF EXISTS `keyword_site`;
CREATE TABLE `keyword_site` (
  `id` int(11) NOT NULL,
  `kid` int(11) NOT NULL,
  `site` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `kid_index` (`kid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of keyword_site
-- ----------------------------
INSERT INTO `keyword_site` VALUES ('1', '1', 'US');
INSERT INTO `keyword_site` VALUES ('2', '1', 'UK');
INSERT INTO `keyword_site` VALUES ('3', '1', 'DE');
INSERT INTO `keyword_site` VALUES ('4', '2', 'US');
INSERT INTO `keyword_site` VALUES ('5', '2', 'UK');
INSERT INTO `keyword_site` VALUES ('6', '3', 'US');
INSERT INTO `keyword_site` VALUES ('7', '4', 'UK');

CREATE VIEW `keywords` AS 
SELECT ks.id, k.keyword, ks.site from keyword as k LEFT JOIN keyword_site as ks ON k.id = ks.kid ;