MySQL数据库——表的CURD（Update）

3.Update

语法：update table_name set column = expr

案例

将孙悟空的数学成绩变更为80

mysql> select name,math from result;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 孙悟空    |   78 |
| 猪悟能    |   98 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 孙权      |   73 |
| 宋公明    |   65 |
+-----------+------+
7 rows in set (0.00 sec)

mysql> update result set math=80 where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name,math from result;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 孙悟空    |   80 |
| 猪悟能    |   98 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 孙权      |   73 |
| 宋公明    |   65 |
+-----------+------+
7 rows in set (0.00 sec)

将曹孟德的数学变更为60，语文变更为70 

mysql> update result set math=60,chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select* from result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   80 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   60 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

把总成绩倒数前三的3为同学数学加上30分 

-- 查询出总成绩倒数前三的同学
mysql> select* from result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   80 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   60 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

mysql> select name,chinese+math+english as total from result;
+-----------+-------+
| name      | total |
+-----------+-------+
| 唐三藏    |   221 |
| 孙悟空    |   244 |
| 猪悟能    |   276 |
| 曹孟德    |   197 |
| 刘玄德    |   185 |
| 孙权      |   221 |
| 宋公明    |   170 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> select name,chinese+math+english as total from result order by total limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
+-----------+-------+
3 rows in set (0.00 sec)

-- 更新数据
mysql> update result set math=math+30 order by chinese+math+english asc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- 后三名发生了变化
mysql> select name,chinese+math+english as total from result order by total limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   200 |
| 刘玄德    |   215 |
| 唐三藏    |   221 |
+-----------+-------+
3 rows in set (0.00 sec)

-- 现在的表
mysql> select name,math+english+chinese as total from result order by math+english+chinese;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   200 |
| 刘玄德    |   215 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 曹孟德    |   227 |
| 孙悟空    |   244 |
| 猪悟能    |   276 |
+-----------+-------+
7 rows in set (0.00 sec)

将所有同学的语文成绩更新为原来的2倍 

-- 整表更新的的语句慎用！
mysql> select * from result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   80 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   90 |      67 |
|  5 | 刘玄德    |      55 |  115 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

mysql> update result set chinese=chinese*2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   80 |      77 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

对于更新操作，有时候可能会发生一些错误比如只需要进行单个数据的更新但是我们进行了整表更新，其他的数据我们也不会一时间想起来，这种操作要慎用。对应的我们也可以使用一些方法去挽回这样的错误，这时候就提到了一个概念：事务。

事务的概念类似于快照，就是将此刻数据库的状态保存下来，如果出错了可以进行回滚，但是两者也有很多区别。

下面是我针对事务提出的一些疑问以及GPT给我的回答，选取了部分直观的回答：

 1. 在mysql中如果做了update，不小心进行了整表更新，可以像版本控制器一样进行回滚吗？

在MySQL中，如果你不小心执行了一个更新操作，并且影响了整张表，你可以尝试以下几种方法来“回滚”或撤销这个操作：

1. 使用事务：如果你的更新操作是在事务中进行的，你可以通过执行ROLLBACK命令来撤销这个事务，前提是你的表使用了事务安全的存储引擎，如InnoDB。
2. 使用二进制日志：如果MySQL的二进制日志（binlog）是开启的，你可以从日志中找到执行的SQL语句，并根据这个语句来手动撤销更改。但是，这通常需要你对SQL语句有深入的了解，并且能够手动构造一个相反的更新语句。
3. 使用备份：如果你有定期备份表的习惯，你可以从备份中恢复到更新之前的状态。
4. 使用撤销日志：某些存储引擎，如InnoDB，会维护撤销日志来支持多版本并发控制（MVCC）。在某些情况下，如果更新操作没有被其他事务看到，可能可以利用撤销日志来恢复数据，但这通常不是直接的方法。
5. 手动构造相反的更新语句：如果你知道更新操作的具体内容，你可以手动构造一个相反的更新语句来撤销更改。例如，如果更新操作是将某个字段的所有值增加了1，你可以构造一个将该字段的所有值减去1的更新语句。
6. 使用时间点恢复：如果你的MySQL支持时间点恢复（PITR），你可以恢复到更新操作之前的某个时间点。

2. 什么是事务，你举个例子演示一下从原数据然后整表更新，然后回滚的过程?

事务是数据库管理系统中的一个基本概念，表示一系列操作，这些操作作为一个整体被执行，具有以下四个重要的属性，通常被称为ACID属性：

