MySQL 資料表建立、刪除、索引與 ALTER TABLE 完整指南

前言

資料表結構的定義與維護是資料庫開發的核心技能。MySQL 提供豐富的 DDL 語句，支援資料表建立、刪除、修改與索引管理。本文系統整理 CREATE TABLE、DROP TABLE、ALTER TABLE、CREATE/DROP INDEX 的語法、選項與實務技巧。

一、CREATE TABLE：建立資料表

基本語法

CREATE TABLE [IF NOT EXISTS] table_name (
    column_definitions,
    [index_definitions],
    [table_options]
) [SELECT_statement];

重要選項與擴充功能

1. 儲存引擎指定 (ENGINE / TYPE)

MySQL 3.23+ 支援在建表時指定儲存引擎：

CREATE TABLE t (id INT) ENGINE = InnoDB;
CREATE TABLE t (id INT) ENGINE = MyISAM;
CREATE TABLE t (id INT) ENGINE = MEMORY;  -- 舊稱 HEAP

三大引擎特性比較：

2. IF NOT EXISTS：避免重複建立報錯

CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name VARCHAR(50));

適用於部署腳本、批次作業，確保冪等性。

3. TEMPORARY：臨時表

CREATE TEMPORARY TABLE temp_session (user_id INT, data TEXT);

• 僅對當前 Session 可見
• Session 結束自動刪除，無需手動 DROP
• 可與永久表同名（隱藏永久表）

4. CREATE TABLE ... SELECT：從查詢結果建表

-- 複製結構與資料
CREATE TABLE backup_users SELECT * FROM users;

-- 僅複製結構（WHERE 條件永假）
CREATE TABLE empty_users SELECT * FROM users WHERE 0;

-- 建立臨時表進行實驗
CREATE TEMPORARY TABLE test_users SELECT * FROM users;

優點：自動推斷欄位型別、複製索引（主鍵需手動加回）、單指令完成。

二、DROP TABLE：刪除資料表

-- 基本刪除
DROP TABLE table_name;

-- 同時刪除多張表
DROP TABLE t1, t2, t3;

-- 安全刪除（不存在不報錯）
DROP TABLE IF EXISTS table_name;

IF EXISTS 同樣適用於部署腳本，避免首次執行失敗。

三、索引管理：CREATE INDEX 與 DROP INDEX

索引類型與特性

• PRIMARY KEY：唯一、非空、每表僅一個
• UNIQUE：唯一、允許 NULL、可多個
• INDEX (普通)：允許重複、允許 NULL
• FULLTEXT：全文搜尋專用
• SPATIAL：空間資料專用

建立索引三種方式

-- 1. CREATE INDEX (MySQL 內部映射為 ALTER TABLE)
CREATE INDEX idx_name ON users(name(10));
CREATE UNIQUE INDEX uk_email ON users(email);

-- 2. ALTER TABLE (最常用，支援一次建多個)
ALTER TABLE users
    ADD INDEX idx_name (name(10)),
    ADD UNIQUE uk_email (email),
    ADD PRIMARY KEY (id);

-- 3. CREATE TABLE 時同步建立
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_name (name(10)),
    UNIQUE uk_email (email)
);

前綴索引

對長字串欄位（CHAR、VARCHAR、BLOB、TEXT）建立索引時，可指定前綴長度：

CREATE INDEX idx_content ON articles(content(100));

• 減少索引大小、提升查詢速度
• BLOB/TEXT 欄位必須指定前綴長度
• 前綴長度不可超過欄位定義長度

刪除索引

DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;
ALTER TABLE users DROP PRIMARY KEY;  -- 刪除主鍵

四、ALTER TABLE：修改資料表結構

常見操作語法

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;

-- 新增欄位
ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0;

-- 新增欄位並指定位置
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users ADD COLUMN created_at TIMESTAMP FIRST;

-- 修改欄位型別/屬性
ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;
ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(100) NOT NULL;

-- 刪除欄位
ALTER TABLE users DROP COLUMN deprecated_field;

-- 變更儲存引擎
ALTER TABLE users ENGINE = InnoDB;

-- 調整 AUTO_INCREMENT 起始值
ALTER TABLE users AUTO_INCREMENT = 10000;

VARCHAR ↔ CHAR 批次轉換技巧

MySQL 要求單次 ALTER TABLE 必須同時轉換所有可變長欄位，否則會自動轉回：

-- 正確：一次轉換所有 VARCHAR
ALTER TABLE t 
    MODIFY c1 CHAR(50), 
    MODIFY c2 CHAR(100);

-- 錯誤：分兩次執行，第一次執行後會被自動轉回 VARCHAR
ALTER TABLE t MODIFY c1 CHAR(50);
ALTER TABLE t MODIFY c2 CHAR(100);

修改欄位型別優化 JOIN 效能

連接欄位型別一致可大幅提升 JOIN 效能：

-- t1.name CHAR(10), t2.name CHAR(15) → 統一為 CHAR(15)
ALTER TABLE t1 MODIFY name CHAR(15);

五、實務建議總結

1. 預設使用 InnoDB：除非有特殊唯讀/全文搜尋需求
2. 善用 IF NOT EXISTS / IF EXISTS：讓部署腳本可重複執行
3. 大量匯入資料時：先建表、匯入資料、再建索引，速度更快
4. 前綴索引：長字串欄位優先考慮
5. ALTER TABLE 合併操作：一次語句完成多項變更，減少鎖表時間
6. 備份優先：生產環境執行 DDL 前務必備份

結語

掌握 MySQL DDL 語句的完整語法與特性，能讓資料庫結構演進更從容。建議搭配 SHOW CREATE TABLE、EXPLAIN 驗證結構與索引效果，打造高效能的資料模型。