一种简单的服务端MySQL–>客户端SQLite单向同步方案

Update: 2016-07-08

发现了这个项目:https://github.com/ptorba/mysql-history
应该就是我想象中的工具,看上去很牛逼的样子


最近遇到个需求,服务端有张表,其中的大部分字段需要同步到客户端,客户端是只读。
查了很多资料,流程不外乎:

  1. 服务端把表的修改记录存起来
  2. 客户端带上版本号,问服务端要修改记录
  3. 客户端把修改记录逐条应用到sqlite中,并更新本地版本号

没有找到现成的通用代码,只能硬着头皮自己实现。
关键就是第一条如何实现,想到至少有三种方案:

  1. 因为我服务端用的django,考虑过用django的post_save
    signal,在其handler里面拿到修改记录,存入xxx_changes表
  2. 自己监控mysql的log,拿到sql操作记录,再存入xxx_changes表
  3. 为那张表写after_insert_trigger/after_update_trigger/after_delete_trigger,取得修改记录,存入xxx_changes表

方案一实现起来应该最简单,因为有django环境,代码写起来会很顺畅,但缺点也很明显,如果绕过应用直接操作数据库,就无法拿到changes了,此外还有个不足之处,bulk insert操作时,django的signal不会触发;
方案二,首先要把对应表的操作过滤出来,还要解析sql语句后作额外处理(因为服务端、客户端的两张表并不是完全同样的结构,无法直接把sql拿过来执行),如果有外键关联的话情况会更复杂,想想还是算了;
方案三,复杂的trigger还真没写过,但衡量三种方案,只有这种最靠谱了。

于是摸索了一下午,总算搞掂,简单记录一下。
假设欲关注的表结构如下:

CREATE TABLE `xxx` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `phonetics` varchar(1024) NOT NULL DEFAULT '',
  `is_freq_used` tinyint(1) NOT NULL DEFAULT '0',
  `pinyin` varchar(255) DEFAULT '',
  `alias` varchar(1024) DEFAULT '',
  `alias_pinyin` varchar(1024) DEFAULT '',
  `desc` mediumtext DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

xxx的操作记录表xxx_changes表结构作如下设计:

CREATE TABLE `xxx_changes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `action` tinyint(11) NOT NULL COMMENT '0-insert,1-update,2-delete',
  `cols` varchar(1024) NOT NULL DEFAULT COMMENT 'separated with |',
  `vals` longtext NOT NULL COMMENT 'separated with |',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `xxx_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

简单说明一下,
action字段有三种取值,0代表insert1代表update2代表delete
cols字段是xxx表实际发生改动的字段列表,用|分隔;
vals是各字段改动后的值,跟cols一一对应,同样用|分隔。字段值中可能本身就含有|,所以还需要对各字段的value进行转义。为了简单起见,直接套用urlencode,即先%–>%25,再|–>%7C,客户端上逆向操作即可还原为本来的value;
time是改动时间,客户端无须关注;
xxx_id是对应的xxx记录的id

after_insert_trigger大致如下:

-- 我是注释行
DROP TRIGGER IF EXISTS xxx_after_insert;    -- 先drop已经存在的同名trigger
DELIMITER $$    -- 将DELIMITER临时改成$$,这样trigger体内的大量分号才不会报错
CREATE TRIGGER xxx_after_insert     -- trigger名称
AFTER INSERT ON xxx_changes     -- 在xxx表发生insert操作之后被触发
FOR EACH ROW
BEGIN

    -- 有两个关键字:NEW和OLD,分别对应改动前的row和改动后的row。
    -- 显而易见,对于insert,只有NEW有意义;对于delete,只有OLD有意义

    -- 既然是insert,那所有字段都发生了变动,所以直接返回所有字段
    SET @cols = "id|name|phonetics|is_freq_used|pinyin|alias|alias_pinyin|desc";

    -- COALESCE函数的作用是返回第一个non-null的值,也就是说如果NEW.phonetics为NULL,就返回空串
    -- 其实将NULL映射为空串并不是很精确,但实际操作过程中是无妨的。如果非要精确反映NULL,
    -- 那就用类似{NULL}这种来标示NULL好了,别忘了你还要对`{}`进行转义。
    SET @phonetics = COALESCE(NEW.phonetics, "");
    SET @pinyin = COALESCE(NEW.pinyin, "");
    SET @alias = COALESCE(NEW.alias, "");
    SET @alias_pinyin = COALESCE(NEW.alias_pinyin, "");
    SET @desc = COALESCE(NEW.desc, "");

    SET @vals = CONCAT(     -- CONCAT是字符串连接函数
        CAST(NEW.id AS CHAR), "|",  -- CAST是转换函数,这里把int转成char,即字符串
        REPLACE(REPLACE(NEW.name, "%", "%25"), "|", "%7C"), "|",    -- 用REPLACE函数进行简单的转义
        REPLACE(REPLACE(@phonetics, "%", "%25"), "|", "%7C"), "|",
        CAST(NEW.is_freq_used AS CHAR), "|",
        REPLACE(REPLACE(@pinyin, "%", "%25"), "|", "%7C"), "|",
        REPLACE(REPLACE(@alias, "%", "%25"), "|", "%7C"), "|",
        REPLACE(REPLACE(@alias_pinyin, "%", "%25"), "|", "%7C"), "|",
        REPLACE(REPLACE(@desc, "%", "%25"), "|", "%7C")
        );
    INSERT INTO `xxx_changes` ( `action`, `xxx_id`, `cols`, `vals`, `time` )    -- 把操作记录存到 xxx_changes表
    VALUES ( 0, NEW.id, @cols, @vals, NOW() );

