MySQL手动数据校验+云数据库数据校验

MySQL手动数据校验+云数据库数据校验

 

校验原理

1、将每条数据需要比对的字段拼接成为字符串，字段顺序需要一致。

2、使用md5摘要算法对字符串进行转换，生产该条数据的checksum，大多数情况checksum会比原始字符串短，便于使用

3、百万数据建议使用聚合比对转步骤4，不使用聚合直接转步骤6

4、对多条数据进行聚合，生成对对应checksum，用于批量数据校验，加快校验速度。

5、比对不同数据源中的聚合checksum，找出不一样的批次。

6、逐条比对（不一致批次内的）每一条记录的checksum，找出不一致的数据

7、对不一致的数据进行同步处理

 

数据行checksum

1、使用concat函数将需要比对字符串拼接成checkString

2、使用ifnull函数，因为当数据为null时，concat不生效

3、使用md5函数生成摘要字符串，即checksum

 

select id,username,phone,sex,

  concat_ws(

    ifnull(username,''),

    ifnull(phone,''),

    ifnull(sex,'')

  ) as check_str,

md5(

  concat(

    ifnull(username,''),

    ifnull(phone,''),

    ifnull(sex,'')

  )

)

from t_user;

 

或者：

select id,username,phone,sex,

concat_ws(username,phone,sex) as check_str,

md5(concat_ws(username,phone,sex)

)

from t_user;

 

 

字段拼接小技巧，快速获取字段的拼接sql,注意字段排序，保证顺序一致,

 

select group_concat('ifnull(',column_name,','''')'  order by ordinal_position asc )

from information_schema.columns

where table_schema = 'company' and table_name = 't_user'

 

 

聚合checksum

按照id大小进行分组统计

对单条checksum生成分组聚合的checksum

显示每组的最大最小id和记录数，便于比对和后续处理。

使用div进行分组，根据数据量多少进行调整

如果数据量百亿，可以按照该方法进行多级聚合。

 

select

min(id) as minID,

max(id) as maxID,

count(1) as rowCnt,

md5(

  group_concat(

    md5(

      concat(

        ifnull(username,''),

        ifnull(phone,''),

        ifnull(sex,'')

      )

    )

    order by id asc

  )

) as checksum

 

from t_user

group by (id div 1000)

 

或者

select

min(id) as minID,

max(id) as maxID,

count(1) as rowCnt,

md5(

  group_concat(

    md5(

      concat_ws(username,phone,sex)

    )

    order by id asc

  )

) as checksum

 

from t_user

group by (id div 1000)

 

 

注意：表为空则不需要进行checksum，先判断一下。

id必须是主键，唯一键可能为空。数据判断可能出错。

div的除数不能为0

对于手动校验，使用md5可能不够精确，你也可以使用sha1，sha2(str,hash_length)   支持SHA224，SHA-256，SHA-384和SHA-512

还可以使用pt-table_checksum的算法:

 

COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', username,phone,sex, CONCAT(ISNULL(`username`), ISNULL(`phone`), ISNULL(`sex`)))) AS UNSIGNED)), 10, 16)), 0) AS crc

 

 

==================== 云数据库 校验方法 ==============

例如：阿里云RDS，百度云RDS，腾讯云RDS，华为云RDS等。

 

都有对应的校验工具。可表结构校验，全量数据校验。。