重复添加字段，索引的sql脚本

解决痛点：解决重复执行的sql脚本，添加字段或者索引sql，会报错的问题

添加字段

DELIMITER //
CREATE PROCEDURE alter_mail_contactor()
BEGIN
  IF NOT EXISTS(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mail_contactor' AND COLUMN_NAME = 'location_update_time')
  THEN
    ALTER TABLE mail_contactor ADD COLUMN `location_update_time` DATETIME DEFAULT NULL COMMENT '位置更新时间';
  END IF;
END//
DELIMITER ;
CALL alter_mail_contactor();
DROP PROCEDURE IF EXISTS alter_mail_contactor;

添加索引

DELIMITER //
CREATE PROCEDURE app_device()
BEGIN
  IF NOT EXISTS(SELECT *
                FROM information_schema.statistics
                WHERE TABLE_SCHEMA = DATABASE()
                      AND TABLE_NAME = 'app_device'
                      AND index_name  = 'index_device_id')
  THEN
    ALTER TABLE app_device ADD  INDEX `index_device_id` (`device_id`);
  END IF;
END//
DELIMITER ;
CALL app_device();
DROP PROCEDURE IF EXISTS app_device;

插入

INSERT INTO `sys_dictionary`(code,name,order_num,is_deleted)
  SELECT 'equipment_allocation', '装备xx', 4, 0
  FROM sys_dictionary
  WHERE NOT EXISTS (SELECT * FROM sys_dictionary WHERE code = 'equipment_allocation')
  LIMIT 0, 1;