END $$      -- 上面已经把DELIMITER改成$$了,所以这里语句结束要用$$,而非分号
DELIMITER ; -- 把DELIMITER改回默认的分号

after_update_trigger大致如下:

-- 我是注释行
DROP TRIGGER IF EXISTS xxx_after_update;    -- 先drop已经存在的同名trigger
DELIMITER $$    -- 将DELIMITER临时改成$$,这样trigger体内的大量分号才不会报错
CREATE TRIGGER xxx_after_update     -- trigger名称
AFTER UPDATE ON xxx_changes     -- 在xxx表发生update操作之后被触发
FOR EACH ROW
    BEGIN

        SET @phonetics = COALESCE(NEW.phonetics, "");
        SET @pinyin = COALESCE(NEW.pinyin, "");
        SET @alias = COALESCE(NEW.alias, "");
        SET @alias_pinyin = COALESCE(NEW.alias_pinyin, "");
        SET @desc = COALESCE(NEW.desc, "");

        SET @cols = "";
        SET @vals = "";

        IF(NEW.id != OLD.id) THEN
            SET @cols = concat(@cols, "id");
            SET @vals = concat(@vals, cast(NEW.id AS CHAR));
        END IF;

        IF(NEW.name != OLD.name) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "name");
            SET @vals = concat(@vals, @seperator, REPLACE(REPLACE(NEW.name, "%", "%25"), "|", "%7C"));
        END IF;

        -- 对于可能为NULL的字段,要用<=>替代==
        IF(NOT NEW.phonetics <=> OLD.phonetics) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "phonetics");
            SET @vals = concat(@vals, @seperator, REPLACE(REPLACE(@phonetics, "%", "%25"), "|", "%7C"));
        END IF;

        IF(NOT NEW.is_freq_used <=> OLD.is_freq_used) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "is_freq_used");
            SET @vals = concat(@vals, @seperator, cast(NEW.is_freq_used AS CHAR));
        END IF;

        IF(NOT NEW.pinyin <=> OLD.pinyin) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "pinyin");
            SET @vals = concat(@vals, @seperator, REPLACE(REPLACE(@pinyin, "%", "%25"), "|", "%7C"));
        END IF;

        IF(NOT NEW.alias <=> OLD.alias) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "alias");
            SET @vals = concat(@vals, @seperator, REPLACE(REPLACE(@alias, "%", "%25"), "|", "%7C"));
        END IF;

        IF(NOT NEW.alias_pinyin <=> OLD.alias_pinyin) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "alias_pinyin");
            SET @vals = concat(@vals, @seperator, REPLACE(REPLACE(@alias_pinyin, "%", "%25"), "|", "%7C"));
        END IF;

        IF(NOT NEW.desc <=> OLD.desc) THEN
            SET @seperator = IF(LENGTH(@cols), "|", "");
            SET @cols = concat(@cols, @seperator, "desc");
            SET @vals = concat(@vals, @seperator, REPLACE(REPLACE(@desc, "%", "%25"), "|", "%7C"));
        END IF;

        IF(LENGTH(@cols)) THEN
            INSERT INTO `xxx_changes` ( `action`, `xxx_id`, `cols`, `vals`, `time` ) 
            VALUES ( 1, OLD.id, @cols, @vals, NOW() );  
        END IF;                                                                                   
    END $$
DELIMITER ; -- 把DELIMITER改回默认的分号

after_delete_trigger最简单,大致如下:

-- 我是注释行
DROP TRIGGER IF EXISTS xxx_after_delete;    -- 先drop已经存在的同名trigger
DELIMITER $$    -- 将DELIMITER临时改成$$,这样trigger体内的大量分号才不会报错
CREATE TRIGGER xxx_after_delete     -- trigger名称
AFTER DELETE ON xxx_changes     -- 在xxx表发生delete操作之后被触发
FOR EACH ROW
BEGIN
    INSERT INTO `xxx_changes` ( `action`, `xxx_id`, `cols`, `vals`, `time` ) 
    VALUES ( 2, OLD.id, "", "", NOW() );                                                                                     
END $$
DELIMITER ; -- 把DELIMITER改回默认的分号

需要改进的地方:

对不同的表,需要手动改动这三个触发器的代码,略显繁琐,尤其是after_update_trigger,每个字段需要手动一个一个写。不知道有没有动态遍历所有字段的方案,不过暂时没空深入了。
实在不行的话,写个python脚本,来动态生成以上三个触发器的代码,也是可接受的解决方案,等以后有空再做吧,这里先mark一记。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据