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

MySQL DDL 入門實戰：庫表創建、修改、刪除與約束管理

編輯：MySQL 綜合教學

MySQL DDL 入門實戰：庫表創建、修改、刪除與約束管理

數據定義語言（DDL）是構建數據庫骨架的工具。本文以實戰為導向，系統講解 CREATE/ALTER/DROP DATABASE、CREATE/ALTER/DROP TABLE、欄位屬性定義、約束類型、字符集配置等核心操作，並給出規範化命名與變更最佳實踐。

一、SQL 語言分類速覽

類別	全稱	核心命令	作用
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE, RENAME	定義數據庫對象結構
DML	Data Manipulation Language	INSERT, UPDATE, DELETE, SELECT	操作數據內容
DCL	Data Control Language	GRANT, REVOKE, COMMIT, ROLLBACK	控制權限與事務

二、數據庫級操作

1. 創建數據庫

CREATE DATABASE [IF NOT EXISTS] db_name
  [[DEFAULT] CHARACTER SET charset_name]
  [[DEFAULT] COLLATE collation_name];

最佳實踐：

CREATE DATABASE IF NOT EXISTS myapp
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

強制 utf8mb4 支持 Emoji 與生僻字
IF NOT EXISTS 防止重複報錯

2. 查看與切換

SHOW DATABASES;
USE myapp;
SHOW TABLES;

3. 刪除數據庫

DROP DATABASE [IF EXISTS] db_name;

⚠️ 不可逆操作，生產環境嚴禁直接執行，需雙人審核+備份驗證。

三、表級操作核心

1. 創建表完整語法

CREATE TABLE [IF NOT EXISTS] tbl_name (
  col_name data_type [NOT NULL | NULL] [DEFAULT default_value]
    [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
    [COMMENT 'string'],
  [PRIMARY KEY (col_name, ...)],
  [UNIQUE KEY uk_name (col_name, ...)],
  [KEY idx_name (col_name, ...)],
  [FOREIGN KEY fk_name (col_name) REFERENCES ref_tbl(ref_col) ON DELETE CASCADE],
  [CHECK (expr)]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. 實戰範例：用戶表

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `username` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
  `email` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '郵箱',
  `phone` CHAR(11) NOT NULL DEFAULT '' COMMENT '手機號',
  `password_hash` CHAR(64) NOT NULL DEFAULT '' COMMENT '密碼哈希',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態:1啟用,0禁用',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  UNIQUE KEY `uk_email` (`email`),
  UNIQUE KEY `uk_phone` (`phone`),
  KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用戶表';

3. 查看表結構

DESC users;
SHOW CREATE TABLE users\G

4. 刪除表

DROP TABLE [IF EXISTS] tbl_name;

四、表結構變更（ALTER TABLE）

核心原則：MySQL 5.6+ 支持 Online DDL，大部分操作不鎖表或僅短暫鎖表。但大表變更仍需謹慎規劃（pt-online-schema-change、gh-ost）。

常用變更操作

操作	語法	說明
修改欄位類型/屬性	ALTER TABLE tbl MODIFY col NEW_TYPE [NOT NULL] [DEFAULT val] [COMMENT 'c'];	類型收窄需確保現有數據兼容
修改欄位名+類型	ALTER TABLE tbl CHANGE old_col new_col NEW_TYPE [NOT NULL] ...;	需重新指定完整類型定義
新增欄位	ALTER TABLE tbl ADD col NEW_TYPE [NOT NULL] [DEFAULT val] [FIRST \| AFTER col2];	指定位置可選
刪除欄位	ALTER TABLE tbl DROP COLUMN col;	數據永久丟失，謹慎操作
新增索引	ALTER TABLE tbl ADD INDEX idx_name (col1, col2);	Online DDL 支持
新增唯一索引	ALTER TABLE tbl ADD UNIQUE KEY uk_name (col);	大表驗證唯一性耗時長
刪除索引	ALTER TABLE tbl DROP INDEX idx_name;	主鍵用 DROP PRIMARY KEY
新增外鍵	ALTER TABLE tbl ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES ref_tbl(ref_col);	生產慎用，影響寫入性能
修改表名	RENAME TABLE old_tbl TO new_tbl;	或 ALTER TABLE old_tbl RENAME TO new_tbl;
修改字符集	ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;	重建表，大表極慢
修改引擎	ALTER TABLE tbl ENGINE=InnoDB;	重建表

五、約束類型詳解

約束	關鍵字	作用	注意點
主鍵	PRIMARY KEY	唯一標識行，隱含 NOT NULL + UNIQUE	單表僅一個，推薦 BIGINT UNSIGNED AUTO_INCREMENT
唯一鍵	UNIQUE KEY	業務唯一性（郵箱、手機、身份證）	允許多個 NULL（除非 NOT NULL）
非空	NOT NULL	禁止 NULL，優化器可優化	默認值配合使用
默認值	DEFAULT	插入時自動填充	函數默認值 8.0.13+ 支持（如 DEFAULT (UUID())）
外鍵	FOREIGN KEY	引用完整性	性能開銷大，互聯網核心表通常應用層維護
檢查	CHECK	列值滿足條件	8.0.16+ 真正生效，舊版本解析但忽略

六、命名規範與變更最佳實踐

小寫+下劃線：user_order_items，避免大小寫敏感問題
前綴區分：pk_、uk_、idx_、fk_
欄位名無前綴：表名不加 t_/tbl_
大表 DDL 使用工具：pt-online-schema-change（觸發器模式）或 gh-ost（無觸發器，基於 Binlog），避免長時間元數據鎖
變更前備份：mysqldump --no-data -d db tbl > tbl_struct.sql
驗證腳本：變更後跑核心 SQL EXPLAIN 確認索引未失效