MySQL 通过关系过滤查询
我在 2 个有关系的 MySQL 表上遇到以下问题: 当我想要完整列表或按姓名或电子邮件等过滤结果时,我可以轻松查询表 1(地址)。但现在我需要查询表1并根据表2的关系内容(兴趣)对其进行过滤。因此,仅当表 2 中满足一个(或多个)条件时,我才需要在表 1 中查找一行(通常是许多行)。
以下是表格:
CREATE TABLE IF NOT EXISTS `address` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`countryCode` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`languageCode` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `emailUnique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO `address` (`id`, `name`, `email`, `countryCode`, `languageCode`, `timestamp`) VALUES
(1, '', '[email protected]', 'BE', 'nl', '2010-07-16 14:07:00'),
(2, '', '[email protected]', 'BE', 'fr', '2010-07-16 14:10:25');
CREATE TABLE IF NOT EXISTS `interests` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`address_id` int(11) unsigned NOT NULL,
`cat` char(2) COLLATE utf8_unicode_ci NOT NULL,
`subcat` char(2) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO `interests` (`id`, `address_id`, `cat`, `subcat`, `timestamp`) VALUES
(1, 1, 'aa', 'xx', '2010-07-16 14:07:00'),
(2, 1, 'aa', 'yy', '2010-07-16 14:07:00'),
(3, 2, 'aa', 'xx', '2010-07-16 14:07:00'),
(4, 2, 'bb', 'zz', '2010-07-16 14:07:00')
(5, 2, 'aa', 'yy', '2010-07-16 14:07:00');
ALTER TABLE `interests`
ADD CONSTRAINT `interests_ibfk_1` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
例如,我需要查找具有 (有)作为兴趣 cat=aa 和 subcat=xx。或者,另一个例子,我需要同时感兴趣的地址 cat=aa 和 subcat=xx 以及 cat=aa 和 subcat=yy。特别是后者很重要,必须记住,地址和兴趣表都将是很长的列表,并且目录/子目录组合的数量会有所不同。我目前正在通过 Zend_Db_Table (findDependentRowset) 处理参考查询,但该解决方案对于命中数为 100 甚至 1000 的地址列表来说会减慢速度。
感谢您的帮助。
I'm having the following problem with 2 MySQL tables that have a relation:
I can easily query table 1 (address) when I want a full list or filter the result by name or email or such. But now I need to query table 1 and filter it based on the relational content of table 2 (interests). So, I need to find a row (usually many rows) in table 1 only if a (or more) conditions are met in table 2.
Here are the tables:
CREATE TABLE IF NOT EXISTS `address` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`countryCode` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`languageCode` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `emailUnique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO `address` (`id`, `name`, `email`, `countryCode`, `languageCode`, `timestamp`) VALUES
(1, '', '[email protected]', 'BE', 'nl', '2010-07-16 14:07:00'),
(2, '', '[email protected]', 'BE', 'fr', '2010-07-16 14:10:25');
CREATE TABLE IF NOT EXISTS `interests` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`address_id` int(11) unsigned NOT NULL,
`cat` char(2) COLLATE utf8_unicode_ci NOT NULL,
`subcat` char(2) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO `interests` (`id`, `address_id`, `cat`, `subcat`, `timestamp`) VALUES
(1, 1, 'aa', 'xx', '2010-07-16 14:07:00'),
(2, 1, 'aa', 'yy', '2010-07-16 14:07:00'),
(3, 2, 'aa', 'xx', '2010-07-16 14:07:00'),
(4, 2, 'bb', 'zz', '2010-07-16 14:07:00')
(5, 2, 'aa', 'yy', '2010-07-16 14:07:00');
ALTER TABLE `interests`
ADD CONSTRAINT `interests_ibfk_1` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
For example, I need to find the address(es) that has (have) as interest cat=aa and subcat=xx. Or, another example, I need the address(es) with as interest both cat=aa and subcat=xx AND cat=aa and subcat=yy. Specially the latter is important and one has to keep in mind that both the address and the interest tables will be long lists and that the amount of cat/subcat combinations will vary. I'm working with reference queries through Zend_Db_Table (findDependentRowset) at the moment but that solution is way to slow for address lists numbering 100s and even 1000s of hits.
Thank you for your help.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我在您的兴趣表中添加了另一行,以演示两个示例之间不同的结果集:
然后您可能需要尝试使用相关子查询,如下所示:
结果:
对于第二个示例,我们正在测试之前添加的新行为了不得到与上面相同的结果:
结果:
使用相关子查询简单明了。但请记住,就性能而言,它可能不是最好的,因为相关子查询将为外部查询中的每个地址执行一次。
I added another row in your interests table, to demonstrate a different result set between the two examples:
Then you may want to try using correlated subqueries as follows:
Result:
For the second example, we're testing for the new row added previously in order not to have the same result as above:
Result:
Using correlated subqueries is easy and straightforward. However keep in mind that it might not be the best in terms of performance, because the correlated subqueries will be executed once for each address in the outer query.