Last active
February 29, 2020 19:01
-
-
Save drawcode/4441638 to your computer and use it in GitHub Desktop.
MYSQL Drop Index If Exists
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELIMITER $$ | |
DROP PROCEDURE IF EXISTS drop_index_if_exists $$ | |
CREATE PROCEDURE drop_index_if_exists(in theTable varchar(128), in theIndexName varchar(128) ) | |
BEGIN | |
IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name = | |
theTable AND index_name = theIndexName) > 0) THEN | |
SET @s = CONCAT('DROP INDEX `' , theIndexName , '` ON `' , theTable, '`'); | |
PREPARE stmt FROM @s; | |
EXECUTE stmt; | |
END IF; | |
END $$ | |
DELIMITER ; | |
-- CALL drop_index_if_exists('#{index_name}','#{model_id}'); |
This was an interesting and very useful solution for the drop and add index thing if it does or does not exists. I have used your example to create a stored procedure to add an index safely so, thank you. Maybe one day MySQL will support an
alter table1 add index if not exists idx1 (col1)
where is your sample?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The args in the example at the end are wrong. The first arg should be table_name and the second index_name.