AnySQL.net

    在MySQL中建立实体化视图日志表, 在这里基表表名是T_MVLOG, 主键字段是COL1. 日志表结构如下:

CREATE TABLE MLOG$_T_MVLOG
(
   SEQUENCE$$ BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   DMLTYPE$$ CHAR,
   COL1 BIGINT
);

    Oracle中的实体化视图是用触发器来实现的, 不同的是早期版本是用Internal Trigger(但还可以在Trigger$表中查到), 而在8i及以后的版本中, 则变为Kernel Level Trigger(在Trigger$中是找不到了). 我们现在在MySQL中手工建立触发器, 说不定以后MySQL真的也引入实体化视图功能呢? 也在Kernel级别来实现呢? 发现建立触发器的语句好象Oracle的PL/SQL.

DELIMITER |

CREATE TRIGGER TLOG$_I_T_MVLOG BEFORE INSERT ON T_MVLOG
  FOR EACH ROW BEGIN
    INSERT INTO MLOG$_T_MVLOG (DMLTYPE$$,COL1) VALUES ('I',NEW.COL1);
  END;
|

CREATE TRIGGER TLOG$_U_T_MVLOG BEFORE UPDATE ON T_MVLOG
  FOR EACH ROW BEGIN
    IF (NEW.COL1 = OLD.COL1) THEN
      INSERT INTO MLOG$_T_MVLOG (DMLTYPE$$,COL1) VALUES ('U',OLD.COL1);
    ELSE
      INSERT INTO MLOG$_T_MVLOG (DMLTYPE$$,COL1) VALUES ('D',OLD.COL1);
      INSERT INTO MLOG$_T_MVLOG (DMLTYPE$$,COL1) VALUES ('I',NEW.COL1);
    END IF;
  END;
|

CREATE TRIGGER TLOG$_D_T_MVLOG BEFORE DELETE ON T_MVLOG
  FOR EACH ROW BEGIN
      INSERT INTO MLOG$_T_MVLOG (DMLTYPE$$,COL1) VALUES ('D',OLD.COL1);
  END;
|

DELIMITER ;

    看看触发器是否工作正常吧:

mysql> INSERT INTO T_MVLOG VALUES (1000,'LOU',100.10,'FANGXIN');
Query OK, 1 row affected (0.07 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM MLOG$_T_MVLOG;
+------------+-----------+------+
| SEQUENCE$$ | DMLTYPE$$ | COL1 |
+------------+-----------+------+
|          1 | I         | 1000 |
+------------+-----------+------+
1 row in set (0.00 sec)

    运行refresh_mysql.pl, 将数据从MySQL中复制到Oracle中, 分别在MySQL和Oracle查询日志表和目标表:

mysql> select * from mlog$_t_mvlog;
Empty set (0.00 sec)

SQL> select * from t_mvlog;

COL1 COL2       COL3 COL4
---- ---- ---------- ----------
1000 LOU       100.1 FANGXIN

    继续检查这些触发器, 在Update时能不能正常工作:

mysql> update t_mvlog set col1='1001';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mlog$_t_mvlog;
+------------+-----------+------+
| SEQUENCE$$ | DMLTYPE$$ | COL1 |
+------------+-----------+------+
|          2 | D         | 1000 |
|          3 | I         | 1001 |
+------------+-----------+------+
2 rows in set (0.00 sec)

    DELETE的结果就不写在这儿了, 要不太长了.