6

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
virrion
  • 416
  • 1
  • 5
  • 18
  • How can we know if a record has been changed? – sagi Feb 04 '16 at 09:14
  • one way is : you have to store prev record somewhere and check it with latest database – Monty Feb 04 '16 at 09:23
  • Do the update. Then use `$pdo->rowCount()` or `mysqli_affected_rows()` to see if any rows were updated. If not, don't update the history table. – Barmar Feb 04 '16 at 09:37
  • Sagi - you run the checksum on the columns and it should match your previously stored checksum - if its change then its been tampered with. Also use a key in addition to columns that prevents users manipulating and generating their own checksum that would pass this test. – PodTech.io Apr 19 '17 at 13:52

5 Answers5

17

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% safe. 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.

xxfelixxx
  • 6,512
  • 3
  • 31
  • 38
Ivar
  • 6,138
  • 12
  • 49
  • 61
  • 4
    You can get around the transposed-character limitation by using `CONCAT_WS` to insert a comma (or something) between columns, so that `foo | xbar` = `foo,xbar` and `foox | bar` = `foox,bar`. – MatrixManAtYrService Oct 22 '18 at 20:31
  • edit ^ it's not transposition, but translation that the included delimiter fixes. – MatrixManAtYrService Oct 22 '18 at 21:32
  • Depending on your usages, MD5 may not be suitable. From the MySQL docs itself: [Exploits for the MD5 and SHA-1 algorithms have become known. You may wish to consider using another one-way encryption function described in this section instead](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html) – Lovethenakedgun Mar 09 '23 at 00:42
5

You can check changes by comparing your old checksum value with current checksum.

CHECKSUM TABLE tbl_name
Rogin Thomas
  • 672
  • 8
  • 17
4

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;
PodTech.io
  • 4,874
  • 41
  • 24
  • 1
    Saw your comment and already updated my answer accordingly. Thanks for the heads up. :) – Ivar Apr 19 '17 at 13:50
2

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}';
Dennis Schwartz
  • 21
  • 1
0

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

Sparsh Agarwal
  • 19
  • 9
  • He's not trying to find out if his update changed anything, he wants to know if his new data is different from the old data before he makes an update. – Barmar Feb 04 '16 at 09:32
  • Actually, this may be the right answer. Do the `UPDATE`, see if `ROW_COUNT() > 0`. If not, don't update the history table. – Barmar Feb 04 '16 at 09:35
  • ya I also thought same – Sparsh Agarwal Feb 04 '16 at 09:36
  • But if the query can update a variable number of rows based on the `WHERE` condition, it may not be easy to tell which were updated and need to be put in history. Also, if history is supposed to contain the old row, it will be too late. – Barmar Feb 04 '16 at 09:38
  • yes but depends on him wht actually he wants to do with it – Sparsh Agarwal Feb 04 '16 at 10:05