总结:
在执行flyway时,如果当前环境中已有一些数据库表和数据,
若想基于这个环境进行升级,先要执行./flyway baseline,然后在执行./flyway migrate
如果执行失败,那么可以在修复后,先执行./flyway repair ,在./flyway migrate
删除所有的执行计划 ./flyway clean
查看计划执行的情况 ./flyway info
写flyway的sql脚本时需要注意的是:
在删除一个表时,最好先判断这个表是否存在,以免删除不存在的表执行计划失败,如下:
DROP TABLE IF EXISTS `warning_repair_statistic`;
向一个表中添加索引时,也要先判断索引是否存在
DROP PROCEDURE IF EXISTS del_idx;
DELIMITER $$
CREATE PROCEDURE del_idx(IN p_tablename VARCHAR(200), IN p_idxname VARCHAR(200))
BEGIN
DECLARE str VARCHAR(250);
SET @str=CONCAT(' drop index ',p_idxname,' on ',p_tablename);
SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE table_name=p_tablename AND index_name=p_idxname ;
IF @cnt >0 THEN
PREPARE stmt FROM @str;
EXECUTE stmt ;
END IF;
END $$
DELIMITER ;
--
-- add server_warning_repaired table index
--
call del_idx('server_warning_repaired','domain_level_time_idx');
alter table server_warning_repaired add index domain_level_time_idx(domain_moid(10), level, start_time);
表的创建方法如下:
-- Table structure for table 'warning_repair_count_statistic'
--
DROP TABLE IF EXISTS `warning_repair_time_statistic`;
CREATE TABLE `warning_repair_time_statistic` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_moid` varchar(36) NOT NULL,
`device_moid` varchar(36) NOT NULL,
`warning_code` smallint(6) NOT NULL,
`warning_name` varchar(128) NOT NULL,
`warning_level` varchar(16) NOT NULL,
`repair_time` smallint(6) NOT NULL,
`statistic_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `warning_repair_time_statistic` WRITE;
UNlOCK TABLES;