Thursday, July 8, 2010

[Level 3] Maintain MySQL schema.

If you want to maintain MySQL with more flexible,
you need a procedure to do this for you.
(especially you want to manage schema upgrade patch...)
Therefore I write a procedure for this purpose, please refer to following code:

-- Usage: sp_altertable('database','table','column','add/drop/modify', 'type');
-- ex. mysql> call sp_altertable('test','mytab','mycol','add', 'varchar(2)');
-- ex. mysql> call sp_altertable('test','mytab','mycol','drop', '');
delimiter //
DROP PROCEDURE IF EXISTS sp_altertable//
CREATE PROCEDURE sp_altertable(in tableschema varchar(32), in tablename varchar(32), in columnname varchar(32), in action varchar(8), in ddl_statement varchar(128))
BEGIN
    set @sDDL='';
    select concat('ALTER TABLE ', tableschema, '.', tablename, ' ', action, ' ', columnname,' ', ddl_statement) into @sDDL;
    -- select @sDDL;
    prepare stmt_name from @sDDL;
    IF action='add' AND NOT EXISTS(
        SELECT * FROM information_schema.COLUMNS
        WHERE COLUMN_NAME=columnname AND TABLE_NAME=tablename AND TABLE_SCHEMA=tableschema
        ) THEN
            execute stmt_name;
    ELSEIF action='drop' AND EXISTS(
        SELECT * FROM information_schema.COLUMNS
        WHERE COLUMN_NAME=columnname AND TABLE_NAME=tablename AND TABLE_SCHEMA=tableschema
        ) THEN
            execute stmt_name;
    ELSEIF action='modify' THEN
        execute stmt_name;
    END IF;
END;
//
delimiter ;

Wish this helps.
regards,
Stanley Huang