Mysql触发器实现同一个主机两个数据库相同表结构数据同步

一、Mysql触发器创建

1、Mysql触发器语法

CREATE　[DEFINER = { 'user' | CURRENT_USER }]　
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

2、MySQL创建语法中的关键词解释

字段	含义	可能的值
DEFINER=	可选参数，指定创建者，默认为当前登录用户（CURRENT_USER）； 该触发器将以此参数指定的用户执行，所以需要考虑权限问题； DEFINER=‘[email protected]%’	DEFINER=CURRENT_USER
trigger_name	触发器名称，最好由表名+触发事件关键词+触发时间关键词组成；
trigger_time	触发时间，在某个事件之前还是之后；	BEFORE、AFTER
trigger_event	触发事件，如插入时触发、删除时触发； INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发；UPDATE：更新操作触发器，UPDATE操作时触发； DELETE：删除操作触发器，DELETE、REPLACE操作时触发；	INSERT、UPDATE、DELETE
table_name	触发操作时间的表名；
trigger_order	可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（ 如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以 使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数 FOLLOWS：当前创建触发器在现有触发器之后**； PRECEDES：当前创建触发器在现有触发器之前**；	FOLLOWS、PRECEDES
trigger_body	触发执行的SQL语句内容，一般以begin开头，end结尾	begin … end

3、MySQL分隔符（DELIMITER）

MySQL默认使用“;”作为分隔符，SQL语句遇到“;”就会提交。而我们的触发器中可能会有多个“;”符，为了防止触发器创建语句过早的提交，我们需要临时修改MySQL分隔符，创建完后，再将分隔符改回来。使用DELIMITER可以修改分隔符

DELIMITER $
... 	-- 触发器创建语句;
$   	-- 提交创建语句;
DELIMITER ;

3、同步插入 测试案例

创建测试表和触发器

-- 创建测试表
DROP TABLE IF EXISTS `test_material`;
CREATE TABLE `test_material` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `test_code` varchar(100) DEFAULT '' COMMENT '测试编号',
  `test_name` varchar(100) DEFAULT '' COMMENT '测试名称',
  `test_quantity` int(11) DEFAULT '5000' comment '数量:默认5000颗'
) engine=innodb DEFAULT charset=utf8 comment='测试表';

-- 创建触发器
delimiter $
DROP TRIGGER IF EXISTS after_material$
CREATE TRIGGER after_material 
AFTER INSERT ON test1.test_material
FOR EACH ROW
BEGIN
 INSERT test2.test_material(test_code, test_name, test_quantity)
 VALUES(new.test_code, new.test_name, test_quantity);
END$
delimiter ;

测试触发同步效果

INSERT INTO test1.test_material (test_code, test_name, test_quantity)
 VALUES ('533-0000-103', '快速二極體(庫存用完料號作廢 T-0958-8)', 1000);

结果：test1.test_material插入数据的同时，test2.test_material也插入了相同的数据

4、同步更新 测试案例

-- 创建触发器
delimiter $
DROP TRIGGER IF EXISTS update_material$
CREATE TRIGGER update_material 
AFTER UPDATE ON test1.test_material
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.  
  IF @disable_update_trigger_o IS NULL THEN  
    -- 设置禁用触发器标志.  
    SET @disable_update_trigger_o = 1;  
    -- 更新目标表  
    UPDATE test2.test_material AS tm2
	SET tm2.test_name = new.test_name, tm2.test_code = new.test_code
	WHERE tm2.id = old.id;
  END IF;
  -- 恢复禁用触发器标志.  
  SET @disable_update_trigger_o = NULL;
END$
delimiter;

测试触发同步效果

UPDATE test1.test_material AS tm1
SET tm1.test_name = '533-0000-1011', tm1.test_name = '533-0000-1011'
WHERE tm1.id = 2;

结果：test1.test_material更新数据的同时，test2.test_material也更新相同的数据

5、同步删除 测试案例

-- 创建触发器
delimiter $
DROP TRIGGER IF EXISTS delete_material$
CREATE TRIGGER delete_material
AFTER DELETE ON test1.test_material
FOR EACH ROW
BEGIN
-- 检查当前 环境，避免递归.  
  IF @disable_delete_trigger_o IS NULL THEN  
    -- 设置禁用触发器标志.  
    SET @disable_delete_trigger_o = 1;  
    -- 更新目标表  
    DELETE FROM test2.test_material WHERE id = old.Id;
  END IF;
  -- 恢复禁用触发器标志.  
  SET @disable_delete_trigger_o = NULL;
END$
delimiter;

测试触发同步效果

DELETE FROM test1.test_material AS tm1 WHERE tm1.id = 2;

结果：test1.test_material删除数据的同时，test2.test_material也删除相同的数据

-- 查看当前数据库创建的触发器
SELECT * from information_schema.`TRIGGERS`;

-- 删除触发器
DROP TRIGGER after_material;

参考链接：
http://aiezu.com/article/mysql_trigger_syntax.html.
https://www.cnblogs.com/bianxj/articles/9173710.html.