代码之家 › 专栏 › 技术社区 › Jamie Wong

更新MySQL中的缓存计数

children tree caching ruby-on-rails mysql

Jamie Wong · 技术社区 · 16 年前

为了修复bug,我必须遍历表中的所有行,将缓存的子级计数更新为它的实际值。表中事物的结构形成一棵树。

在Rails中,以下功能满足我的需要:

Thing.all.each do |th|
  Thing.connection.update(
    "
      UPDATE #{Thing.quoted_table_name} 
        SET children_count = #{th.children.count}
        WHERE id = #{th.id}
    "
  )
end

在一个MySQL查询中有什么方法可以做到这一点吗? 或者,在多个查询中,除了纯MySQL,还有其他方法可以做到这一点吗?

我想要类似的东西

UPDATE table_name
  SET children_count = (
    SELECT COUNT(*) 
      FROM table_name AS tbl 
      WHERE tbl.parent_id = table_name.id
  )

除了上面的不起作用(我明白为什么不起作用)。

2 回复 | 直到 16 年前

Ike Walker 16 年前

你可能犯了这个错误,对吧?

ERROR 1093 (HY000): You can't specify target table 'table_name' for update in FROM clause

解决这个问题的最简单方法可能是将子计数选择到一个临时表中,然后连接到该表进行更新。

如果父/子关系的深度始终为1,则此操作应该有效。根据您的原始更新,这似乎是一个安全的假设。

我在表上添加了一个显式的写锁,以确保在创建临时表之后不会修改任何行。只有在更新期间(这将取决于数据量)能够将其锁定时,才应执行此操作。

lock tables table_name write;

create temporary table temp_child_counts as
select parent_id, count(*) as child_count
from table_name 
group by parent_id;

alter table temp_child_counts add unique key (parent_id);

update table_name
inner join temp_child_counts on temp_child_counts.parent_id = table_name.id
set table_name.child_count = temp_child_counts.child_count;

unlock tables;

Steven Soroka 16 年前

您的选择性更新应该可以工作;让我们试着修补一下:

UPDATE table_name
  SET children_count = (
    SELECT COUNT(sub_table_name.id) 
      FROM sub_table_name 
      WHERE sub_table_name.parent_id = table_name.id
  )

或者如果子表是同一个表:

UPDATE table_name as top_table
  SET children_count = (
    SELECT COUNT(sub_table.id) 
      FROM (select * from table_name) as sub_table
      WHERE sub_table.parent_id = top_table.id
  )

但我想那不是超高效的。

推荐文章

aarona · Rails迁移批量更新change_column_default

1 年前