本文 AI 產出，尚未審核

MySQL 教學：資料庫與資料表操作

CREATE TABLE / ALTER TABLE / DROP TABLE

簡介

在任何關係式資料庫系統中，資料表（Table）是資料的核心結構。無論是簡單的部落格、電商平台，或是大型企業級系統，都必須先建立適當的資料表，才能存放與管理資料。
CREATE TABLE、ALTER TABLE、DROP TABLE 三大指令，分別負責建立、修改與刪除資料表，是每位 MySQL 使用者在日常開發與維運時必會碰到的基本操作。掌握這些指令不僅能讓你快速搭建資料模型，也能在需求變更時靈活調整結構，避免因為設計不良而產生的維護成本。

本篇文章以 繁體中文（台灣） 為語言，從概念說明、實作範例、常見陷阱與最佳實踐，逐步帶領讀者建立對資料表操作的完整認知，適合 初學者到中級開發者 參考。

核心概念

1. CREATE TABLE：建立新資料表

CREATE TABLE 用來定義資料表的名稱、欄位、資料型別、約束條件（PRIMARY KEY、UNIQUE、NOT NULL、DEFAULT 等），以及儲存引擎與字元集等選項。

基本語法

CREATE TABLE 表格名稱 (
    欄位1 資料型別 [約束條件],
    欄位2 資料型別 [約束條件],
    ...
) [表格屬性];

範例 1：建立最簡單的使用者表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- 主鍵且自動遞增
    username VARCHAR(50) NOT NULL,      -- 使用者名稱，必填
    email VARCHAR(100) UNIQUE,           -- 電子郵件，唯一值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

說明

  • AUTO_INCREMENT 讓 id 在插入新資料時自動加 1。
  • PRIMARY KEY 為唯一且不可為 NULL 的索引。
  • UNIQUE 確保 email 不會重複。
  • ENGINE=InnoDB 推薦使用 InnoDB，支援事務與外鍵。
  • DEFAULT CHARSET=utf8mb4 讓資料表支援全 Unicode（含 Emoji）。

範例 2：建立含外鍵的訂單表

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','shipped','cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

說明

  • FOREIGN KEY 讓 orders.user_id 參照 users.id，保持資料一致性。
  • ON DELETE CASCADE 表示當使用者被刪除時，相關訂單也會自動刪除。

範例 3：使用 IF NOT EXISTS 防止重複建立

CREATE TABLE IF NOT EXISTS products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

技巧：在部署腳本中常加入 IF NOT EXISTS，避免因已存在的表格導致錯誤中斷。

2. ALTER TABLE：修改既有資料表

當需求變更、功能擴充或性能優化時，需要對已存在的資料表進行增、改、刪欄位、新增或移除索引、調整欄位屬性，這時就會使用 ALTER TABLE。

基本語法

ALTER TABLE 表格名稱
    操作1,
    操作2,
    ...;

常見操作

操作類型 範例 說明
新增欄位 ADD COLUMN col_name datatype [AFTER existing_col] 在指定位置加入新欄位
修改欄位 MODIFY COLUMN col_name new_datatype 變更資料型別或屬性（不改名）
重新命名欄位 CHANGE COLUMN old_name new_name datatype 同時改名與型別
刪除欄位 DROP COLUMN col_name 移除欄位
新增索引 ADD INDEX idx_name (col1, col2) 建立普通索引
新增唯一索引 ADD UNIQUE uniq_name (col) 建立唯一索引
刪除索引 DROP INDEX idx_name 移除索引
重新命名表格 RENAME TO new_table_name 改表名

範例 4：為 users 表新增生日欄位

ALTER TABLE users
    ADD COLUMN birthday DATE AFTER email;

註解：AFTER email 表示欄位會被放在 email 之後，若不指定則預設加在最後。

範例 5：將 price 欄位從 DECIMAL(10,2) 改為 DECIMAL(12,4)

ALTER TABLE products
    MODIFY COLUMN price DECIMAL(12,4) NOT NULL;

注意：修改欄位型別時，若已有資料，MySQL 會嘗試自動轉換；若轉換失敗會拋出錯誤。

範例 6：刪除 orders 表的 status 欄位

ALTER TABLE orders
    DROP COLUMN status;

範例 7：新增複合唯一索引（防止同一使用者同一天下多筆相同金額的訂單）

