一文搞懂，MySQL查询和事务的关系？！，完整SQL大放送，老铁们，顶起来

确保所有SQL语句在MySQL 5.7.21中都能正确执行，没有语法错误，并且能够展示事务和查询之间的关系。

以下是针对 MySQL 5.7.21 环境的完整建库、建表、插入测试数据及验证事务的 SQL 语句，可直接在 MySQL 中执行测试。

---

1. 创建测试数据库

-- 创建数据库，指定字符集和排序规则
CREATE DATABASE IF NOT EXISTS `transaction_demo` 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_general_ci;

USE `transaction_demo`;

---

2. 创建测试表

-- 账户表（用于转账场景）
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL COMMENT '用户ID',
  `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品库存表（用于库存扣减场景）
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL COMMENT '商品名称',
  `stock` INT NOT NULL DEFAULT 0 COMMENT '库存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表（用于加锁场景）
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'shipped') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

3. 插入测试数据

-- 插入账户数据（用户1初始金额500，用户2初始金额0）
INSERT INTO `accounts` (`user_id`, `balance`) VALUES
(1, 500.00),
(2, 0.00);

-- 插入商品数据（手机库存10）
INSERT INTO `products` (`name`, `stock`) VALUES
('Phone', 10);

-- 插入订单数据（状态为pending）
INSERT INTO `orders` (`user_id`, `amount`, `status`) VALUES
(1, 499.00, 'pending'),
(1, 100.00, 'pending');

---

4. 测试事务与查询（转账场景）

操作步骤：验证原子性和一致性

-- 会话1（事务转账）
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1; -- 查询余额应为500
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

-- 验证结果
SELECT * FROM accounts; -- user1=400, user2=100

---

5. 测试隔离级别（脏读示例）

-- 会话1（未提交的修改）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE name = 'Phone'; -- stock=9（未提交）

-- 会话2（脏读）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT stock FROM products WHERE name = 'Phone'; -- 可能读到9（脏读）

-- 会话1回滚
ROLLBACK;

-- 会话2再次查询（实际库存仍为10）
SELECT stock FROM products WHERE name = 'Phone';

---

6. 测试显式锁（SELECT FOR UPDATE）

-- 会话1（锁住pending订单）
START TRANSACTION;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE; -- 加锁
-- 执行发货操作...

-- 会话2（尝试修改被锁住的订单）
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 1; -- 会被阻塞，直到会话1提交

-- 会话1提交后，会话2才能执行
COMMIT;

---

7. 测试自动提交模式

-- 关闭自动提交
SET autocommit = 0;

-- 执行查询和更新
SELECT * FROM accounts WHERE user_id = 1; 
UPDATE accounts SET balance = balance - 50 WHERE user_id = 1;

-- 手动提交
COMMIT;

-- 重新开启自动提交
SET autocommit = 1;

---

验证结果

执行以上语句后，可以通过以下命令验证数据状态：

-- 查看账户余额
SELECT * FROM accounts;

-- 查看商品库存
SELECT * FROM products;

-- 查看订单状态
SELECT * FROM orders;

---

完整测试脚本

将以下内容保存为 .sql 文件，在 MySQL 5.7.21 中执行：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `transaction_demo` 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_general_ci;

USE `transaction_demo`;

-- 创建表
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `stock` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'shipped') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO `accounts` (`user_id`, `balance`) VALUES
(1, 500.00),
(2, 0.00);

INSERT INTO `products` (`name`, `stock`) VALUES
('Phone', 10);

INSERT INTO `orders` (`user_id`, `amount`, `status`) VALUES
(1, 499.00, 'pending'),
(1, 100.00, 'pending');

---

总结

通过以上代码和测试步骤，可以验证：

1. 事务的原子性：转账操作要么全部成功，要么全部失败。
2. 隔离级别的影响：不同隔离级别下查询结果的可见性差异。
3. 显式锁控制：通过 FOR UPDATE 避免并发冲突。
4. 自动提交行为：手动控制事务提交的边界。

建议在 MySQL 命令行或客户端（如 MySQL Workbench）中逐步执行这些语句，观察结果是否符合预期！

完整操作过程如下

[root@hanywdb01 ~]# vim /opt/hanyw_demo01.sql
[root@hanywdb01 ~]# sh /data/sh/3316_conn.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql3316>source /opt/hanyw_demo01.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql3316>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hanyw01            |
| mysql              |
| performance_schema |
| sys                |
| transaction_demo   |
+--------------------+
6 rows in set (0.00 sec)

mysql3316>se transaction_demo;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'se transaction_demo' at line 1
mysql3316>
mysql3316>use transaction_demo;
Database changed
mysql3316>show tables;
+----------------------------+
| Tables_in_transaction_demo |
+----------------------------+
| accounts                   |
| orders                     |
| products                   |
+----------------------------+
3 rows in set (0.00 sec)

mysql3316>^DBye
[root@hanywdb01 ~]# cat /opt/hanyw_demo01.sql
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `transaction_demo` 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_general_ci;

USE `transaction_demo`;

-- 创建表
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `stock` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'shipped') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO `accounts` (`user_id`, `balance`) VALUES
(1, 500.00),
(2, 0.00);

INSERT INTO `products` (`name`, `stock`) VALUES
('Phone', 10);

INSERT INTO `orders` (`user_id`, `amount`, `status`) VALUES
(1, 499.00, 'pending'),
(1, 100.00, 'pending');