过程的核心问题是该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 ;