MySQL 資料表操作全攻略：類型選擇、建立修改、資訊查看與最佳實踐

前言：表是資料的容器，選對類型事半功倍

MySQL 支援多種儲存引擎（表類型），各有優劣。本文系統整理三大經典類型特性、建立/修改/刪除語法、資訊查看工具、隱式列變更規則，並提供選型決策樹與實戰建議。

一、三大經典表類型對比（歷史視角）

現代建議：生產環境首選 InnoDB（交易、行鎖、外鍵、崩潰恢復、聚簇索引），MyISAM 僅用於唯讀分析表或全文索引舊版相容。

二、建立表：CREATE TABLE 核心語法與進階技巧

基本語法

CREATE TABLE tbl_name (
  col_name type [NOT NULL | NULL] [DEFAULT default_val] [AUTO_INCREMENT] [PRIMARY KEY],
  ...
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

指定引擎與字符集

CREATE TABLE t (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

隱式列定義變更（必須知道的坑）

• VARCHAR(n) n<4 → 自動轉 CHAR(n)
• 表含任何可變長列（VARCHAR/TEXT/BLOB）→ 所有 CHAR(n>3) 自動轉 VARCHAR
• TIMESTAMP 顯示寬度：奇數→下一偶數，0 或 >14 → 14，範圍 2~14。
• TIMESTAMP 不接受字面 NULL，設 NULL 實為 CURRENT_TIMESTAMP。
• 建表後用 DESCRIBE tbl_name 驗證實際類型。

利用 SELECT 結果建表 (CREATE TABLE ... SELECT)

-- 複製結構+資料
CREATE TABLE new_tbl SELECT * FROM old_tbl;

-- 僅複製結構
CREATE TABLE new_tbl SELECT * FROM old_tbl WHERE 0;

-- 自定義主鍵+索引並複製資料
CREATE TABLE new_tbl (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  KEY idx_name (name)
) SELECT name, email FROM old_tbl;

優勢：單指令完成結構推導、索引建立、資料填充；錯誤自動回滾刪表。

建立臨時表進行安全實驗

CREATE TEMPORARY TABLE tmp_tbl SELECT * FROM real_tbl;
-- 修改 tmp_tbl 不影響 real_tbl，連線結束自動銷毀

三、查看表資訊：SHOW / DESCRIBE / mysqlshow

命令列工具 mysqlshow

mysqlshow                    # 列出所有資料庫
mysqlshow db_name            # 列出資料庫所有表
mysqlshow db_name tbl_name   # 顯示表結構
mysqlshow -k db_name tbl_name # 顯示索引

四、修改表結構：ALTER TABLE 實戰語法

-- 新增欄位
ALTER TABLE tbl ADD col_name type [FIRST | AFTER exist_col];

-- 刪除欄位
ALTER TABLE tbl DROP col_name;

-- 修改欄位類型/屬性
ALTER TABLE tbl MODIFY col_name new_type;
ALTER TABLE tbl CHANGE old_col new_col new_type;  -- 可同時改名

-- 重命名欄位
ALTER TABLE tbl CHANGE old_col new_col type;

-- 重命名表
ALTER TABLE tbl RENAME new_tbl;
-- 或
RENAME TABLE old_tbl TO new_tbl;

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

注意：ALTER TABLE 多為複製表重建（5.6+ 部分操作支援 Online DDL），大表操作會鎖表久，建議用 pt-online-schema-change 或 gh-ost。

五、刪除表：DROP TABLE

DROP TABLE [IF EXISTS] tbl_name [, tbl_name2 ...];
-- IF EXISTS 避免不存在報錯

六、選型決策樹與最佳實踐

1. 需要交易/外鍵/行鎖/崩潰安全 → InnoDB (預設 5.5+)。
2. 純唯讀、全文搜尋、空間索引 (舊版) → MyISAM。
3. 極高速查找、資料可重建、小表 → MEMORY (HEAP)。
4. 建表即指定 ENGINE、CHARSET、COLLATE，避免隱式預設值差異。
5. 主鍵選自增整數，InnoDB 聚簇索引受益；避免 UUID、長字串。
6. 大表 DDL 用線上工具，業務低峰期執行，監控 Threads_running。
7. 定期 ANALYZE TABLE 更新統計，配合 OPTIMIZE TABLE 整理碎片 (MyISAM/InnoDB 均適用)。