mysql 多字段更新

更新一个字段当好写

update user set collect_num=(select sum(collect_num) from article where user_id=user.id) where user.id=1;
Query OK, 0 rows affected (17.36 sec)
Rows matched: 1 Changed: 0 Warnings: 0

 

问题是想更新多个字段
sql server 支持下面这种语法

update user set (article_num,collect_num,like_num)=(select count(*),sum(collect_num),sum(like_num) from article where user_id=user.id) where user.id=1;

 

试过并查官网后，发现mysql并不支持

先用最笨的办法

update user set 
article_num=(select count(*) from article where user_id=user.id),
collect_num=(select sum(collect_num) from article where user_id=user.id),
like_num=(select sum(collect_num) from article where user_id=user.id) 
where user.id=1;
Query OK, 1 row affected (54.79 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

时间居然是更新一个字段的的3倍，可能是查了三次article表，验证确实如此

mysql> explain select  (select count(*) from article where user_id=1),(select sum(collect_num) from article where user_id=1),(select sum(collect_num) from article where user_id=1);
+----+-------------+---------+-------+------------------------------+------------------------------+---------+------+---------+--------------------------+
| id | select_type | table   | type  | possible_keys                | key                          | key_len | ref  | rows    | Extra                    |
+----+-------------+---------+-------+------------------------------+------------------------------+---------+------+---------+--------------------------+
|  1 | PRIMARY     | NULL    | NULL  | NULL                         | NULL                         | NULL    | NULL |    NULL | No tables used           |
|  4 | SUBQUERY    | article | index | user_id_like_num_collect_num | user_id_like_num_collect_num | 104     | NULL | 3047331 | Using where; Using index |
|  3 | SUBQUERY    | article | index | user_id_like_num_collect_num | user_id_like_num_collect_num | 104     | NULL | 3047331 | Using where; Using index |
|  2 | SUBQUERY    | article | index | user_id_like_num_collect_num | user_id_like_num_collect_num | 104     | NULL | 3047331 | Using where; Using index |
+----+-------------+---------+-------+------------------------------+------------------------------+---------+------+---------+--------------------------+

 

不能忍

 

能想到的办法就是起临时表（或外部写代码，python什么的，实现类似临时表的功能）了，毕竟临时表万能。

 

但还是想在sql层面解决

 

update user u 
JOIN (select user_id as user_id,count(*) as article_num,sum(collect_num) as collect_num,sum(like_num) as like_num from article where user_id=1) t 
on u.id=t.user_id 
set u.article_num=t.article_num,u.collect_num=t.collect_num,u.like_num=t.like_num 
where u.id=1;

 

虽然代码不如sqlserver 漂亮，需要改两个值，不过达到目的了，时间并不比单字段耗时。

 

更改为单查询条件

update user u 
JOIN (select user_id as user_id,count(*) as article_num,sum(collect_num) as collect_num,sum(like_num) as like_num 
from article 
group by user_id) t
 on u.id=t.user_id  
set u.article_num=t.article_num,u.collect_num=t.collect_num,u.like_num=t.like_num  where u.id=101;

 

总算差不多了

个人最熟悉sqlserver,mysql只是顺带打酱油的，如果mysql有更有效的办法，还望不吝告知。