MySQL Q & amp; A about checksum table

MySQL Q & A talked about whether checksum table can be used to verify data consistency before and after the logical backup. Some interesting problems are found, such as different data insertion sequence, different table engines, and different operating system bits. Whether the insert order of www.2cto.com varies. We know that the full table scan can have many orders. Especially when the delete action appears in the table, the logical export operation is performed and then the other table is imported, the full table scan results of the two tables may be different. The logic for calculating the return value of Checksum table is roughly as follows: Cpp Code ha_checksum crc = 0; foreach (row in table) {row_crc = get_crc (row); crc + = row_crc;} return crc; it can be seen that as long as the total number of rows has the same content, it is irrelevant to the order of reading rows. From this logic, we can also draw several conclusions: www.2cto.com 1) has nothing to do with the engine used, that is, even if the master and slave nodes do not use the same engine, checksum can also be used for inspection. Although InnoDB has hidden rows, ignore them here. 2) It is irrelevant to whether an index exists. Row_crc is calculated only based on the row data, excluding the index data. That is to say, if the data in the two tables is the same, the table structure (the column content and sequence are the same), the operating system is the same, and the MySQL version is the same, the checksum result can be ensured. In this section, we will discuss whether the order of the "different" www.2cto.com fields varies. When row_crc is calculated in a row, each field is calculated in sequence. However, the result of the previous field is used as the input to calculate the next value. Switch (f-> type () {case MYSQL_TYPE_BLOB: case MYSQL_TYPE_VARCHAR: case MYSQL_TYPE_GEOMETRY: case MYSQL_TYPE_BIT: {String tmp; f-> val_str (& tmp ); row_crc = my_checksum (row_crc, (uchar *) tmp. ptr (), tmp. length (); break;} default: row_crc = my_checksum (row_crc, f-> ptr, f-> pack_length (); break;} the field order affects the result. Whether the length of a field varies. Even if the same content is displayed, different checksum values may be obtained. From the above calculation of the crc of each field, if it is a variable-length field (such as varchar), it is not affected because it is used to calculate the actual length. For example, changing the varchar (20) field of a table to varchar (25) does not change the value of checksum. If you change char (20) to char (25) or int to bigint, the checksum is changed. The number of digits in the operating system is different because the returned value is unsigned long, so we are worried about the overflow of 32-bit and 64-bit machines. Fortunately, in the calculation process, ha_myisam is directly defined as uint32, and is converted to unsigned long only when it is returned, so there is no impact. The problem of different character sets has always been vague. It is actually related to the input character set. However, there is a positive conclusion: If the unhex () value of the fields in the table is the same, the obtained checksum is the same.