mysql 条件列创建索引_如何使用MySQL有条件地创建索引？

您可以更改Mitch Wheat提供的示例,以便它检查给定列名是否存在索引,但不检查索引名本身：

delimiter //

DROP PROCEDURE IF EXISTS create_index_if_not_exists//

CREATE PROCEDURE `create_index_if_not_exists` (IN param_schema CHAR(255), IN param_table CHAR(255), IN param_column CHAR(255))

BEGIN

SELECT @indexes := COUNT(*)

FROM INFORMATION_SCHEMA.STATISTICS

WHERE table_schema = param_schema

AND table_name = param_table

AND COLUMN_NAME = param_column;

IF @indexes = 0 THEN

SET @sql_cmd := CONCAT(

'ALTER TABLE ',

param_table,

' ADD INDEX ',

'`', param_column, '` ',

'(', param_column, ')');

SELECT @sql_cmd;

PREPARE stmt FROM @sql_cmd;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END IF;

END//

delimiter ;

例：

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (

`id` int(10) unsigned NOT NULL auto_increment,

`col1` int(10) unsigned,

`col2` int(10) unsigned,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

CALL create_index_if_not_exists('test', 'table1', 'col2');

+--------------------------------------------+

| @sql_cmd |

+--------------------------------------------+

| ALTER TABLE table1 ADD INDEX `col1` (col1) |

+--------------------------------------------+

SHOW CREATE TABLE `table1`;

CREATE TABLE `table1` (

`id` int(10) unsigned NOT NULL auto_increment,

`col1` int(10) unsigned default NULL,

`col2` int(10) unsigned default NULL,

PRIMARY KEY (`id`),

KEY `col1` (`col1`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

下面的行显示了将要执行的命令,如有必要,可将其从存储过程中删除：

SELECT @sql_cmd;