Menu
Currently I'm manually creating a string where I concatenate all the values in each row in my table. I'm hashing this string for each row to get a hash value for the current values (/status) of the row, which I'm later is using to determine if the row has changed.
Instead of doing this manually, is there an build-in way i mySQL to get a unique hash value for each row?
695
This is how it can be done via a select statement: SELECT Pk1 ,ROW_NUMBER() OVER ( ORDER BY Pk1 ) 'RowNum' ,(SELECT hashbytes('md5', ( SELECT Pk1, Col2, Col3 FOR XML raw ))) 'HashCkSum' FROM [MySchema]. [MyTable]; where Pk1 is the Primary Key of the table and ColX are the columns you want to monitor for changes.
MD5() function MySQL MD5() Calculates an MD5 128-bit checksum for a string. The value is returned as a binary string of 32 hex digits, or NULL if the argument was NULL. The return value can, for example, be used as a hash key. A string whose MD5 value is to be calculated.
You can conveniently get a hash of the string directly in the table, using the md5 function directly on the column. MD5 is great for simple purposes of generating a smaller token for uniqueness. But you are correct that it should no longer be used for security purposes.
The MySQL SHA1() function is used for encrypting a string using the SHA-1 technique. The SHA1 stands for secure hash algorithm and it produces a 160-bit checksum for a user inputted string. The MySQL SHA1() function returns NULL if the string passed as an argument is a NULL string.
you could do something like
SELECT MD5(concat(field1, field2, field3, ...)) AS rowhash
but you can't get away from listing which fields you want, as concat(*) is not an option (syntax error).
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
