BigData-MySQL（事务和存储过程）

1.事务

1.1 存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。Mysql的核心就是存储引擎。

常见存储引擎
- InnoDB是事务型数据库的首选，执行安全性数据库，行锁定和外键。mysql5.5之后默认使用。
- MyISAM插入速度和查询效率较高，但不支持事务。
- MEMORY将表中的数据存储在内存中，速度较快。
查看存储引擎
```
show engines;
```
修改存储引擎: my.ini
```
default-storage-engine=INNODB
```

1.2 事务

事务用于保证数据的一致性，由一组DML操作组成，该组SQL语句要么同时成功，要么同时失败。例如转账。

事务的四个特性
- A(Atomicity)原子性:事务是原子工作单元，要么同时执行，要么同时不执行。
- C(Consistency)一致性:符合约束规则；执行之前的整体状态和执行后数据一致。
- I(Isolation)隔离性:并发事务之间相互不影响。
- D(Durability)持久性:事务完成之后，对数据库的影响是永久的。

如何使用事务

# mysql默认开启自动事务提交,将每个dml操作当做一个事务。
# 如果需要将多个dml操作放在同一事务,需要关闭自动事务提交
set Autocommit=0;

//开启事务
* start TRANSACTION;
#一组dml操作
update user set money = money -100 where userid = 1;
update user set money = money + 100 where userid = 2;
...

commit;//提交事务(数据持久化到底层文件中)
rollback;//回滚事务(数据恢复到开启事务之前的状态)

并发事务的存在问题
- 脏读
  一个事务处理过程里读取了另一个未提交的事务中的数据
  
  当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下
```
update account set money=money+100 where name=’B’;  (此时A通知B)
update account set money=money - 100 where name=’A’;
```
  当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。
- 不可重复读
  
  在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
  
  例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。
- 虚读(幻读)
  
  事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
  
  幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
事务的隔离级别
- Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
- Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
- mysqlRead committed (读已提交)：可避免脏读的发生。
- Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。
查看事务隔离级别
```
select @@tx_isolation;
```

修改事务隔离级别

set  [glogal | session]  transaction isolation level 隔离级别名称;
或
set tx_isolation=’隔离级别名称;’

2.存储过程

一组存储和执行在数据库服务器端的程序,在服务器的进程或者线程的内存中执行的  
包含输入输出参数实现数据的传入和传出,内部执行一组sql命令

参数的三种模式
- in: 输入模式(默认),将值传入该存储程序
- out: 输出模式,从程序中将值传出
- inout: 输入输出模式

存储过程创建和调用

不带参数

# 创建
delimiter //; #修改结束标识符为//(默认mysql的结束标识为;,但是在存储程序中需要多次用到;,不能代表结束)
create procedure sel_user()
BEGIN
  select username from user;
end //;
#调用
call sel_user();

带参数

# 创建
delimiter //;
create PROCEDURE sel_user_id(uid int)
BEGIN
 select * from user where userid = uid;
end //;

#调用
call sel_user_id(1);

#创建
delimiter //;
create PROCEDURE sel_user_id( INOUT count int)
BEGIN
select sal into count from emp where empno = count;
end //;

#调用
set @count = 7788;
call sel_user_id(@count);
select @count;

变量的创建和修改

# 创建
delimiter //;
create PROCEDURE sel_user()
BEGIN
 -- 声明变量
 DECLARE v_name varchar(20);
 -- 设置变量值
 set v_name = 'zs';
 select v_name;
end //;

# 调用
call sel_user();

流程控制

# 选择结构

delimiter //;
CREATE PROCEDURE test(IN v_count int,OUT v_name VARCHAR(20))
BEGIN
 IF v_count > 10 THEN
   set v_name = 'zs';
 ELSEIF v_count > 5 THEN
   set v_name = 'ls';
 ELSE
   set v_name = 'ww';
 END IF;
END //;
# 调用存储过程
call test(12,@name);
select @name;

# 循环结构
# while..do..end while/loop…end loop/repeat … util..end repeat

-- 使用while来计算1....10的和
delimiter //;
create PROCEDURE my_sum(n int,out m_sum int)
BEGIN
  DECLARE i int;
  set i=0;
  set m_sum=0;
  -- while 和do中间加条件
  while i<=n DO
      set m_sum=m_sum+i;
      set i=i+1;
  end while;
end //;

call my_sum(10,@m_sum);
select @m_sum;

-- 使用loop来计算1....10的和
delimiter //;
create PROCEDURE my_sum1(n int,out m_sum1 int)
BEGIN
  DECLARE i int;
  set i=0;
  set m_sum1=0;
  -- loop需加标记 方便leave时指定
  lip:loop
  
      set m_sum1=m_sum1+i;
      set i=i+1;
      if i>n THEN leave lip;
      end if;
  end loop lip;
end //;

call my_sum1(10,@m_sum1);
select @m_sum1;



-- 使用repeat来计算1....10的和
delimiter //;
create PROCEDURE my_sum2(n int,out m_sum2 int)
BEGIN
  DECLARE i int;
  set i=0;
  set m_sum2=0;
  -- loop需加标记 方便leave时指定
  REPEAT
      set m_sum2=m_sum2+i;
      set i=i+1;
      UNTIL i>n
  end REPEAT;
end //;
call my_sum2(10,@m_sum2);
select @m_sum2;