您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 入門到進階：建表、結構變更、數據約束、外鍵實戰全指南

編輯：MySQL 資料庫相關知識

從創建第一張表到掌握外鍵約束、DDL 變更、數據完整性，本文為 MySQL 初學者與進階開發者提供結構化知識地圖，含標準語法、常用變體、避坑指南與生產級建議。

一、建表三大範式

1. 標準 CREATE TABLE

CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` CHAR(15) NOT NULL DEFAULT '',
  `sex` ENUM('M','F') DEFAULT 'M',
  `userid` VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`, `userid`),          -- 聯合主鍵
  INDEX `idx_user` (`userid`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 克隆結構 (LIKE)

CREATE TABLE vip_user LIKE user;              -- 僅結構
CREATE TABLE vip_user SELECT * FROM user WHERE 0; -- 同效

3. 克隆結構 + 數據 (CTAS)

CREATE TABLE dst_tbl (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT a, b, c FROM src_tbl;

二、查看結構命令

DESC tbl; / SHOW COLUMNS FROM tbl; — 列定義
SHOW CREATE TABLE tbl\G — 完整 DDL (垂直顯示)
SHOW TABLE STATUS LIKE 'tbl'\G — 統計信息 (行數、大小、自增值)

三、 DDL 結構變更實戰

操作	語法	關鍵點
修改列類型/屬性	`ALTER TABLE vip MODIFY id SMALLINT DEFAULT 1;`	Auto_increment 列必須是主鍵或唯一鍵首列
修改列名 + 類型 + 位置	`ALTER TABLE vip CHANGE sex usersex TINYINT DEFAULT 0 AFTER username;`	CHANGE 可改名，MODIFY 不可
新增列	`ALTER TABLE vip ADD sex ENUM('M','F') DEFAULT 'M' AFTER id;`	FIRST/AFTER 控制位置
刪除列	`ALTER TABLE vip DROP usersex;`	數據永久丟失，謹慎操作
重命名表	`ALTER TABLE vip RENAME TO vip_user;`	原子操作，無數據拷貝
變更引擎	`ALTER TABLE vip ENGINE=MyISAM;`	重建表，大表極慢，建議 pt-osc

四、數據約束與外鍵 (InnoDB 專屬)

外鍵創建三條件

父表、子表均為 InnoDB。
被引用列 (父表 PK/UK) 與引用列 (子表 FK) 類型、長度、字符集完全一致。
子表 FK 列 必須有索引 (自動創建或顯式創建)。

標準語法

CREATE TABLE child (
  id INT,
  parent_id INT,
  CONSTRAINT fk_child_parent 
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

級聯動作對比

動作	父表 DELETE/UPDATE 時	子表 FK 可否為 NOT NULL
RESTRICT / NO ACTION	阻止操作，報錯	是
SET NULL	子表匹配行 FK 置 NULL	否 (必須允許 NULL)
CASCADE	同步刪除/更新子表匹配行	是

動態修改外鍵 (必須重建)

-- 1. 查約束名
SHOW CREATE TABLE child\G
-- 2. 刪除
ALTER TABLE child DROP FOREIGN KEY fk_child_parent;
-- 3. 重建
ALTER TABLE child ADD CONSTRAINT fk_child_parent
  FOREIGN KEY (parent_id) REFERENCES parent(id)
  ON DELETE CASCADE ON UPDATE CASCADE;

五、外鍵效能與大數據操作

每次 INSERT/UPDATE/DELETE 都需檢查父表 → 顯著降低寫入吞吐。
大批量導入 / 結構變更時，暫時關閉檢查：

SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE ... / ALTER TABLE ...;
SET FOREIGN_KEY_CHECKS = 1;

速度提升 20x+，操作完成後立即開啟。

六、避坑指南

CHAR vs VARCHAR 隱式轉換： 表含可變長列 → 全表行變可變長 → CHAR(>3) 自動轉 VARCHAR，影響存儲預估。
外鍵名衝突： 同庫約束名唯一，建議前綴 fk_子表_父表。
DDL 鎖表： 5.6 前全鎖，5.6+ Online DDL (部分操作 INSTANT)，大表必用 pt-online-schema-change / gh-ost。
備份不含外鍵： mysqldump --no-create-info 導出數據還原時需先建結構或關閉檢查。

七、總結

建表規範：InnoDB + utf8mb4 + 顯式主鍵 + 業務唯一鍵 + 命名規範。DDL 變更：區分 MODIFY/CHANGE、控制列位置、大表走在線工具。外鍵：InnoDB 專屬、三條件、級聯動作選對、大操作關檢查。將這些內化為肌肉記憶，數據庫結構演進將不再是恐懼來源，而是可控的工程動作。