第九章 事务与高级操作 v1.0

一、事务的使用

START TRANSACTION;  # 开始事务，也可以使用 BEGIN
    <操作语句>
COMMIT;             # 提交事务

BEGIN;              # 开始事务，也可以使用 START TRANSACTION
    <操作语句>
ROLLBACK;           # 回滚事务

事务使用时需要注意：

1. 事务尽可能简短，避免长时间占用资源。
2. 事务中访问的数据量尽量少。
3. 查询数据时尽量不使用事务，避免占用过量资源。
4. 事务处理过程中尽量不要等待用户输入，避免造成系统阻塞。

二、事务的隔离级别

MySQL 中的事务隔离级别一共有四种：

• 序列化（SERIALIZABLE）：提供最大限度的隔离，事务按顺序执行，可以防止脏读、不可重复读和幻读。
• 可重复读（REPEATABLE READ）：MySQL 默认隔离级别，可以阻止脏读和不可重复读，但仍需结合具体实现理解幻读问题。
• 读已提交（READ COMMITTED）：可以看到其他事务已经提交的数据，可以阻止脏读，但不能避免幻读或不可重复读。
• 未提交读（READ UNCOMMITTED）：可以读到其他事务尚未提交的数据，可能导致脏读、幻读或不可重复读。

三、数据库备份与恢复

1.备份类型

按照备份时数据库是否运行，可以分为热备、冷备和温备：

• 热备：数据库运行时直接备份，对读写操作影响较小。热备又可分为逻辑备份和裸文件备份。
• 冷备：数据库停止后进行备份，通常直接复制数据库物理文件。
• 温备：数据库运行时进行备份，但备份期间通常只支持读操作。

按照备份数据库的内容，可以分为完全备份和部分备份。部分备份又分为增量备份和差异备份。

2.备份数据库

使用 mysqldump 命令：

# 备份一个数据库，也可以指定数据表
mysqldump -u username -p dbname [tbname ...] > fname.sql
# 备份多个数据库
mysqldump -u username -p --databases dbname1 [dbname2 ...] > fname.sql
# 备份所有数据库
mysqldump -u username -p --all-databases > fname.sql

也可以使用 SELECT ... INTO OUTFILE：

SELECT 列名 FROM table [WHERE 语句]
INTO OUTFILE '目标文件' [OPTIONS];

常用 OPTIONS 参数：

FIELDS TERMINATED BY '字符串'  # 设置字段之间的分隔符，默认为 '\t'
FIELDS ESCAPED BY '字符'      # 设置转义字符，默认为 '\\'
LINES STARTING BY '字符串'    # 设置每行开头字符
LINES TERMINATED BY '字符串'  # 设置结尾字符，默认为 '\n'

3.恢复数据库

# 使用 mysql 命令恢复数据库
mysql -u username -p [dbname] < fname.sql

LOAD DATA INFILE '目标文件' INTO TABLE 表名;

四、日志管理

1.错误日志（Error Log）

记录MySQL启动、运行、关闭过程中出现的问题，默认开启。

-- 查看错误日志路径
SHOW VARIABLES LIKE 'log_error';

2.二进制日志（Binlog）

记录所有修改数据库数据的SQL语句，用于数据恢复和主从复制。

-- 查看是否开启binlog
SHOW VARIABLES LIKE 'log_bin';

-- 查看所有binlog文件
SHOW BINARY LOGS;

-- 查看binlog内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 使用mysqlbinlog工具查看
-- mysqlbinlog --no-defaults mysql-bin.000001

-- 删除binlog（保留最近7天）
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

3.通用查询日志（General Log）

记录所有SQL语句，包括查询和修改，用于调试，生产环境建议关闭。

-- 查看是否开启
SHOW VARIABLES LIKE 'general_log';

-- 开启/关闭
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';

4.慢查询日志（Slow Query Log）

记录执行时间超过阈值的SQL语句，默认关闭，建议生产环境开启用于性能优化。

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 2;

-- 查看慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

五、存储过程与函数

1.存储过程

存储过程是一组预编译的SQL语句集合，通过一次调用执行多条SQL。

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM user;
END //
DELIMITER ;

-- 调用存储过程
CALL GetAllUsers();

-- 带参数的存储过程
DELIMITER //
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
    SELECT * FROM user WHERE id = userId;
END //
DELIMITER ;

-- 调用
CALL GetUserById(1);

-- 带输出参数
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM user;
END //
DELIMITER ;

-- 调用
CALL GetUserCount(@total);
SELECT @total;

-- 删除存储过程
DROP PROCEDURE IF EXISTS GetAllUsers;

2.存储函数

-- 创建函数
DELIMITER //
CREATE FUNCTION GetUserName(userId INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE userName VARCHAR(50);
    SELECT name INTO userName FROM user WHERE id = userId;
    RETURN userName;
END //
DELIMITER ;

-- 使用函数
SELECT GetUserName(1);

-- 删除函数
DROP FUNCTION IF EXISTS GetUserName;