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