Is it possible to select entire row in a table and get sort of checksum? I am looking for a way to tell my code to update data only if at least one record has been changed. From perspective of data changes tracking it would help me to size down a number of changes noted in history table. - Thanks.
You can combine the MD5() and CONCAT() functions to generate an MD5 checksum for the row:
SELECT MD5(CONCAT(col1, col2, col3, ...)) as MD5_checksum FROM table;
If one of the columns is nullable, be sure you wrap it in IFNULL(col, ''), as a null will make the result of the CONCAT() also null.
Also be aware that this is not 100% save. If you remove the 1 character from a column and add it as the first character of the next column, the outcome of the CONCAT() and thus of the MD5 hash will be the same.
yes you can use the mentioned code
SELECT ROW_COUNT();
and then use the condition of like if Row_Count()>1 then you can process the data Hope it works
You can check changes by comparing your old checksum value with current checksum.
CHECKSUM TABLE tbl_name
Extending Ivars answer for a more robust MD5 checksum of columns;
If concating a NULL field you will not get any result. If its NULL replace the field with the letter 'A'.
SELEECT MD5(concat(IFNULL(col1, "A"), IFNULL(col2, "A"), IFNULL(col3, "A")))
AS md5_checksum FROM tbl_name;
This one worked very well to me, in order to make sure database structure at all tables are the same as before:
SET SESSION group_concat_max_len = 1000000;
SELECT MD5( GROUP_CONCAT( CONCAT( TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE ) ) ) AS md5 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{your_database_name}';