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 用户名;
  • 12
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值