mysql 多表更新

在数据库操作中，经常会遇到需要同时更新多个关联表的情况，这就需要借助 MySQL 的多表更新功能。本文将详细介绍如何使用 MySQL 进行多表更新操作。

1. 创建示例数据库

首先，我们需要创建一个示例数据库，包含多个关联表。以一个图书馆管理系统为例，我们创建以下几个表：

books：存储图书信息
borrowers：存储借书人信息
borrow_records：存储借书记录信息

可以使用以下 SQL 语句创建这些表：

CREATE DATABASE bookstore;

USE bookstore;

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  stock INT
);

CREATE TABLE borrowers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE borrow_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  book_id INT,
  borrower_id INT,
  borrow_date DATE,
  return_date DATE,
  FOREIGN KEY (book_id) REFERENCES books(id),
  FOREIGN KEY (borrower_id) REFERENCES borrowers(id)
);

INSERT INTO books (title, author, stock) VALUES
  ('Book A', 'Author A', 5),
  ('Book B', 'Author B', 3),
  ('Book C', 'Author C', 1);

INSERT INTO borrowers (name, email) VALUES
  ('User A', 'userA@example.com'),
  ('User B', 'userB@example.com'),
  ('User C', 'userC@example.com');

INSERT INTO borrow_records (book_id, borrower_id, borrow_date, return_date) VALUES
  (1, 1, '2022-01-01', '2022-01-08'),
  (1, 2, '2022-01-02', '2022-01-09'),
  (2, 3, '2022-01-03', '2022-01-10');

以上代码创建了一个名为 bookstore 的数据库，并在其中创建了三个表 books、borrowers 和 borrow_records，并向这些表插入了一些示例数据。

2. 多表更新基本语法

MySQL 中的多表更新语句使用 UPDATE 命令，涉及到多个表时需要使用 JOIN 子句将这些表关联起来。下面是多表更新的基本语法：

UPDATE table1
JOIN table2 ON condition1
JOIN table3 ON condition2
...
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

其中，table1、table2、table3 表示要更新的表，condition1、condition2 表示关联条件，column1、column2 表示要更新的列，value1、value2 表示要更新的值，condition 表示更新条件。

3. 实例：图书归还后库存增加

假设图书馆规定，当借书记录中的 return_date 字段不为空时，表示图书已经归还。当图书归还后，需要将该图书的库存增加。现在我们来通过多表更新实现这个功能。

UPDATE books
JOIN borrow_records ON books.id = borrow_records.book_id
SET books.stock = books.stock + 1
WHERE borrow_records.return_date IS NOT NULL;

以上 SQL 语句中，我们使用了两个表 books 和 borrow_records，通过 JOIN 子句将它们关联起来。然后，我们通过设置 books.stock 列的值为 books.stock + 1，实现库存增加。最后，通过 WHERE 子句限定了只有 borrow_records.return_date 不为空的记录才会被更新。

执行以上 SQL 语句后，可以使用以下查询语句验证库存是否正确增加：

SELECT * FROM books;

输出如下：

+----+--------+---------+-------+
| id | title  | author  | stock |
+----+--------+---------+-------+
|  1 | Book A | Author A |     6 |
|  2 | Book B | Author B |     3 |
|  3 | Book C | Author C |     1 |
+----+--------+---------+-------+

可以看到，Book A 的库存从 5 增加到了 6，符合预期。