Hash a SQL Row

hash a SQL row?

There are CHECKSUM(*), BINARY_CHECKSUM(*) and CHECKSUM_AGG. They do CRC32 like checkum, but for detecting changes to a row it should be more than enough (you are talking about 1 in 4 billion chances for a false negative collision).

Doing a cryptographic hash using HASHBYTES requires you to construct an expression representing the 'row'.

Generate a hash for a set of rows in sql server

You can use the CHECKSUM_AGG aggregate. it is made for that purpose.

Dynamic row hash based on information_schema for MySQL

We need to set a variable with the dynamic sql so it needs to be done using a procedure. Replace the table name & the excluded columns from the WHERE clause and you should be good to go. This version updates a column called "rowHash" in the same table we are calculating the row hash for but can be modified for other needs.

DROP PROCEDURE IF EXISTS sp_set_dynamic_rowhash;
CREATE PROCEDURE sp_set_dynamic_rowhash()
    SQL SECURITY INVOKER
BEGIN

SET @sql = (SELECT CONCAT('UPDATE TableName SET rowHash = MD5(CONCAT(',GROUP_CONCAT('IFNULL(LTRIM(RTRIM(',column_name,')),"")'), '))')
            FROM information_schema.columns 
            WHERE table_name = 'TableName'
            AND table_schema = 'TableSchema'
            AND column_name NOT IN ('excluded_col_1','excluded_col_2','excluded_col_3', 'Rowhash')
           );
         
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;

Finding the hash value of a row in postgresql

Cast the row to text and use md5 to make a hash:

SELECT
    md5(CAST((f.*)AS text))
FROM
    foo f;

---

Related Topics