mysql 触发器拼接字符串,MYSQL 批量生成触发器

最新推荐文章于 2023-11-22 18:59:20 发布

weixin_39927214

最新推荐文章于 2023-11-22 18:59:20 发布

阅读量443

点赞数

文章标签： mysql 触发器拼接字符串

本文介绍了一种在MySQL中批量生成触发器的方法，用于限制特定表的修改操作，确保只有JAVA程序可以对其进行更新。通过创建两个表：一个记录受保护的核心表名，另一个记录黑名单中的用户，再结合存储过程动态生成触发器。

摘要由CSDN通过智能技术生成

MYSQL 批量生成触发器

祖仙教小凡仙海鲨数据库架构师

上周实现银行一个需求，需求是这样的，有些项目的核心表只能通过JAVA程序进行修改，不允许其他程序修改。这个需求基于ORACLE的在去年也实现了。

大概是通过创建两个表，其中一个用来保存需要保护的核心表的名字，另外个叫黑名单的表保存禁止机器名。

然后写个过程根据两个表生产触发器，有多少核心表，就生成多少个触发器。

上周基于ORACLE设计想法，在MYSQL上重现一次就能完成需求。

当然MYSQL的路很曲折

第一创建表结构

/*被保护的表列表*/

CREATE TABLE `PARAM_TABLE` (

`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`TABLENAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '''注意表名的大小写''',

`MODIFY_TIME` datetime DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci

/*黑名单表注意用户名应该输入 ‘布拉格@192.168.2.13’ 形式*/

CREATE TABLE `black_user` (

`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`USER_NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '''username@ip''',

`MODIFY_TIME` datetime DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci

第二创建存储过程

MYSQL的触发器好像不支持多个动作，本来想INSERT UPDATE DELETE合在一起，结果不行。只好分成3个来搞！

/*触发器核心程序*/

DELIMITER $$

USE `bookstore`$$

DROP PROCEDURE IF EXISTS `p_procedurecode`$$

CREATE DEFINER=`root`@`%` PROCEDURE `p_procedurecode`(IN v_schema VARCHAR(50))

BEGIN

DECLARE v_command_num INTEGER DEFAULT 1; ##触发器类型数量

DECLARE no_more_PARAM_TABLES INTEGER DEFAULT 0; ##定义游标退出变量

DECLARE v_table_name VARCHAR(100); ##定义存放表名的变量

DECLARE c_result CURSOR FOR SELECT DISTINCT TABLENAME FROM PARAM_TABLE; ##定义游标的SQL

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_PARAM_TABLES=1; ##关联游标NOT FOUD变量

##如果没有找到数据则设置该变量为1

OPEN c_result;

WHILE no_more_PARAM_TABLES <> 1 DO ##第一层外层游标循环这里要判断退出标志

FETCH c_result INTO v_table_name; ##提取游标结果

IF no_more_PARAM_TABLES <> 1 THEN ##第二次判断游标是否可退出

SET v_command_num = 1; ##如果有数据则进入内循环

WHILE v_command_num < 4 DO ##内循环退出标志

SET @sql='DELIMITER $$ '; ##很烦这个为啥不像ORACLE学习呢？

SET @sql=CONCAT(@sql,CHAR(10)); ##CONCAT是字符串拼接不如ORACLE | 符合方便

SET @sql=CONCAT(@sql,'USE `');

SET @sql=CONCAT(@sql,v_schema);

SET @sql=CONCAT(@sql,'`$$');

SET @sql=CONCAT(@sql,CHAR(10)); ##CHAR 10是换行符号

IF v_command_num = 1 THEN ## 判断触发器类型生成不同的触发器名字

SET @sql=CONCAT(@sql,'DROP TRIGGER `tr_protect_u_');

END IF;

IF v_command_num = 2 THEN

SET @sql=CONCAT(@sql,'DROP TRIGGER `tr_protect_i_');

END IF;

IF v_command_num = 3 THEN

SET @sql=CONCAT(@sql,'DROP TRIGGER `tr_protect_d_');

END IF;

SET @sql=CONCAT(@sql,v_table_name);

SET @sql=CONCAT(@sql,'`$$');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,'CREATE TRIGGER ');

SET @sql=CONCAT(@sql, v_schema);

IF v_command_num = 1 THEN

SET @sql=CONCAT(@sql,'.`tr_protect_u_');

END IF;

IF v_command_num = 2 THEN

SET @sql=CONCAT(@sql,'.`tr_protect_i_');

END IF;

IF v_command_num = 3 THEN

SET @sql=CONCAT(@sql,'.`tr_protect_d_');

END IF;

SET @sql=CONCAT(@sql, v_table_name);

IF v_command_num = 1 THEN

SET @sql=CONCAT(@sql,'` BEFORE UPDATE ON ');

END IF;

IF v_command_num = 2 THEN

SET @sql=CONCAT(@sql,'` BEFORE INSERT ON ');

END IF;

IF v_command_num = 3 THEN

SET @sql=CONCAT(@sql,'` BEFORE DELETE ON ');

END IF;

##下面是拼接触发器核心内容，大意是判断当前用户是否在黑名单中

SET @sql=CONCAT(@sql, v_schema);

SET @sql=CONCAT(@sql,'.');

SET @sql=CONCAT(@sql,v_table_name);

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' FOR EACH ROW BEGIN');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' set @count=(SELECT COUNT(*) FROM ');

