更新和删除数据

我们已经知道了很多关于插入和检索数据的知识，但如果应用程序只能做到这一点，那么它们将是非常静态的。根据我们的需要编辑这些数据，才能使应用程序充满活力，并为用户带来一些价值。在 MySQL 和大多数数据库系统中，有两种更改数据的命令： UPDATE 和 DELETE。让我们详细讨论一下。

更新数据

在 MySQL 中更新数据时，最重要的是为要更新的记录提供唯一引用。为此，主键非常有用；不过，如果你的表没有主键（大多数情况下都没有主键），你仍然可以根据其他字段更新行。除了引用，你还需要新值，当然还有表名和要更新的字段。让我们来看一个非常简单的示例：

mysql> UPDATE book SET price = 12.75 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
bash

在这个 UPDATE 查询中，我们将 ID 为 2 的图书价格设置为 12.75。SET 子句不需要只指定一项更改；只要用逗号分隔，就可以在同一条记录上指定多项更改—​例如，SET price = 12.75, stock = 14。另外，请注意 WHERE 子句，在该子句中，我们指定了要更改的记录。MySQL 会根据这些条件获取表中的所有记录，就像 SELECT 查询一样，并将更改应用到这组记录中。

MySQL 将返回的内容非常重要：匹配的行数和更改的行数。第一项是与 WHERE 子句中的条件相匹配的记录数。第二个是可以更改的记录数。不更改记录有不同的原因，例如，当记录已经有相同的值时。要了解这一点，让我们再次运行相同的查询：

mysql> UPDATE book SET price = 12.75 WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
bash

现在，同一行显示有 1 行匹配，正如预期的那样，但 0 行被更改。原因是我们已经将这本书的价格设置为 12.75，所以 MySQL 现在不需要对此做任何处理。

如前所述，WHERE 子句是本查询中最重要的部分。很多时候，我们会发现开发人员在运行先验无误的 UPDATE 查询时，由于漏掉了 WHERE 子句，最终会更改整个表；因此，MySQL 会将整个表匹配为要更新的有效行。这通常不是开发人员的本意，也不是什么令人愉快的事情，因此要尽量确保总是提供一组有效的条件。好的做法是先写下返回需要编辑的行的 SELECT 查询，一旦确定条件与所需行集匹配，就可以写 UPDATE 查询了。

但有时，影响多行才是我们想要的情况。设想一下，我们正处于艰难时期，需要提高所有书籍的价格。我们决定将价格提高 16%，相当于当前价格乘以 1.16。我们可以运行以下查询来执行这些更改：

mysql> UPDATE book SET price = price * 1.16;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
bash

该查询不包含任何 WHERE 子句，因为我们希望匹配所有图书。还要注意的是，SET 子句使用价格字段来获取 price 的当前值，这是完全正确的。最后，请注意匹配和更改的行数，即 8 条—​该表的整组行数。

在本小节的最后，让我们考虑一下如何通过 PDO 使用 PHP 中的 UPDATE 查询。一种非常常见的情况是，我们想在库存中添加已有图书的副本。给定一个图书 ID 和一个可选的图书数量—​默认值为 1—​我们将增加这本书的库存价值，增加的数量就是这本书的数量。请在 init.php 文件中编写此函数：

function addBook(int $id, int $amount = 1): void {
    $db = new PDO(
        'mysql:host=127.0.0.1;dbname=bookstore',
        'root',
        ''
    );

    $query = 'UPDATE book SET stock = stock + :n WHERE id = :id';
    $statement = $db->prepare($query);
    $statement->bindValue('id', $id);
    $statement->bindValue('n', $amount);

    if (!$statement->execute()) {
        throw new Exception($statement->errorInfo()[2]);
    }
}
php

有两个参数： $id 和 $amount。第一个参数始终是必选参数，第二个参数可以省略，默认值为 1。函数首先准备一个与本节第一个查询类似的查询，其中我们增加了给定图书的库存量，然后将两个参数绑定到语句中，最后执行查询。如果发生了异常情况，而 execute 返回 false，我们将抛出一个异常，其中包含来自 MySQL 的错误信息的内容。

当我们购买更多库存或客户退回图书时，这个函数非常有用。我们甚至可以通过为 $amount 提供一个负值来删除书籍，但这是非常糟糕的做法。因为即使我们强制库存字段为无符号，将其设置为负值也不会触发任何错误，只会发出警告。MySQL 不会将记录设置为负值，但执行调用会返回 true，而我们对此一无所知。最好是创建第二个方法，即 removeBook，并首先验证要删除的书籍数量小于或等于当前库存。

外键行为

