You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = '<given schema>'
AND table_name = '<given table name>'
AND index_name = '<given index name>';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE <tbl-name> DROP INDEX <index-name>;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount > 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-----Original Message-----
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to "drop index if exists"
On Nov 29, 2007 4:34 AM, Adam Lipscombe <[EMAIL PROTECTED]> wrote:
> Folks
>
>
> How can one conditionally drop an index in MySQL?
>
> Googling shows that the "drop index" does not support an "if exists"
> qualifier - apparently a bug
> has been raised but as far as I know its not fixed yet.
>
> Does anyone know of a work-around?
>
> TIA - Adam
>
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
