php - 如果插入另一个表中的值，如何更新表

Question

mysql> select * from Teams;  
+-----------+---------+  
| team_name | team_id |
+-----------+---------+  
| India     |       1 |  
| England   |       2 |  
| Germany   |       3 |  
| Italy     |       4 |  
| Spain     |       5 |  
| Belgium   |       6 |  
| Brazil    |       7 |  
| France    |       8 |  
+-----------+---------+
8 rows in set (0.00 sec)


 select * from Players where team_id=7 or team_id=8;
+-----------+----------------+------------+------+--------+--------+---------+
| player_id | player_name    | position   | age  | height | weight | team_id |
+-----------+----------------+------------+------+--------+--------+---------+
|       130 | Jefferson      | Goalkeeper |   25 |      6 |     90 |       7 |
|       131 | Dani Alves     | Defender   |   30 |      6 |     65 |       7 |
|       132 | David Luiz     | Defender   |   26 |      6 |     73 |       7 |
|       133 | Thiago Silva   | Defender   |   29 |      6 |     69 |       7 |
|       134 | Marcelo        | Defender   |   24 |      5 |     65 |       7 |
|       135 | Paulinho       | Midfielder |   27 |      6 |     68 |       7 |
|       136 | Ramires        | Midfielder |   25 |      6 |     59 |       7 |
|       137 | Oscar          | Midfielder |   21 |      5 |     61 |       7 |
|       138 | Lucas Moura    | Striker    |   19 |      5 |     62 |       7 |
|       139 | Neymar         | Striker    |   21 |      5 |     63 |       7 |
|       140 | Alex Pato      | Striker    |   25 |      6 |     69 |       7 |
|       150 | Hugo Lloris    | Goalkeeper |   28 |      6 |     75 |       8 |
|       151 | Mathiu Debuchy | Defender   |   25 |      6 |     70 |       8 |
|       152 | Philip Mexes   | Defender   |   34 |      6 |     73 |       8 |
|       153 | Younes Kaboul  | Defender   |   29 |      6 |     81 |       8 |
|       154 | Patrice Evra   | Defender   |   34 |      6 |     75 |       8 |
|       155 | Paul Pogba     | Midfielder |   21 |      6 |     68 |       8 |
|       156 | Samir Nasri    | Midfielder |   27 |      6 |     69 |       8 |
|       157 | Yohan Cabaye   | Midfielder |   26 |      6 |     64 |       8 |
|       158 | Frank Ribery   | Striker    |   28 |      5 |     63 |       8 |
|       159 | Oliver Giroud  | Striker    |   25 |      6 |     74 |       8 |
|       160 | Karim Benzema  | Striker    |   24 |      6 |     71 |       8 |
+-----------+----------------+------------+------+--------+--------+---------+
22 rows in set (0.00 sec)



mysql> select * from Matches where match_id=4;
+----------+------------+----------+----------+-----------+-----------+
| match_id | match_date | hometeam | awayteam | homescore | awayscore |
+----------+------------+----------+----------+-----------+-----------+
|        4 | 2014-06-28 |        7 |        8 |         0 |         0 |
+----------+------------+----------+----------+-----------+-----------+
1 row in set (0.00 sec)

(hometeam,awayteam 是外键 Teams.team_id)

mysql> select * from livescore;
+----------+-----------+----------+---------+
| score_no | player_id | match_id | team_id |
+----------+-----------+----------+---------+
|        1 |       155 |        4 |       8 |
+----------+-----------+----------+---------+
1 row in set (0.00 sec)

(player_id,match_id,team_id 是 Players,Matches,Teams 的外键)我有一个 Players 表，其中包含所有玩家信息。

我想要的是，当在 'livescore' 中插入新行时，homescore,awayscore 应该相应增加。即如果球员在livescore 中属于team_id=8 则awayscore 其中awayteam=8 必须增加+1。

Answer 1

有一个小技巧，您可以使用 ON DUPLICATE KEY 构造在一个查询中完成此操作:

INSERT INTO `table` (`field_a`, `field_b`, `field_c`)
VALUES('val_a', 'val_b', 'val_c')
ON DUPLICATE KEY UPDATE `field_c` = `field_c` + 1;

当使用“ON DUPLICATE KEY”时，重复的键会自动用作 WHERE，所以这很舒服。

请记住，您尝试插入的 rowa 或至少一个键必须是拒绝重复条目的主键或唯一索引，这样该结构才能工作。