mysql 关联表更新 join-update

背景

最近做 instagram 爬虫，涉及到关联表的更新，记录下来

instagram 的帖子，可以是单个图片或视频，也可以包含多个图片/视频，我把帖子抽象为 album，帖子里的图片或视频抽象为 media，即一个 album 包含 n 个 media

要爬取 instagram 里收藏的图片或视频，第一步是访问收藏夹，返回一个 album 列表，然后遍历该 album 列表，获得每个 album 里的 media 信息

在获得 album 列表后，我会将其保存到 instagram_album 表，获得每个 album 里的 media 后，保存到 instagram_media 表

如果一个 album 的 media 信息已经保存到数据库，我会更新 instagram_album 表的状态字段 media_status=Y，表示其 media 已经获取，下次爬取时会略过这些 album

后续我优化了 instagram_album 表，用 media_count 记录该 album 包含几个 media，初始时 media_count=0，获取到 media 信息并保存后，更新该字段，这样只要 media_count>0，其含义和 media_status=Y 就是一样的，media_status 字段就没有必要了

此时，我已经爬取了大量 album 的 media 信息，新增加的 media_count 字段初始值是 0，所以我需要根据 instagram_media 的数据来更新 media_count 字段

关联更新

先统计下 instagram_media 里各 album 含有多少 media，如下

mysql> SELECT album_shortcode, COUNT(1) FROM instagram_media
    ->  GROUP BY album_shortcode limit 20;
+----------------+----------+
| album_shortcode | COUNT(1) |
+----------------+----------+
| _MbLf8iL7k     |        1 |
| B_0CFQrDnRD    |        1 |
| B_0CwGjIxhq    |        1 |
| B_0DvOeJa0c    |        1 |
| B_0K6MOnEvp    |        1 |
| B_0KBcWhm76    |        1 |
| B_0PV7Rn6XG    |        1 |
| B_0SqJiJyU-    |        1 |
| B_2f9E9hN5H    |        1 |
| B_2o8wwIqa-    |        1 |
| B_2qLAFn9T_    |        1 |
| B_2zqPeno2t    |        2 |
| B_5XcuEoIUx    |        1 |
| B_7-qYtIcj5    |        1 |
| B_7rwQCFwos    |        1 |
| B_A-Vz1H9RV    |        1 |
| B_AfdkgHx4h    |        1 |
| B_AgITYpUbn    |        1 |
| B_aHSkHj5Al    |        1 |
| B_aK31DnslP    |        1 |
+----------------+----------+
20 rows in set (0.00 sec)

只要把 COUNT(1) 字段更新到 instagram_album 即可，如下

mysql> SELECT shortcode, media_count
    ->  FROM instagram_album LIMIT 20;
+-------------+-------------+
| shortcode   | media_count |
+-------------+-------------+
| CN5n7Qtjs_k |          10 |
| CN5XuBJpDEr |           1 |
| CN5RQa3nDKB |           3 |
| CN4sKYeDB_d |           5 |
| CN3BkGmsiS4 |           1 |
| CN2soSsLRGq |           1 |
| CN5AfW4FJbR |           1 |
| CN2tVtgDCr7 |           1 |
| CN0MXblBmSH |           1 |
| CN2z7MgHvYk |           1 |
| CN4veaepbIM |           1 |
| CN5IAN4Anz1 |           1 |
| CN0dPRjhvKb |          10 |
| CNvBtQOBk2- |           1 |
| CN0wL8FB_lc |           2 |
| CN0AURjpo5b |           2 |
| CN0TsrZH3Dx |           1 |
| CN0NbOMJZpF |           1 |
| CN0PmfvLqBT |           1 |
| CNz-dYGLYWE |           1 |
+-------------+-------------+
20 rows in set (0.00 sec)

关联更新

UPDATE instagram_album INNER JOIN 
(SELECT album_shortcode, COUNT(1) AS c FROM instagram_media GROUP BY album_shortcode) v 
ON instagram_album.shortcode=v.album_shortcode 
SET instagram_album.media_count=v.c 
WHERE instagram_album.shortcode=v.album_shortcode