便于DBA在后台操作人员权限时候能更好的进行时间线上的回溯，加了个触发器，记录下修改前后的权限明细。
代码语言：javascript复制use yearning;
CREATE TABLE `core_graineds_audit_log` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`core_graineds_id` int unsigned NOT NULL COMMENT 'core_graineds表的主键id',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
`group_changes` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '权限组',
`action` char(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作',
`action_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '变更时间',
PRIMARY KEY (`id`),
KEY `user_idx` (`username`),
KEY `idx_atime` (`action_time`)
) ENGINE = InnoDB AUTO_INCREMENT = 102 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '记录权限变更情况';
-- 创建3个触发器，为了避免阻塞主流程， 这里统一用 INSERT IGNORE INTO
CREATE TRIGGER `dba_core_graineds_upd`
AFTER
UPDATE ON `yearning`.`core_graineds` FOR EACH ROW
INSERT IGNORE INTO `yearning`.`core_graineds_audit_log` (
`core_graineds_id`,
`username`,
`group_changes`,
`action`
)
VALUES (
NEW.`id`,
NEW.`username`,
concat (OLD.`group`, ' ----> ', NEW.`group`),
'updated'
);
CREATE TRIGGER `dba_core_graineds_del`
AFTER
DELETE ON `yearning`.`core_graineds` FOR EACH ROW
INSERT IGNORE INTO `yearning`.`core_graineds_audit_log` (
`core_graineds_id`,
`username`,
`group_changes`,
`action`
)
VALUES (
OLD.`id`,
OLD.`username`,
OLD.`group`,
'deleted'
);
CREATE TRIGGER `dba_core_graineds_ins`
AFTER
INSERT ON `yearning`.`core_graineds` FOR EACH ROW
INSERT IGNORE INTO `yearning`.`core_graineds_audit_log` (
`core_graineds_id`,
`username`,
`group_changes`,
`action`
)
VALUES (
NEW.`id`,
NEW.`username`,
NEW.`group`,
'inserted'
);
-- 效果
(test) > select * from core_graineds_audit_log ;
---- ------------------ ------------- ---------------------------------------------------------------- ---------- ---------------------
| id | core_graineds_id | username | group_changes | action | action_time |
---- ------------------ ------------- ---------------------------------------------------------------- ---------- ---------------------
| 11 | 8 | john | ["develop-abc", "develop-bcd"] ----> ["abc-bcd", "def-aaa"] | updated | 2022-03-31 17:35:58 |
| 12 | 10 | lisi | ["develop-aa"] | deleted | 2022-03-31 17:53:29 |
| 13 | 22 | zhangsan | ["develop-aa", "develop-bb", "dev-aaa", "develop-cccc"] | inserted | 2022-03-31 17:58:30 |
---- ------------------ ------------- ---------------------------------------------------------------- ---------- --------------------- 
