MySQL 查看與刪除唯一索引：SHOW INDEX 與 ALTER TABLE DROP INDEX 實戰

MySQL 中唯一索引 (UNIQUE INDEX) 兼具「加速查詢」與「約束唯一性」雙重職責。本文演示如何查看索引定義、確認唯一索引名稱、安全刪除，並說明主鍵與唯一索引的異同。

1. 查看表的所有索引

SHOW INDEX FROM `my_table`;  -- 或 SHOW KEYS FROM `my_table`;

關鍵輸出列解讀：

2. 刪除唯一索引

-- 語法：ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE `my_table` DROP INDEX `uk_email`;

注意：

• 索引名為 Key_name 列的值，**不是**列名。
• 主鍵 (PRIMARY) 不能用此語法刪除，需 ALTER TABLE ... DROP PRIMARY KEY。
• 外鍵引用的列上若有唯一索引，刪除前需先刪除外鍵。

3. 等效替代語法 (MySQL 5.7+ / 8.0)

-- DROP INDEX 獨立語法 (語法糖，內部轉為 ALTER TABLE)
DROP INDEX `uk_email` ON `my_table`;

4. 實戰示例

-- 建表示例
CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20),
    UNIQUE KEY `uk_email` (`email`),
    UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB;

-- 查看
SHOW INDEX FROM `users`;
-- Non_unique=0, Key_name=uk_email/uk_phone

-- 刪除 email 唯一索引
ALTER TABLE `users` DROP INDEX `uk_email`;

-- 再次查看確認
SHOW INDEX FROM `users`;  -- 僅剩 PRIMARY, uk_phone

5. 常見誤區

1. 以為刪除列自動刪除索引：ALTER TABLE ... DROP COLUMN email 會隱式刪除該列上的索引，但最好顯式先刪索引再刪列，避免外鍵依賴報錯。
2. 混淆「索引名」與「列名」：刪除時需精確指定 Key_name。
3. 忽略外鍵依賴：被外鍵引用的唯一索引/主鍵不可直接刪除。
4. 以為 DROP INDEX 釋放空間即時生效：InnoDB 標記刪除，空間回收需 OPTIMIZE TABLE 或重建表 (Online DDL 8.0+ 即時)。

6. 程式化批量操作 (信息庫查詢)

-- 查詢指定庫所有唯一索引 (除主鍵)
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'my_db' AND NON_UNIQUE = 0 AND INDEX_NAME <> 'PRIMARY';

-- 生成刪除腳本 (謹慎執行)
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP INDEX `', INDEX_NAME, '`;') AS DropStmt
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'my_db' AND NON_UNIQUE = 0 AND INDEX_NAME <> 'PRIMARY';

掌握 SHOW INDEX 讀取與 ALTER TABLE DROP INDEX 精確刪除，是 MySQL 結構變更與性能調優的基礎技能。變更前務必備份、驗證外鍵依賴、預留回滾腳本。