SET @sql=CONCAT(@sql, v_schema);

SET @sql=CONCAT(@sql,'.black_user WHERE user_name = USER());');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' IF @count>0 THEN ');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' SIGNAL SQLSTATE ''48000'' SET MESSAGE_TEXT = ''您没有权限操作参数表！'';' );

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' END IF;');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' END; ');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,' $$');

SET @sql=CONCAT(@sql,CHAR(10));

SET @sql=CONCAT(@sql,'DELIMITER ;');

SELECT @sql; ##由于MYSQL不支持动态执行CREATE TRRIGE的DDL语句只能先打印出来

SET v_command_num = v_command_num +1;

END WHILE;

END IF;

END WHILE;

CLOSE C_RESULT;

END$$

DELIMITER ;

写这个存储过程时感觉比ORACLE 繁琐。

1 不同动态执行SQL FOR DDL

2 DELIMITER 好烦

3 没有FOR CUR_XX () LOOP 语句

4 UNITE REPLACE 循环会多循环一次，不得不采用两个WHILE来循环游标

5 @变量和自定义变量感觉有很大的区别，实际没啥区别。

总得来说MSQL 开发对开发人员不太友好。也就是用户友好性不够好

第三执行存储过程

不能动态执行，只好先把执行结果，就是打印DDL语句到LINUX 文件中

这里要用到MYSQL客户端 -E 参数来执行SQL。然后执行导出来的DDL.SQL

#/bin/bash

#write by ZengFanKun doctor

#on 2021-02-28

USER=root

PWD=123456

#HOST=192.168.2.13

HOST=localhost

PROT=3306

DATABASE=bookstore

MYSQL_PATH=/app/mysql8/mysql8_soft

cd ${MYSQL_PATH}

./bin/mysql -uroot -p123456 -hlocalhost -P3306 -D${DATABASE} --vertical --skip-column-names -e"CALL p_procedurecode('${DATABASE}')" |grep -v row > create_trigger_ddl.sql

./bin/mysql -uroot -p123456 -hlocalhost -P3306 -D${DATABASE}

--force < create_trigger_ddl.sql

--vertical ##垂直显示结果类似 \G

--skip-column-names ##不显示字段名称类似SQLPLUS 不显示标题

-e"CALL p_procedurecode('${DATABASE}')" ##执行存储过程

grep -v row > create_trigger_ddl.sql ##过滤掉 -----1row----

忽悠该类型的错误和警告

[root@centos7 mysql8]# sh make_tr_ddl.sh

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1360 (HY000) at line 3: Trigger does not exist

ERROR 1360 (HY000) at line 15: Trigger does not exist

ERROR 1360 (HY000) at line 27: Trigger does not exist

ERROR 1360 (HY000) at line 39: Trigger does not exist

ERROR 1360 (HY000) at line 51: Trigger does not exist

ERROR 1360 (HY000) at line 63: Trigger does not exist

本来是使用SHELL变量来登录的，不管如何折腾发现MYSQL报错

说 'ROOT'@'LOCALHOST' 拒绝登陆。只好让开发人员自己去填实参

执行脚本需要手工输入数据库的执行用户名，密码，端口，路径，SCHEMA

我用SQLyog 工具无法调试MYSQL的存储过程！

weixin_39927214

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