Mysql calling procedure failed when dynamically alter table in it

Question

I want to alter my tables dynamically based on whether the table has specific column.

My database name is summer_cms, and there are over 50 tables in it.

What I want are below:

1. If a table has a column named add_time, then I would like to add a column add_user_id in it.
2. Similarly, I would like to add update_user_id in the table if update_time is found.

I know I should get it down in the process of creating the database schemas, but my database has been built and I have to alter it by need.

So I create a procedure to do it:

CREATE PROCEDURE ALTER_SUMMER_TABLE()
BEGIN
    DECLARE tableName VARCHAR(64);
    DECLARE exitence VARCHAR(64);

    DECLARE ntable INT;    # number of tables
    DECLARE i INT;         # index
    SET i = 0;

    # get the count of table
    SELECT COUNT(DISTINCT(TABLE_NAME)) INTO ntable FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'summer_cms';

    WHILE i < ntable DO
        # select the specific table name into the variable of `tableName`.
        SELECT TABLE_NAME INTO tableName
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'summer_cms'
        AND COLUMN_NAME = 'add_time'
        LIMIT 1 OFFSET i;

        # alter table, but I get error in this clause.
        ALTER TABLE tableName ADD COLUMN `add_user_id` INT NOT NULL DEFAULT 0 COMMENT 'add user id';

        # check if the table has `update_time`
        SELECT TABLE_NAME INTO exitence
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'summer_cms'
        AND TABLE_NAME = tableName
        AND COLUMN_NAME = 'update_time';

        # add `update_user_id` if `update_time` be found.
        IF exitence THEN
            ALTER TABLE tableName ADD COLUMN `update_user_id` INT NOT NULL DEFAULT 0 COMMENT 'update user id';
        END IF;
        SET i = i + 1;
    END WHILE;
END

But I got an error when I call this procedure.

Procedure execution failed
1146 - Table 'summer_cms.tableName' doesn't exist

Dose anyone could tell me what I was missing or wrong? Any help will be appreciated.

Answer 1

There a a few alterations you can make to your procedure to make it more streamlined as well as getting round a few problems.

First using a cursor to select the table names rather than using the two selects your using. Secondly to use a prepared statement to allow you to dynamically set the table name...

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ALTER_SUMMER_TABLE`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tableName VARCHAR(64);
    declare cur cursor for SELECT TABLE_NAME
                             FROM information_schema.COLUMNS
                            WHERE TABLE_SCHEMA = 'summer_cms'
                            AND COLUMN_NAME = 'add_time';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    open cur;

    start_loop: loop
        fetch cur into tableName;
        if (done = 1 )THEN
             LEAVE start_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ', tableName,' ADD COLUMN `add_user_id` INT NOT NULL DEFAULT 0 ');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;

    end loop;

    close cur;
END$$
DELIMITER ;

You could do a few tweaks - only fetch table names where the column doesn't already exist for example.