歡迎來到啾啾的博客🐱。
這是一個致力于構建完善 Java 程序員知識體系的博客📚。
它記錄學習點滴，分享工作思考和實用技巧，偶爾也分享一些雜談💬。
歡迎評論交流，感謝您的閱讀😄。

本篇簡單記錄總結一下數據庫、表創建事項。
持續更新。

創建數據庫

字符集（Charset）與排序規則（Collation）

使用可視化工具創建MySQL數據庫時，可以選擇數據編碼（CHARSET）與排序規則（COLLATE）。
不選擇時模式字符集為utf8mb4，排序規則為utfmb4_0900_ai_ci。

下面做這兩者的具體解釋。

字符集 Charset

字符集是數據庫中字符的編碼方式，決定了可以存儲哪些字符（如字母、符號、表情等）。

• utf8mb4：支持完整的Unicode字符（如Emoji），推薦使用。

• latin1：僅支持西歐語言字符。

• gbk：支持簡體中文。

• 若需多語言支持（如中文、日文、Emoji），使用 utf8mb4（MySQL的utf8僅支持3字節，已過時）。

• 僅需英文或西歐字符時，可用更節省空間的latin1。

排序規則

排序規則是字符集中字符的比較和排序規則，影響ORDER BY、WHERE查詢及索引行為。

排序規則組成部分為：字符集_版本_附加規則_比較規則

以默認的排序規則為utfmb4_0900_ai_ci為例

0900（基于 Unicode 9.0 標準）
常見版本：

• unicode:：版本<MySQL8.0使用
• 0900： 新版unicode，MySQL8.0+版本支持
• general：較簡單的排序規則，性能略優但不精確

ai-發音無關
ci-不區分大小寫
常見后綴（附加規則、比較規則）：

• ci（Case Insensitive）：不區分大小寫（如'a' = 'A'）。
• cs（Case Sensitive）：區分大小寫（如'a' ≠ 'A'）。
• bin（Binary）：按二進制值比較（區分大小寫和重音）。
• ai（Accent Insensitive）：不區分重音符號（如 a = à = á）。
• as（Accent Sensitive）：區分重音符號（如 a ≠ à ≠ á）。

MySQL 8按默認使用utfmb4_0900_ai_ci，之前版本使用utf8mb4_unicode_ci即可。

引擎

MySQL常見的引擎有MyISAM和InnoDB。

特性	MyISAM	InnoDB
事務支持	? 不支持事務	? 支持 ACID 事務（提交、回滾、隔離）
鎖機制	表級鎖（寫操作會鎖全表）	行級鎖（寫操作僅鎖定特定行）
外鍵約束	? 不支持	? 支持外鍵約束
崩潰恢復	? 數據易損壞，需手動修復	? 通過 redo log 自動恢復，可靠性高
索引結構	非聚簇索引（索引與數據分離）	聚簇索引（主鍵索引直接存儲數據行）
全文索引	? 支持（早期版本僅支持 MyISAM）	? MySQL 5.6+ 支持
存儲文件	3 個文件：.frm（表結構）、.MYD（數據）、.MYI（索引）	1 個文件：.ibd（表空間，包含數據和索引）
MVCC（多版本并發控制）	? 不支持	? 支持，適合高并發讀操作
緩存機制	僅緩存索引，數據依賴 OS 緩存	緩存索引和數據（Buffer Pool）

MyISAM逐漸要被淘汰了，但還是有面試題在問兩者差別。其實在MySQL5.6開始支持全文索引后，創建的時候基本不考慮MyISAM引擎了。

不過MyISAM的高性能讀設計值得了解。僅了解部分和InnoDB設計差異的地方，不一定比InnoDB好。
MyISAM索引與數據完全分離，索引加載到內存的數據更快，數據連續存儲掃描更快。
MyISAM 通過 key_buffer_size 配置項緩存索引塊，減少磁盤 I/O。InnoDB則是按需加載，LRU淘汰。
不支持事務也就沒有更多的鎖競爭。
總的來說適合寫入后基本不修改的讀多寫少場景。

MySQL現在默認創建都是InnoDB。

創建表

命名

