Using MySQL 8.0 Statement Digest and Digest Text Functions for SQL Summarization

The article introduces MySQL 8.0's newly added functions statement_digest() and statement_digest_text() , which compute a SHA2 hash of a SQL statement and return its normalized text, respectively. These utilities simplify the classification of similar queries, especially when analyzing slow‑log output.

By filtering a slow‑log with mysqldumpslow -s c -t 10 -g 'order by' , the author shows three different queries that differ only in constant values. Using statement_digest_text() on each query produces the same digest text where the constants are replaced by "?", demonstrating that the functions abstract away variable parts of the statement.

The two functions are described as follows:

statement_digest() : returns the SHA2 hash of the normalized statement.

statement_digest_text() : returns the normalized statement text itself.

Usage Scenario 1 – Performance Schema : After enabling the statements consumer with call sys.ps_setup_enable_consumer('statements'); , the sys.statement_analysis table can be queried using the digest to retrieve execution count, total latency, rows examined, and other metrics for a specific query pattern.

SELECT * FROM sys.`statement_analysis`
WHERE digest = statement_digest('select * from p1 where id > 1000 order by rand() limit 2')\G

Usage Scenario 2 – Query Rewrite Plugin : The digest text can be used to define rewrite rules that automatically transform statements. For example, a rule replaces delete from p1 where id = 1000 with delete from p1 where id = -1 by inserting the corresponding statement_digest_text() values into the query_rewrite.rewrite_rules table.

INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database)
VALUES (statement_digest_text('delete from p1 where id = 1000'),
        statement_digest_text('delete from p1 where id = -1'),
        'ytt');
CALL query_rewrite.flush_rewrite_rules();

The rewritten query is then executed, and MySQL logs a warning indicating that the original statement was rewritten by the plugin.

In summary, MySQL 8.0's statement digest functions provide a convenient way to group, analyze, and rewrite SQL statements, making performance diagnostics and query management considerably easier than previous manual methods.