MySQL：创建索引（如果不存在）

CREATE INDEX IF NOT EXISTS index_name ON table(column)
ERROR 1064 (42000): You have an error in your SQL syntax;...

#1 楼

该功能不存在。需要牢记两件事：
仍然创建索引
您可以通过创建索引的方式来创建索引，而无需事先检查索引是否存在。例如，您可以运行以下命令：

ALTER TABLE table_name ADD INDEX (column_to_index);
ALTER TABLE table_name ADD INDEX (column_to_index);

这肯定会创建两个索引，而无需检查。每个索引都将被分配一个名称（也许column_to_index，column_to_index_1）。当然，您正在尝试避免这种情况。
先检查INFORMATION_SCHEMA。
这是INFORMATION_SCHEMA.STATISTICS的布局：

mysql> show create table statistics\G
*************************** 1. row ***************************
       Table: STATISTICS
Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
  `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
  `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLLATION` varchar(1) DEFAULT NULL,
  `CARDINALITY` bigint(21) DEFAULT NULL,
  `SUB_PART` bigint(3) DEFAULT NULL,
  `PACKED` varchar(10) DEFAULT NULL,
  `NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
  `COMMENT` varchar(16) DEFAULT NULL,
  `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

您可以通过以下方式查询索引的存在名称。例如，在运行

CREATE INDEX index_name ON mytable(column);

之前，您需要运行

SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';

如果IndexIsThere为0，则可以在索引中创建。也许您可以编写一个存储过程，以便在您选择的表上创建索引。 ）：

DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
CREATE PROCEDURE `adam_matan`.`CreateIndex`
(
    given_database VARCHAR(64),
    given_table    VARCHAR(64),
    given_index    VARCHAR(64),
    given_columns  VARCHAR(64)
)
BEGIN

    DECLARE IndexIsThere INTEGER;

    SELECT COUNT(1) INTO IndexIsThere
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_schema = given_database
    AND   table_name   = given_table
    AND   index_name   = given_index;

    IF IndexIsThere = 0 THEN
        SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
        given_database,'.',given_table,' (',given_columns,')');
        PREPARE st FROM @sqlstmt;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    ELSE
        SELECT CONCAT('Index ',given_index,' already exists on Table ',
        given_database,'.',given_table) CreateindexErrorMessage;   
    END IF;

END $$

DELIMITER ;

试试看!!!

#2 楼

如果您尝试不使用过程，则与在MySQL中使用SELECT IF()语句有类似的用法： select if是一个虚拟机壳。 if (condition, true_case, false_case)扮演别名的角色。如果未完成别名，则列名和行均显示select 'index index_1 exists'，这会进一步混淆。要更具描述性，可以使用_____。

#3 楼

如果您命名索引，则如果索引已存在（在MySQL 8.0中测试），查询将失败：名称'col_idx';


所以您可以捕获异常并将其忽略，例如在PHP中：

ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC);

#4 楼

SELECT COUNT(*)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'table_name' 
  AND INDEX_NAME = 'index_name'; 

我的查询将为您提供具有特定index_name的表上存在的索引计数。基于该计数，您可以决定是否发出CREATE INDEX命令。

在MySQL 5.5版上进行了测试。

MariaDB支持IF NOT EXISTS语法。您可以在那里使用CREATE INDEX IF NOT EXISTS。