【数据库】数据库管理（下）存储过程 触发器 慢查询日志 备份与恢复

文章目录

• • 存储过程
  • • 参数
    • 控制语句
  • 触发器
  • 慢日志
  • 备份 & 恢复
  • • 备份方法
    • 恢复方法
    • 导入导出数据

存储过程

数据库存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集合，这些语句被预先编译并保存在数据库中。存储过程可以接受输入参数、执行复杂的业务逻辑，并返回结果或输出参数。它们通常用于封装重复使用的代码块，提高性能，并增强安全性。

存储过程的优点

1. 提高性能：存储过程在创建时会被编译一次，然后存储在数据库中。当调用存储过程时，不需要再次编译，从而减少了编译时间。
2. 减少网络流量：通过将多条SQL语句打包成一个存储过程，可以减少客户端和服务器之间的通信量。
3. 增强安全性和数据完整性：可以通过权限控制来限制用户直接访问表，而是通过存储过程进行操作，这样可以隐藏内部表结构，并实现更细粒度的安全控制。
4. 模块化编程：存储过程支持模块化设计，使得代码更加清晰，易于维护和重用。
5. 事务处理：存储过程可以在一个事务中包含多个数据库操作，保证了操作的一致性。

存储过程的基本元素

• 参数：可以定义输入参数（IN）、输出参数（OUT）和既可输入又可输出的参数（INOUT）。
• 变量：用于存储中间计算结果。
• 控制流语句：如 IF-THEN-ELSE、CASE、LOOP 等，用于实现逻辑判断和循环。

创建和使用语法

DELIMITER //

CREATE PROCEDURE procedure_name ( [IN|OUT|INOUT] param_name type, ...)
BEGIN
  -- 存储过程的逻辑
  -- 可以包含多个SQL语句
  -- 使用DECLARE声明变量
  -- 使用IF, WHILE, LOOP等控制流语句
  -- 使用异常处理
END //

DELIMITER ;

CALL procedure_name(param_value1, param_value2, ...);

示例

向 grade 表中插入多条记录，每条记录的 GradeName 字段值是当前循环计数器 i 和字符串 "abc" 的连接。

CREATE PROCEDURE addGrade(IN count INT(2))
BEGIN
   DECLARE i INT(2) DEFAULT 0;
   WHILE i < count DO
      INSERT INTO grade(GradeName) VALUES(CONCAT(i, 'abc'));
      SET i = i + 1;
   END WHILE;
END;
CALL addGrade(5);

showfun 的主要功能是根据传入的学生姓名从 student 表中查找该学生的信息，并使用查找到的学号从 result 表中获取相关的结果记录。

-- 删除存储过程（如果存在）
DROP PROCEDURE IF EXISTS showfun;

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE showfun(IN str VARCHAR(20))
BEGIN
    -- 定义局部变量
    DECLARE id INT DEFAULT 1;
    DECLARE sname VARCHAR(20);

    -- 查询学生信息并赋值给局部变量
    SELECT StudentNo, StudentName INTO id, sname 
    FROM student WHERE StudentName = str;

    -- 输出学生的姓名
    SELECT sname;

    -- 使用变量 id 进行进一步查询
    SELECT * FROM result WHERE StudentNo = id;
END //
DELIMITER ;

-- 调用存储过程
CALL showfun("李文才");

参数

IN：该值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT：该值可在存储过程内部被改变，并可返回

INOUT：调用时指定，并且可被改变和返回

DROP PROCEDURE IF EXISTS showfun; #删除存储过程
#创建
CREATE PROCEDURE showfun(? str VARCHAR(20))
BEGIN
    select str;
	set str = "张三";
	select str;
end;
set @uname = "李四";
#调用过程
call showfun(@uname);
select @uname;

MySQL中通常以 @ 声明用户变量，其声明和赋值可以使用：SET @variable_name = value; 或 SELECT @variable_name := value;

控制语句

变量作用域：在 MySQL 存储过程中，变量可以定义为局部变量或会话变量。局部变量的作用域仅限于定义它的 BEGIN…END 块内，当执行到 END 时，局部变量将不再可见。可以通过 OUT 参数或将其值指派给会话变量来保存其值。

局部变量

DELIMITER //

