MySQL和Oracle在索引名称唯一性方面的不同设计

bisal的个人杂货铺 2022-04-13

410

最近碰到要删除一个MySQL数据库索引的需求，按照Oracle的思维，"drop index index_name"会提示错误，MySQL删除索引，有两种形式，

(1) 使用DROP INDEX语句

语法格式，

DROP INDEX <索引名> ON <表名>
复制

语法说明如下，

<索引名>：要删除的索引名。
<表名>：指定该索引所在的表名。

(2) 使用ALTER TABLE语句

根据ALTER TABLE语句的语法可知，该语句可以用于删除索引。具体使用方法是将ALTER TABLE语句的语法中部分指定为以下子句中的某一项。

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

猜想一下，之所以在MySQL中不支持"drop index index_name"的操作，应该是说index_name不能唯一标识一个索引，很容易进行验证，在如下这两张表上各创建一个同名的索引，这是可行的，

root@mysqldb 14:41:  [test]> create table t1(id int);
Query OK, 0 rows affected (0.29 sec)

root@mysqldb 14:41:  [test]> create table t2(id int);
Query OK, 0 rows affected (0.06 sec)

root@mysqldb 14:41:  [test]> create index idx_t on t1(id);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 14:41:  [test]> create index idx_t on t2(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制

从数据字典可以看到，确实两张表，有相同名称索引，

root@mysqldb 14:50:  [test]> select t1.name index_name, t2.name table_name
    -> from information_schema.innodb_indexes t1, information_schema.innodb_tables t2
    -> where t1.table_id=t2.table_id and t1.name='idx_t';
+------------+------------+
| index_name | table_name |
+------------+------------+
| idx_t      | test/t1    |
| idx_t      | test/t2    |
+------------+------------+
2 rows in set (0.04 sec)
复制

这说明了MySQL并不是通过索引名称唯一标识一个索引的，而在Oracle中，创建一个同名的索引，就会提示ORA-00955，

SQL> create index idx_01 on a(object_id);
create index idx_01 on a(object_id)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
复制