MySQL深度探索：掌握触发器自动化与精细用户权限管理，提升数据库效能与安
https://i-blog.csdnimg.cn/direct/71d9920920d94be5a43e8513e5057c01.gif[*] 作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢各人的关注
[*] 座右铭： 云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
[*] 个人主页：团儿.-CSDN博客
目次
前言：
触发器（Triggers）：
用户权限（User Permissions）：
一.触发器
1.MySQL触发器简介
2.引发触发器实行的事件，如下：
3.触发步伐的优点，如下：
4.触发器的特性：
5.MySQL 所支持的触发器有三种：
6.触发器包含两个假造表：new表、old表：
二.创建触发器
创建触发器的语法如下：
可以说MySQL创建以下六种触发器：
案例1：before触发器
（1）创库
（2）创表
（3）创建求和触发器
（4）验证求和函数
案例2：alter触发器
案例3：delete触发器 ***
update 触发器
案例1
案例2
三.创建有多个实行语句的触发器
1.CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
2.用户users表
3.日志logs表：
4.创建触发器：
5.插入数据，并检察日志表
6.检察触发器
7.删除触发器
8.限制和注意事项
触发器会有以下两种限制：
注意事项：
9.总结
数据库触发器有以下的作用（增强性）：
（1）安全性。
（2）审计。
（3）实现复杂的数据完整性规则
（4）实现复杂的非尺度的数据库相关完整性规则。
（5）同步及时地复制表中的数据。
（6）自动计算数据值。
四.用户和权限
1.select user,host,grant_priv from mysql.user;
2.创建新用户
3.删除用户
4.修改用户暗码
5.常用权限
6.撤权
7.检察权限
前言：
在当今数据驱动的期间，数据库管理体系（DBMS）作为企业信息架构的核心，饰演着至关重要的角色。MySQL，作为最流行的开源关系型数据库管理体系之一，凭借其高性能、可靠性、灵活性和成本效益，在各行各业中得到了广泛应用。无论是初创企业还是大型企业，MySQL都是管理数据、支持业务决策和驱动应用步伐运行的首选之一。
在MySQL的强盛功能中，触发器和用户权限是两个不可或缺的特性，它们在保障数据一致性、自动化数据利用以及确保数据库安全方面发挥着至关重要的作用。
触发器（Triggers）：
触发器是MySQL中一种特殊类型的存储过程，它会在指定的数据库表上实行INSERT、UPDATE或DELETE等利用时自动实行。通过巧妙地利用触发器，数据库管理员和开发者可以实现复杂的业务规则自动化，确保数据的完整性和一致性。比方，当向某个表中插入新记载时，触发器可以自动更新另一个表中的相关统计信息，大概根据特定条件阻止不合规的数据被插入。触发器简化了数据利用逻辑，提高了开发效率，同时也淘汰了因人为错误导致的数据问题。
用户权限（User Permissions）：
在数据库管理中，确保数据安全是至关重要的。MySQL通过精细的权限控制机制，允许数据库管理员为不同用户分配不同的利用权限，从而有效防止未授权访问和数据泄露。理解并正确配置用户权限，对于维护数据库的安全性和完整性至关重要。从简单的读取权限到复杂的数据修改和表结构管理权限，MySQL提供了丰富的权限选项，以满足不同场景下的安全需求。
本文旨在深入探讨MySQL数据库中的触发器和用户权限两大核心特性，通过理论讲解与实例分析相结合的方式，资助读者理解这些概念的基本原理、应用场景以及最佳实践。无论是数据库初学者还是经验丰富的数据库管理员，相信都能从本文中获益匪浅，进一步提升在MySQL数据库管理方面的能力和程度。
https://i-blog.csdnimg.cn/direct/59e4fa702d674ec696b4bb590f274f8c.png
正文：
一.触发器
1.MySQL触发器简介
MySQL 数据库中触发器是一个特殊的存储过程；
存储过程要利用 CALL 语句来调用，而触发器的实行是事件(insert、update、delete)自动触发。
2.引发触发器实行的事件，如下：
增长一条弟子记载时，会自动检查年事是否符合范围要求。
每当删除一条弟子信息时，自动删除其成绩表上的对应记载。
每当删除一条数据时，在数据库存档表中保留一个备份
3.触发步伐的优点，如下：
触发步伐的实行是自动的，当对触发步伐相关表的数据做出相应的修改后立刻实行。
触发步伐可以通过数据库中相关的表层叠修改另外的表。
触发步伐可以实施比 FOREIGN KEY 束缚、CHECK 束缚更为复杂的检查和利用。
4.触发器的特性：
有begin end体，begin end;之间的语句可以写的简单大概复杂
什么条件会触发：Insert、Update、Delete
什么时间触发：在增删改前大概后
触发频率：针对每一行实行
触发器定义在表上，附着在表上
5.MySQL 所支持的触发器有三种：
INSERT 触发器、UPDATE 触发器和 DELETE 触发器
6.触发器包含两个假造表：new表、old表：
insert触发器引用new表来存储被插入的行；
update触发器引用new表存储更新后的值，引用old表存储更新前的值；
delete触发器引用old表存储删除前的值。
二.创建触发器
创建触发器的语法如下：
create trigger trigger_name trigger_time trigger_event ON tb_name for each row trigger_stmt trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE大概AFTER
trigger_event：触发事件，为INSERT、DELETE大概UPDATE
tb_name：表示创建触发器的表名，就是在哪张表上创建触发器
for each row: 表示任何一条记载上的利用满足触发事件都会触发该触发器
trigger_stmt：触发器的步伐体，可以是一条SQL语句大概是用BEGIN和END包含的多条语句
可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE
案例1：before触发器
（1）创库
create database benet;
use benet; （2）创表
create table tb_emp8
(id int not null primary key,
name varchar(22) not null,
deptid int not null,
salary float not null); （3）创建求和触发器
create trigger sumofsalary
before insert on tb_emp8
for each row
set @sum=@sum+NEW.salary; （4）验证求和函数
set @sum=0;
insert into tb_emp8 values (1,'A',1,1000),(2,'B',2,500); select * from tb_emp8;
https://i-blog.csdnimg.cn/direct/1cb25194f2444261a1f53aa14a8e5e58.png
select @sum; https://i-blog.csdnimg.cn/direct/626c721ff5504870923f0f736623ca28.png
案例2：alter触发器
use benet;
create table tb_emp6 like tb_emp8;
create table tb_emp7 like tb_emp8;
create trigger doubleofsalary
after insert on tb_emp6
for each row
insert into tb_emp7
values (
NEW.id,NEW.name,NEW.deptId,2*NEW.salary);
INSERT INTO tb_emp6 VALUES (1,'A',1,1000),(2,'B',1,500); SELECT * FROM tb_emp6;
https://i-blog.csdnimg.cn/direct/b437ea3c51e94c09b2c7b6bb2ecdb465.png
SELECT * FROM tb_emp7; https://i-blog.csdnimg.cn/direct/96d0888eb38b4842a2be78600e35da1f.png
案例3：delete触发器 ***
delimiter //
create trigger deny_del before delete
on tb_emp7 for each row
begin
rollback;
end // 报错，显式语句start transaction,commit,rollback在触发器语句中不识别。
https://i-blog.csdnimg.cn/direct/e16e9d94aaf749e6afca86741df7eb74.png
create procedure rollbk() begin rollback; end//
create trigger deny_del before delete
on tb_emp7 for each row
begin
call rollbk;
end //
delimiter ;
delete from tb_emp7 where id=1; update 触发器
案例1
create table test(
id int primary key,
flg nvarchar(20)
);
insert into test values(1,'');
insert into test values(2,'');
delimiter //
CREATE TRIGGER trig_test_update
before update on test for each row
BEGIN
set new.flg='有更新';
END;
//
delimiter ;
update test set id='3' where id=2;
select * from test; https://i-blog.csdnimg.cn/direct/2a87de5a0ae440db806bef03dafe1052.png
案例2
项目需求: 提现表,付出宝信息被修改,怎么限制, 插入的数据针对字段不被修改
create table user_withdraw
(id int primary key,name varchar(20),alipay_num int,money int,real_money int);
insert into user_withdraw values
(1,'zhangsan','10001','100000','200000'),
(2,'lisi',10002,300000,400000);
delimiter //
CREATE TRIGGER user_withdraw_update BEFORE UPDATE ON user_withdraw
FOR EACH ROW
BEGIN
IF OLD.alipay_num is not null THEN
SET NEW.alipay_num = OLD.alipay_num,NEW.money= OLD.money,NEW.real_money=OLD.real_money;
END IF;
END;//
delimiter ;
update user_withdraw set money='30000' where id=2;
select * from user_withdraw; https://i-blog.csdnimg.cn/direct/a93c15dd0d9948a9b4c1f30c9afda6c9.png
三.创建有多个实行语句的触发器
1.CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
实行语句列表
END
DELIMITER //
CREATE TRIGGER demo BEFORE DELETE
ON users FOR EACH ROW
BEGIN
INSERT INTO logs VALUES(NOW());
INSERT INTO logs VALUES(NOW());
END //
DELIMITER ;
load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的环境下和insert差不多，但是如果表中存在primary 大概unique索引的时间，如果插入的数据和原来的primary key大概unique相同的时间，会删除原来的数据，然后增长一条新的数据，所以有的时间实行一条replace语句相当于实行了一条delete和insert语句。
2.用户users表
CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT primary key,
name varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
add_time varchar(20) DEFAULT NULL )
AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4;
3.日志logs表：
CREATE TABLE `logs` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`log` varchar(255) DEFAULT NULL COMMENT '日志说明',
PRIMARY KEY (`Id`)
) DEFAULT CHARSET=utf8mb4 COMMENT='日志表';
4.创建触发器：
当在users中插入一条数据，就会在logs中生成一条日志信息。
DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
declare s1 VARCHAR(40)character set utf8mb4;
declare s2 VARCHAR(20) character set utf8mb4;
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);
INSERT INTO logs(log) values(s1);
END $
DELIMITER ;
5.插入数据，并检察日志表
insert into users(name,add_time) values('zhangsan',now());
6.检察触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.triggers;
7.删除触发器
drop trigger user_log;
8.限制和注意事项
触发器会有以下两种限制：
（1）触发步伐不能调用将数据返回客户端的存储步伐，也不能利用接纳CALL语句的动态SQL语句，但是允许存储步伐通过参数将数据返回触发步伐，也就是存储过程大概函数通过OUT大概INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
（2）不能在触发器中利用以显示或隐式方式开始或竣事事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。
注意事项：
MySQL的触发器是按照BEFORE触发器、行利用、AFTER触发器的次序实行的，其中任何一步发生错误都不会继续实行剩下的利用，如果对事务表举行的利用，如果出现错误，那么将会被回滚，如果是对非事务表举行利用，那么就无法回滚了，数据可能会堕落。
9.总结
触发器是基于行触发的，所以删除、新增大概修改利用可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增长过多的触发器，如许会对数据的插入、修改大概删除带来比较严重的影响，同时也会带来可移植性差的结果，所以在设计触发器的时间一定要有所思量。
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发实行，它比数据库本身尺度的功能有更精细和更复杂的数据控制能力。
数据库触发器有以下的作用（增强性）：
（1）安全性。
可以基于数据库的值利用户具有利用数据库的某种权利。
# 可以基于时间限制用户的利用，比方不允许放工后和节假日修改数据库数据。
# 可以基于数据库中的数据限制用户的利用，比方不允许股票的价格的升幅一次超过10%。
（2）审计。
可以跟踪用户对数据库的利用。
# 审计用户利用数据库的语句。
# 把用户对数据库的更新写入审计表。
（3）实现复杂的数据完整性规则
# 实现非尺度的数据完整性检查和束缚。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。比方，触发器可回退任何企图吃进超过自己保证金的期货。
# 提供可变的缺省值。
（4）实现复杂的非尺度的数据库相关完整性规则。
触发器可以对数据库中相关的表举行连环更新。比方，在auths表author_code列上的删除触发器可导致相应删除在别的表中的与之匹配的行。
# 在修改或删除时级联修改或删除别的表中的与之匹配的行。
# 在修改或删除时把别的表中的与之匹配的行设成NULL值。
# 在修改或删除时把别的表中的与之匹配的行级联设成缺省值。
# 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图举行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。比方，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。
（5）同步及时地复制表中的数据。
（6）自动计算数据值。
如果数据的值到达了一定的要求，则举行特定的处理。比方，如果公司的帐号上的资金低于5万元则立刻给财务人员发送警告数据
四.用户和权限
1.select user,host,grant_priv from mysql.user;
mysql.user表：特殊列属性
max_questions: 用户每小时允许实行的查询利用次数
max_updates: 用户每小时允许实行的更新利用次数
max_connections: 服务器每小时最大允许实行的连接利用次数
max_user_connections: 单用户允许同时创建的连接次数
mysql.db表中存储了用户对某个数据库的利用权限，决定用户能从哪个主机存取哪个数据库。
select user,host,db from mysql.db;
2.创建新用户
CREATE USER 'tom'@'%' IDENTIFIED BY 'mypass'; #仅创用户
GRANT SELECT,insert,UPDATE ON world.* TO 'tom'@'%' IDENTIFIED BY '123.com'; #创用户并赋权，mysql 8.0不允许
FLUSH privileges; #革新权限列表
3.删除用户
DROP USER tom@'%';
或
DELETE FROM mysql.user WHERE host='%' and user='tom';
4.修改用户暗码
mysqladmin -u root -p password "rootpassword"
GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY '123.com';
update mysql.user set password=password("rootpwd") where user="root" and host="localhost";
SET PASSWORD=PASSWORD("123.com"); #默认修改root账户密码
SET PASSWORD FOR 'testUser'@'localhost'=PASSWORD("newped"); #修改普通用户密码
5.常用权限
https://i-blog.csdnimg.cn/direct/20f540b3edfc4e429565f43979ac35ff.png
6.撤权
REVOKE INSERT ON *.* FROM 'tom'@'%';
7.检察权限
SHOW GRANTS FOR 'tom'@'%'\G 等待您的关注~
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]