How to Pass MySQL Table and Column Names as Stored Routine Arguments

If you are working with or exploring MySQL 5's stored routines you may come across an instance where you would like to pass along a table or table column name as a routine argument. Natively stored routines does not support this behavior but with the help of user variables and prepared statements we can get around this limitation.

MySQL's stored routines comes in two different flavors. There are stored functions and stored routines. We aren't going to go into detail about the differences, but here is most important distinction:

• Stored Functions: Returns a single scalar value, not query results.
• Stored Procedures: Returns query results.

We are going to use a stored procedure in the following demonstration.

Goal

Analyze how using an Indexed Column Prefix could better optimize our queries on table columns for tables that we will specify as procedure arguments before actually adding an index to a table.

Static Query

The following query will determine the number of records in the table, the number of distinct values in the column, and the number of duplicates:

SELECT
    COUNT(*) AS 'Total Rows',
    COUNT(DISTINCT title) AS 'Distinct Values',
    COUNT(*)  - COUNT(DISTINCT title) AS 'Duplicate Values'
FROM node;

The query gives you an estimate of the amount of uniqueness in the title values. The next query will use prefixed values:

SELECT
    COUNT(*) AS 'Total Rows',
    COUNT(DISTINCT LEFT(title,n)) AS 'Distinct Prefix Values',
    COUNT(*)  - COUNT(DISTINCT LEFT(title,n)) AS 'Duplicate Prefix Values'
FROM node;

n = integer (i.e. 5)

The query tells you how the uniqueness characteristics change when you use an n-character prefix of the title value. We can run this query with different values of n to determine an acceptable prefix length.

Note: The tables and columns referenced are found within Drupal 6.

Stored Procedure: Adding Some flexibility

The two queries provide above are great but they can get a little annoying if we have to constantly type them over, and over, and over again to see what character count(prefix) will work best as a prefixed index. So, what we are going to do is generalize the two queries and make a flexible tool using a stored procedure.

Here is our stored procedure:

DROP PROCEDURE IF EXISTS analyze_col_prefix;
DELIMITER //
CREATE PROCEDURE analyze_col_prefix(p_tbl CHAR(64),  p_col CHAR(64), p_chars INT(10))
BEGIN
    -- set user variable
    SET @npfx_query = CONCAT("SELECT COUNT(*) AS 'Total Rows', COUNT(DISTINCT ", p_col,") AS 'Distinct Values', COUNT(*) - COUNT(DISTINCT ", p_col,") AS 'Duplicate Values' FROM ", p_tbl);

    -- prepared statement 1
    PREPARE not_prefixed FROM @npfx_query;
    EXECUTE not_prefixed;

    -- set user variable
    SET @pfx_query = CONCAT("SELECT COUNT(*) AS 'Total Rows', COUNT(DISTINCT LEFT(", p_col,",",p_chars,")) AS 'Distinct Prefix Values', COUNT(*) - COUNT(DISTINCT LEFT(", p_col,",",p_chars,")) AS 'Duplicate Prefix Values' FROM ", p_tbl);

   -- prepared statement 2
    PREPARE prefixed FROM @pfx_query;
    EXECUTE prefixed;

END//
DELIMITER ;

As you can see, the stored routine, analyze_col_prefix, takes three arguments:

1. p_tbl = Name of the table
2. p_col = Name of the column
3. p_chars = Prefix length

Once passed into the procedure we can then reconstruct our query statements using the CONCAT() function and then assigning it to the user variables, @npfx_query and @pfx_query. Once set, the user variables are used within the PREPARE prepated_statement_name FROM query statement which will be executed, EXECUTE prepared_statement_name, to display the respective query results.

Because we are using prepared statements to execute our queries we are confined to using either a static sql expression or an expression stored as a user variable. But because we want out statements to be dynamic, we used user variables.

Execute Stored Procedure

Execute the stored procedure with the following statement:

CALL analyze_col_prefix('node', 'title', 5);

Here is a screen shot of my results.

Resources

• Stored Procedures in MySQL 5.0
• Using Stored Routines (Procedures and Functions)
• MySQL 5: Prepared statement syntax and Dynamic SQL
• SQL Syntax for Prepared Statements