数据库语法 2
本节继续学习 MySQL 数据库语法，首先整理存储过程的创建、参数、调用与删除，并对比存储过程和自定义函数的使用方式。
随后介绍触发器的触发时机以及 OLD、NEW 的含义，最后通过转账场景理解事务的作用、ACID 特性和 START TRANSACTION、ROLLBACK、COMMIT 等基本操作。
一、存储过程
1. 什么是存储过程
存储过程是保存在数据库中的一组 SQL 语句。创建完成后，可以通过过程名重复调用。
它与视图有相似之处：都能把较长的数据库操作保存下来，使用时只需要写较短的调用语句。
但二者也有区别：
- 视图主要保存查询逻辑；
- 存储过程可以包含多条 SQL 和流程控制语句；
- 视图不会提高原查询的执行效率；
- 存储过程会预先编译并保存在数据库中，通常将其视为可以提高运行效率的一项优点。
2. 存储过程的优点
- 节省流量、相对安全
- 客户端不必每次传输一大段 SQL，只需传递过程名和参数；
- 网络上传输的内容更少，暴露的具体 SQL 细节也更少。
- 提高运行效率
- 存储过程编译后保存在数据库中，调用时直接执行。
- 灵活、提高代码复用性
- 多处都要完成同一种数据库操作时，可以统一封装为存储过程，不必反复编写相同 SQL。
3. 创建存储过程
基本语法：
DELIMITER //
CREATE PROCEDURE 过程名( 参数名 数据类型, 参数名 数据类型, ...)BEGIN 过程语句; 过程语句;END //
DELIMITER ;说明：
CREATE PROCEDURE：创建存储过程；- 圆括号内是参数列表；
BEGIN ... END中可以放多条 SQL；- 过程体中存在多个分号，因此仍然要临时修改结束符；
- 创建完成后使用
DELIMITER ;恢复默认结束符。
4. IN、OUT、INOUT 参数
存储过程的参数可以设置为：
| 参数类型 | 作用 |
|---|---|
IN | 输入参数，调用者把数据传给存储过程 |
OUT | 输出参数，存储过程通过该参数向调用者返回数据 |
INOUT | 既可以接收输入，也可以向外返回数据 |
5. 调用和删除存储过程
调用：
CALL 过程名(参数列表);删除：
DROP PROCEDURE IF EXISTS 过程名;加入 IF EXISTS 后，过程不存在时不会因为删除失败而中断整段脚本。
6. 存储过程与函数的区别
| 对比项 | 自定义函数 | 存储过程 |
|---|---|---|
| 返回值 | 必须使用 RETURN 返回值 | 没有普通的 RETURN 返回值 |
| 返回数据 | 通过返回值 | 可以通过 OUT、INOUT 参数向外传数据 |
| 调用方式 | 通常写在 SQL 表达式中，如 SELECT myfun(...) | 使用 CALL 过程名(...) |
| SQL 操作 | 函数主要用于计算 | 存储过程中可以执行多条 SQL |
| 相互调用 | 存储过程可以调用函数 | 不建议在函数中调用存储过程 |
二、触发器
1. 什么是触发器
触发器是一种特殊的存储过程。
当指定表发生指定事件时，数据库会自动调用触发器，不需要程序员使用 CALL 手动执行。
触发事件：
INSERT：新增；UPDATE：修改；DELETE：删除。
2. BEFORE 与 AFTER
触发器可以在操作的不同时间执行：
BEFORE：在原操作之前触发；AFTER：在原操作之后触发。
3. 创建触发器的语法
DELIMITER //
CREATE TRIGGER 触发器名BEFORE或AFTER INSERT或UPDATE或DELETEON 表名FOR EACH ROWBEGIN 执行语句; 执行语句;END //
DELIMITER ;结构说明：
- 触发器必须绑定到一张表；
- 必须指定触发事件；
- 必须指定在事件之前还是之后执行；
FOR EACH ROW表示受影响的每一行都会执行一次触发器。
4. 删除触发器
DROP TRIGGER IF EXISTS 触发器名;5. OLD 与 NEW
触发器需要访问“发生变化之前”和“发生变化之后”的行数据：
OLD.字段名：变化之前的数据；NEW.字段名：变化之后的数据。
三种事件可以使用的数据不同：
| 事件 | OLD | NEW |
|---|---|---|
INSERT | 没有旧数据 | 新插入的数据 |
DELETE | 被删除前的数据 | 没有新数据 |
UPDATE | 修改前的数据 | 修改后的数据 |
例如学生学号从 15 修改为 20：
OLD.S = '15'NEW.S = '20'三、事务
1. 为什么需要事务
现实中的一件完整业务，在代码中可能对应多条 SQL。
例如“王1向王2借 5000 元”需要执行两条语句：
UPDATE bankSET money = money + 5000WHERE name = '王1';
UPDATE bankSET money = money - 5000WHERE name = '王2';如果第一条成功、第二条失败，就会出现一方的钱增加了，而另一方的钱没有减少，数据处于错误状态。
事务用于把多条相关 SQL 组合成一个整体：
- 要么全部成功；
- 要么全部失败并恢复原状；
- 不能只执行成功一部分。
2. 服务器必须再次校验数据
以转账金额为例说明：
- 客户端可以先判断转账金额是否超过余额；
- 但客户端程序可能被篡改或绕过；
- 服务器不能完全相信客户端传来的数据；
- 关键业务规则必须在服务器或数据库一侧再次校验。
3. 事务的 ACID 特性
1. A：原子性（Atomicity）
事务是最小的执行单元，不可以继续拆分。
事务中的操作：
- 要么全部执行成功；
- 要么全部不执行。
转账中的“加钱”和“减钱”必须作为一个整体。
2. C：一致性（Consistency）
事务执行前后，数据库必须始终满足完整性约束，不能把数据库从一个正确状态变成错误状态。
例如转账前后，双方总金额应保持一致，不能凭空增加或减少。
3. I：隔离性（Isolation）
数据库可能同时被多个用户或多台服务器访问。
并发执行的事务之间需要相互隔离，避免彼此的中间状态互相干扰。隔离性还涉及不同的隔离级别，本文不再继续展开。
4. D：持久性（Durability）
事务一旦提交，其修改就会永久保存到数据库中。
提交之前的修改仍属于事务中的临时状态；提交完成后，即使连接结束，结果仍然保留。
4. 事务的基本用法
1. 开启事务
START TRANSACTION;2. 回滚
ROLLBACK;回滚后，数据库恢复到开启事务之前的状态。
3. 提交
COMMIT;提交后，事务中的修改永久保存。
4. 基本结构
START TRANSACTION;
执行语句;执行语句;
-- 根据执行结果二选一ROLLBACK;-- 或COMMIT;ROLLBACK 和 COMMIT 应当二选一：
- 先回滚后，事务已经结束，再提交不会让刚才的修改重新出现；
- 先提交后，修改已经永久保存，再回滚也不能撤销已经提交的事务。
如果这篇文章对你有帮助，欢迎分享给更多人！