MySQL、MariaDB多表联合更新语法详解

简介

• 一个项目中，为了逻辑更清晰，我们通常会在一个数据库中创建多个表来保存数据
• 这些不同的表中往往都是有关联性的，所以当我们对其中一个表的内容进行更改的时候，其他的表对应的内容也需要同步修改

那么下面就来演示一下如何实现这个需求

场景模拟

表名称	作用
type	类型
user	用户信息

当前数据:

MariaDB [test]> select * from user;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
|  1 | liu  | 2021-04-09 13:05:32 |
+----+------+---------------------+
1 row in set (0.000 sec)

MariaDB [test]> select * from type;
+----+------+--------+-----------------+
| id | name | permit | submission_date |
+----+------+--------+-----------------+
|  1 | liu  | 1491   | 2021-03-28      |
+----+------+--------+-----------------+
1 row in set (0.000 sec)

需求:

需要修改user表中某个用户的名字,同步修改type表中该用户的名字

常用语法:

update <数据表1> a,<数据表2> b  set b.<表1字段>=a.<表2字段> where a.id=b.id;

实践操作:

MariaDB [test]> update user set name='liuyi';
Query OK, 1 row affected (0.037 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from user;
+----+-------+---------------------+
| id | name  | create_time         |
+----+-------+---------------------+
|  1 | liuyi | 2021-04-09 13:13:02 |
+----+-------+---------------------+
1 row in set (0.000 sec)

MariaDB [test]> select * from type;
+----+------+--------+-----------------+
| id | name | permit | submission_date |
+----+------+--------+-----------------+
|  1 | liu  | 1491   | 2021-03-28      |
+----+------+--------+-----------------+
1 row in set (0.001 sec)
MariaDB [test]> update user a,type b  set b.name=a.name;
Query OK, 1 row affected (0.037 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from type;
+----+-------+--------+-----------------+
| id | name  | permit | submission_date |
+----+-------+--------+-----------------+
|  1 | liuyi | 1491   | 2021-03-28      |
+----+-------+--------+-----------------+
1 row in set (0.000 sec)

MariaDB [test]>