mysql触发器

mysql触发器是一种在表上自动执行的存储过程，当表上的特定事件（例如插入、更新或删除）发生时触发。触发器可以用于实现数据验证、日志记录、派生数据等功能。这是触发器的功能简单介绍。

本次演示环境介绍，将各分表的数据汇总到总表中，查询时只需要查询总表即可。

本次测试是在mysql5.7版本演示的，目前此版本触发器还不支持在从库上监听从库同步表的变化，意思是如果监控的表是从主库同步过来的，那么可能会不起作用，我测试没起作用

分表：user1、user2、user3表结构完全相同

[root@mysql.sock][a]>show tables;
+-------------+
| Tables_in_a |
+-------------+
| user1       |
| user2       |
| user3       |
| users       |
+-------------+
[root@mysql.sock][a]>show create table user1\G;
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
​
ERROR: 
No query specified
​
[root@mysql.sock][a]>

总表：users和分表结构也相同

[root@mysql.sock][a]>show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)
​
ERROR: 
No query specified
​
[root@mysql.sock][a]>

将分表user1、user2、user3的数据全部汇总到users中，每个表需要创建三个触发器，分别是insert、update、delete三种触发器，以user1为例

insert触发器

[root@mysql.sock][a]>create trigger user1insert after insert on user1 for each row insert into a.users(id,name) values(new.id,new.name);

这里的new是关键字，表示分表中的新数据

update触发器

[root@mysql.sock][a]>create trigger user1_update after update on user1 for each row update a.users set name=new.name where id=old.id;

同样这里old表示总表中的旧数据

delete触发器

[root@mysql.sock][a]>create trigger user1_delete after delete on user1
for each row delete from a.users where id = OLD.id;

查看触发器

*************************** 1. row ***************************
             Trigger: user1_insert
               Event: INSERT
               Table: user1
           Statement: insert into a.users(id,name) 
  values(new.id,new.name)
              Timing: AFTER
             Created: 2023-11-08 17:46:04.28
            sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci
*************************** 2. row ***************************
             Trigger: user1_update
               Event: UPDATE
               Table: user1
           Statement: update a.users set name=new.name where id=old.id
              Timing: AFTER
             Created: 2023-11-08 18:14:57.95
            sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci
*************************** 3. row ***************************
             Trigger: user1_delete
               Event: DELETE
               Table: user1
           Statement: DELETE FROM users WHERE id = OLD.id
              Timing: AFTER
             Created: 2023-11-08 18:10:15.33
            sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci
​
[root@mysql.sock][a]>

删除触发器

[root@mysql.sock][a]>drop trigger user1_insert;
Query OK, 0 rows affected (0.00 sec)
​
[root@mysql.sock][a]>