更新或删除记录时需要处理的一个棘手问题是，当我们更新的记录是其他地方外键的一部分时。例如，我们的 borrowed_books 表包含客户和图书的 ID，正如你已经知道的，MySQL 强制规定这些 ID 始终有效，并存在于各自的表中。那么，如果我们更改了图书表中图书本身的 ID，会发生什么情况呢？或者更糟糕的是，如果我们从 book 表中删除了一本书，而 borrowed_books 表中有一条记录引用了这个 ID，会发生什么情况？

MySQL 允许你设置发生这些情况时的预期反应。它必须在添加外键时定义；因此，在我们的例子中，我们需要先删除现有的外键，然后再添加。要删除或丢弃一个键，你需要知道这个键的名称，我们可以使用 SHOW CREATE TABLE 命令找到它，如下所示：

mysql> SHOW CREATE TABLE borrowed_books \G
*************************** 1. row ***************************
Table: borrowed_books
Create Table: CREATE TABLE `borrowed_books` (
`book_id` int(10) unsigned NOT NULL,
`customer_id` int(10) unsigned NOT NULL,
`start` datetime NOT NULL,
`end` datetime DEFAULT NULL,
KEY `book_id` (`book_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `borrowed_books_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES
`book` (`id`),
CONSTRAINT `borrowed_books_ibfk_2` FOREIGN KEY (`customer_id`)
REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
bash

我们要删除的两个外键是 borrowed_books_ibfk_1 和 borrowed_books_ibfk_2。让我们像之前一样，使用 ALTER TABLE 命令删除它们：

mysql> ALTER TABLE borrowed_books
    -> DROP FOREIGN KEY borrowed_books_ibfk_1;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE borrowed_books
    -> DROP FOREIGN KEY borrowed_books_ibfk_2;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
bash

现在，我们需要再次添加外键。命令的格式将与我们添加外键时的格式相同，只是添加了新的所需行为。在我们的例子中，如果我们从表中删除了客户或书籍，我们希望从 borrowed_books 中删除引用这些书籍和客户的记录；因此，我们需要使用 CASCADE 选项。让我们看看它们会是什么样子：

mysql> ALTER TABLE borrowed_books
    -> ADD FOREIGN KEY (book_id) REFERENCES book (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE,
    -> ADD FOREIGN KEY (customer_id) REFERENCES customer (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
bash

请注意，我们可以为更新和删除记录这两种操作定义 CASCADE 行为。除了 CASCADE 之外，还有其他选项—​例如 SET NULL（将外键列设置为 NULL，并允许删除原始记录），或者默认选项 RESTRICT（拒绝更新/删除命令）。

删除数据

删除数据与更新数据几乎是一样的。你需要提供一个 WHERE 子句来匹配要删除的记录。此外，与更新数据一样，强烈建议在执行 DELETE 命令之前，首先创建 SELECT 查询，以检索要删除的记录。不要认为使用这种方法是在浪费时间；俗话说，两次测量，一次切割。删除记录后并不总能恢复数据！

让我们通过观察之前设置的 CASCADE 选项的效果来尝试删除一本书。为此，我们首先通过下面的命令查询现有的借阅图书列表：

mysql> SELECT book_id, customer_id FROM borrowed_books;
+---------+-------------+
| book_id | customer_id |
+---------+-------------+
| 1       | 1           |
| 4       | 1           |
| 4       | 2           |
| 1       | 2           |
+---------+-------------+
4 rows in set (0.00 sec)
bash

有两本不同的书，分别是 1 和 4，每本书都被借阅了两次。让我们尝试删除 ID 为 4 的书。首先，建立一个查询，如 SELECT * FROM book WHERE id = 4，以确保 WHERE 子句中的条件是适当的。确定后，执行以下查询：

mysql> DELETE FROM book WHERE id = 4;
Query OK, 1 row affected (0.02 sec)
bash

正如你所注意到的，我们只指定了 DELETE FROM 命令，然后是表名和 WHERE 子句。MySQL 告诉我们有 1 条记录受到了影响，考虑到我们之前的 SELECT 语句，这是有道理的。

如果我们返回到 borrowed_books 表并查询现有记录，我们会发现所有引用 ID 为 4 的书的记录都不见了。这是因为在从 book 表中删除这些记录时，MySQL 注意到了外键引用，检查了删除时需要执行的操作（在本例中为 CASCADE），然后也删除了 borrowed_books 表中的记录。请看下面的内容：

mysql> SELECT book_id, customer_id FROM borrowed_books;
+---------+-------------+
| book_id | customer_id |
+---------+-------------+
| 1       | 1           |
| 1       | 2           |
+---------+-------------+
2 rows in set (0.00 sec)
bash