MySQL基础
创建示例数据表
我们以一张表来进行操作。我们以一个简单的 products (商品) 表为例。
-- 创建一个名为 "products" 的表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL
);
-- 插入三条示例数据
INSERT INTO products (name, price, stock) VALUES
('笔记本电脑', 6999.00, 50),
('智能手机', 3999.50, 200),
('无线耳机', 799.00, 500);基础增删改查
增 - INSERT
INSERT 语句用于向表中添加新的记录。
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);添加一个新商品“机械键盘”。
INSERT INTO products (name, price, stock)
VALUES ('机械键盘', 899.00, 150);执行后，products 表中会增加一条新的记录。
查 - SELECT
SELECT 语句用于从表中查询数据。这是最常用、最强大的 SQL 命令。
SELECT column1, column2, ...
FROM table_name
WHERE [condition];示例 1：查询所有商品的所有信息 使用 * 代表所有列。
SELECT * FROM products;示例 2：只查询商品的名称和价格
SELECT name, price FROM products;示例 3：查询价格低于 1000 元的商品 使用 WHERE 子句来添加过滤条件。
SELECT * FROM products WHERE price < 1000.00;改 - UPDATE
UPDATE 语句用于修改表中的现有记录。
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE [condition];将“笔记本电脑”的价格修改为 6888.00 元。
UPDATE products
SET price = 6888.00
WHERE name = '笔记本电脑';删 - DELETE
DELETE 语句用于删除表中的记录。
DELETE FROM table_name WHERE [condition];删除 ID 为 3 的商品（即“无线耳机”）。
DELETE FROM products WHERE id = 3;事务
事务简介 (Transaction)
在讨论锁之前，必须先理解 事务 (Transaction)。事务是数据库管理系统执行过程中的一个逻辑单位，它由一个有限的数据库操作序列构成。简单来说，事务是一组不可分割的 SQL 操作，这些操作要么 全部成功执行，要么 全部失败回滚。这确保了数据库的完整性和一致性。
一个经典的例子就是银行转账：从账户 A 向账户 B 转账 100 元。这个操作包含两个步骤
- 账户 A 的余额减少 100 元。
- 账户 B 的余额增加 100 元。
这两个步骤必须被捆绑在一个事务中。如果第一步成功后，系统崩溃导致第二步失败，那么整个事务将自动 回滚 (Rollback)，账户 A 减少的 100 元将被返还，就好像什么都没发生过一样。只有当两个步骤都成功完成后，事务才能 提交 (Commit)，转账结果才被永久保存。
事务的 ACID 特性
事务具有四个标准的特性，通常被称为 ACID：
- 原子性 (Atomicity): 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。不存在部分执行成功的情况。
- 一致性 (Consistency): 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。例如，在转账前后，银行系统总的资金额应该是不变的。
- 隔离性 (Isolation): 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性 (Durability): 事务成功提交后，它对数据库的修改是永久性的，即使系统发生故障也不会丢失。
事务的控制命令
在 MySQL 的 InnoDB 存储引擎中，事务通过以下命令控制：
START TRANSACTION;或BEGIN;：显式地开启一个新事务。COMMIT;：提交事务，将事务期间所做的所有修改永久保存到数据库。ROLLBACK;：回滚事务，撤销事务期间所做的所有修改，使数据库回到事务开始之前的状态。
默认情况下，MySQL 运行在自动提交模式（autocommit=on）。这意味着你执行的每一条 SQL 语句（如
UPDATE,
INSERT）都会被立即当作一个独立的事务并自动提交。要执行一个多语句事务，你必须使用
START TRANSACTION 来禁用该行为。
一个完整的事务示例： 假设我们要执行上述的转账操作。
-- 开始事务
START TRANSACTION;
-- 1. 检查 Alice 的余额是否充足
-- (此步通常在应用代码中完成)
-- 2. Alice 的账户扣款
UPDATE accounts SET balance = balance - 100 WHERE user = 'Alice';
-- 3. Bob 的账户收款
UPDATE accounts SET balance = balance + 100 WHERE user = 'Bob';
-- 4. 如果以上操作全部成功，则提交事务
COMMIT;
-- 如果任何一步出错（比如 Bob 的账户不存在），则应回滚事务
-- ROLLBACK;锁与事务的关系
锁是在事务的范围内生效的。当一个事务为了保证其隔离性而需要对某些数据加锁时（例如，使用
SELECT ... FOR UPDATE），这些锁会一直被持有，直到事务结束（通过 COMMIT 或
ROLLBACK）。一旦事务结束，它所持有的所有锁都会被立即释放，其他等待的事务才可以继续执行。理解这一点对于分析和解决并发问题至关重要。
锁机制：共享锁与排它锁
为什么需要锁？
在多用户并发访问数据库的环境下，可能会出现多个用户同时修改同一条数据的情况，这会导致数据不一致或其他的并发问题。例如，A 用户和 B 用户同时读取了商品库存为 10，然后都售出 1 件商品，各自将库存更新为 9。最终结果库存是 9，但实际上应该为 8。
锁机制就是为了解决这类并发问题，保证数据在任何时候都只有被授权的事务可以访问和修改。
共享锁 (Shared Lock)
共享锁又称 读锁。它的特点是：
- 一个事务对某行数据加上共享锁后，其他事务 可以读取 这行数据，也可以对这行数据 再加共享锁。
- 但是，任何其他事务都 不能 对这行数据加排它锁（写锁），也不能修改这行数据，直到所有共享锁被释放。
简单来说：大家都可以读，但谁都不能改。
如何加共享锁： 在 SELECT 语句的末尾加上 LOCK IN SHARE MODE。
场景示例： 假设商品“智能手机” (id=2) 的库存需要被多个后台客服同时查看，但不希望在查看期间库存被修改。
| 时间点 | 事务 A (客服 A) | 事务 B (运维 B) | 说明 |
|---|---|---|---|
| 1 | START TRANSACTION; | A 开启事务 | |
| 2 | SELECT * FROM products WHERE id = 2 LOCK IN SHARE MODE; | A 读取 id=2 的数据并加上共享锁，查询成功 | |
| 3 | START TRANSACTION; | B 开启事务 | |
| 4 | SELECT * FROM products WHERE id = 2 LOCK IN SHARE MODE; | B 也可以读取 id=2 的数据并加上共享锁，查询成功 | |
| 5 | UPDATE products SET stock = 190 WHERE id = 2; | B 尝试修改数据，但因为 A 持有共享锁，此操作被阻塞，等待 A 释放锁 | |
| 6 | COMMIT; | A 提交事务，释放共享锁 | |
| 7 | B 的 UPDATE 操作在 A 释放锁后立即执行成功 | ||
| 8 | COMMIT; | B 提交事务 |
排它锁 (Exclusive Lock)
排它锁又称 写锁。它的特点是：
- 一个事务对某行数据加上排它锁后，其他任何事务都 不能 再对这行数据加任何锁（无论是共享锁还是排它锁）。
- 其他事务既 不能读取（加锁读），也 不能修改 这行数据，直到排它锁被释放。
简单来说：我一个人操作，谁也别想动，连看（加锁看）都不行。
如何加排它锁： 在 SELECT 语句的末尾加上 FOR UPDATE。UPDATE 和 DELETE
操作会自动加上排它锁。
场景示例： 用户购买“智能手机” (id=2)，需要读取库存，计算，然后更新库存。这个过程必须是原子的，不允许其他事务干扰。
| 时间点 | 事务 A (用户购买) | 事务 B (其他操作) | 说明 |
|---|---|---|---|
| 1 | START TRANSACTION; | A 开启事务 | |
| 2 | SELECT stock FROM products WHERE id = 2 FOR UPDATE; | A 查询 id=2 的库存并加上排它锁 | |
| 3 | START TRANSACTION; | B 开启事务 | |
| 4 | SELECT * FROM products WHERE id = 2 LOCK IN SHARE MODE; | B 尝试加共享锁读取数据，但因 A 持有排它锁，此操作被阻塞 | |
| 5 | UPDATE products SET stock = 180 WHERE id = 2; | B 尝试修改数据，同样被阻塞 | |
| 6 | UPDATE products SET stock = 199 WHERE id = 2; | A 在自己的事务内成功修改了库存 | |
| 7 | COMMIT; | A 提交事务，释放排它锁 | |
| 8 | A 释放锁后，B 的操作（无论是SELECT...LOCK IN SHARE MODE还是UPDATE）会立即执行 | ||
| 9 | COMMIT; | B 提交事务 |
悲观锁：悲观读与悲观写
什么是悲观锁？
悲观锁是一种并发控制的策略。它总是假设最坏的情况会发生——也就是每次去拿数据的时候，都认为别人会修改它。所以，在整个数据处理过程中，它都会将数据锁定，以防止其他事务的干扰。
在 MySQL 中，我们上面讨论的 共享锁和排它锁 就是悲观锁思想的两种具体实现。
悲观读 (LOCK IN SHARE MODE)
“悲观读”指的是以一种悲观的方式去读取数据。它担心在自己读取数据的过程中，别的事务会把数据改掉。因此，它在读取数据时会加上一个 共享锁。
- 实现方式：
SELECT ... LOCK IN SHARE MODE; - 作用：确保自己读取数据时，别人不能修改它。但允许别人也用同样的方式（加共享锁）来读取。适用于“读后不一定更新，但读取的数据在后续逻辑中很重要，不希望它变化”的场景。
悲观写 (FOR UPDATE)
“悲观写”的态度更加悲观。它不仅担心别人会修改数据，而且断定自己接下来肯定要修改数据，为了防止一切可能的冲突，它会直接上一个最强力的锁。
- 实现方式：
SELECT ... FOR UPDATE; - 作用：在读取数据时直接加上一个 排它锁。这样，其他任何事务都不能对这行数据进行任何操作（包括加锁读和写），直到自己完成操作并释放锁。这通常用于“读取- 修改-写入”的原子操作中，比如经典的扣减库存业务。
上次更新于: 2025-12-25 07:43