• 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个环节。
• 一致性（Consistency）：事务必须保证数据库从一个一致的状态转移到另一个一致的状态。
• 隔离性（Isolation）：并发执行的事务之间不会互相影响。
• 持久性（Durability）：一旦事务提交，它对数据库的改变就是永久性的

即使系统发生故障也不会丢失。以下是一个事务的例子，演示了从原始数据开始，进行整表更新，然后执行回滚的过程：

①原始数据状态：
假设我们有一个名为accounts的表，里面有两行数据，表示两个账户的余额。

+---------+-------+
| account| amount|
+---------+-------+
|   1     |  100  |
|   2     |  200  |
+---------+-------+

②开始事务并执行更新操作：
开始一个事务，然后对accounts表执行一个更新操作，将每个账户的余额增加50。

START TRANSACTION;
UPDATE accounts SET amount = amount + 50;

③检查更新后的数据：
在提交事务前，我们先检查更新后的数据状态。

SELECT * FROM accounts;

④此时，查询结果应该显示每个账户的余额都增加了50。

+---------+-------+
| account| amount|
+---------+-------+
|   1     |  150  |
|   2     |  250  |
+---------+-------+

⑤执行回滚操作：
在实际应用中，可能由于某些原因（比如业务规则验证失败），我们决定不提交这个事务，而是要撤销所有更改。这时，我们使用ROLLBACK命令来回滚事务。

ROLLBACK;

⑥验证数据状态：
回滚事务后，我们可以再次查询accounts表来验证数据是否回到了原始状态。

SELECT * FROM accounts;

⑦查询结果应该和事务开始前一样，显示每个账户的原始余额。

+---------+-------+
| account| amount|
+---------+-------+
|   1     |  100  |
|   2     |  200  |
+---------+-------+

这个过程演示了如何使用事务来确保数据库操作的原子性，即使在操作过程中遇到问题，也可以安全地撤销所有更改，保证数据的一致性。

3. 这个操作相当于打快照对吗?

是的，这个操作相当于在事务开始时对数据库状态打了一个“快照”。在事务的上下文中，这个快照的概念与多版本并发控制（MVCC）中的快照读（Snapshot Read）有关，尤其是在支持MVCC的存储引擎如InnoDB中。

在MVCC中，当一个事务开始时，它会“看到”一个数据的一致性快照，这个快照是事务开始时数据库状态的版本。即使其他事务在并发修改数据，当前事务也可以通过这个快照来保证读取操作的一致性。这就是为什么在可重复读（REPEATABLE READ）隔离级别下，你可以在同一个事务中多次读取同一数据，得到的结果都是一致的。

在上述事务的例子中，虽然我们没有进行标准的快照读操作（比如没有使用SELECT ... FOR UPDATE或SELECT ... IN SHARE MODE），但事务本身提供了一种机制，允许你在事务的开始和结束之间，对数据库的修改进行快照式的管理：

• 开始事务（START TRANSACTION）：相当于创建了一个数据库状态的快照，事务中的操作都是基于这个快照进行的。
• 执行更新操作：对数据库状态进行修改，但这些修改在事务提交之前对其他事务是不可见的。
• 回滚事务（ROLLBACK）：撤销所有更改，数据库状态回到事务开始时的快照。

这种机制确保了事务的原子性和一致性，允许你在事务提交之前安全地测试和验证数据库状态，如果发现问题，可以回滚到原始状态，就像时间倒流一样。

4. 那这个时候存储的是什么能够使数据回滚，存储的是事务开启的时候的数据表还是在事务开始之前的所有表的操作又或者是其他?

在事务开始时，存储的并不是完整的数据表副本，而是足够的信息来允许在事务失败或执行ROLLBACK时能够恢复原始数据。这些信息包括：

1. Undo日志（回滚日志）
2. Read View（读视图）
3. 版本链（Version Chain）
4. 系统表空间和段
5. Change Buffer（变更缓冲）
6. Redo日志（重做日志）
7. Savepoints（保存点）

5. 所以我能理解为，回滚的时候其实是把这个表删除，然后重新走了一遍事务开启之前的所有表的操作的语句吗？

不完全是这样。在数据库中，回滚操作并不是通过删除表然后重新执行事务开启之前的所有操作来实现的。实际上，回滚是通过以下方式来恢复数据到事务开始之前的状态：

