【mysql】mysql触发器使用示例

时间：2021-09-02 05:06:51

mysql触发器

  • 时间点：before/after
  • 触发事件： update/delete/insert
  • 时间点+触发事件：构成一个完整的触发器的触发时机；
  • 一个触发时机最多只能由1个Trigger：如 before-insert最多只能有1个触发器，如果需要多个，需要在1个Trigger内些sql Statement；

old和new

  • insert：只有new关键字可以使用；
  • update: new和old关键字都可以使用；
  • delete: 只有old关键字可以使用；

建表语句 + 触发器 （capacity_pm）

CREATE TABLE `capacity_pm` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `pool_id` char(36) CHARACTER SET utf8 DEFAULT NULL COMMENT '资源池ID',
  `cluster_lv1` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '集群分类',
  `cluster_lv2` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '集群2级分类',
  `update_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新或创建时间',
  `templete_id` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '模板ID',
  `templete_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '模板名称',
  `templete_cpu_core` int(10) unsigned zerofill NOT NULL COMMENT '模板CPU核数',
  `templete_mem_size` double NOT NULL COMMENT '模板内存大小',
  `templete_disk_size` double NOT NULL COMMENT '模板磁盘大小',
  `host_total` int(11) unsigned zerofill DEFAULT NULL COMMENT '主机总数',
  `host_used` int(11) unsigned zerofill DEFAULT NULL COMMENT '主机已分配数量',
  `cpu_core_total` int(11) unsigned zerofill DEFAULT NULL COMMENT 'cpu总核数',
  `cpu_core_free` int(11) DEFAULT NULL,
  `cpu_core_used` int(11) DEFAULT NULL COMMENT 'cpu已分配数量',
  `cpu_core_util` double(10,3) DEFAULT NULL COMMENT 'cpu核数使用占比',
  `mem_total` double DEFAULT NULL COMMENT '内存总空间',
  `mem_free` double DEFAULT NULL,
  `mem_used` double DEFAULT NULL,
  `mem_util` double DEFAULT NULL COMMENT '内存使用占比',
  `disk_total` double DEFAULT NULL,
  `disk_free` double DEFAULT NULL,
  `disk_used` double DEFAULT NULL,
  `disk_util` double DEFAULT NULL COMMENT '磁盘使用占比',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_templete_all` (`pool_id`,`templete_id`) USING BTREE COMMENT '模块ID做完整索引'
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TRIGGER `pm_before_insert_trigger` BEFORE INSERT ON `capacity_pm` FOR EACH ROW begin
set new.cpu_core_total=new.host_total * new.templete_cpu_core;
set new.cpu_core_used=new.host_used * new.templete_cpu_core;
set new.cpu_core_free=new.cpu_core_total - new.cpu_core_used;
set new.cpu_core_util=new.cpu_core_used / new.cpu_core_total;
end;

CREATE TRIGGER `pm_before_update_trigger` BEFORE UPDATE ON `capacity_pm` FOR EACH ROW begin
set new.cpu_core_total=new.host_total * old.templete_cpu_core;
set new.cpu_core_used=new.host_used * old.templete_cpu_core;
set new.cpu_core_free=new.cpu_core_total - new.cpu_core_used;
set new.cpu_core_util=new.cpu_core_used / new.cpu_core_total;
end;

测试1：直接插入数据

INSERT INTO `capacity_pm` (
    pool_id,
    cluster_lv1,
    cluster_lv2,
    update_at,
    templete_id,
    templete_name,
    templete_cpu_core,
    templete_mem_size,
    templete_disk_size,
    host_total,
    host_used
)
VALUES
    (
        '7b8f0f5e2fbb4d9aa2d5fd55466d638h',null,null,now(),
        't003',
        'null',
        '8',
        '100',
        '200',
        '100',
        '20'
    ),
    (
        '7b8f0f5e2fbb4d9aa2d5fd55466d638h',null,null,now(),
        't002',
        'null',
        '16',
        '100',
        '200',
        '40',
        '30'
    ) ON DUPLICATE KEY UPDATE host_total =VALUES(host_total),   host_used =VALUES(host_used);

测试2：insert into table...select from other table;

  1. 待插入的数据：来自于其他表的查询；
  2. 使用 ON DUPLICATE KEY UPDATE批量更新；

创建另一个表，模拟数据来源

CREATE TABLE `capacity_pm_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `pool_id` char(36) CHARACTER SET utf8 DEFAULT NULL COMMENT '资源池ID',
  `cluster_lv1` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '集群分类',
  `cluster_lv2` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '集群2级分类',
  `update_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新或创建时间',
  `templete_id` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '模板ID',
  `templete_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '模板名称',
  `templete_cpu_core` int(10) unsigned zerofill NOT NULL COMMENT '模板CPU核数',
  `templete_mem_size` double NOT NULL COMMENT '模板内存大小',
  `templete_disk_size` double NOT NULL COMMENT '模板磁盘大小',
  `host_total` int(11) unsigned zerofill DEFAULT NULL COMMENT '主机总数',
  `host_used` int(11) unsigned zerofill DEFAULT NULL COMMENT '主机已分配数量',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_templete_all` (`pool_id`,`templete_id`) USING BTREE COMMENT '模块ID做完整索引'
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `capacity_pm_tmp` VALUES ('1', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't001', null, '0000000008', '2000', '1000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('2', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't002', null, '0000000008', '1000', '1000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('3', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't003', null, '0000000008', '1000', '1000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('4', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't004', null, '0000000008', '1000', '2000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('5', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't005', null, '0000000008', '1000', '2000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('6', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't001', null, '0000000008', '1000', '2000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('7', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't002', null, '0000000008', '1000', '2000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('8', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't003', null, '0000000008', '1000', '2000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('9', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't004', null, '0000000008', '1000', '2000', '00000000100', '00000000030');
INSERT INTO `capacity_pm_tmp` VALUES ('10', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't005', null, '0000000008', '1000', '2000', '00000000100', '00000000020');
INSERT INTO `capacity_pm_tmp` VALUES ('11', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't006', null, '0000000008', '1000', '2000', '00000000100', '00000000020');
INSERT INTO `capacity_pm_tmp` VALUES ('12', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't007', null, '0000000008', '1000', '2000', '00000000100', '00000000020');
INSERT INTO `capacity_pm_tmp` VALUES ('13', '7b8f0f5e2fbb4d9aa2d5fd55466d638e', null, null, null, 't00x', null, '0000000008', '1000', '2000', '00000000100', '00000000020');
INSERT INTO `capacity_pm_tmp` VALUES ('14', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't008', null, '0000000008', '1000', '2000', '00000000100', '00000000010');
INSERT INTO `capacity_pm_tmp` VALUES ('15', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't009', null, '0000000008', '1000', '2000', '00000000100', '00000000010');
INSERT INTO `capacity_pm_tmp` VALUES ('16', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't018', null, '0000000008', '1000', '2000', '00000000100', '00000000010');
INSERT INTO `capacity_pm_tmp` VALUES ('17', '7b8f0f5e2fbb4d9aa2d5fd55466d638f', null, null, null, 't019', null, '0000000008', '1000', '2000', '00000000100', '00000000010');
INSERT INTO `capacity_pm_tmp` VALUES ('18', '7b8f0f5e2fbb4d9aa2d5fd55466d638h', null, null, null, 't001', null, '0000000008', '1000', '2000', '00000000100', '00000000010');
INSERT INTO `capacity_pm_tmp` VALUES ('19', '7b8f0f5e2fbb4d9aa2d5fd55466d638h', null, null, null, 't002', null, '0000000008', '1000', '2000', '00000000100', '00000000010');
INSERT INTO `capacity_pm_tmp` VALUES ('20', '7b8f0f5e2fbb4d9aa2d5fd55466d638h', null, null, null, 't003', null, '0000000008', '3000', '2000', '00000000100', '00000000020');

测试插入数据

INSERT INTO `capacity_pm` (
    pool_id,
    cluster_lv1,
    cluster_lv2,
    update_at,
    templete_id,
    templete_name,
    templete_cpu_core,
    templete_mem_size,
    templete_disk_size,
    host_total,
    host_used
) 
SELECT
    pool_id,
    cluster_lv1,
    cluster_lv2,
    update_at,
    templete_id,
    templete_name,
    templete_cpu_core,
    templete_mem_size,
    templete_disk_size,
    host_total,
    host_used
FROM
    capacity_pm_tmp 
ON DUPLICATE KEY UPDATE host_total = VALUES(host_total), host_used=VALUES(host_used);

参考

mysql5.6-trigger官网

标签：

相关文章