Shulou(Shulou.com)05/31 Report--

Editor to share with you MySQL quick comparison of data skills example analysis, I hope you will learn something after reading this article, let's discuss it together!

In MySQL operation and maintenance, R & D colleagues want to compare the data on two different instances and find out the differences. What should they do if they need to compare each field except the primary key?

In the first scheme, the program is written to compare each row of data on the two instances, which is theoretically feasible, but the comparison time is longer.

The second scheme is to merge all the fields for each row of data, take the checksum value, and compare it according to the checksum value.

First of all, to merge the values of all fields, choose the CONCAT function provided by MySQL. If the CONCAT function contains null values, the final result will be NULL, so you need to use the IFNULL function to replace null values, such as:

CONCAT (IFNULL (C1), IFNULL (C2))

There are many rows to join the table. It is tiring to spell out a script manually. Don't worry, you can use information_schema.COLUMNS to deal with it:

# # get the concatenated string SELECTGROUP_CONCAT ('IFNULL (', COLUMN_NAME,','''')') FROM information_schema.COLUMNS WHERE TABLE_NAME='table_name' of the column name

Suppose we have a test table:

CREATE TABLE t_test01 (id INT AUTO_INCREMENT PRIMARY KEY, C1 INT, C2 INT)

We can concatenate the following SQL:

SELECTid,MD5 (CONCAT (IFNULL (id,''), IFNULL (C1 recording'), IFNULL (C2 recording'),) AS md5_valueFROM t_test01

After executing on two instances, and then comparing the results with beyond compare, it is easy to find different lines and primary key ID

For a table with a large amount of data, the result set is also very large, which is difficult to compare, so first try to reduce the result set. You can combine the MD5 values of multiple rows of records to calculate the MD5 value. If the final MD5 value is the same, then these rows are the same. If different, it is proved that there is a difference, and then compare row by row according to these rows.

Suppose we compare the grouped results according to a set of 1000 rows. If you need to merge the grouped results, you need to use the GROUP_CONCAT function. Note that you add sorting to the GROUP_CONCAT function to ensure the order of the merged data. SQL is as follows:

SELECTmin (id) as min_id,max (id) as max_id,count (1) as row_count,MD5 (GROUP_CONCAT (MD5 (CONCAT (IFNULL (id,''), IFNULL (C1 recording'), IFNULL (c2 recording') AS md5_valueFROM t_test01GROUP BY (id div 1000)

The execution result is:

Min_id max_id row_count md5_value0 999 1000 7d49def23611f610849ef559677fec0c1000 1999 1000 95d61931aa5d3b48f1e38b3550daee082000 2999 1000 b02612548fae8a4455418365b3ae611a3000 3999 1000 fe798602ab9dd1c69b36a0da568b6dbb

When there are few differences in data, even if we need to compare tens of millions of data, we can easily locate which 1000 pieces of data have differences according to min_id and max_id, and then compare the MD5 values line by line, and finally find the differences.

The final comparison picture:

PS:

When using GROUP_CONCAT, you need to configure the MySQL variable group_concat_max_len, with a default value of 1024, and the excess will be phased.

After reading this article, I believe you have some understanding of "sample Analysis of MySQL Rapid comparison data skills". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.