MySQL(05)触发器&事件&事务&锁

触发器

create trigger  trigger_name 

　　before | after　　insert | update | delete

　　on 表名 for each row

　　trigger_body

触发器有六种:　触发时机before(之前)和after(之后), 触发动作insert | update | delete

 

提供了两个对象 new和old 

　insert 没有 old(源) 数据对象, 只有new对象　update有new和old两种数据对象　　delete没有new 只有 old

create table if not exists test1
(
    id int primary key auto_increment,
    number decimal(10,2)    #设置float的精度, 10代表小数点前保留10位, 2代表小数点后只保留两位精度
)engine innodb default charset utf8;
insert into test1 values(NULL,1.22),(NULL,2.22),(NULL,3.22);

#创建触发器:限制修改表, 触发器被创建后只能插入 0~100的数据
create trigger test_trigger
before update                    #表数据更新前触发
on test1 for each row
begin
    if new.number < 0 THEN
            set new.number = 0;
    elseif new.number > 100 THEN
            set new.number = 100;
    end if;
end;

update test1 set number=102 where id=3;
select * from test1;

drop trigger test_trigger;

#触发器都存放在information_schema.trigger
show DATABASES;
use information_schema;
show tables;
#查看所有触发器
select * from information_schema.TRIGGERS;

create table review
(
    id int primary key auto_increment,
    username varchar(64),
    action varchar(10),
    action_time date
)engine innodb default charset utf8;

#test1的增删改都会触发, 做成日志信息
create trigger test_insert
after insert
on test1 for each row
begin
    insert into review value(null,user(),'insert',now());
end;

create trigger test_update
after update
on test1 for each row
begin
    insert into review value(null,user(),'update',now());
end;

create trigger test_delete
after delete
on test1 for each row
begin
    insert into review value(null,user(),'delete',now());
end;

insert into test1  value(null,101);
update test1 set number=102 where id=4;
delete from test1 where id=4;

触发器中不能将结果集返回(不能使用select来打印结果集) 和 call的动态SQL(不能用使用存储过程) , 但可以通过参数带出数据

create trigger tri_test
after update
on test1 for each row
begin
    select 'update' into @msg;
end;

update test1 set number=66 where id=3;
select @msg;

drop trigger tri_test;

 

---

 

事件

create event insert_event        #创建事件
on schedule                                 #设置事件计划任务(时间点,间隔时间)
every 4 second                            #计划任务设置成 每隔4秒执行一次 do 任务
do insert into test1 value(null,1000);

create event truncate_event        #创建事件
on schedule                                 #设置事件计划任务(时间点,间隔时间)
every 1 minute                            #计划任务设置成 每隔40秒执行一次 do 任务
do truncate table test1;        #清空表数据

#查看事件状态
show events;
#查看调度器状态并开启
show variables like '%schedule%';
set global event_scheduler = on;

#查看进程列表
show processlist;

#禁用个别事件
alter event insert_event disable;
#开启个别事件
alter event insert_event ensable;

#drop event insert_event;
#drop event truncate_event;
#set global event_scheduler = off;


#7天后开启事件, 每天清空表 , 一个月后停止事件, 不保留事件
create event clear_event
on schedule
every 1 day
starts current_timestamp + interval 7 day    #当前时间 + 间隔7天后启动
ends current_timestamp + interval 1 month #当前时间 + 间隔1个月后停止
on completion not preserve    #完成后不保留事件
do truncate table test1;

创建事件后,事件默认是开启的, 但事件调度器默认是关闭的, 需要开启后才能触发事件(事件调度器是通过变量控制的), SQL系统会开启一个进程来执行事件

 

---

 

事务　作为单个逻辑单元执行的一系列操作(创建 销毁 增删改查都是单个的逻辑单元)

事务四个特性 　ACID    分别是原子性、一致性、隔离性、持久性。

1、原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2、一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

3、隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

4、持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。

 

事务控制

　　隐式事务　insert update delete 单个事务默认是隐式事务, 由系统自动提交

　　显式事务　需手动提交, 

