萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 數據類型全解析:數值、日期時間、字符串、二進制、枚舉選型指南

數值類型

類型存儲有符號範圍無符號範圍典型用途
TINYINT1B-128 ~ 1270 ~ 255布爾、狀態碼、小枚舉
SMALLINT2B-32,768 ~ 32,7670 ~ 65,535較小計數、年齡
MEDIUMINT3B-8,388,608 ~ 8,388,6070 ~ 16,777,215中等計數、IPv4 轉換
INT / INTEGER4B-2,147,483,648 ~ 2,147,483,6470 ~ 4,294,967,295**主鍵、外鍵、通用計數**
BIGINT8B-9.22E18 ~ 9.22E180 ~ 1.84E19大數據 ID、毫秒時間戳、雪花算法 ID
FLOAT(M,D)4B±1.17E-38 ~ ±3.40E38—科學計算、非精確測量(7 位有效數字)
DOUBLE(M,D)8B±2.22E-308 ~ ±1.79E308—高精度科學計算(15-16 位有效數字)
DECIMAL(M,D) / NUMERICM+2 B精確定點數—**金融金額、精確計算** (M≤65, D≤30, D≤M)

M, D 說明: M=總位數(含小數點/符號), D=小數位數。
UNSIGNED:禁止負數,正數範圍翻倍。
ZEROFILL:自動補 0(如 INT(5) ZEROFILL 存 123 → 顯示 00123),隱含 UNSIGNED。

日期時間類型

類型存儲範圍格式特性
DATE3B1000-01-01 ~ 9999-12-31YYYY-MM-DD純日期,生日、截止日
TIME3B-838:59:59 ~ 838:59:59HH:MM:SS時間間隔、鬧鐘時間
DATETIME8B (5.6.4 前)/5B+小數1000-01-01 00:00:00 ~ 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS[.fsp]**創建/更新時間**,不依賴時區
TIMESTAMP4B (5.6.4 前)/4B+小數1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTCYYYY-MM-DD HH:MM:SS[.fsp]**自動設置/更新**,存儲 UTC,顯示按會話時區轉換
YEAR1B1901 ~ 2155 (4位) / 1970~2069 (2位)YYYY / YY年份存儲

關鍵差異:

  • DATETIME:時區無關,存什麼顯示什麼,適合業務時間(如下單時間)。
  • TIMESTAMP:存儲 UTC,讀取時按 time_zone 轉換,**自動更新**(DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP),適合審計日誌、最後修改時間。
  • 5.6.4+ 支持微秒精度 DATETIME(3)、TIMESTAMP(3)。

字符串類型

類型最大長度存儲方式適用場景
CHAR(M)255 字符固定長度,右填充空格MD5(32)、UUID(36)、國標代碼、固定長度代碼
VARCHAR(M)65,535 字符 (行總長限制)實際長度 + 1-2B 長度前綴**通用變長文本**:姓名、標題、地址、JSON 片段
TINYTEXT / TINYBLOB255 B長度前綴 1B極短文本/二進制
TEXT / BLOB64 KB長度前綴 2B文章內容、日誌
MEDIUMTEXT / MEDIUMBLOB16 MB長度前綴 3B長文檔、圖片
LONGTEXT / LONGBLOB4 GB長度前綴 4B超大文本/視頻/文件流

TEXT vs BLOB: TEXT 按字符集排序比較(大小寫不敏感依賴 Collation),BLOB 按字節比較(二進制、大小寫敏感)。
VARCHAR vs TEXT: VARCHAR 可建索引、可內存臨時表、性能優;TEXT 只能建前綴索引、磁盤臨時表。能用 VARCHAR 絕不用 TEXT。

二進制類型

類型對應文本類型特性
BINARY(M)CHAR(M)固定長度二進制,右填充 0x00
VARBINARY(M)VARCHAR(M)變長二進制
TINYBLOB..LONGBLOBTINYTEXT..LONGTEXT二進制大對象,不指定長度

用於存儲加密數據、哈希值、文件流、圖片二進制(建議存對象存儲路徑,DB 存元數據)。

枚舉與集合

類型存儲特性示例
ENUM('A','B','C')1-2B單選,內部存數字索引(1-based),NULL 為 0,空字符串為錯誤值性別、狀態、類型
SET('A','B','C')1-8B多選,位掩碼存儲,最多 64 個成員權限標簽、多屬性

ENUM 陷阱: 數字上下文會返回索引而非字符串(SELECT enum_col+0),排序按索引而非字符串值。
ALTER TABLE 增加 ENUM 值 需重建表(Metadata Lock),高併發慎用。

列屬性速查

屬性含義
NULL / NOT NULL允許/禁止 NULL,**主鍵/唯一索引列建議 NOT NULL**
DEFAULT value默認值(常量/函數:CURRENT_TIMESTAMP、UUID() 8.0+)
AUTO_INCREMENT自增(整數類型),每表僅一列
PRIMARY KEY主鍵(隱含 NOT NULL + UNIQUE),InnoDB 聚簇索引
UNIQUE KEY唯一約束,允許多個 NULL
COMMENT 'text'列註釋,文檔化利器
CHARACTER SET / COLLATE列級字符集/校對規則

選型決策樹

  1. **整數首選**:TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT 按範圍最小化存儲。
  2. **精確小數**:DECIMAL(10,2) 金額,**禁用 FLOAT/DOUBLE**。
  3. **日期時間**:業務時間用 DATETIME,審計/自動更新用 TIMESTAMP。
  4. **字符串**:VARCHAR 覆蓋 90% 場景;固定長度用 CHAR;超長用 TEXT(盡量避免)。
  5. **枚舉/集合**:低基數、固定集合、頻繁過濾 → ENUM/SET;否則用關聯表/查找表。
  6. **二進制**:哈希、加密、文件指紋 → BINARY/VARBINARY。

總結

「用最小的類型滿足業務需求」是索引性能、緩存命中、磁盤 I/O、備份恢復速度的基石。建表前先做數據剖析,選對類型,事半功倍。

copyright © 萬盛學電腦網 all rights reserved