mysql - 如何使用触发器更新其他表的行？

Question

users:
+----+----------+-------------------------------------------+-------------------+-------+
| id | username | password                                  | email             | score |
+----+----------+-------------------------------------------+-------------------+-------+
|  1 | user_1   | *79457HG5456756799547645767567E0C18660CF8 | user1@example.com |     0 |
+----+----------+-------------------------------------------+-------------------+-------+
|  2 | user_2   | *79457HG5456756799547645767567E0C18660CF8 | user2@example.com |     0 |
+----+----------+-------------------------------------------+-------------------+-------+
|  3 | user_3   | *79457HG5456756799547645767567E0C18660CF8 | user3@example.com |     0 |
+----+----------+-------------------------------------------+-------------------+-------+

score_changes
+---------+-------+-------------+
| user_id | score | reason      |
+---------+-------+-------------+
|       1 |     2 | played well |
+---------+-------+-------------+
|       1 |    -2 | foul        |
+---------+-------+-------------+
|       2 |    -5 | spammed     |
+---------+-------+-------------+
|       3 |   -10 | cheated     |
+---------+-------+-------------+
|       1 |     1 | played well |
+---------+-------+-------------+

在上面的设置中，我想计算 score_changes 中每个用户的分数总和，并将其更新为 users.score。 score_changes 是动态的，每当用户获得分数时就会插入。

1. 在 score_changes 中插入一行时，如何自动更新 users.score ？触发器是最好的选择吗？如何实现？
2. 最好使用 SUM(SELECT Score from Score_changes where user_id = users.id); 重新计算分数，还是直接在 users.score 中添加新的更改？

Answer 1

检查此链接是否使用了内部联接。 http://dev.mysql.com/doc/refman/5.5/en/update.html您必须先加入两个表。您的问题与此相同，请引用:MySql update two tables at once