代码之家 › 专栏 › 技术社区 › neilcrookes

在1个查询中为多行及其所有父级更新多对多计数器缓存

many-to-many mysql

neilcrookes · 技术社区 · 15 年前

考虑一个博客应用程序,它有一个日志、类别表和一个查阅表,将一个日志与一个或多个类别链接起来。类别是分级的。日志可以分配给任何类别,而不仅仅是叶节点。

Categories表有一个 post_count 用于缓存分配给特定类别的文章数的字段。它也有 parent_id , lft 和 rght MPTT的列。

但它也有一个 under_post_count 用于缓存分配给它或它的任何子类别的不同文章数的字段。这很有用,所以你可以 显示具有分配给它的文章数的类别层次列表, 或者它的一个孩子 ,在它旁边 .

我的应用程序已经到了这样的地步:在用类别创建一个帖子,或者编辑它的类别,或者删除一个有类别的帖子之后,我有一个新旧类别的类别ID列表,其中 后计数 字段需要更新。我希望下一步我能做的是通过一个查询, 更新 在后计数下 已识别的所有类别及其所有父项的字段,以及分配给每个类别或其任何子类别的不同职位数。 .

下面是为类别创建表和一些测试数据所需的SQL:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rght` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `post_count` int(11) NOT NULL DEFAULT '0',
  `under_post_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `categories_posts` (
  `category_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`post_id`)
) ENGINE=MyISAM;

INSERT INTO `categories` (`id`, `parent_id`, `lft`, `rght`, `name`) VALUES
(1, NULL, 1, 8, 'Cat 1'),
(4, 1, 2, 3, 'Cat 1.1'),
(5, 1, 4, 5, 'Cat 1.2'),
(6, 1, 6, 7, 'Cat 1.3'),
(2, NULL, 9, 16, 'Cat 2'),
(7, 2, 10, 11, 'Cat 2.1'),
(8, 2, 12, 13, 'Cat 2.2'),
(9, 2, 14, 15, 'Cat 2.3'),
(3, NULL, 17, 24, 'Cat 3'),
(10, 3, 18, 19, 'Cat 3.1'),
(11, 3, 20, 21, 'Cat 3.2'),
(12, 3, 22, 23, 'Cat 3.3');

运行几次以为 categories_posts 表:

INSERT IGNORE INTO `categories_posts` (`category_id`, `post_id`) 
SELECT `id`, CEILING(10 * RAND()) FROM `categories` ORDER BY RAND() LIMIT 6

有人能理解吗?非常感谢你的帮助。

1 回复 | 直到 15 年前

Unreason 15 年前

好吧,这里有几种给猫剥皮的方法(假设是5.1和触发器)

您可以从应用程序层更新所有内容
您可以触发更新 post_count 从 categories_posts 并触发更新(层叠)到 under_post_count 从 categories
最后,您可以触发 类别\帖子

另外,根据类别的实际数量,您可能不需要取消规格化。 在后计数下 因为带着它去买应该很容易也很便宜

SELECT c.id, SUM(cc.post_count) 
FROM categories c 
LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
GROUP BY c.id;

获取精确匹配的实际计数是

SELECT c.id, COUNT(*) 
FROM categories c 
LEFT JOIN categories_posts cp ON c.id = cp.post_id 
GROUP BY c.id;

结合这两个给出了包括层次结构在内的计数

SELECT c.id, COUNT(*) 
FROM categories c 
LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
LEFT JOIN categories_posts cp ON cc.id = cp.post_id
GROUP BY c.id;

编辑

从上面构造update语句应该不那么困难

UPDATE categories 
SET post_count = (SELECT COUNT(*) 
                  FROM categories_posts cp 
                  WHERE cp.post_id = categories.id)

应该为 后计数

的情况 在后计数下 是不同的,因为mysql不喜欢在where部分中提到目标表,所以你必须做一些这样的怪事。

UPDATE categories LEFT JOIN 
       (SELECT c.id, COUNT(*) AS result 
        FROM categories c 
        LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
        INNER JOIN categories_posts cp ON cc.id = cp.post_id
        GROUP BY c.id) AS x ON categories.id = x.id
SET under_post_count = x.result

编辑2
实际上,上面所有的查询中都有一个错误——每当我加入类别和帖子时,我都应该加入。 cc.id = cp.category_id 而不是 cp.post_id 我没有检查。不想改正…但只有在最后一个查询中

UPDATE categories LEFT JOIN 
       (SELECT c.id, COUNT(*) AS result 
        FROM categories c 
        LEFT JOIN categories cc ON c.lft <= cc.lft AND c.rght >= cc.rght 
        INNER JOIN categories_posts cp ON cc.id = cp.category_id
        INNER JOIN posts p ON cp.post_id = p.id
        WHERE p.status = 'published'
        GROUP BY c.id) AS x ON categories.id = x.id
SET under_post_count = x.result,
    post_count = (SELECT COUNT(*) 
                  FROM categories_posts cp 
                  WHERE cp.category_id = categories.id)

编辑3
只有几条注释:

上述查询将修复 在后计数下 和 后计数 不管数据的状态如何,
有一些查询更便宜,如果数据访问层被正确地抽象、保护并且可以保证原子性,那么这些查询只能做到这一点。 post_count = post_count +/- 1 在适当的状态记录上(类似于 在后计数下 )
如果您不能从应用程序级别可靠地模拟触发器,那么检查是否需要运行上述查询(尽管MySQL在这方面很好,但是如果您想成为DB不可知论者),或者采用一些通常只增加/减少只定期重新计算数字。