IT评测·应用市场-qidao123.com

标题: MySQL深度探索：掌握触发器自动化与精细用户权限管理，提升数据库效能与安 [打印本页]
作者: 鼠扑    时间: 2024-9-26 15:21
标题: MySQL深度探索：掌握触发器自动化与精细用户权限管理，提升数据库效能与安

   
  目次

前言：
触发器（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数据库管理方面的能力和程度。

正文：

一.触发器

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表存储删除前的值。
二.创建触发器

创建触发器的语法如下：

  1. 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）创库

  1. create database benet;
  3. use benet;
复制代码
（2）创表

  1. create table tb_emp8
  3. (id int not null primary key,
  5. name varchar(22) not null,
  7. deptid int not null,
  9. salary float not null);
复制代码
（3）创建求和触发器

  1. create trigger sumofsalary
  3. before insert on tb_emp8
  5. for each row
  7. set @sum=@sum+NEW.salary;
复制代码
（4）验证求和函数

  1. set @sum=0;
  3. insert into tb_emp8 values (1,'A',1,1000),(2,'B',2,500);
复制代码
  1. select * from tb_emp8;
复制代码
 

  1. select @sum;
复制代码



案例2：alter触发器

  1. use benet;
  3. create table tb_emp6 like tb_emp8;
  5. create table tb_emp7 like tb_emp8;
  9. create trigger doubleofsalary
  11. after insert on tb_emp6
  13. for each row
  15. insert into tb_emp7
  17. values (
  19. NEW.id,NEW.name,NEW.deptId,2*NEW.salary);
  23. INSERT INTO tb_emp6 VALUES (1,'A',1,1000),(2,'B',1,500);
复制代码
  1. SELECT * FROM tb_emp6;
复制代码
 

  1. SELECT * FROM tb_emp7;
复制代码



案例3：delete触发器 ***

  1. delimiter //
  3. create trigger deny_del before delete
  5. on tb_emp7 for each row
  7. begin
  9. rollback;
  11. end //
复制代码
报错，显式语句start transaction,commit,rollback在触发器语句中不识别。

  1. create procedure rollbk() begin rollback; end//
  5. create trigger deny_del before delete
  7. on tb_emp7 for each row
  9. begin
  11. call rollbk;
  13. end //
  17. delimiter ;
  21. delete from tb_emp7 where id=1;
复制代码
update 触发器

案例1

  1. create table test(
  3. id int primary key,
  5. flg nvarchar(20)
  7. );
  9. insert into test values(1,'');
  11. insert into test values(2,'');
  15. delimiter //
  17. CREATE TRIGGER trig_test_update
  19. before  update  on  test for each row
  21. BEGIN
  23.   set new.flg='有更新';
  25. END;
  27. //
  29. delimiter ;
  33. update test set id='3' where id=2;
  37. select * from test;
复制代码



案例2

项目需求: 提现表,付出宝信息被修改,怎么限制, 插入的数据针对字段不被修改
  1. create table user_withdraw
  3. (id int primary key,name varchar(20),alipay_num int,money int,real_money int);
  7. insert into user_withdraw values
  9. (1,'zhangsan','10001','100000','200000'),
  11. (2,'lisi',10002,300000,400000);
  15. delimiter //
  17. CREATE TRIGGER user_withdraw_update  BEFORE UPDATE ON user_withdraw
  19.        FOR EACH ROW
  21.        BEGIN
  23.            IF OLD.alipay_num is not null THEN
  25.                SET NEW.alipay_num = OLD.alipay_num,NEW.money= OLD.money,NEW.real_money=OLD.real_money;
  27.            END IF;
  29.        END;//
  31. delimiter ;
  35. update user_withdraw set money='30000' where id=2;
  37. select * from user_withdraw;
复制代码

三.创建有多个实行语句的触发器

1.CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW
BEGIN
    实行语句列表
END

  1. DELIMITER //
  3. CREATE TRIGGER demo BEFORE DELETE
  5. ON users FOR EACH ROW
  7. BEGIN
  9. INSERT INTO logs VALUES(NOW());
  11. INSERT INTO logs VALUES(NOW());
  13. END //
  15. DELIMITER ;
复制代码

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的环境下和insert差不多，但是如果表中存在primary 大概unique索引的时间，如果插入的数据和原来的primary key大概unique相同的时间，会删除原来的数据，然后增长一条新的数据，所以有的时间实行一条replace语句相当于实行了一条delete和insert语句。

2.用户users表

  1. CREATE TABLE users (   
  3. id int  NOT NULL AUTO_INCREMENT primary key,
  5. name varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  7. add_time varchar(20) DEFAULT NULL )  
  9. AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4;
复制代码

3.日志logs表：

  1. CREATE TABLE `logs` (
  3.   `Id` int(11) NOT NULL AUTO_INCREMENT,
  5.   `log` varchar(255) DEFAULT NULL COMMENT '日志说明',
  7.   PRIMARY KEY (`Id`)
  9. )  DEFAULT CHARSET=utf8mb4 COMMENT='日志表';
复制代码

4.创建触发器：

当在users中插入一条数据，就会在logs中生成一条日志信息。
  1. DELIMITER $
  3. CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
  5. BEGIN
  7. declare s1 VARCHAR(40)character set utf8mb4;
  9. declare s2 VARCHAR(20) character set utf8mb4;
  11. SET s2 = " is created";
  13. SET s1 = CONCAT(NEW.name,s2);     
  15. INSERT INTO logs(log) values(s1);
  17. END $
  19. DELIMITER ;
复制代码

5.插入数据，并检察日志表

  1. insert into users(name,add_time) values('zhangsan',now());
复制代码

6.检察触发器

  1. SHOW TRIGGERS;
  3. SELECT * FROM information_schema.triggers;
复制代码

7.删除触发器

  1. 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.删除用户

  1. DROP USER tom@'%';
  3. 或
  5. DELETE FROM mysql.user WHERE host='%' and user='tom';
复制代码

4.修改用户暗码

  1. mysqladmin -u root -p password "rootpassword"
  5. GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY '123.com';
  9. update mysql.user set password=password("rootpwd") where user="root" and host="localhost";
  13. SET PASSWORD=PASSWORD("123.com");  #默认修改root账户密码
  15.   
  17. SET PASSWORD FOR 'testUser'@'localhost'=PASSWORD("newped");  #修改普通用户密码
复制代码

5.常用权限



6.撤权

  1. REVOKE INSERT ON *.* FROM 'tom'@'%';
复制代码

7.检察权限

  1. SHOW GRANTS FOR 'tom'@'%'\G
复制代码
等待您的关注~


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) Powered by Discuz! X3.4