命名規范

參考這篇"良好的命名規范能減輕工作負擔"。創建表也是，需要盡可能精簡。
實際生產中，因為表數量特別多，多采取分段式命名，例如：
xx系統_xx模塊_xx業務，或者 xx模塊_xx實體_xx關系。

另外，MySQL 表名長度限制為 64 個字符（包括字符集編碼后的字節數）。

注意事項

還有值得注意的一點，不同操作系統的文件系統對文件名大小寫敏感性的處理不同。
Linux通常是大小寫敏感的，table1 和 Table1 被視為不同文件。
Windows是大小寫不敏感的，table1 和 Table1 被視為相同文件。

MySQL 的 lower_case_table_names 參數定義了表名的大小寫處理規則，常用值如下：

• 0：表名大小寫敏感，存儲和查詢時嚴格區分大小寫。
• 1：表名存儲為小寫，查詢時大小寫不敏感（將表名轉換為小寫后比較）。
• 2：表名大小寫敏感，但存儲時保持原始大小寫，查詢時大小寫不敏感。

Linux/Unix：默認為 0（大小寫敏感）。
Windows：默認為 1（表名存儲為小寫，查詢大小寫不敏感）。
macOS：默認為 2（大小寫敏感存儲，查詢不敏感，但 macOS 文件系統默認不敏感）。

所以，考慮跨平臺影響，推薦設計時全小寫表名。

三范式

滿足基本三范式:原子性，主鍵依賴，非傳遞依賴

• 第一范式(關系型數據庫的基本需求)
  列（屬性）的原子性
• 第二范式
  滿足第一范式，表必須有主鍵，且非主鍵屬性必須完全依賴于主鍵。
  （非主鍵列必須直接依賴主鍵）
• 第三范式
  滿足第二范式，且非主鍵列不傳遞依賴主鍵

理解數據類型

可以看之前的這篇MySQL數據類型。

選擇數據類型時需要權衡可維護性、可拓展性、存儲效率、性能。遵循的原則優先級為：

1. 功能正確性：確保數據類型滿足業務需求（范圍、精度、操作）。
2. 存儲效率：在功能正確的前提下，選擇占用空間較小的類型。
3. 性能優化：考慮查詢、排序、索引等性能影響。
4. 可維護性和擴展性：選擇便于維護和未來擴展的類型。
5. 實現復雜性：盡量降低開發和維護的復雜度。

假設你要設計一個字段存儲用戶的年齡：

• 功能正確性：年齡通常是 0-150 之間的整數，TINYINT UNSIGNED（0-255）足夠。
• 存儲效率：TINYINT（1 字節）優于 INT（4 字節）。
• 性能優化：整數類型比字符串類型更快，TINYINT 索引效率高。
• 可擴展性：TINYINT 足以應對未來需求（150+ 的年齡極少見）。
• 實現復雜性：TINYINT 直觀易用，無需復雜轉換。

如果選 VARCHAR(3) 存儲年齡，雖然功能上可行，但存儲效率低、性能差、維護復雜，完全不可取。

必須字段

• 有序的主鍵
  在沒有主鍵時，InnoDB會檢查是否存在一個唯一非空列索引并將其作為實際上主鍵，沒有則會生成隱式ROWID，6字節長度。
  定義主鍵可以確保數據唯一，避免重復或歧義，也避免隱式主鍵帶來的額外管理開銷，隱式主鍵性能也低于顯示主鍵。

通用冗余字段

為了優化查詢性能、簡化開發、記錄元數據或支持未來擴展，設計表時常在表中添加一些并非嚴格必需的字段。

• created_at、updated_at
  創建時間created_at與更新時間updated_at，便于審計和排序。
  命名為created_time、updated_time也可以。沒這個需求可以不創建，很多時候只創建一個updated_at也能滿足需求。

• status
  支持業務狀態管理。

• is_deleted
  用于實現軟刪除。

• created_by、updated_by
  創建人和更新人，適用于多用戶系統。

注意，VARCHAR類型可變長度，但在MySQL中，單行最大大小被限制為65535字節，即64KB。

合適的索引

可以看MySQL索引這篇。