您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL DDL 語句完整手冊：庫表結構管理的核心語法與細節陷阱

編輯：MySQL 綜合教學

MySQL DDL 語句完整手冊：庫表結構管理的核心語法與細節陷阱

DDL (Data Definition Language) 是資料庫結構變更的基石。本文系統整理 MySQL 中庫、表、欄位、索引、約束的完整建立、修改、刪除語法，並針對 MySQL 特有的細節差異（如 AUTO_INCREMENT、DEFAULT 值語法、字符集指定）進行深度解析，助您精準掌握結構變更操作。

一、資料庫級操作

-- 建立資料庫（指定字符集與排序規則）
CREATE DATABASE `dbname` 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-- 刪除資料庫
DROP DATABASE `dbname`;

-- 查看所有資料庫
SHOW DATABASES;

二、資料表核心操作

建表語法全解析

CREATE TABLE `Person` (
  `ID` INT(10) NOT NULL COMMENT '主鍵ID',
  `Name` VARCHAR(10) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人員表';

關鍵細節：

INT(10) 中的 10 僅為顯示寬度，**不影響儲存範圍**（仍為 4 字節），不指定預設 INT(11)
VARCHAR(10) 需顯式指定長度，字符集繼承表級或庫級設定
強烈建議：ENGINE=InnoDB、DEFAULT CHARSET=utf8mb4、COMMENT 三大件缺一不可

自增主鍵

CREATE TABLE `AI` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID'
) ENGINE=InnoDB;

限制：每表僅能有一個 AUTO_INCREMENT 欄位，且必須為索引鍵（通常為主鍵或唯一索引首欄）。

查看表結構與建表語句

DESC `Person`;                    -- 簡略結構
SHOW CREATE TABLE `Person`\G;     -- 完整可執行 DDL（含分區、外鍵、索引細節）

三、表結構變更 (ALTER TABLE) 完整語法

操作類型	語法範例	MySQL 特有細節
重命名表	`ALTER TABLE old_name RENAME TO new_name;`	原子操作，極快，不鎖資料
修改欄位型別	`ALTER TABLE t MODIFY col_name BIGINT UNSIGNED NOT NULL DEFAULT 0;`	MODIFY 需指定完整新定義，含屬性
重命名欄位	`ALTER TABLE t CHANGE old_col new_col BIGINT UNSIGNED NOT NULL DEFAULT 0;`	CHANGE 同 MODIFY，但可改名；需重述完整定義
新增欄位	`ALTER TABLE t ADD col_name INT NOT NULL DEFAULT 0 AFTER other_col;`	`FIRST`/`AFTER col` 指定位置；DEFAULT 值不加括號
刪除欄位	`ALTER TABLE t DROP COLUMN col_name;`	若被索引/外鍵引用需先刪除約束
新增索引	`ALTER TABLE t ADD INDEX idx_name (col_name);`	支援 `UNIQUE`/`FULLTEXT`/`SPATIAL`
刪除索引	`ALTER TABLE t DROP INDEX idx_name;`	主鍵用 `DROP PRIMARY KEY`

四、MySQL 關鍵語法差異陷阱

DEFAULT 值語法：

-- MySQL：整型直接寫值，字符型加單引號
ALTER TABLE User ADD Age INT NOT NULL DEFAULT 0;
ALTER TABLE User ADD Name VARCHAR(20) NOT NULL DEFAULT 'unknown';
-- SQL Server：DEFAULT((0)) / DEFAULT(('unknown')) 加括號

字符集與排序規則層級：

-- 表級指定（建表時）
CREATE TABLE t (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 欄位級覆蓋
`col` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

外鍵約束命名：CONSTRAINT fk_name FOREIGN KEY (...) REFERENCES ...，名稱全庫唯一，建議採用 fk_子表_父表 規範

五、刪除表

DROP TABLE `tablename`;
-- 安全版本（不存在不報錯）
DROP TABLE IF EXISTS `tablename`;

六、DDL 執行效能與鎖影響（InnoDB）

操作	鎖模式	是否重建表	線上執行建議
ADD COLUMN (有 DEFAULT)	排他鎖	是	大表禁用，用 pt-online-schema-change
ADD COLUMN (無 DEFAULT)	排他鎖	是 (8.0 秒加)	8.0+ 秒加極快，5.7 需工具
DROP COLUMN	排他鎖	是	大表禁用
MODIFY COLUMN	排他鎖	是	大表禁用
ADD INDEX	共享鎖 → 排他鎖	是	大表必用 Online DDL 或工具
RENAME TABLE	元資料鎖	否	安全、極快