1. 建立测试表,插入数据。
use test;
create table t_color (
id int unsigned not null auto_increment primary key,
color varchar(10)
) engine=MyISAM;
insert into t_color (color) values('Black'),('Green'),('Red'),('Blue');
select * from t_color;
2. 建立快照表。
use test;
create table t_color_stg engine=MyISAM as select * from t_color ;
select * from t_color_stg;
3. 建立目标表。
use test;
create table dim_color engine=MyISAM as select * from t_color ;
select * from dim_color;
4. 建立基于快照的CDC转换
说明:
5. 测试 -- 执行转换 -- 查看dim_color表 mysql> select * from dim_color; +----+--------+ | id | color | +----+--------+ | 1 | Black | | 2 | Green | | 3 | Red | | 4 | Blue | +----+--------+ 4 rows in set (0.00 sec) -- 修改数据
delete from t_color where id=3;
update t_color set color='Grey' where id=1;
insert into t_color (color) values('Yellow');
-- 执行转换 -- 查看dim_color表 mysql> select * from dim_color; +----+--------+ | id | color | +----+--------+ | 1 | Grey | | 2 | Green | | 5 | Yellow | | 4 | Blue | +----+--------+ 4 rows in set (0.00 sec) 6. 总结
比较的SQL语句如下:
select 'U' as flag, t2.id as id, t2.color as color
from t_color_stg t1 inner join t_color t2 ON t1.id = t2.id
where t1.color != t2.color
union all
select 'D' as flag, t1.id as id, t1.color as color
from t_color_stg t1 left join t_color t2 ON t1.id = t2.id
where t2.id is null
union all
select 'I' as flag, t2.id as id, t2.color as color
from t_color as t2 left join t_color_stg as t1 ON t2.id = t1.id
where t1.id is null;
结果如下:
+------+----+--------+ | flag | id | color | +------+----+--------+ | U | 1 | Grey | | D | 3 | Red | | I | 5 | Yellow | +------+----+--------+ 3 rows in set (0.00 sec)