MySQL学习笔记9——触发器和权限管理

一、触发器

当商品信息和库存信息分别存放在两个不同的数据表中时，可以创建一个触发器， 让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。

1、如何操作触发器

创建触发器

创建触发器语法结构：

CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} 
ON 表名 FOR EACH ROW 表达式:

在创建时，你一定要注意触发器的三个要素：

• 表名：表示触发器监控的对象。
• INSERT | UPDATE | DELETE：表示触发的事件。INSERT表示插入记录时触发; UPDATE表示更新记录时触发; DELETE 表示删除记录时触发。
• BEFORE | AFTER：表示触发的时间。BEFORE表示在事件之前触发; AFTER表示在事件之后触发。

查看触发器

SHOW TRIGGERS\G; 

删除触发器

DROP TRIGGER 触发器名称;

案例
超市项目实际实施过程中，客户经常要查询储值余额变动的明细，但是，查询会员消费流水时，存在数据汇总不及时、查询速度比较慢的问题。这时，我们就想到用触发器，及时把会员储值金额的变化信息记录到一个专门的表中。

DELIMITER //
CREATE TRIGGER demo.upd_membermaster BEFORE UPDATE 	-- 在更新前触发
ON demo.membermaster
FOR EACH ROW										-- 表示每更新-条记录，触发一次
BEGIN												-- 开始程序体
IF (new.memberdeposit <> old.memberdeposit) 		-- 如果储值金额有变化
THEN
INSERT INTO demo.deposithist
(
memberid,
transdate,
oldvalue,
newvalue,
changedvalue
)
SELECT
NEW.memberid,
NOW(),
OLD.memberdeposit,										-- 更新前的储值金额
NEW.memberdeposit,                                 	    -- 更新后的储值金额
NEW.memberdeposit-OLD.memberdeposit; 					-- 储值金额变化值
END IF;
END
DELIMITER ;

2、触发器的优缺点

优点：

• 触发器可以确保数据的完整性；

• 触发器可以帮助我们记录操作日志；利用触发器，可以具体记录什么时间发生了什么。

• 触发器可以用在操作数据前，对数据进行合法性检查；通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统。

缺点：

• 可读性差。因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。
  另外，相关数据的变更，特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

一个小建议：维护一个完整的数据库设计文档。因为运维人员可能会经常变动，如果有一个完整的数据库设计文档，就可以帮助新人快速了解触发器的设计思路，从而减少错误，降低系统维护的成本。

二、权限管理

1、角色的作用

角色是在MySQL 8.0中引入的新功能，相当于一个权限的集合。引入角色的目的是方便管理拥有相同权限的用户。

2、角色的操作

创建角色

CREATE ROLE 角色名;
-- 例如：创建一个经理的角色
CREATE ROLE 'manager'@'localhost';

这里的意思是，创建一个角色， 角色名称是"manager" ，角色可以登录的主机是"localhost"，意思是只能从数据库服务器运行的这台计算机登录这个账号。

CREATE ROLE 'manager';
-- 如果不写主机名，MySQL默认是通配符“%”，
--意思是这个账号可以从任何一台主机上登录数据库。

MySQL中创建了角色之后，默认都是没有被激活的，也就是不能用，必须要用下面的语句激活:

SET global activate_all_roles_on_login=ON;
-- 这条SQL语句的意思是，对所有角色永久激活。

给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。

GRANT 权限 ON 表名 TO 角色名;
-- 举例
GRANT SELECT ON demo.settlement TO 'manager';
GRANT SELECT , INSERT , DELETE , UPDATE ON demo.invcount TO 'stocker'

查看角色权限

SHOW GRANTS FOR 'manager';

只要你创建了一个角色，系统就会自动给你一个"USAGE" 权限，意思是连接登录数据库的权限。

删除角色
当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。

DROP ROLE 角色名称;

3、用户的操作

我们可以把数据库中的角色看作是一个权限集。角色本身不能操作数据,这个任务还是要靠用户来完成。在操作用户前，我们先要创建一个用户。

创建用户

CREATE USER 用户名 [IDENTIFIED BY 密码]; 
-- 举例
CREATE USER 'zhangsan' IDENTIFIED BY 'mysql';

“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。

给用户授权

给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权，和直接给用户授权。
通过把角色赋予用户给用户授权的语法结构如下:

GRANT 角色名称 TO 用户名称;
GRANT 权限 ON 表名 TO 用户名;

查看用户权限

SHOW GRANTS FOR 用户名;
-- 举例
SHOW GRANTS FOR 'zhangsan';

删除用户

DROP USER 用户名;