1. 使用Undo日志：当事务中的某个操作修改了数据时，数据库会记录一条Undo日志，这条日志包含了将数据恢复到修改之前状态所需的所有信息。如果需要回滚，数据库会利用这些Undo日志来逆向执行操作，从而恢复数据的原始状态。
2. 版本链（Version Chain）：在支持MVCC的存储引擎中，每次数据行更新时，都会创建一个新的数据版本并链接到前一个版本，形成版本链。回滚操作可以通过版本链找到并恢复到事务开始前的数据版本。
3. Read View：在MVCC中，Read View是一个事务开始时数据库快照的引用，它确保了事务可以看到一致性的数据视图。当事务需要回滚时，数据库可以使用Read View来确定哪些数据版本对当前事务可见，从而恢复到一致的状态。
4. Change Buffer：对于二级索引的变更，在InnoDB存储引擎中，变更可能会首先记录在Change Buffer中，而不是直接更新到索引页。如果事务回滚，这些未应用的变更可以简单地从Change Buffer中丢弃。
5. Redo日志（重做日志）：虽然Redo日志主要用于保证事务的持久性，确保在发生故障后可以重做事务操作，但它也间接支持回滚操作，因为如果需要，可以使用Redo日志来重建页的变更。
6. 原子性：事务的原子性保证了事务中的所有操作要么全部成功，要么全部失败。在回滚时，事务中的所有操作都会被撤销，但这不是通过重新执行事务开始前的操作来实现的，而是通过逆向操作或恢复到操作前的状态来实现的。

因此，回滚操作是通过撤销事务中所做的修改，而不是通过重新创建表或重新执行旧的操作来完成的。这种方式更为高效，因为它避免了重复大量的I/O操作，并且能够快速地将数据恢复到一致的状态。

6. 如果我撤销事务所做的修改后又想恢复了还可以吗？

在大多数情况下，一旦事务被回滚，它所做的所有修改都会被撤销，数据库状态会回到事务开始之前的样子。默认情况下，这些撤销的操作是不可逆的，数据库不会存储足够的信息来重新应用那些被回滚的更改。

然而，有一些特殊情况和方法可能允许你在回滚之后重新应用更改：

1. 备份和恢复：如果你在事务之前或事务过程中有数据库的备份，你可以使用这个备份来恢复到事务发生之前的状态，然后再尝试重新执行事务中的操作。
2. 二进制日志（Binary Log）：如果数据库配置了二进制日志记录，那么即使事务被回滚，二进制日志中仍然可能记录了事务的操作。在某些情况下，如果操作是可逆的，你可以从二进制日志中提取相应的SQL语句并重新执行它们。
3. Redo日志（重做日志）：对于InnoDB存储引擎，如果事务已经提交，即使后续被其他事务所覆盖，也可以通过Redo日志来恢复数据。但是，如果事务被回滚，通常意味着事务的更改没有被提交到Redo日志中，因此无法使用Redo日志来恢复。
4. Change Buffer和Undo日志：在事务回滚时，Change Buffer和Undo日志用于撤销事务中的更改。一旦事务被成功回滚，这些日志通常会被清理，因此通常不能用来重新应用更改。
5. 数据库快照：某些数据库系统支持快照技术，允许你在特定时间点创建数据库状态的快照。如果在使用快照之前执行了事务回滚，你可以恢复到快照状态，然后再次尝试事务。
6. 应用程序逻辑：如果事务的操作是由应用程序逻辑控制的，应用程序可以捕获回滚事件，并根据需要重新执行事务中的操作。

7. 所以这种机制是只有start事务了才能保存，如果没有开启事务，还是不能直接撤销我的部分操作对吗？

是的，您理解得正确。数据库中的事务撤销（回滚）机制依赖于事务的开始。数据库的事务回滚机制只能撤销在同一个事务中已经做过的操作。如果操作是在事务之外执行的，它们不能被直接回滚，除非采取其他措施，如使用备份或应用程序逻辑来逆转这些更改。

8. 假如我在一张表的构建过程中开启了两次事务会发生什么，还会记录第一次的状态吗，又或者不能够开启两次还是会发生冲突之类的

在数据库操作中，如果尝试在同一个数据库连接上开启两次事务，通常会遇到一些问题。数据库连接java.sql.Connection对象本身的特性要求事务和事务之间的执行是线性的，即一个新事务的开启必须在上一个事务完成之后（如果存在的话）。这意味着，如果在第一个事务还未提交或回滚的情况下尝试开启第二个事务，这可能会导致异常或者错误。

