MySQL 基础（四）：用户权限、事务 ACID 与隔离级别

1. DCL：用户与权限#

用户由“用户名 + 主机”共同标识，例如 'report_user'@'localhost' 和 'report_user'@'%' 是两个不同账号。

1.1 用户管理#

1
-- 查看用户
2
SELECT User, Host FROM mysql.user;
3

4
-- 创建仅允许本机访问的用户
5
CREATE USER 'report_user'@'localhost'
6
IDENTIFIED BY '请替换为高强度密码';
7

8
-- 修改密码
9
ALTER USER 'report_user'@'localhost'
10
IDENTIFIED BY '请替换为新的高强度密码';
11

12
-- 删除用户
13
DROP USER 'report_user'@'localhost';

IDENTIFIED WITH mysql_native_password 是指定认证插件的兼容性写法。没有旧客户端兼容要求时，通常只写 IDENTIFIED BY，让服务器使用默认认证插件。

1.2 权限控制#

1
-- 查看权限
2
SHOW GRANTS FOR 'report_user'@'localhost';
3

4
-- 仅授予查询权限
5
GRANT SELECT ON mysql_learn.*
6
TO 'report_user'@'localhost';
7

8
-- 授予多项权限
9
GRANT SELECT, INSERT, UPDATE ON mysql_learn.emp
10
TO 'app_user'@'10.%';
11

12
-- 撤销权限
13
REVOKE INSERT, UPDATE ON mysql_learn.emp
14
FROM 'app_user'@'10.%';

安全原则：

遵循最小权限原则，不轻易授予 ALL PRIVILEGES。
生产应用不要使用 root 连接数据库。
'%' 表示任意主机，范围很大，应结合网络、防火墙和 TLS 策略谨慎使用。
密码不要写入代码仓库或命令历史，应使用安全的配置和密钥管理方式。

2. 事务#

2.1 事务的作用#

事务是一组不可分割的操作，要么全部成功，要么全部失败。典型场景是转账：付款方扣款和收款方加款必须作为一个整体。

默认情况下，MySQL 通常开启自动提交，每条独立 DML 语句执行后会自动提交：

1
SELECT @@autocommit;

2.2 基本控制#

推荐显式开启事务：

1
START TRANSACTION;
2

3
UPDATE account
4
SET money = money - 1000
5
WHERE id = 1;
6

7
UPDATE account
8
SET money = money + 1000
9
WHERE id = 2;
10

11
COMMIT;
12
-- 发生异常时执行 ROLLBACK;

也可以修改当前会话的自动提交：

1
SET SESSION autocommit = 0;
2
COMMIT;
3
SET SESSION autocommit = 1;

长期关闭自动提交容易忘记提交或回滚，学习和业务代码中通常更推荐明确的事务边界。

2.3 SAVEPOINT#

保存点可以只回滚事务的一部分：

1
START TRANSACTION;
2

3
UPDATE account SET money = money - 100 WHERE id = 1;
4
SAVEPOINT after_debit;
5

6
UPDATE account SET money = money + 100 WHERE id = 2;
7

8
ROLLBACK TO SAVEPOINT after_debit;
9
RELEASE SAVEPOINT after_debit;
10
COMMIT;

ROLLBACK TO SAVEPOINT 不会结束整个事务；最终仍需 COMMIT 或 ROLLBACK。

2.4 ACID#

特性	含义
Atomicity，原子性	事务中的操作要么全部成功，要么全部失败
Consistency，一致性	事务前后数据必须满足业务规则和约束
Isolation，隔离性	并发事务之间尽量互不干扰
Durability，持久性	事务提交后的结果应被持久保存

回滚是原子性的表现；“持久性”主要指提交后的改变。课程原文中“提交或回滚后的改变永久”表述不够严谨，因为回滚的目标是撤销未提交的改变。

2.5 并发事务问题#

问题	含义
脏读	读到其他事务尚未提交的数据
不可重复读	同一事务两次读取同一行，值发生变化
幻读	同一事务按相同条件查询，返回的行集合发生变化

2.6 隔离级别#

隔离级别	脏读	不可重复读	幻读（SQL 标准定义）
`READ UNCOMMITTED`	可能	可能	可能
`READ COMMITTED`	避免	可能	可能
`REPEATABLE READ`	避免	避免	可能
`SERIALIZABLE`	避免	避免	避免

InnoDB 默认使用 REPEATABLE READ，并通过 MVCC、间隙锁等机制处理许多并发场景；具体表现还取决于查询是否为快照读、当前读以及是否使用索引。

1
-- 查看当前会话隔离级别
2
SELECT @@transaction_isolation;
3

4
-- 设置当前会话隔离级别
5
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔离级别越高，并发能力通常越低。应根据一致性要求和访问模式选择，而不是简单追求最高级别。

2.7 转账示例的进一步完善#

转账还需要考虑余额不足和并发扣款。可在事务中锁定账户行：

1
START TRANSACTION;
2

3
SELECT id, money
4
FROM account
5
WHERE id IN (1, 2)
6
ORDER BY id
7
FOR UPDATE;
8

9
-- 应用层确认付款方余额充足后再执行
10
UPDATE account
11
SET money = money - 1000
12
WHERE id = 1
13
  AND money >= 1000;
14

15
UPDATE account
16
SET money = money + 1000
17
WHERE id = 2;
18

19
COMMIT;

实践要点：

金额字段使用 DECIMAL，不要使用 DOUBLE。
应检查扣款语句影响行数，若为 0 则回滚。
多行加锁时采用一致顺序，可降低死锁概率。
事务尽量短，不要在事务中等待用户输入或执行耗时外部调用。
CREATE、ALTER、DROP、TRUNCATE 等 DDL 通常会触发隐式提交，不要把它们当成普通 DML 回滚。

音乐

I'm Aron

音乐