MySQL中判断索引不存在时添加索引

MySQL中判断索引不存在时添加索引

网上参考好多方法，都绕不开存储过程，没法做到直接一条语句IF NOT EXIST ...。

# vim update.sql

use mydatabase;

alter table tbl01 modify column name varchar(255);
alter table tbl02 modify column taskList mediumtext;
alter table tbl03 modify column info mediumtext NOT NULL;

DROP PROCEDURE IF EXISTS add_index;
DELIMITER //
CREATE PROCEDURE add_index()
  BEGIN
    DECLARE  CurrentDatabase VARCHAR(100);
    SELECT DATABASE() INTO CurrentDatabase;
    IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = 'tbl03' AND index_name = 'start_idx') THE
N
    ALTER TABLE tbl03 ADD INDEX start_idx (start);
    END IF;
    IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = 'tbl03' AND index_name = 'stop_idx') THEN
    ALTER TABLE tbl03 ADD INDEX stop_idx (stop);
    END IF;
END
//
DELIMITER ;

CALL add_index();
DROP PROCEDURE IF EXISTS add_index;