mysql创建TRIGGER触发器实现相同服务器下不同库的表数据同步

实现功能：mysql创建INSERT|UPDATE|DELETE触发器实现相同服务器下不同库的表数据同步。比如table1表的数据有insert|update|delete操作，便同步到sync表中。然后再根据sync表中state（同步状态）和oper（操作方式）字段的状态将sync表中数据insert|update|delete到最终需要同步的业务表中（这里涉及到跨级数据同步）。

INSERT Code

#tri_afterinsert_on_camera
DROP TRIGGER IF EXISTS tri_afterinsert_on_camera;
DELIMITER $
CREATE TRIGGER tri_afterinsert_on_camera AFTER INSERT ON CAMERA FOR EACH ROW 
BEGIN
	declare id bigint(20);
	declare intelligentarea_id bigint(20);
	declare hierarchy varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci;
	set id=new.id;
	set intelligentarea_id=new.intelligentarea_id;
	set hierarchy=new.hierarchy;
	INSERT INTO sync.camera(id,intelligentarea_id,hierarchystate,state,oper) VALUES(id,intelligentarea_id,hierarchy,,0,'add');
END$
DELIMITER ;
COMMIT;

注意declare和set

DELETE Code

#tri_afterdelete_on_camera
DROP TRIGGER IF EXISTS tri_afterdelete_on_camera;
DELIMITER $
CREATE TRIGGER tri_afterdelete_on_camera AFTER DELETE ON CAMERA FOR EACH ROW 
BEGIN
	INSERT INTO sync.camera(id,intelligentarea_id,hierarchy,state,oper) VALUES(old.id,old.intelligentarea_id,old.hierarchy,0,'delete');
END$
DELIMITER ;
COMMIT;

注意没有declare和set，在INSERT的字段传值中有old

INSERT Code

#tri_afterupdate_on_camera
DROP TRIGGER IF EXISTS tri_afterupdate_on_camera;
DELIMITER $
CREATE TRIGGER tri_afterupdate_on_camera AFTER UPDATE ON CAMERA FOR EACH ROW 
BEGIN
	declare id bigint(20);
	declare intelligentarea_id bigint(20);
	declare hierarchy varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci;
	set id=new.id;
	set intelligentarea_id=new.intelligentarea_id;
	set hierarchy=new.hierarchy;
	INSERT INTO sync.camera(id,intelligentarea_id,hierarchy,state,oper) VALUES(id,intelligentarea_id,hierarchy,0,'update');
END$
DELIMITER ;
COMMIT;

注意declare和set