如何从存储过程创建索引或在MySQL中的每个表上创建索引? [英] How do I create an index from a stored procedure or create index on every table in MySQL?
问题描述
这是我到目前为止尝试过的两件事,以及他们的错误消息:
These are two things that I've tried so far, along with their error messages:
DELIMITER //
CREATE PROCEDURE createModifiedIndex(t varchar(256))
BEGIN
declare idx varchar(256);
DECLARE i int;
declare makeIndexSql varchar(256);
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;
end if;
END //
DELIMITER ;
call createModifiedIndex ('ACHDebitFrequencies');
call createModifiedIndex ...
ERROR 1064(42000)第5行:您的SQL语法有错误;检查与您的MySQL服务器版本对应的手册,以便在'makeIndexSql附近使用正确的语法;
执行stmt;
结束如果;
END'在第10行
ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'makeIndexSql; execute stmt; end if; END' at line 10
这是另一种不同的尝试但不起作用,因为MySQL不允许IF /那么在存储过程之外。
This is another different attempt but won't work because MySQL doesn't allow IF/THEN outside of stored procedures.
set @i = (select count(*) from INFORMATION_SCHEMA.STATISTICS where table_name = 'ACHDebitFrequencies' and index_name = 'idx_ACHDebitFrequencies_modified_on');
if @i > 0 then begin
create index 'idx_ACHDebitFrequencies_modified_on' on ACHDebitFrequencies (modified_on);
end;
end if;
...
ERROR 1064(42000)at line 3:您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'if @i> 0附近使用正确的语法然后在第1行开始
在ACHD'上创建索引'idx_ACHDebitFrequencies_modified_on'
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if @i > 0 then begin create index 'idx_ACHDebitFrequencies_modified_on' on ACHD' at line 1
版本mysql-5.1.62-r1
Version mysql-5.1.62-r1
推荐答案
核心问题您的过程是 PREPARE
语句仅适用于用户变量或字符串文字。它无法从过程变量中准备语句。
The core problem with your procedure was that PREPARE
statement only works with a user variable or a string literal. It can't prepare a statement from a procedure variable.
PREPARE语法
PREPARE stmt_name FROM preparable_stmt
... preparable_stmt是字符串文字或用户变量,
包含SQL语句的文本。
PREPARE Syntax
PREPARE stmt_name FROM preparable_stmt
... preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement.
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 ;
这篇关于如何从存储过程创建索引或在MySQL中的每个表上创建索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!