mysql触发器-插入更新数据时字段值自动加密

Mysql字段值加密解密的触发器：
因业务场景需要给敏感字段直接在数据表上加密。
工作中遇到临时写的demo，复制可以直接使用。

-- 创建表

CREATE TABLE `t_tab1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `encry` varchar(255) NOT NULL DEFAULT '',
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

-- 创建插入触发器

DROP TRIGGER IF EXISTS t_insert_make_encry; -- 删除原先的触发器
CREATE TRIGGER t_insert_make_encry -- 创建触发器
BEFORE INSERT ON t_tab1 FOR EACH ROW -- 触发在这张表新增数据前
BEGIN  -- 要执行的sql list 开始
        IF new.encry IS NOT NULL THEN
        SET new.encry = to_base64(aes_encrypt(new.encry,'_111'));
        SET new.type =1;  -- 1:密文 0:明文
        END IF;
END;

-- 插入数据

INSERT INTO t_tab1(encry) VALUES ('123456');

-- 解密

SELECT AES_DECRYPT(from_base64('5Ir/+8TLI38bZ1+faWhkhA=='),'_111');

-- 查看触发器

SHOW TRIGGERS;

-- 更新触发器 - 改变原值

DROP TRIGGER IF EXISTS t_update_make_encry;
CREATE TRIGGER t_update_make_encry -- 创建触发器
BEFORE UPDATE ON t_tab1 FOR EACH ROW
BEGIN  -- 要执行的sql list 开始
        IF new.encry IS NOT NULL   THEN
        SET new.encry = to_base64(aes_encrypt(new.encry,'_111'));
        SET new.type = 1;  --  1:密文 0:明文
        END IF;
END;

-- 更新未加密的字段

UPDATE t_tab1 SET encry = '123456', where id = 9;

-- 更新触发器 只加密不改变值 （临时触发器，批量修改数据用）

DROP TRIGGER IF EXISTS t_modify_make_encry;
CREATE TRIGGER t_modify_make_encry 
BEFORE UPDATE ON t_tab1 FOR EACH ROW
BEGIN
        IF new.encry IS NOT NULL AND old.type = 0 THEN
        SET new.encry = to_base64(aes_encrypt(new.encry,'_111'));
        SET new.type = 1;  -- 默认密文
        END IF;
END;

更新触发器 只加密不改变值

UPDATE t_tab1 SET encry = t_tab1.encry where encry != '';

mysql在5的版本添加了触发器的功能。
在了解触发器之前，可以先去网上了解一下SqlServer的触发器。

---

什么是触发器？
触发器是与表事件相关的，由mysql数据库在监控到响应的操作(新增一条数据或修改一条数据)时激活执行的。
在mysql中，我们可以去监听用户的DML语句，然后可以在执行前或执行后去做一些特殊的操作。

触发器能做什么？
1、字段值的格式处理
2、字段值加密
3、总价格字段的计算
4、删除数据后，将这条数据进行一个备份（类似于回收站）
4、记录数据库操作日志(操作前后）

ps. 大家应该可以发现，上面的例子都是在表发生变化时让mysql数据库自己去完成的一些功能。
（这里的变化，主要指执行insert update delete语句时）

语法分析：

CREATE TRIGGER 名称
创建触发器，并且为它取一个名称

{BEFORE|AFTER}  {INSERT|DELETE|UPDATE}  ON 表名

-- 在哪张表添加/删除/修改 的 之前/之后执行

FOR EACH ROW  -- 执行相应的sql

-- 每一行都会执行相应的操作

ps. 触发器的名称是唯一的，千万不要重复。mysql尽管要求每个表唯一，但是建议最好整个库唯一。
每个表的事件每次都只能创建一次。比如说一个tb1表已经创建了一个insert之前的事件，就不能再创建一个Insert之前的事件，

拓展：mysql自定义加密解密函数