CREATE PROCEDURE example_procedure()
BEGIN
    DECLARE local_var INT DEFAULT 0;  -- 局部变量
    SET @session_var = 0;  -- 会话变量

    -- 使用局部变量
    SET local_var = 10;
    SELECT local_var;

    -- 将局部变量的值赋给会话变量
    SET @session_var = local_var;
    SELECT @session_var;
END //

DELIMITER ;

IF-THEN-ELSE 条件语句

DELIMITER //

CREATE PROCEDURE conditional_example(IN var INT)
BEGIN
    IF var = 0 THEN
        SELECT 'var is 0';
    ELSE
        SELECT 'var is not 0';
    END IF;
END //

DELIMITER ;

-- 调用
CALL conditional_example(0);  -- 输出: var is 0
CALL conditional_example(1);  -- 输出: var is not 0

CASE-WHEN-THEN-ELSE 条件语句

DELIMITER //

CREATE PROCEDURE case_example(IN var INT)
BEGIN
    CASE var
        WHEN 0 THEN
            SELECT 'var is 0';
        WHEN 1 THEN
            SELECT 'var is 1';
        ELSE
            SELECT 'var is neither 0 nor 1';
    END CASE;
END //

DELIMITER ;

-- 调用
CALL case_example(0);  -- 输出: var is 0
CALL case_example(1);  -- 输出: var is 1
CALL case_example(2);  -- 输出: var is neither 0 nor 1

-- 获得每门课程的总成绩 要求横向显示，课程名称与总成绩合并为一列
SELECT 
    CASE 
        WHEN s.SubjectName IN (SELECT SubjectName FROM subject) 
				THEN CONCAT(s.SubjectName, '-', SUM(r.StudentResult))
        ELSE NULL
    END AS TotalScore
FROM `result` r
JOIN `subject` s ON r.SubjectNo = s.SubjectNo
GROUP BY  s.SubjectName;

WHILE-DO…END WHILE 循环语句

DELIMITER //

CREATE PROCEDURE loop_example()
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < 6 DO
        SET i = i + 1;
        SELECT i;
    END WHILE;
END //

DELIMITER ;

-- 调用
CALL loop_example();

综合示例

存储过程 updateUserMoney 用于在一个事务中更新两个用户的金额。这个存储过程使用了事务来确保操作的原子性，即要么全部成功，要么全部失败。

-- 删除存储过程（如果存在）
DROP PROCEDURE IF EXISTS updateUserMoney;

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE updateUserMoney(IN m DOUBLE)
BEGIN
    -- 定义变量记录错误信息
    DECLARE t_error INT DEFAULT 0;
    -- 定义发生异常触发 t_error + 1
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
    -- 开启事务
    START TRANSACTION;
    -- 更新张三的金额
    UPDATE usermoney SET money = money - m WHERE uname = '张三';
    -- 更新李四的金额
    UPDATE usermoney SET money = money + m WHERE uname = '李四';
    -- 检查是否有错误
    IF t_error > 0 THEN 
        ROLLBACK;  -- 回滚事务
    ELSE
        COMMIT;  -- 提交事务
    END IF;
END //

DELIMITER ;

-- 调用存储过程
CALL updateUserMoney(100);

触发器

触发器（Trigger）是数据库中的一种特殊类型的存储过程，它会在特定的表上执行某些操作时自动触发。触发器可以用于在插入、更新或删除数据之前或之后执行自定义的逻辑。触发器的主要用途包括：

1. 数据验证：确保数据在插入或更新时满足某些条件。
2. 数据审计：记录对数据的所有更改。
3. 数据同步：在多个表之间保持数据的一致性。

触发器的基本语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
  -- 触发器逻辑
END;

• trigger_name：触发器的名称。
• {BEFORE | AFTER}：指定触发器是在事件发生前还是发生后执行。
• {INSERT | UPDATE | DELETE}：指定触发器响应的事件类型。
• ON table_name：指定触发器关联的表。
• FOR EACH ROW：指定触发器为每一行受影响的数据执行一次。

1. BEFORE INSERT 触发器

假设我们有一个employees表，想要确保每次插入新员工时其工资不能低于最低工资标准。我们可以创建一个BEFORE INSERT触发器来检查这一点。

CREATE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 30000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be at least 30000';
    END IF;
