mysql--How to quickly compare data

In MySQL Ops, the research colleague wants to compare the data on the next two different instances and find out the difference, how do you want to compare each field in addition to the primary key?

In the first scenario, the write program extracts each row of data from two instances for comparison, which is theoretically feasible, but has a longer time to compare.

The second scheme, for each row of data all fields merged, take checksum value, and then according to the checksum value comparison, look at the feasibility, try.

To merge the values of all fields first, choose the Concat function provided by MySQL, and if the CONCAT function contains null values, it will cause the final result to be null, so you need to replace the null value with the Ifnull function, such as:

CONCAT (Ifnull (C1,"), Ifnull (C2,'))

Join the table There are many lines, manually spell a script rather tired, don't worry, you can use INFORMATION_SCHEMA. Columns to handle:

# # Gets the column name of the concatenation string Selectgroup_concat ('ifnull (', column_name,', " '" )') from INFORMATION_SCHEMA. COLUMNS WHERE table_name='table_name';

Let's say we have a test table:

CREATE TABLE t_test01 (    intPRIMARYKEY,    int,     INT )

We can then stitch out the following sql:

SELECT id,md5 (CONCAT (ifnull (ID,"), Ifnull (C1,'), ifnull (C2,'   as md5_value from t_test01

It is easy to find the different rows and the primary key IDs by executing on the two instances and then using the results in beyond compare comparison.

For a large number of tables, the execution of the result set is also very large, compared with a laborious, then try to narrow the result set, you can combine the MD5 values of multiple rows of records to find the MD5 value, if the last MD5 value is the same, then the same rows, if different, the difference is proved, and then the row by row comparison.

Let's say we compare 1000 rows in a group, and if we need to merge the grouped results, we need to use the Group_concat function, and note that in the Group_concat function, the order of the sorted guaranteed merge data is added, SQL is as follows:

SELECTmin(ID) asmin_id,Max(ID) asmax_id,Count(1) asrow_count,md5 (Group_concat (MD5 (CONCAT (ifnull (ID,"'), Ifnull (C1,"'), ifnull (C2,"'),)) ORDER  byID)) asMd5_value fromt_test01GROUP  by(ID Div +)

The result of the execution is:

min_id max_id row_count md5_value0        999         +7d49def23611f610849ef559677fec0c +     1999        +95d61931aa5d3b48f1e38b3550daee08 -     2999        +b02612548fae8a4455418365b3ae611a the     3999        +Fe798602ab9dd1c69b36a0da568b6dbb

When the difference data is small, even if we need to compare tens of millions of data, we can easily according to MIN_ID and max_id to quickly locate the difference between the 1000 data, then the row by line MD5 value comparison, finally found the difference row.

Final Comparison chart:

##=====================================================================##

Ps:

When using Group_concat, the MySQL variable needs to be configured group_concat_max_len , the default value is 1024, and the excess part will be staged.

Reference Link: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

##=====================================================================##

Wishing you a happy Spring festival in advance

mysql--How to quickly compare data