danfengcao / binlog2sql

Parse MySQL binlog to SQL you want
GNU General Public License v3.0
3.32k stars 1.04k forks source link

增加JSON格式解析支持(已附上代码),修复UnicodeDecodeError,增加默认sql_type #44

Open mysqldba-nandy opened 5 years ago

mysqldba-nandy commented 5 years ago
  1. MySQL 5.7新增JSON格式,虽然没有MongoDB支持得那么好,但是用起来已经很方便了。烦请有时间新增对JSON格式的解析支持,谢谢啦~

    想了想,自己动手先实现一下,还请参考:

    import json
    ...
    def process_binlog(self):
    ...
    elif is_dml_event(binlog_event) and event_type(binlog_event) in self.sql_type:
        for row in binlog_event.rows:
            # add self.json
            if self.json:
                for column in binlog_event.columns:
                    # json(column.type) = 245
                    if column.type == 245:
                        value = row['values'][column.name]
                        if isinstance(value, dict):
                            value = json.dumps({fix_object(k): fix_object(v) for k, v in value.items()}, ensure_ascii=False)
                        elif isinstance(value, list):
                            value = json.dumps([fix_object(k) for k in value], ensure_ascii=False)
                        row['values'][column.name] = value
    
            sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, no_pk=self.no_pk,
                                                row=row, flashback=self.flashback, e_start_pos=e_start_pos)
  2. UnicodeDecodeError: 'utf8' codec can't decode byte ... block = platform.system() == 'Windows' and block.decode("gbk") or block.decode("utf-8") https://github.com/danfengcao/binlog2sql/issues/33#issuecomment-449703838,这个`bug`也麻烦修复一下~

  3. 感觉设置默认sql_type更加方便新手调试 self.sql_type = [t.upper() for t in sql_type] if sql_type else ['INSERT', 'UPDATE', 'DELETE']

zj5220924 commented 3 years ago
  1. MySQL 5.7新增JSON格式,虽然没有MongoDB支持得那么好,但是用起来已经很方便了。烦请有时间新增对JSON格式的解析支持,谢谢啦~ 想了想,自己动手先实现一下,还请参考:
import json
...
def process_binlog(self):
    ...
    elif is_dml_event(binlog_event) and event_type(binlog_event) in self.sql_type:
        for row in binlog_event.rows:
            # add self.json
            if self.json:
                for column in binlog_event.columns:
                    # json(column.type) = 245
                    if column.type == 245:
                        value = row['values'][column.name]
                        if isinstance(value, dict):
                            value = json.dumps({fix_object(k): fix_object(v) for k, v in value.items()}, ensure_ascii=False)
                        elif isinstance(value, list):
                            value = json.dumps([fix_object(k) for k in value], ensure_ascii=False)
                        row['values'][column.name] = value

            sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, no_pk=self.no_pk,
                                                row=row, flashback=self.flashback, e_start_pos=e_start_pos)
  1. UnicodeDecodeError: 'utf8' codec can't decode byte ... block = platform.system() == 'Windows' and block.decode("gbk") or block.decode("utf-8") https://github.com/danfengcao/binlog2sql/issues/33#issuecomment-449703838,这个bug也麻烦修复一下~
  2. 感觉设置默认sql_type更加方便新手调试 self.sql_type = [t.upper() for t in sql_type] if sql_type else ['INSERT', 'UPDATE', 'DELETE']

通过import json来处理,带来的一个问题是json中的元素顺序会发生变化