END;

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be at least 30000';

SIGNAL语句用于引发一个错误或警告，并可以自定义错误消息。如果某个条件满足（例如插入的新员工工资低于30000），则会抛出一个带有自定义消息“Salary must be at least 30000”的异常，并且异常的SQL状态码为45000。

OLD和NEW是两个特殊的引用，用于表示触发器事件发生前后的行数据：

• OLD 表示触发器事件发生之前（即更新或删除操作前）的行数据。
• NEW 表示触发器事件发生之后（即插入或更新操作后）的行数据。

2. AFTER UPDATE 触发器

假设有两个表orders和order_history。每当更新订单状态时，我们希望记录这一更改到历史表中。

CREATE TRIGGER log_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
BEGIN
    INSERT INTO order_history(order_id, old_status, new_status, change_date)
    VALUES (OLD.order_id, OLD.status, NEW.status, NOW());
END;

OLD.status IS DISTINCT FROM NEW.status

条件语句，用来检查status字段是否发生了变化。确保只有当status字段实际发生变化时才会执行触发器中的逻辑。

IS DISTINCT FROM 运算符会处理NULL值的情况。如果两边都为NULL，则结果为FALSE；如果只有一边为NULL，则结果为TRUE；如果两边都不是NULL且值不同，则结果也为TRUE。

3. BEFORE DELETE 触发器

考虑一种情况，在删除客户信息之前需要确认该客户的账户余额是否已经清零。

CREATE TRIGGER check_balance_before_delete
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
    IF (SELECT balance FROM accounts WHERE customer_id = OLD.customer_id) > 0 THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Cannot delete customer with non-zero account balance';
    END IF;
END;

这个触发器会在删除操作前检查相关联的账户是否有未结清的余额。

慢日志

MySQL的慢查询日志（Slow Query Log）可以帮助数据库管理员识别执行时间较长的SQL语句。通过分析这些慢查询，可以发现性能瓶颈并采取相应的优化措施。

启用慢查询日志，需要在MySQL配置文件（通常是my.cnf或my.ini）中设置相关参数，或者直接在运行时使用SET命令来修改。

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | OFF                                     |
| slow_query_log_file | D:\develop\...\LAPTOP-3JODIVUS-slow.log |
+---------------------+-----------------------------------------+
2 rows in set, 1 warning (0.02 sec)

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

配置文件设置

[mysqld]
slow_query_log = 1  # 开启慢查询日志
slow_query_log_file = /path/to/your/slow-query.log  # 指定日志文件路径
long_query_time = 2  # 设置阈值，默认单位是秒
log_queries_not_using_indexes = 1  # 记录没有使用索引的查询

• slow_query_log：开启慢查询日志功能。
• slow_query_log_file：指定慢查询日志文件的存储位置。
• long_query_time：定义了查询需要多长时间才被记录到慢查询日志中。默认单位是秒，也可以设置为毫秒。
• log_queries_not_using_indexes：如果设置为1，那么所有不使用索引的查询都将被记录，无论它们是否超过了long_query_time。

运行时设置

在 MySQL 服务器运行时通过 SQL 命令临时更改设置

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

查看和分析慢查询日志

一旦开启了慢查询日志，并且有一些慢查询被记录下来后，可以使用文本编辑器打开日志文件查看内容。通常会使用一些专门的工具来分析这些日志，例如mysqldumpslow、pt-query-digest（Percona Toolkit的一部分）等。

• mysqldumpslow：MySQL 自带的简单工具，可以用来汇总慢查询日志中的数据。
• pt-query-digest：更强大的工具，可以从慢查询日志中提取出详细的统计信息，并生成报告。

备份 & 恢复

数据库备份是任何数据管理系统中至关重要的组成部分，其主要目的是在发生数据丢失、损坏或系统故障时能够恢复数据。MySQL 提供了多种备份方法：mysqldump 备份工具、数据库管理工具，如SQLyog、直接拷贝数据库文件和相关配置文件，可以根据具体需求选择合适的备份策略。

备份方法

mysqldump 是一个命令行工具，可以用来导出数据库为 SQL 脚本文件。这种方法适用于小到中型数据库。

作用：转储数据库；搜集数据库进行备份；将数据转移到另一个SQL服务器(不一定是MySQL服务器)

