When you write stored procedures in MySQL, you sometimes need to generate queries on the fly, for example as you process the result of another query. This is supported using prepared statements. This blog explores how you can take advantage of the
sys schema to simplify the use of dynamic queries.
sys schema includes several stored procedures and functions as well as views to make the database administrator's life easier. One of these is the
execute_prepared_stmt() procedure which can be used to execute a query given as a string. An example is:
mysql> CALL sys.execute_prepared_stmt( -> 'SELECT * FROM world.city WHERE ID = 130'); +-----+--------+-------------+-----------------+------------+ | ID | Name | CountryCode | District | Population | +-----+--------+-------------+-----------------+------------+ | 130 | Sydney | AUS | New South Wales | 3276207 | +-----+--------+-------------+-----------------+------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
Let's take a step back and look at how it works when you execute a dynamic query in MySQL before looking at a more realistic example.
Dynamic Queries and Prepared Statements
A prepared statement is created by providing the query in a string. Optionally, the query can include placeholders, so you can reuse the prepared statement with different parameters. However, here I will look at queries without placeholders.
You prepare the query with the
PREPARE statement. The query can be specified either using a literal string or in a user variable. The above example uses a literal string, but when you work with dynamic queries in a stored procedure, you will assign the query to a user variable.
Once you have prepared the query, you execute it using the
EXECUTE statement. If you need to execute the query several times, you can do this without preparing it again, however typically that is not the case with dynamic queries.
Finally, you should tell MySQL that you are done with the prepared statement, so the memory used to store the statement can be freed. If you do not do this, you will end up using more memory than necessary, and you can potentially run out of prepared statements (the maximum number allowed can be configured with
max_prepared_stmt_count option). You tell MySQL to forget about the prepared statement with the
DEALLOCATE PREPARE statement.
If you combine it all, you have the following sequence of steps to generate and execute the dynamic query:
mysql> SET @sql = CONCAT('SELECT COUNT(*) FROM ', -> sys.quote_identifier('world'), -> '.', -> sys.quote_identifier('city')); Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt_count FROM @sql; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE stmt_count; +----------+ | COUNT(*) | +----------+ | 4079 | +----------+ 1 row in set (0.01 sec) mysql> DEALLOCATE PREPARE stmt_count; Query OK, 0 rows affected (0.00 sec)
In the example, the query is created by concatenating the different parts of the query. The
sys.quote_identifier() function is used to quote the schema and table names. Then the query is executed, and finally deallocated.
This is simple enough but can become tedious, if you execute many dynamic queries, and there is always the chance that you forget to include
DEALLOCATE PREPARE causing higher memory usage than necessary. That is where
sys.execute_prepared_stmt() comes into the picture.
sys.execute_prepared_stmt() procedure was born when Mark Leith and I were working on the
sys schema in preparation to include it into MySQL 5.7. In the end we got tired of the repeated pattern with preparing, executing, and deallocating the statements. So, we decided to create a utility procedure for it.
Building on the above example, consider a stored procedure that takes a schema name as a parameter and goes through all the tables in the schema executing
SELECT COUNT(*) to find the number of rows in each table. An example of a procedure that does this is:
DELIMITER $$ CREATE PROCEDURE row_count(IN in_schema varchar(64) CHARSET utf8mb4) SQL SECURITY INVOKER DETERMINISTIC READS SQL DATA BEGIN DECLARE v_table varchar(64) CHARSET utf8mb4; DECLARE v_sql varchar(271); DECLARE v_done bool DEFAULT FALSE; DECLARE c_tables CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_schema AND TABLE_TYPE = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; OPEN c_tables; c_tables: LOOP FETCH c_tables INTO v_table; IF v_done THEN LEAVE c_tables; END IF; SET v_sql = CONCAT( 'SELECT ', QUOTE(v_table), ' AS TableName, ' 'COUNT(*) AS RowCount FROM ', sys.quote_identifier(in_schema), '.', sys.quote_identifier(v_table) ); CALL sys.execute_prepared_stmt(v_sql); END LOOP; CLOSE c_tables; END$$ DELIMITER ;
The most interesting part of the procedure with respect to this discussion is the loop where the
information_schema.TABLES view is queried to get all tables in the provided schema. Once the table name has been fetched into the
v_table variable, the query is generated in lines 24 to 31 in a very similar way to the previous example. Then the generated query is executed using
sys.execute_prepared_stmt() in line 32.
If you are using dynamic queries, I hope you will find
sys.execute_prepared_stmt() useful. Happy coding.