MySQLはトリガを作成して統合ホストの下の2つのデータベースのテーブル同期を実現する

                 
create database a;
create database b;

use a
create table table1(id int, val int);

use b
create table table2(id int, val int);

    

use a

DELIMITER //
CREATE TRIGGER tr_Insert_t1
  AFTER INSERT ON table1
FOR EACH ROW
BEGIN
  --           .
  IF @disable_trigger IS NULL THEN
    --          .
    SET @disable_trigger = 1;
    --      
    INSERT INTO
      b.table2
    VALUES
      (new.id, new.val);
    --          .
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

use b

DELIMITER //
CREATE TRIGGER tr_Insert_t2
  AFTER INSERT ON table2
FOR EACH ROW
BEGIN
  --           .
  IF @disable_trigger IS NULL THEN
    --          .
    SET @disable_trigger = 1;
    --      
    INSERT INTO
      a.table1
    VALUES
      (new.id, new.val);
    --          .
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

     ：

mysql> use b
Database changed
mysql> insert into table2 values(2,2);
Query OK, 1 row affected (0.01 sec)

mysql> use a
Database changed
mysql> insert into table1 values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+------+------+
| id   | val  |
+------+------+
|    2 |    2 |
|    1 |    1 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from b.table2;
+------+------+
| id   | val  |
+------+------+
|    2 |    2 |
|    1 |    1 |
+------+------+
2 rows in set (0.00 sec)

delete   update   ,                  ,    
        
            .
   
delete       ，   delete      
update      ，   update      

       @disable_trigger   
      ，        
     
SET @disable_trigger = 1;

         ，     。

原文住所:http://zhidao.baidu.com/link?url=e0CwvF_BShuLu_uSJ7VktwOOSYRxASOVu0fy0B6CYJCp6Ra9as_opFG-zlW0nBLXPoCg7zj_uLRHgrw0vqktwq