#数据的事务且由系统自动提交的,有变量控制
show variables like 'autocommit';
#关闭自动提交
set autocommit=off;
#当关闭自动提交后 delete事务是无法对源表的数据有影响的
delete from test1 where id = 3;
#虽然无法对源表做出影响,但能影响从表中读到内存中的数据的结果集
select * from test1;

#回滚,对结果集的数据回滚到事务未提交时
#rollback;
#select * from test1;

#手动提交事务, 对源表进行影响,提交后的事务无法回滚
# commit;


#显式事务
#重新开启自动提交
set autocommit=on;
#开启显式控制,作用和关闭自动提交是一样的
start transaction;
#后面的事务都都无法对源表有影响了,只会对结果集有影响
#可以通过rollback和commit,对结果集和源表进行回滚和提交

开启事务 start transaction;  提交commit;后就算完成当前事务了, 要继续使用显示事务时需再次开启

 

---

 

事务隔离级别

当多个连接同时连接数据库并开启事务(start transaction),要控制好 并发的事务 的隔离

　读未提交: A连接事务未提交　B连接可以读到A连接未提交事务,但A有可能回滚,导致B读取到错误数据　脏读问题

　读提交:　大多数数据库的默认级别  解决了脏读问题  但B第一次读和第二次读同一数据可能不一致,因为可能A在中间提交了事务改变了B要读的数据   导致不可重复读

　可重读:　MySQL默认级别   解决了不可重复读　　AB两个连接在 开启事务 时, 等于各复制了一张和源表互不影响的临时表, 在未提交时这张临时表不会被源表所影响, 一旦提交则会更新这张临时表     幻读问题: 如果当前事务未提交临时表而更新不及时, 其他连接的事务影响了该数据, 导致未提交的表可能对不存在的数据进行操作

　串行化读:　不允许多个事务同时操作　大大牺牲了效率 , 一般使用可重读就够了

#数据的事务且由系统自动提交的,有变量控制
show variables like 'autocommit';

#关闭自动提交
set autocommit=off;

#当关闭自动提交后 delete事务是无法对源表的数据有影响的
delete from test1 where id = 3;

#虽然无法对源表做出影响,但能影响从表中读到内存中的数据的结果集
select * from test1;

#回滚,对结果集的数据回滚到事务未提交时#rollback;
#select * from test1;

#手动提交事务, 对源表进行影响,提交后的事务无法回滚
#commit;

#显式事务
#重新开启自动提交
set autocommit=on;

#开启显式控制,作用和关闭自动提交是一样的
start transaction;
#后面的事务都都无法对源表有影响了,只会对结果集有影响
#可以通过rollback和commit,对结果集和源表进行回滚和提交

#查看事务隔离级别
#当前会话(连接)的事务隔离级别
select @@session.tx_isolation;
#当前服务的事务隔离级别
select @@global.tx_isolation;

#设置当前会话(连接)的事务隔离等级
#读未提交
set session transaction isolation level read uncommitted;
#读提交
set session transaction isolation level read committed;
#可重读
set session transaction isolation level repeatable read;
#串行化读
set session transaction isolation level serializable;


#设置当前服务的事务隔离等级
#读未提交
set global transaction isolation level read uncommitted;
#读提交
set global transaction isolation level read committed;
#可重读
set global transaction isolation level repeatable read;
#串行化读
set global transaction isolation level serializable;

 

---

 

 锁　协调多并发访问同一资源的机制

锁的类型

MyISAM和MEMORY 存储引擎默认采用 表级锁(对整张表进行加锁)

BDB 存储引擎采用页面锁(数据库可以分页读,而页的数据可能来自多张表, 加了页面锁后, 页里的数据也会加锁,不在页里的数据不加锁)

 

innodb 引擎　采用表级锁和行级锁(可以对某一行数据加锁),  默认是行级锁 可以切换

　　读锁(共享锁)　 对数据加了读锁后其他事务只能读不能对其写, 其他事务可以对该数据叠加读锁

　　写锁(排他锁)　对数据加了写锁后,其他事务不能读写, 也不可以叠加锁

MyISAM 用户读数据时自动加read锁, 写数据时自动加write锁
加锁　Lock tables 表名 read/write; 锁

解锁　Unlock tables;

MyISAM 当前连接对某个表加了锁后, 就无法再访问其他的表只能访问加了锁的表