DB設定常常忘記一此筆記

在 MySQL / MariaDB 管理資料庫結構時，主鍵（PRIMARY KEY）和索引（INDEX）的新增與刪除有一些限制和注意事項，容易踩坑，在此整理記錄。

刪除主鍵（PRIMARY KEY）

刪除主鍵之前，若主鍵欄位設定了 AUTO_INCREMENT，必須先移除 AUTO_INCREMENT 屬性，否則會報錯。

1
2
3
4
5

-- 先移除 AUTO_INCREMENT
ALTER TABLE your_table MODIFY COLUMN id INT NOT NULL;

-- 再刪除主鍵
ALTER TABLE your_table DROP PRIMARY KEY;

刪除索引（INDEX）

刪除一般索引相對單純：

1
2
3
4
5

-- 查看目前的索引
SHOW INDEX FROM your_table;

-- 刪除索引
ALTER TABLE your_table DROP INDEX index_name;

新增主鍵

若資料表目前沒有主鍵，可以直接新增：

1

ALTER TABLE your_table ADD PRIMARY KEY (id);

若要新增複合主鍵：

1

ALTER TABLE your_table ADD PRIMARY KEY (col1, col2);

新增索引

1
2
3
4
5

-- 新增一般索引
ALTER TABLE your_table ADD INDEX idx_name (column_name);

-- 新增唯一索引
ALTER TABLE your_table ADD UNIQUE INDEX idx_name (column_name);

注意事項

• 在早期版本的 MySQL（5.x），對大型資料表執行 ALTER TABLE 操作（包含新增/刪除主鍵或索引）時，MySQL 會鎖定整張表並重建，期間資料表無法寫入，影響線上服務。
• MySQL 5.6 以後引入了「Online DDL」功能，部分 ALTER TABLE 操作可以在不鎖表的情況下執行，例如新增索引。
• MySQL 8.0 / MariaDB 10 以後，支援更多的 Instant DDL，許多操作不需要重建整張表，速度大幅提升。

實務建議

• 在上線環境執行結構變更前，先在測試環境驗證。
• 大型資料表建議使用 pt-online-schema-change（Percona Toolkit）或 gh-ost（GitHub 開源工具）來進行無鎖表的結構變更。