mysql 修改表结构脚本

参考 http://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists 
$$

DROP FUNCTION IF EXISTS isFieldExisting 
$$

CREATE FUNCTION isFieldExisting (schema_name_IN VARCHAR(100), table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = schema_name_IN 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
    IN schema_name_IN VARCHAR(100),
    IN table_name_IN VARCHAR(100),
    IN field_name_IN VARCHAR(100),
    IN field_definition_IN VARCHAR(100)
)
BEGIN
    SET @isFieldThere = isFieldExisting(schema_name_IN, table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN
        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);


        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END;
$$


-- 测试表
CREATE TABLE `mytbl` (
`c1` INT(11) NULL DEFAULT NULL,
`c2` INT(11) NULL DEFAULT NULL,
`c3` INT(11) NULL DEFAULT NULL
)
ENGINE=MyISAM;

CALL addFieldIfNotExists ('vnap_user', 'mytbl', 'c1_1', 'INT(11) NULL DEFAULT NULL AFTER `c1`');
CALL addFieldIfNotExists ('vnap_user', 'mytbl', 'c2_1', 'INT(11) NULL DEFAULT NULL AFTER `c2`');

CALL addFieldIfNotExists ('vnap_user', 'mytbl', 'c3_1', 'INT(11) NULL DEFAULT NULL AFTER `c3`');