MySQL 資料庫命名與設計規範：從 3NF 到工程落地的完整標準
統一的命名規範、設計原則與文檔標準是團隊協作、系統可維護性的基石。本文涵蓋設計原則、字符規範、庫/表/欄位命名、類型選擇、文檔模板、索引原則、SQL 規範與工程技巧。
一、設計原則
1. 標準化與規範化 (3NF)
- 核心原則：「One Fact in One Place」—— 事實只在一處存儲
- 表僅包含實體固有屬性，關聯通過外鍵連接
- 例：
Customer、Order 兩表，Order 僅存 customer_id 指向 Customer
- 效率優先時可適度反範式化（冗餘字段減少 JOIN），但須文檔記錄
2. 數據驅動
- 策略、配置、流程數據化存儲，避免硬編碼
- 例：外部數據源連接信息、工作流定義、角色權限矩陣均入庫
- 將維護權限下放給業務用戶，減少代碼發布
3. 考慮變化
- 預留歷史追蹤欄位：
start_date、end_date、version
- 例：姓名變更（婚姻改姓）需保留歷史記錄
二、字符規範
- 允許字符：26 英文字母（區分大小寫）、0-9、下劃線
_，共 63 個
- 禁止：中文、特殊符號、空格、保留字
- 長度限制：對象名 ≤ 30 字符，變量名 ≤ 29 字符（不含
@）
三、資料庫命名規範
| 類型 | 格式 | 範例 |
|---|
| 正式庫 | {biz}_{env} 小寫+下劃線 | web_19floor_net, web_car |
| 備份庫 | {正式庫}_{YYYYMMDD} | web_19floor_net_20240115 |
四、資料表命名規範
| 類型 | 格式 | 範例 |
|---|
| 業務表 | {biz_prefix}_{entity} 前綴 ≤ 5 字符 | web_user, web_group, supe_userspace |
| 備份表 | {正式表}_{YYYYMMDD} | web_user_20240115 |
| 關聯表 | {entity1}_{entity2} | user_role, order_item |
| 臨時表 | tmp_{biz}_{purpose} | tmp_order_export_202401 |
五、欄位命名規範
- 格式：
lowerCamelCase，建議帶表前綴
- 範例：
web_user 表 → userId, userName, userPassword
- 關聯欄位跨表同名：
web_user.userId ↔ web_group.userId
- 布爾型建議
is_/has_/can_ 前綴：isDeleted, hasPermission
- 時間戳：
createdAt (INT, Unix 秒)、gmtCreated (DATETIME)
- 枚舉值：
status (TINYINT) + 字典表/註釋說明
六、欄位類型選擇原則
| 原則 | 錯誤示例 | 正確示例 |
|---|
| 用最小足夠類型 | INT 存狀態值(0-3) | TINYINT UNSIGNED |
| 定長優於變長 | VARCHAR(255) 存 MD5 | CHAR(32) 或 BINARY(16) |
| 精確類型 | FLOAT 存金額 | DECIMAL(19,4) 或 BIGINT 存分 |
| 時間類型 | VARCHAR 存日期 | DATETIME / TIMESTAMP / INT(Unix秒) |
| 大文本 | TEXT 存 200 字描述 | VARCHAR(500) (能用 VARCHAR 別用 TEXT) |
七、資料庫設計文檔模板
—————————————————————————
表名： web_user
作者： ZhangSan
日期： 2024-01-15
版本： 1.0
描述： 存儲用戶基礎資料
具體內容：
userId BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY 用戶唯一標識
userName VARCHAR(32) NOT NULL UNIQUE 用戶名
userPassword CHAR(60) NOT NULL bcrypt 哈希
email VARCHAR(128) NOT NULL UNIQUE 郵箱
phone VARCHAR(20) NULL 手機號
status TINYINT UNSIGNED NOT NULL DEFAULT 1 1=正常 2=禁用 3=鎖定
createdAt INT UNSIGNED NOT NULL 創建時間戳
gmtCreated DATETIME NOT NULL 創建時間
updatedAt INT UNSIGNED NOT NULL 更新時間戳
gmtModified DATETIME NOT NULL 更新時間
version INT UNSIGNED NOT NULL DEFAULT 1 樂觀鎖版本號
—————————————————————————
八、索引使用原則
- 邏輯主鍵用唯一組合索引，系統鍵（存儲過程用）用唯一非組合索引，外鍵列建非組合索引
- **必須索引外鍵**—— 關聯查詢高頻使用
- **禁止索引**
BLOB/TEXT、大字段（前綴索引除外）
- **小表不建索引**—— 全表掃描比維護索引更快（頻繁增刪時尤甚）
- 單表索引數 ≤ 5 個（非核心表 ≤ 3），定期清理未用索引
pt-index-usage
九、SQL 編寫規範
- 關鍵字**全大寫**：
SELECT, FROM, WHERE, ORDER BY, GROUP BY, JOIN
- 表名、庫名、欄位名用**反引號包裹**：
\`table\`, \`column\`
- 字符串用**單引號**：
'value'
- 範例：
SELECT COUNT(*) FROM \`web_user\` WHERE \`userName\` = 'zhangsan';
- 複雜查詢使用 CTE (
WITH) 替代嵌套子查詢
- 禁止
SELECT *，禁止 INSERT 不指定欄位名
十、工程落地技巧
- 避免觸發器： 改用應用層事件/消息隊列，調試困難、隱式邏輯
- 用英文而非拼音縮寫：
user_name 而非 un，下拉選單按英文排序
- 保存元數據： 專表存
db_version, last_check, doc_link, owner
- 版本機制： 表結構變更記錄
schema_version 表，支援回滾
- 文檔化一切： 註釋、命名規範、限制、函數，工具支援 (DataGrip/PDMan/DBDoc)
- 測試三部曲： 單元測試 → 集成測試 → 用戶驗收測試 (UAT)，上線前必做
- 原型驗證設計： 針對每個展示頁/報表，驗證數據模型能否支撐查詢
