In MySQL, the sys.format_statement() system function reduces the length of a given string/SQL statement to the length stored in the statement_truncate_len configuration option.

This function can be handy for truncating potentially long SQL statements retrieved from Performance Schema tables into a more suitable length (64 characters by default).

Syntax

The syntax goes like this:

sys.format_statement(statement)

Where path is a LONGTEXT value that represents the statement to format/shorten.

Example

Here’s an example to demonstrate:

SET @sql_satement = 'SELECT *
FROM Pets INNER JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId
ORDER BY Pets.PetId, Pets.PetName
FOR XML AUTO, XMLSCHEMA;';

SELECT sys.format_statement( @sql_satement );

Result:

+-------------------------------------------------------------------+
| sys.format_statement( @sql_satement )                             |
+-------------------------------------------------------------------+
| SELECT * FROM Pets INNER JOIN  ... tName FOR XML AUTO, XMLSCHEMA; |
+-------------------------------------------------------------------+

By default, the sys.format_statement() function truncates statements to be no more than 64 characters. In this example, the SQL statement that I passed is much more than that, so it was truncated accordingly.

The sys.format_statement() function replaced the middle part of the statement with an ellipsis.

Passing a Shorter String

If we pass a SQL statement that’s shorter than the amount specified in the statement_truncate_len configuration option, the full SQL statement is returned:

SELECT sys.format_statement( 'SELECT * FROM Pets;' );

Result:

+-----------------------------------------------+
| sys.format_statement( 'SELECT * FROM Pets;' ) |
+-----------------------------------------------+
| SELECT * FROM Pets;                           |
+-----------------------------------------------+

Passing an Empty String

Passing an empty string simply returns an empty string without error:

SELECT sys.format_statement( '' );

Result:

+----------------------------+
| sys.format_statement( '' ) |
+----------------------------+
|                            |
+----------------------------+
1 row in set (0.00 sec)

Change the Resulting Length

We can change the length of the resulting string by changing the value of our statement_truncate_len configuration option:

SET @sys.statement_truncate_len = 32;

Result:

Query OK, 0 rows affected (0.00 sec)

Now when we run the above code, the output is shorter:

SET @sql_satement = 'SELECT *
FROM Pets INNER JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId
ORDER BY Pets.PetId, Pets.PetName
FOR XML AUTO, XMLSCHEMA;';

SELECT sys.format_statement( @sql_satement );

Result:

+---------------------------------------+
| sys.format_statement( @sql_satement ) |
+---------------------------------------+
| SELECT * FROM  ... TO, XMLSCHEMA;     |
+---------------------------------------+

Passing an Empty String

Passing an empty string simply returns an empty string without error:

SELECT sys.format_statement( '' );

Result:

+----------------------------+
| sys.format_statement( '' ) |
+----------------------------+
|                            |
+----------------------------+
1 row in set (0.00 sec)

Passing NULL

Passing NULL returns NULL:

SELECT sys.format_statement( NULL );

Result:

+------------------------------+
| sys.format_statement( NULL ) |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.00 sec)

Omitting the Argument

Omitting the argument altogether results in an error:

SELECT sys.format_statement( );

Result:

ERROR 1318 (42000): Incorrect number of arguments for FUNCTION sys.format_statement; expected 1, got 0