使用flyway升级数据库, 编写可重复执行的增加字段和索引的存储过程

/* 添加字段 */
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_column` $$
CREATE PROCEDURE add_column()
BEGIN
	IF NOT EXISTS( SELECT * FROM  information_schema.columns WHERE table_schema='dbName' AND table_name='tableName' AND column_name='field1')  
	THEN
	ALTER TABLE `dbName`.`tableName` ADD COLUMN `field1` TEXT NULL AFTER `create_time`;
	END IF;
END $$
DELIMITER ; 

CALL add_column;



/* 添加索引 */
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_index` $$
CREATE PROCEDURE add_index()
BEGIN
	IF NOT EXISTS( SELECT * FROM  information_schema.statistics WHERE table_schema='dnName' AND table_name='tableName' AND index_name='idx_user_id')
	THEN
        ALTER TABLE `dbName`.`tableName` ADD INDEX `idx_user_id` (`user_id`);
	END IF;
END $$
DELIMITER ;

CALL add_index;