ALTER TABLE orders
    ADD UNIQUE uq_user_date_amount (user_id, DATE(created_at), total);

3. DROP TABLE：刪除資料表

DROP TABLE 會永久移除資料表結構與其中的所有資料，操作不可逆。通常在測試環境、資料清理或資料表不再使用時才會使用。

基本語法

DROP TABLE [IF EXISTS] 表格名稱 [, 其他表格];

範例 8：刪除單一表格

DROP TABLE IF EXISTS temp_sessions;

範例 9：一次刪除多個表格

DROP TABLE IF EXISTS logs, audit_trail, cache_data;

建議：在正式環境執行前，先確認是否真的不需要該表，或使用備份還原機制。

常見陷阱與最佳實踐

陷阱 說明 防範 / 最佳實踐
忘記加 IF NOT EXISTS 重複執行部署腳本會因表已存在而失敗。 在 CREATE TABLE、DROP TABLE 前加上 IF NOT EXISTS / IF EXISTS。
欄位命名衝突 ALTER TABLE … ADD COLUMN 時若欄位已存在會錯誤。 先 SHOW COLUMNS FROM table_name LIKE 'col_name' 確認。
資料遺失 DROP TABLE 或 ALTER TABLE … DROP COLUMN 會直接刪除資料。 先備份（mysqldump）或使用 軟刪除（加入 is_deleted 欄位）。
索引遺漏 新增欄位後忘記建索引，導致查詢效能下降。 根據查詢需求，在 ALTER TABLE 時同步建立適當的索引。
外鍵相依 刪除有外鍵相依的表格會失敗。 先 DROP FOREIGN KEY，或使用 ON DELETE CASCADE 設計。
資料型別不匹配 修改欄位型別時，若已有資料不符合新型別會拋錯。 先 檢查資料（SELECT * FROM table WHERE col NOT REGEXP …），或 分批遷移。
鎖表 大型表格的 ALTER TABLE 會導致整表鎖定，影響線上服務。 使用 Online DDL（ALGORITHM=INPLACE, LOCK=NONE），或在低峰時段執行。

最佳實踐總結：

  1. 版本控制：將所有 DDL（Data Definition Language）腳本放入 Git，確保變更可追溯。
  2. 使用 Migration 工具：如 Flyway、Liquibase，可自動管理升級與回滾。
  3. 測試環境驗證：在正式環境部署前，於測試庫執行完整的 CREATE / ALTER / DROP 流程。
  4. 備份與還原：每次結構變更前，使用 mysqldump --no-data 產生結構備份。
  5. 命名規範：表名、欄位、索引統一使用小寫加底線（snake_case），便於跨平台協作。

實際應用場景

場景 需求 可能使用的指令
部落格系統 新增文章類別（category）欄位 ALTER TABLE posts ADD COLUMN category_id INT;
電商平台 上線新促銷活動，需要儲存折扣碼 CREATE TABLE discount_codes (code VARCHAR(20) PRIMARY KEY, amount DECIMAL(8,2), expires_at DATE);
多租戶 SaaS 為每個租戶新增自訂屬性表 CREATE TABLE tenant_custom_fields (tenant_id INT, field_name VARCHAR(50), field_value TEXT);
資料清理 每月刪除超過一年未使用的暫存表 DROP TABLE IF EXISTS temp_cache_2024_01;
性能優化 為常用查詢加入複合索引 ALTER TABLE orders ADD INDEX idx_user_date (user_id, created_at);
法規遵從 需要將個人資料欄位加密 ALTER TABLE users MODIFY COLUMN email VARBINARY(255);（配合應用層加解密）

總結

  • CREATE TABLE 是資料模型的起點，正確設計欄位、索引與約束能為後續開發奠定穩固基礎。
  • ALTER TABLE 讓資料庫結構能夠靈活演進，但每次變更都應先評估資料量、鎖表影響與相依關係。
  • DROP TABLE 雖簡單卻危險，務必要在備份、審核後才執行。

透過本文的概念說明、實作範例與最佳實踐，你應該已能在日常開發與維運工作中自信地使用 CREATE / ALTER / DROP 這三大指令，為 MySQL 資料庫打造既 穩定 又 可擴充 的資料結構。祝你在 MySQL 的世界裡玩得開心、寫得順利！