mysql update count_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万条数据执行也是很快的，

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