# 预存文件目录，须有该目录读写权限
mysqldump -h 主机名 –u 用户名 –p [options] 数据库名 [table1 table2 table3] > path/filename.sql

• -h 主机名：指定 MySQL 服务器的主机名或 IP 地址。如果 MySQL 服务器运行在同一台机器上，可以省略此选项。
• -u 用户名：指定连接到 MySQL 服务器的用户名。
• -p：提示输入密码。您可以直接在命令中指定密码（不推荐），例如 -p密码，但出于安全考虑，通常建议使用交互式输入。
• [options]：可选参数，可以根据需要添加其他选项。例如：
  • --routines：导出存储过程和函数。
  • --triggers：导出触发器。
  • --single-transaction：对于 InnoDB 表，在备份过程中使用单个事务，以确保一致性。
• 数据库名：要备份的数据库名称。
• [table1 table2 table3]：可选参数，指定要备份的表。如果不指定表名，则备份整个数据库。
• > path/filename.sql：将导出的 SQL 脚本重定向到指定路径和文件名。path 是文件的保存目录，filename.sql 是备份文件的名称。

# 全库备份
mysqldump -u username -p database_name > backup.sql
# 单表备份
mysqldump -u username -p database_name table_name > backup_table.sql
# 所有数据库备份
mysqldump -u username -p --all-databases > all_databases_backup.sql
# 包含存储过程和触发器
mysqldump -u username -p --routines --triggers database_name > backup_with_routines.sql
# 压缩备份
mysqldump -u username -p database_name | gzip > backup.sql.gz

其他常用选项

备份 MySchool 数据库的 subject 课程表，保存为 subject.sql 脚本文件

要求：在每个 INERT 语句的列上加上字段名

详细步骤

1. 打开终端或命令提示符

2. 运行命令

   # 默认情况下mysqldump 生成的 INSERT 语句包含字段名，为确保可使用 --complete-insert 选项
   mysqldump -u myuser -p --complete-insert MySchool subject > /backup/subject.sql
   

3. 输入密码

4. 确认备份：检查 /backup/ 目录下是否生成了 subject.sql 文件。

恢复方法

方法一：用 SOURCE 语法

• /path/ 是一个绝对路径，并且必须是 mysql 运行用户有权限读取的文件

• SOURCE 命令允许在 MySQL 命令行中直接执行 SQL 脚本文件。这个方法适用于已经连接到 MySQL 服务器的情况。

# 打开 MySQL 命令行
mysql -u 用户名 -p
# 输入密码 选择要导入数据的数据库（如果脚本中没有指定数据库）
USE 数据库名;
# 执行 SOURCE 命令
SOURCE /path/db_name.sql;

方法二：用 mysql 客户端

• 这种方法通过命令行直接将 SQL 脚本文件导入到指定的数据库中，而不需要先手动连接到 MySQL 服务器。

# 运行命令
mysql –u 用户名 –p 数据库名 < /path/db_name.sql
# 输入密码

导入导出数据

使用 SELECT ... INTO OUTFILE 语句可以将查询结果导出到一个文件中。这个文件通常是 CSV 格式，但也可以是其他格式。

# 导出数据
SELECT * INTO OUTFILE 'file_name'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM tbl_name;

• file_name：要输出的文件名，必须是绝对路径，并且文件不能已经存在
• FIELDS TERMINATED BY：指定字段之间的分隔符，默认为逗号 ,
• OPTIONALLY ENCLOSED BY：指定字段是否用引号包围，默认为空，即不包围
• LINES TERMINATED BY：指定行之间的分隔符，默认为换行符 \n

使用 LOAD DATA INFILE 语句可以从一个文件中读取数据并将其插入到表中

# 导入数据
LOAD DATA INFILE 'file_name'
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 如果文件包含标题行

• file_name：要导入的文件名，必须是绝对路径
• FIELDS TERMINATED BY：指定字段之间的分隔符，默认为逗号 ,
• OPTIONALLY ENCLOSED BY：指定字段是否用引号包围，默认为空，即不包围
• LINES TERMINATED BY：指定行之间的分隔符，默认为换行符 \n
• IGNORE 1 ROWS：如果文件的第一行是列标题，可以忽略这一行

输出的文件不能先存在，否则报错