sql可重复执行语句例子

1.添加字段

SET @add_key_type_to_report = (SELECT IF(
    (SELECT count(1)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'report'
        AND table_schema = DATABASE()
        AND column_name = 'key_type'
    ) > 0,
    "select 1",
    "alter table report add `key_type` int(4) NOT NULL COMMENT '认证key类型 --1 group类型 2 sn类型'"
));
PREPARE stmt FROM @add_key_type_to_report;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

-- 可重复性添加索引

SET @add_index_idx_group_id_location_name_to_plan_location = (SELECT IF(
    (SELECT count(1)
        FROM INFORMATION_SCHEMA.STATISTICS

　　　　　WHERE table_name = 'plan_location'

        AND table_schema = DATABASE()
        AND index_name = 'idx_group_id_location_name'
    ) > 0,
    "select 1",
    "alter table plan_location add INDEX idx_group_id_location_name(`location_name`, `group_id`)"
));
PREPARE stmt FROM @add_index_idx_group_id_location_name_to_plan_location;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 -- 插入可重复性语句

INSERT IGNORE INTO `intl_api_msg_en` (`code`, `msg`) VALUES (7036, 'Group or sub group have bound template,need to remain the binding?');