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有更有效的办法，还望不吝告知。

巴特西

mysql 多字段更新

最新文章

热门文章