MySQL 之 事务、存储过程、索引

事务

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言书写的用户程序的执行所引起。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务基本原理

基本原理：Mysql允许将事务统一进行管理（存储引擎INNODB），将用户所做的操作，暂时保存起来，不直接放到数据表（更新），等到用于确认结果之后再进行操作。 保证了对数据操作的数据安全性。

事务在mysql中通常是自动提交的，但是也可以使用手动事务。

事务ACID特性

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wow',1000),
('linwow',1000),
('lin',1000);

修改数据之前先开启事务操作
start transaction;

修改操作
update user set balance=900 where name='wow'; #买支付100元
update user set balance=1010 where name='linwow'; #中介拿走10元
update user set balance=1090 where name='lin'; #卖家拿到90元

回滚到上一个状态
rollback;

开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;

开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作

存储过程

一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程优点

• 对于一些复用性高或者业务复杂的一些操作，封装到一个存储过程中，避免了重复编写SQL造成漏写或错写操作，简化了SQL的调用
• 批量处理：SQL + 循环，减少流量
• 数据迁移，数据备份
• 统一接口，保证数据安全(重点，尤其是银行系统)

存储过程语法

创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
     存储过程体（一组合法的SQL语句）
END

存储过程参数

在存储过程创建的时候，参数可有0到多个，参数的属性可分为以下3种：

• IN 输入参数：表示调用者向存储过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

使用存储过程

使用存储过程，使用的是【CALL】命令，具体语法如下：

CALL 存储过程名（参数1，...）;

删除存储过程

删除已经创建的存储过程使用【DROP】语句，具体语法如下：

DROP PROCEDURE 存储过程名;

存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

创建一个p1存储过程
delimiter $$
create procedure p1(
    in m int,  #  in 表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out 表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;
end $$
delimiter ;

1、直接在mysql中调用
set &