Collections:
STATEMENT_DIGEST() - Statement Digest Hash
How to calculate MySQL statement digest hash using the STATEMENT_DIGEST() function?
✍: FYIcenter.com
STATEMENT_DIGEST(statement) is a MySQL built-in function that normalizes a given MySQL statement into a statement digest and returns the SHA-256 hash as a 64 hexadecimal digits string. For example:
SELECT STATEMENT_DIGEST('SELECT NOW()'); -- +------------------------------------------------------------------+ -- | STATEMENT_DIGEST('SELECT NOW()') | -- +------------------------------------------------------------------+ -- | 2fad55a506c3342fe82629447a3412f5dfb1aaaa20b13be45f1b9c42175da923 | -- +------------------------------------------------------------------+ SELECT STATEMENT_DIGEST('SELECT help_topic_id, name FROM mysql.help_topic'); -- +----------------------------------------------------------------------+ -- | STATEMENT_DIGEST('SELECT help_topic_id, name FROM mysql.help_topic') | -- +----------------------------------------------------------------------+ -- | 7a0ad58a8e461ec08bbc4740055be7c272bf0bb684968e1bf39eafa49fd3c54c | -- +----------------------------------------------------------------------+
Note that multiple MySQL statements may have the same statement digest. In that case, they will have the same statement digest hash. For example:
SET @stmt = 'SELECT * FROM mytable WHERE cola = 10 AND colb = 20'; SELECT STATEMENT_DIGEST_TEXT(@stmt); -- +---------------------------------------------------------+ -- | STATEMENT_DIGEST_TEXT(@stmt) | -- +---------------------------------------------------------+ -- | SELECT * FROM `mytable` WHERE `cola` = ? AND `colb` = ? | -- +---------------------------------------------------------+ SELECT STATEMENT_DIGEST(@stmt); -- +------------------------------------------------------------------+ -- | STATEMENT_DIGEST(@stmt) | -- +------------------------------------------------------------------+ -- | 3bb95eeade896657c4526e74ff2a2862039d0a0fe8a9e7155b5fe492cbd78387 | -- +------------------------------------------------------------------+ SET @stmt = 'SELECT * FROM mytable WHERE cola = 1 AND colb = 2'; SELECT STATEMENT_DIGEST_TEXT(@stmt); -- +---------------------------------------------------------+ -- | STATEMENT_DIGEST_TEXT(@stmt) | -- +---------------------------------------------------------+ -- | SELECT * FROM `mytable` WHERE `cola` = ? AND `colb` = ? | -- +---------------------------------------------------------+ SELECT STATEMENT_DIGEST(@stmt); -- +------------------------------------------------------------------+ -- | STATEMENT_DIGEST(@stmt) | -- +------------------------------------------------------------------+ -- | 3bb95eeade896657c4526e74ff2a2862039d0a0fe8a9e7155b5fe492cbd78387 | -- +------------------------------------------------------------------+
Also note that STATEMENT_DIGEST() validates the given MySQL statement before calculating the hash value. If an invalid statement is provided, you will get an error. For example:
SELECT STATEMENT_DIGEST('I AM NOT A STATEMENT'); ERROR 3676 (HY000): Could not parse argument to digest function: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'I AM NOT A STATEMENT' at line 1".
Reference information of the STATEMENT_DIGEST() function:
STATEMENT_DIGEST(statement): hash Normalizes a given MySQL statement into a statement digest and returns the SHA-256 hash as a 64 hexadecimal digits string. Arguments, return value and availability: statement: Required. The MySQL statement string to be processed. hash: Return value. The hash value of the statement digest. Available since MySQL 4.1.
Related MySQL functions:
⇒ STATEMENT_DIGEST_TEXT() - Calculating Statement Digest
⇐ SHA2() - Calculating SHA-2 Hash
2023-12-14, 238🔥, 0💬
Popular Posts:
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...