In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.
Below is my dropIndexIfExists stored procedure:
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;
-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$
-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name VARCHAR(64)
, pv_index_name VARCHAR(64))
BEGIN
/* Declare a local variable for the SQL statement. */
DECLARE stmt VARCHAR(1024);
/* Set a session variable with two parameter markers. */
SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);
/* Check if the constraint exists. …[Read more]