例如，在MySQL中，如果在第一个事务未提交的情况下尝试再次执行START TRANSACTION，MySQL会抛出一个异常，因为它不允许在同一连接上嵌套开始事务。这是因为数据库期望每个事务明确地提交或回滚，以保持数据的一致性和完整性20。

如果确实需要执行多个需要事务性的操作，应该在一个事务中完成所有这些操作，或者在第一个事务提交或回滚之后，再开始新的事务。这样可以确保数据库的操作符合ACID原则，即原子性、一致性、隔离性和持久性24。

此外，值得注意的是，即使在自动提交模式下（即每个单独的SQL操作默认作为一个事务执行），数据库系统通常也会期望操作之间有一定的隔离，以避免潜在的数据不一致问题。因此，合理的做法是确保每个事务性操作都在明确开启和结束的事务块中执行，以避免冲突和数据不一致。

9. 怎么结束或者提交事务

在数据库中，结束或提交事务通常涉及以下两个操作之一：

1. 提交事务（Commit）：

• 提交事务是一个操作，它告诉数据库管理系统（DBMS）将事务中的所有更改永久保存到数据库中。
• 一旦事务被提交，更改将变得不可逆，即使系统发生故障，更改也会保留。
• 在SQL中，通常使用以下命令来提交事务：

COMMIT;

1. 回滚事务（Rollback）：

• 如果事务中的某个操作失败，或者出于任何原因需要撤销事务中的所有更改，可以使用回滚操作。
• 回滚事务会撤销事务中进行的所有更改，将数据库状态恢复到事务开始之前。
• 在SQL中，通常使用以下命令来回滚事务：

ROLLBACK;

在某些数据库系统中，当一个事务被显式开启后，如果没有任何提交或回滚操作，并且在完成所有操作后连接被关闭，数据库可能会自动回滚该事务，以确保数据的一致性。但这种行为依赖于具体的数据库配置和设置，因此显式提交或回滚事务是最佳实践。

此外，如果你使用的是自动提交模式（每个单独的SQL操作默认作为一个事务执行），则不需要显式地提交事务，因为每个操作在执行后立即自动提交。但是，如果你需要执行一系列操作作为一个单元，你应该手动开始一个事务，然后在所有操作完成后提交或回滚事务。

例如，在自动提交模式下，每个单独的更新操作如下：

UPDATE table_name SET column_name = new_value WHERE condition;

而在手动事务管理的情况下，你可能会有如下操作序列：

START TRANSACTION;  -- 或 BEGIN;

-- 执行一系列需要原子性的更新操作
UPDATE table_name SET column_name = new_value WHERE condition;
-- ... 可能还有其他更新操作 ...

-- 如果一切顺利，提交事务
COMMIT;

-- 如果出现错误，回滚事务
ROLLBACK;

使用事务时，务必确保在操作序列的末尾正确地提交或回滚事务，以保持数据的完整性。

10. 像mysql这样进行日志的保存，也就是将所有的操作进行存档目的是为了什么，为什么不直接备份表，这样的存储方式不会占用比表本身更多的内存消耗吗？

MySQL中进行日志保存的主要目的是为了确保事务的持久性、原子性、一致性和隔离性，以及提供数据恢复和主从复制的能力。日志记录了所有的数据变更操作，使得在系统崩溃或发生故障时可以从日志中恢复数据，保证数据不会丢失。此外，日志在主从复制中也扮演着重要的角色，通过日志复制来保持数据的一致性 33。

与直接备份表相比，日志存储方式并不会占用比表本身更多的内存消耗。这是因为日志记录的是数据变更的部分，而不是整个表的副本。例如，如果一个表中只有一行数据被更新，日志只需要记录这一行数据的变化，而不是整个表的数据。此外，日志文件通常是循环使用的，写满后可以从开始位置覆盖旧的日志记录，这样就不会无限制地增长 33。

日志系统主要包括Redo Log（重做日志）、Undo Log（回滚日志）和Bin Log（备份日志）。Redo Log用于保证事务的持久性，记录了数据页的物理修改，通过WAL（Write-Ahead Logging）机制先写日志再写磁盘，以提高性能。Undo Log用于事务的原子性和MVCC（多版本并发控制），记录了逻辑日志，可以通过undo信息恢复到修改前的状态。Bin Log记录了所有的修改操作的原始SQL语句，用于数据备份和主从复制。