MySQL快速比较数据库表数据

1. 前言

有时需要比较MySQL不同数据库间结构相同的表数据是否相同，例如在测试环境与生产环境之间比较，或多个测试环境之间比较，以下提供一种通用的快速比较方式。

2. 比较方法

对于需要比较数据的数据库表，将每行中关心的字段进行拼接，计算其HASH值；再根据主键或其他字段对每行的HASH值进行排序，并将每行的HASH值拼接，对拼接结果计算最终的HASH值。

在比较MySQL不同数据库间结构相同的表数据是否相同时，比较以上HASH值即可。若最终的HASH值不同，则说明数据库表中的数据不同；若最终的HASH值相同 ，则说明数据库表中的数据大概率相同。

3. 使用的MySQL函数

3.1. HASH函数

参考 https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html ，MySQL提供了MD5()、SHA1()、SHA2()等函数用于计算HASH。

MD5的碰撞概率比SHA1高，SHA2计算结果长度至少为224，不便于比较。因此选择SHA1()函数计算HASH，其结果长度为40。

以下语句及执行结果如下：

select sha1('a');
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8

3.2. 拼接同一行中的字段

参考 https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat ，使用CONCAT()函数可拼接字段，当某个字段为NULL时，返回NULL。因此不能使用CONCAT()函数进行拼接。

以下语句及执行结果如下：

select concat('1',null,'2');
null

参考 https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat-ws ，使用CONCAT_WS()函数可以拼接字段，功能与CONCAT()函数类似，可以使用第1个参数指定剩余参数间的分隔符。当参数1为NULL时，结果也为NULL。

CONCAT()函数不会跳过空字符串’’，但会跳过参数1之后的NULL值。

以下语句及执行结果如下：

select concat_ws(':', '1', '2', '3');
1:2:3

select concat_ws(':','1',null,'2',null,null,'3');
1:2:3

若某个需要拼接的字段为二进制格式，可使用HEX()、TO_BASE64()等函数对其进行编码，使其变为字符串格式。

以下语句及执行结果如下：

select hex(0x0a);
0A

select to_base64(0x0a);
Cg==

3.3. 拼接不同行的数据

参考 https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html#function_group-concat ，使用GROUP_CONCAT()函数，可以将一组非空值拼接，返回一个字符串。当没有非空值时，返回NULL。

在GROUP_CONCAT()函数中，可以使用ORDER BY对结果排序。

在进行拼接时，默认使用“,”作为分隔符，可以使用SEPARATOR 'xxx’指定分隔符。

GROUP_CONCAT()函数的结果会被截取为group_concat_max_len系统变量指定的长度，其默认值为1024。即默认情况下GROUP_CONCAT()函数生成的字符串长度不超过1024。

参考 https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_group_concat_max_len ，group_concat_max_len系统变量在32位平台最大值为4294967295，在64位平台最大值为18446744073709551615。

按照每一行包含40个字节的SHA1值，及一个分隔符计算，32位平台的最大值4294967295最多支持超过4亿行数据的SHA1值拼接。

执行以下语句，可用于查询及修改当前使用的group_concat_max_len系统变量。

select @@group_concat_max_len;

SET @@SESSION.group_concat_max_len = 4294967295;

可以将concat_ws()与group_concat()函数一起使用，使用ORDER BY指定group_concat()函数拼接时的排序字段，使用SEPARATOR指定其分隔符。

以下语句及执行结果如下：

select group_concat(r.a order by b SEPARATOR ':') from
(
select concat_ws(':', 'a', '2') as a,'b2' as b
union
select concat_ws(':', 'a', '3') as a,'b3' as b
union
select concat_ws(':', 'a', '1') as a,'b1' as b
) as r;
a:1:a:2:a:3

4. 测试数据

4.1. 测试数据库表

创建以下数据库表用于测试。

CREATE TABLE test_table_concat (
  id varchar(20) NOT NULL,
  int_value int(11) NOT NULL,
  decimal_value decimal(18,2) NOT NULL,
  create_time datetime(3) NOT NULL,
  update_time datetime(3) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

4.2. 生成测试数据

生成以下数据用于测试。

insert into test_table_concat(id,int_value,decimal_value,create_time,update_time) value (uuid_short(), ROUND((rand() * 100) % 100), ROUND((rand() * 100) % 100, 2), now(), now());

5. 快速比较数据库表数据的SQL脚本

为了快速获取目标数据库表中的字段名拼接结果，可以从information_schema.COLUMNS查询字段名称，使用group_concat()函数拼接，示例语句如下：

以下语句及执行结果如下：

select group_concat(column_name order by ordinal_position) from information_schema.COLUMNS where TABLE_SCHEMA='testdb' and table_name='test_table_concat';
id,int_value,decimal_value,create_time,update_time

以下为快速比较数据库表数据的SQL脚本，对上述测试数据库表进行操作。

在执行以下脚本之前，需要先将group_concat_max_len系统参数设置为足够大的值，示例如下：

SET @@SESSION.group_concat_max_len = 4294967295;

以下脚本中除了计算目标数据库表的总HASH值之外，也会获取字段总长度及总行数，便于观察数据差异。

select sha1(group_concat(sha1(r1.content) order by r1.id)) as hash, sum(length(r1.content)) as sum_length, count(r1.content) as total_count
from
(
select concat_ws(':',id,int_value,decimal_value,create_time,update_time) as content, id from test_table_concat
) as r1;

执行结果示例：

| hash                                     | sum_length | total_count |
+------------------------------------------+------------+-------------+
| 2ee683441ca43c0aa41a52a757decc4f2ebc2032 |        221 |           3 |

为了便于在不同环境的数据库之间进行对比，可在SQL脚本中写入预期的总HASH值、总长度及总数量，与实际值进行比较，使比较结果更直观，示例如下：

select r2.hash,(case when r2.hash='2ee683441ca43c0aa41a52a757decc4f2ebc2032' then '相符-HASH值' else '不相符-HASH值！' end) as hash_check,
r2.sum_length,(case when r2.sum_length=221 then '相符-总长度' else '不相符-总长度！' end) as length_check,
r2.total_count,(case when r2.total_count=3 then '相符-总数量' else '不相符-总数量！' end) as count_check
from (
select sha1(group_concat(sha1(r1.content) order by r1.id)) as hash, sum(length(r1.content)) as sum_length, count(r1.content) as total_count
from
(
select concat_ws(':',id,int_value,decimal_value,create_time,update_time) as content, id from test_table_concat
) as r1
) as r2;

执行结果示例：

| hash                                     | hash_check  | sum_length | length_check | total_count | count_check |
+------------------------------------------+-------------+------------+--------------+-------------+-------------+
| 2ee683441ca43c0aa41a52a757decc4f2ebc2032 | 相符-HASH值  |        221 | 相符-总长度   |           3 | 相符-总数量  |

若比较结果不一致，可以生成每行数据的HASH值，根据主键或其他字段排序，人工检查差异数据。

如执行以下SQL语句：

select sha1(concat_ws(':',id,int_value,decimal_value,create_time,update_time)),id from test_table_concat order by id

得到以下数据，可通过人工检查差异数据：

ecfa4ba762407c2544daf0aa483ddc614a1ba7c6	98984592530210830
a3bf188f45615ff722ad70672568f924a4c95374	98984592530210831
290f3f3bdd8f856ea531f71664e23324d3d11bd4	98984592530210832