索引mysql 存储过程_如何从存储过程创建索引或在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) 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

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: 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

Version mysql-5.1.62-r1

解决方案

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.

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 ;