如何从存储过程创建索引或在MySQL中的每个表上创建索引？

如何从存储过程创建索引或在MySQL中的每个表上创建索引？

过程的核心问题是该

PREPARE

语句仅适用于用户变量或字符串文字。它无法从过程变量准备语句。

PREPARE语法

PREPARE stmt_name FROM preparable_stmt

… preparable_stmt是 字符串文字或 包含SQL语句文本的 用户变量 。

DELIMITER //CREATE PROCEDURE createModifiedIndex(t VARCHAr(256))  BEGIN    DECLARE idx VARCHAr(256);    DECLARE i INT;    SET idx = CONCAt('idx_', t, '_modified_on');    SET i = (SELECt COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERe table_name = t AND index_name = idx);    IF i = 0 THEN        SET @makeIndexSql = CONCAt('CREATE INDEX ', idx, ' ON ', t, ' (modified_on);');        PREPARE stmt FROM @makeIndexSql;        EXECUTE stmt;        DEALLOCATE PREPARE stmt; -- Use DEALLOCATE when you're done with the statement    END IF;  END //DELIMITER ;