MySQL更新字段来自另一个表的count()值

假设有文章post和评论comment两个表,文章表记录有评论的数量,但是这个值我们要一次更新。

如下,现在post表的comment_count都是0,我们的目标是:执行一个SQL语句,让其把统计comment表的数据数量。

post表数据如下:

+---------+-------------------+---------------+
| post_id | title             | comment_count |
+---------+-------------------+---------------+
|       1 | How to lean MySQL |             0 |
|       2 | How to lean PHP   |             0 |
|       3 | How to lean Redis |             0 |
+---------+-------------------+---------------+

comment表数据如下:

+------------+---------+---------+
| comment_id | post_id | content |
+------------+---------+---------+
|          1 | 1       | Good!   |
|          2 | 1       | Nice!   |
|          3 | 1       | Yeap!   |
+------------+---------+---------+

因为这里只有post_id=1的文章有评论,所以最终的comment_count值分别为:3 0 0

1 使用SQL语句

在数据量为100W以下的时候,可以用一条命令解决:

UPDATE post AS a SET comment_count = (select count(*) FROM comment WHERE post_id=a.post_id);

2 使用存储过程(数据量巨大时用)

当数据量很大的时候,比如1亿条记录,如果再用上面这条命令,就会导致所有行被锁定(InnoDB存储引擎)。

UPDATE语句首先会查找需要更新的行,如果符合要求,先锁定,然后再更新,

上面这条语句会锁定所有行,等效于锁定全表

解决这个问题的办法就是,post表的post_id范围批量更新,具体代码如下:

DROP PROCEDURE IF EXISTS UpdateCount;

DELIMITER //
CREATE PROCEDURE UpdateCount()
BEGIN
    DECLARE lower INT;	# ID下限
    DECLARE upper INT;  # ID上限
    DECLARE step INT;	# 次增加步数
    DECLARE max_id INT; # 最大ID
    
    SET lower = 1;
    SET upper = 0;
    SET step = 10000;   # 每次更新1万条数据
    SET max_id = 0;
    
    # 从库中读取最大ID
    SELECT max(post_id) INTO max_id FROM post;
    
    WHILE lower <= max_id DO
        # 上限等于下限加上步宽
        SET upper = lower + step;
        
        # 还是和上面的方法一样,只是多了post_id的限定范围
        UPDATE post AS a
        SET comment_count = (SELECT count(*) FROM comment WHERE post_id=a.post_id)
        WHERE post_id >= lower and post_id < upper;
        
        # 更新下限
        SET lower = upper;
    END WHILE;
END;
//
DELIMITER ;

最后,调用一遍这个存储过程

call UpdateCount();

每次会更新1万条数据,也就是更新时暂时锁住这1万条,

一般来说,更新1万条数据执行也是很快的

所以这样就避免了整表所有记录被锁的问题

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

昵称 *