Index with empty name is created unexpectedly

[CbcWestwolf]

Bug Report

An index with empty name is created unexpectedly

1. Minimal reproduce step (Required)

create table t (
	pid char(64) NOT NULL,
	another_id int NOT NULL,
	field varchar(255),
	PRIMARY KEY (`pid`) /*T![clustered_index] CLUSTERED */,
	KEY `idx` (`another_id`)
);

CREATE INDEX if not exists idx ON t (`another_id`);

2. What did you expect to see? (Required)

Only one index named idx is created

3. What did you see instead (Required)

MySQL [test]> CREATE INDEX if not exists idx ON t (`another_id`);
Query OK, 0 rows affected, 1 warning (0.104 sec)

MySQL [test]> show warnings;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Note  | 1061 | Duplicate key name 'idx' |
+-------+------+--------------------------+
1 row in set (0.000 sec)

MySQL [test]> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `pid` char(64) NOT NULL,
  `another_id` int NOT NULL,
  `field` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pid`) /*T![clustered_index] CLUSTERED */,
  KEY `idx` (`another_id`),
  KEY `` (`another_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

4. What is your TiDB version? (Required)

v8.5.1

[CbcWestwolf]

If the empty name index is created, we can drop the index by

alter table t drop index if exists ``

Workaround:

1. to avoid adding empty name index, we should check duplicate index name before CREATE INDEX
2. After creating the index with CREATE INDEX ... IF NOT EXISTS, manually drop the empty name index immediately.

[CbcWestwolf]

MySQL does NOT support if not exists option when creating index.