mysql创建与回滚

学习记录

ALTER TABLE `cx_promotion_city` ADD COLUMN `bu_id` int(11) NOT NULL DEFAULT 0 COMMENT '事业部ID';
ALTER TABLE `cx_promotion_city` ADD COLUMN `utime` int(11) NOT NULL DEFAULT 0 COMMENT '更改时间';
alter table cx_promotion_city drop index idx_promotion_city;
alter table cx_promotion_city add index idx_promotion_bu(promotion_id,bu_id);
alter table cx_promotion_city add unique index idx_promotion_city_bu(promotion_id, city_id, bu_id);



DROP PROCEDURE IF EXISTS schema_change;  
DELIMITER //
CREATE PROCEDURE schema_change()
BEGIN
DECLARE  CurrentDatabase VARCHAR(100);
SELECT DATABASE() INTO CurrentDatabase;
IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=CurrentDatabase AND table_name = 'cx_promotion_city' AND column_name = 'bu_id')
THEN
    ALTER TABLE `cx_promotion_city` DROP COLUMN `bu_id`;
END IF;
IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=CurrentDatabase AND table_name = 'cx_promotion_city' AND column_name = 'utime')
THEN
    ALTER TABLE `cx_promotion_city` DROP COLUMN `utime`;
END IF;
IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = 'cx_promotion_city' AND index_name = 'idx_promotion_bu')
THEN
    ALTER TABLE `cx_promotion_city` DROP INDEX `idx_promotion_bu`;
END IF;
IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = 'cx_promotion_city' AND index_name = 'idx_promotion_city_bu')
THEN
    ALTER TABLE `cx_promotion_city` DROP INDEX `idx_promotion_city_bu`;
END IF;
IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = 'cx_promotion_city' AND index_name = 'idx_promotion_city')
THEN
    ALTER TABLE `cx_promotion_city` ADD UNIQUE INDEX `idx_promotion_city` (`promotion_id`, `city_id`);
END IF;
END//
DELIMITER ;
CALL schema_change();