常見字符串類型

char

• 概述：char是固定長度的字符串類型，這意味着無論實際存儲的字符串長度是多少，每條記錄都佔用相同的存儲空間
• 例如，char(10)，不論插入“abc"還是”abcdefghij“，每條記錄都佔用10個字符的空間
• 填充機制：對於實際長度不足定義長度的字符，char 類型會在右側用空格符填充
• 空格處理：檢索時，MySQL會自動去除尾部填充的空格

特點：

• char 取值範圍 0-255
• char 類型在比較時會忽略尾部空格
• 對於長度基本相同的字符串，CHAR 比 VARCHAR 更高效

優勢：

• 固定長度有助於數據對齊，提高數據檢索和訪問效率，每條記錄長度固定，性能表現較為穩定
• 固定長度使得內存分配和計算更簡單高效
• 適合短且長度固定的數據，如國家代碼、性別標誌、MD5 值等
• 不像 VARCHAR 需要額外字節存儲長度信息

劣勢：

• 對於長度不足的數據，填充空格會導致空間浪費，當存儲大量短字符串時，整體存儲效率較低
• 不適合存儲長度變化大的數據
• 最大隻能定義 255 個字符

示例：

-- 創建表使用 CHAR 類型
CREATE TABLE users (
    id INT PRIMARY KEY,
    username CHAR(20),
    country_code CHAR(2),
    gender CHAR(1)
);

-- 插入數據
INSERT INTO users VALUES (1, 'john_doe    ', 'US', 'M'); -- 插入時尾部空格會被存儲

-- 查詢時注意
SELECT * FROM users WHERE username = 'john_doe'; -- 能匹配，因為比較時忽略尾部空格

性能考慮：

1、表連接性能：CHAR 類型的列在表連接時通常比 VARCHAR 更快
2、內存表：MEMORY 存儲引擎的表使用固定長度存儲，CHAR 和 VARCHAR 表現相同
3、排序操作：CHAR 類型的排序通常比 VARCHAR 更高效

varchar

• varchar是可變長度的字符串類型，只存儲實際的字符長度，加上1 或 2 字節用於存儲字符串的長度信息（取決於最大字符數）
• varchar 取值範圍 0-65535，對於0-255之間的字符串，使用1 個字節記錄長度，256-65535 之間的字符串，用 2 個字節記錄長度

存儲機制

1.可變長度存儲：

• 僅使用必要的空間存儲字符串內容
• 需要額外1-2個字節記錄字符串長度（長度≤255時用1字節，>255時用2字節）

2.空格處理：

• 存儲時：保留字符串中的所有空格（包括尾部空格）
• 檢索時：返回存儲的原始內容（包括尾部空格）

3.字符集影響：

• 最大長度限制以字節計算（65,535字節）
• 不同字符集下，每個字符佔用的字節數不同

• 例如 utf8mb4 字符集下，實際可存儲字符數約為 16,383 (65,535/4)

  主要特點

• 最大長度：最大可定義長度為 65,535 字節（受行大小限制）
• 比較行為：比較時包括所有空格（與 CHAR 不同）
• 存儲效率：只佔用實際需要的空間加長度字節

優點：

1.空間效率高：只佔用實際需要的存儲空間，減少空間浪費
2.靈活性強：適合存儲長度變化大的字符串數據
3.支持長文本：最大支持約16K-64K字符（取決於字符集）
4.尾部空格保留：完整保留原始數據中的空格
5.存儲空間利用高，僅存儲實際字符數和少量長度信息，適用於存儲長度差異較大的字符串數據

缺點：

1.讀取性能稍低：相比 CHAR 需要額外處理長度信息
2.更新可能引起行遷移：長度變化大的更新可能導致行移動
3.內存計算複雜：需要動態計算存儲空間
4.碎片化風險：頻繁更新不同長度的值可能導致存儲碎片
5.每條記錄需要額外的長度信息存儲，帶來一些存儲和計算開銷。某些情況下，插入和更新操作科能略低於 CHAR 類型

使用建議

1、適合使用 VARCHAR 的情況：

• 存儲用户輸入的變長數據（如用户名、地址等）
• 存儲長度變化大的文本數據
• 存儲長度不確定的中長字符串
• 需要保留原始空格的數據

2、不適合使用 VARCHAR 的情況：

• 存儲固定長度的代碼或標識符
• 對性能要求極高的短字符串列
• 非常短的字符串（可能比 CHAR 佔用更多空間）

示例：

-- 創建表使用 VARCHAR 類型
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(2000),
    sku VARCHAR(20)
);

-- 插入數據
INSERT INTO products VALUES 
(1, 'Laptop', 'High-performance gaming laptop with 16GB RAM', 'LP-1001-X');

-- 查詢時注意
SELECT * FROM products WHERE name = 'Laptop   '; -- 不匹配，因為VARCHAR比較包含空格

和CHAR比較：

性能考慮

行格式影響：

• COMPACT 行格式：768字節後內容會存儲在溢出頁
• DYNAMIC 行格式：更適合長VARCHAR列

索引限制：

• InnoDB索引前綴最長767字節（innodb_large_prefix開啓時為3072字節）
• 使用utf8mb4時，索引VARCHAR(191)已達到限制

內存使用：

• 排序操作可能使用臨時表，會按最大長度分配內存

最佳實踐

合理設置長度：

• 不要過度分配（如VARCHAR(255)當只需要50）

字符集選擇：

• utf8mb4已成為現代應用的標準

大文本考慮：

• 超過VARCHAR限制時使用TEXT類型

索引優化：

• 對長VARCHAR列考慮使用前綴索引

text

概述： TEXT 是 MySQL 中用於存儲大文本數據的可變長度字符串類型，適合存儲超過 VARCHAR 容量限制的長文本內容。TEXT 類型實際上是一個系列，包括 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 四種變體。

• 同 char、varchar 類似，都可以存儲字符串，一般情況，遇到存儲長文本字符串的需求可以考慮使用 text 類型
• 日常場景中，存儲字符串儘量用 varchar
• text 類型無需指定長度
• 若數據庫未啓用嚴格的 sqlmode，當插入的值超過 text 的最大長度時，該值會被截斷插入並生成警告
• text 類型字段不能有默認值
• varchar 可直接創建索引，text 字段創建索引要制定前多少個字符
• text 類型檢索效率比 varchar 類型要低

存儲機制

1. 可變長度存儲：

   • 僅存儲實際內容，不預先分配固定空間
   • 內容與行數據分開存儲（行中只存儲20字節的指針）

2. 溢出存儲：

   • 當內容超過一定大小時，存儲在溢出頁中
   • 行格式為 COMPACT 或 DYNAMIC 時處理方式不同

3. 字符集支持：

   • 支持所有字符集
   • 最大長度以字符計算（不同字符集下字節限制不同）

TEXT 類型家族

主要特點

• 大容量存儲：最大可存儲4GB文本數據 (LONGTEXT)
• 嚴格模式限制：在嚴格SQL模式下插入超長數據會報錯
• 排序限制：只能使用前1024字節進行排序 (可配置)
• 默認值限制：不能有DEFAULT值 (除BLOB/TEXT列)

優點

 1. 超大容量：遠超 VARCHAR 的存儲能力
 2. 存儲效率：只佔用實際需要的空間
 3. 靈活性：適合存儲不可預知長度的文本
 4. 專用優化：MySQL對TEXT類型有專門的存儲處理

缺點

 1. 性能開銷：比 CHAR/VARCHAR 有更高的IO開銷
 2. 索引限制：必須使用前綴索引(最多1000字節)
 3. 內存使用：處理大文本可能消耗大量內存
 4. 功能限制：不能作為主鍵，不能有完整默認值

與 VARCHAR 比較

使用建議

適合使用 TEXT 的情況：

• 存儲文章內容、產品描述等長文本
• 存儲JSON/XML等結構化文本數據
• 存儲用户生成的富文本內容
• 需要超過VARCHAR容量的文本

不適合使用 TEXT 的情況：

• 短於65535字符的文本(可用VARCHAR)
• 需要完整索引的列
• 頻繁參與WHERE條件的列
• 內存表中的列

示例

-- 創建表使用TEXT類型
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    full_text LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入數據
INSERT INTO articles (title, content) 
VALUES ('MySQL指南', '這是關於MySQL TEXT類型的詳細指南...');

-- 查詢示例(使用前綴索引)
CREATE INDEX idx_content ON articles(content(100));
SELECT * FROM articles WHERE content LIKE '%MySQL%';

性能考慮

查詢優化：

• 避免SELECT * 查詢包含TEXT列的表
• 對TEXT列使用覆蓋索引很困難

內存使用：

• 排序操作可能使用磁盤臨時表
• 大文本會消耗大量內存緩衝區

複製影響：

• 大文本會增加複製延遲
• 基於行的複製更高效

InnoDB注意事項：

• COMPACT行格式只存儲前768字節在行內
• DYNAMIC行格式更適合大文本

最佳實踐

1. 合理選擇子類型： 根據實際需要選擇最小夠用的類型
2. 分離大文本： 考慮將大文本存儲在單獨的表中
3. 延遲加載： 應用層實現大文本的按需加載
4. 避免過度使用： 能用VARCHAR解決的不用TEXT
5. 字符集選擇： utf8mb4推薦用於完整Unicode支持

blob

• 是一個可以存儲二進制文件的容器，主要用於存儲二進制大對象，例如可以存儲圖片，音視頻等文件，按存儲容量大小不同來分類，可分為四類

存儲機制

二進制存儲：

• 直接存儲原始字節數據，不進行字符集轉換
• 與行數據分開存儲（行中只存儲20字節的指針）

溢出存儲：

• 內容存儲在單獨的溢出頁中 -DYNAMIC 行格式更高效處理大BLOB對象

無字符集：

• 不關聯字符集，按原始字節存儲

主要特點

二進制安全： 完全保留原始字節數據

大容量存儲： 最大可存儲4GB數據

無字符集轉換： 適合存儲非文本數據

嚴格模式限制：插入超長數據會報錯

默認值限制： 不能有DEFAULT值

使用場景

適合使用 BLOB 的情況：

• 存儲圖片、音頻、視頻等媒體文件
• 存儲PDF、Word等文檔文件
• 存儲加密的二進制數據
• 存儲序列化的對象數據

不適合使用 BLOB 的情況：

• 純文本數據（應使用TEXT）
• 需要頻繁查詢的內容
• 需要作為索引列的完整內容

BLOB 與 TEXT 對比

BLOB 與 VARCHAR 對比

BLOB 子類型對比

示例

-- 創建包含BLOB列的表
CREATE TABLE user_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    document_data MEDIUMBLOB,
    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入BLOB數據（通常由應用程序完成）
-- 在應用程序中使用參數化查詢插入二進制數據

-- 查詢BLOB數據（通常只查詢元數據）
SELECT id, document_name, upload_time 
FROM user_documents 
WHERE user_id = 1001;

最佳實踐

考慮文件系統存儲： 對於大於1MB的文件，考慮存儲在文件系統中，數據庫中只存路徑

限制BLOB大小： 評估實際需求，選擇合適大小的BLOB類型

避免SELECT ： 查詢時避免不必要地檢索BLOB內容

分表設計： 將BLOB列放在單獨的表中，減少主表IO壓力

應用層處理： 在應用層實現大對象的流式讀寫

性能注意事項

內存使用： 大BLOB對象會消耗大量內存

複製延遲： 大BLOB會增加複製延遲

備份影響： 含大BLOB的表備份較慢

連接性能： 避免對含BLOB的表進行頻繁連接操作

替代方案

對於現代應用，考慮以下替代方案：

• 文件系統存儲 + 數據庫路徑記錄
• 專用對象存儲服務（如AWS S3）
• 分佈式文件系統

enum

• 枚舉，ENUM類型允許字段的值從一個預定義的值集合中選擇，並且可以通過限制用户輸入的方式，確保數據的一致性和完整性
• ENUM類型的值以整數形式存儲，而不是存儲為字符串，例如：值1 對應 1 ，值2 對應 2，以此類推
• 1-255個枚舉值，需要一個字節存儲
• 256-65535個枚舉值，需要2個字節存儲
• 在查詢時，ENUM字段返回的是字符串值，而不是對應的整數值
• 可以為 ENUM類型字段指定一個默認值，如果插入數據時該字段沒有賦值，會使用這個默認值
• 優點：數據完整性，避免非法值
• ENUM值以整數形式存儲，空間佔用少，存儲效率高
• 代碼可讀性，使代碼和數據結構更清晰，很容易看到一個字段的允許值範圍
• 缺點：擴展性差，一旦定義了ENUM類型，如果需要添加新的枚舉值，必須使用 ALTER TABLE 修改表結構，對大表來説，修改表結構可能會影響性能
• 限制性，ENUM類型只適合值範圍固定且相對較小的字段
• 查詢複雜性，ENUM 的底層存儲為整數，而返回的是字符串，在一些情況下，特別設計數值比較時，可能產生混淆

與VARCHAR/SET的比較

示例

-- 創建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('Male', 'Female', 'Other'),
    status ENUM('Active', 'Inactive', 'Pending') DEFAULT 'Pending'
);

-- 插入數據
INSERT INTO users VALUES 
(1, 'John', 'Male', 'Active'),
(2, 'Jane', 'Female', DEFAULT);

-- 查詢示例
SELECT * FROM users WHERE gender = 'Female';

注意事項

1、空值與NULL：

    ENUM('') 允許空字符串
    ENUM 列也可以為NULL

2、數值上下文

-- ENUM在數值運算中使用索引值
SELECT gender+0 FROM users; -- 返回1,2等數字

3、排序規則

-- 按定義順序排序，而非字母順序
SELECT * FROM users ORDER BY status;

4、建議

• 適合不常變化的選項列表
• 選項值應簡潔明確
• 避免使用數字作為枚舉值（易混淆）

性能考慮

索引效率： ENUM上的索引非常高效

連接性能： 比VARCHAR連接性能更好

內存使用： 比VARCHAR更節省內存

不適合場景： 頻繁變化的選項集不適合

set-

• 用於存儲一組預定義的字符串值，允許從預定義的值中選擇零個或多個值進行存儲
• 一個SET列可以存儲多個值
• 在表創建時定義SET值
• 存儲的值順序不影響實際值
• 優點： 存儲效率高，佔用空間小
• 查詢效率高，和ENUM一樣，可以利用位運算，值1 對應1，值2 對應2，以此類推
• 數據完整性，只能存儲預定義的值，保證數據一致性
• 單個列可以存儲多個值，而ENUM 單個列只能存儲一個值
• 缺點： 修改SET預定義值需要 ALTER TABLE ，對大表很耗時
• 成員數量限制，最多隻能有64個不同的成員
• 可讀性差，位掩碼的存儲方式對直接查看不友好
• 不是所有數據庫都支持SET，可移植性差
• 排序問題，基於位掩碼，而不是字符串值排序
• 注意事項：SET值存儲時會自動去重和排序
• 空字符串也是有效的SET值

存儲機制-

內部存儲: 按位存儲（每個值對應一個bit）
空間佔用: 1-8字節（取決於成員數量）
最大成員數: 64個不同元素

成員數量與存儲空間-

優缺點-

與ENUM/VARCHAR的比較-

使用示例-

-- 創建表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    tags SET('tech', 'science', 'health', 'business', 'entertainment'),
    permissions SET('read', 'write', 'delete', 'share') DEFAULT 'read'
);

-- 插入數據
INSERT INTO articles VALUES 
(1, 'AI Research', 'tech,science', 'read,write'),
(2, 'Market News', 'business', DEFAULT);

-- 查詢示例
-- 查找包含'tech'標籤的文章
SELECT * FROM articles WHERE FIND_IN_SET('tech', tags);

-- 查找有write權限的文章
SELECT * FROM articles WHERE permissions & 2;  -- write是第2個選項

常用操作函數-

位運算操作（數值上下文）

注意：所有函數操作都不會修改原始數據，而是返回新的結果。實際存儲的SET值是用逗號分隔的字符串形式（如 'tech,science'），但在數值上下文中會被轉換為位掩碼數值進行計算。

注意事項-

1、空值與NULL：

• SET('') 允許空字符串
• SET 列也可以為NULL

2、數值運算：

-- SET在數值運算中使用位掩碼值
SELECT tags+0 FROM articles; -- 返回位掩碼數值

3、排序規則：

• 按數值（位掩碼）排序，可能不符合預期

4、建議：

• 適合中等規模的固定選項集
• 選項名應簡潔明確
• 避免頻繁修改選項集合

MySQL基本整數類型

MySQL 整數類型作用説明

補充説明

• UNSIGNED：所有整數類型都可添加此屬性，用於存儲非負數
• 存儲範圍：類型名稱前綴(TINY/SMALL/MEDIUM/)表示其存儲能力大小
• 自動遞增：這些類型都可用於AUTO_INCREMENT列

特殊屬性

INT UNSIGNED -- 無符號整數（只存儲非負數）
TINYINT(1) -- 常用於表示布爾值（0/1）

MySQL 浮點與精確數值類型作用説明

使用場景對比

語法示例

-- 單精度浮點
FLOAT
FLOAT(p) -- p為精度位數

-- 雙精度浮點 
DOUBLE
DOUBLE PRECISION

-- 精確小數
DECIMAL(10,2) -- 共10位，2位小數
DECIMAL(M,D)  -- M=總位數，D=小數位
NUMERIC(M,D)  -- DECIMAL的別名

注意事項-

FLOAT/DOUBLE存在浮點誤差，不適合精確計算

DECIMAL以字符串形式存儲，確保精確但佔用更多空間

未指定精度時，FLOAT默認約7位，DOUBLE默認約15位

對於貨幣金額，推薦使用DECIMAL(19,4)

MySQL 日期時間類型作用説明

使用場景對比

語法示例

-- 創建包含各種時間類型的表
CREATE TABLE time_examples (
    birth_date DATE,
    meeting_time TIME,
    graduation_year YEAR,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入數據示例
INSERT INTO time_examples VALUES 
('1990-05-15', '14:30:00', 2020, '2023-01-15 09:30:45', NULL);

注意事項-

TIMESTAMP會受時區影響，DATETIME不會

TIMESTAMP有2038年問題（最大到2038-01-19）

YEAR類型可以存儲2位或4位年份（4位推薦）

TIMESTAMP列在記錄更新時會自動更新（需配置）

時間函數（NOW(), CURDATE()等）適用於這些類型

MySQL 常見字符集作用説明

使用場景對比

重要説明

1. utf8mb4替代utf8：MySQL的utf8是閹割版(只支持3字節)，實際應使用utf8mb4
2. 排序規則：每種字符集都有對應的排序規則(如utf8mb4_general_ci)

3. 存儲影響：

   • utf8mb4比GBK多佔用存儲空間(中文都是3字節)
   • latin1存儲效率最高但功能有限

MySQL常見字符集排序規則對照表

排序規則命名解析

• 後綴含義：

  • ci : Case Insensitive (不區分大小寫)
  • cs : Case Sensitive (區分大小寫)
  • bin : Binary (二進制比較)
  • ai : Accent Insensitive (不區分重音)

性能與準確性對比

配置建議

-- 推薦使用(MySQL 5.7+)
CREATE TABLE my_table (
    ...
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MySQL 8.0+推薦
CREATE TABLE my_table (
    ...
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

配置示例

-- 創建使用utf8mb4的表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4,
    bio TEXT CHARACTER SET utf8mb4
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 修改數據庫字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

關聯查詢中，如果關聯的字段字符集不一樣，會影響查詢

注意事項-

> utf8mb4_unicode_ci能正確處理多語言排序（如中文按拼音）
> 
> general_ci系列性能更好但排序準確性較低
> 
> 區分大小寫的查詢應使用*_bin或*_cs規則
> 
> 排序規則影響GROUP BY、DISTINCT、ORDER BY等操作

設置字符集

服務器級設置-

配置文件修改 (my.cnf/my.ini)

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

#重啓MySQL 服務生效
#影響所有新建數據庫的默認字符集

-

數據庫級設置-

創建時指定：

CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

修改現有數據庫：

ALTER DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-

表級設置-

創建時指定：

CREATE TABLE mytable (
  id INT PRIMARY KEY
) DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci;

修改現有表：

ALTER TABLE mytable 
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-

列級設置-

創建時指定：

CREATE TABLE mytable (
  id INT,
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

修改現有列：

ALTER TABLE mytable 
  MODIFY COLUMN name VARCHAR(100) 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_bin;

-

優先級説明-

列級 > 表級 > 數據庫級 > 服務器級

-

查看當前設置-

-- 查看服務器設置
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

-- 查看數據庫設置
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'mydb';

-- 查看錶設置
SELECT TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable';

-- 查看列設置
SELECT CHARACTER_SET_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'mydb' 
  AND TABLE_NAME = 'mytable' 
  AND COLUMN_NAME = 'name';

-

最佳建議-

1、統一使用utf8mb4字符集

2、服務器級設置基礎默認值

3、關鍵表/列單獨指定更嚴格的規則

4、混合語言環境使用utf8mb4_unicode_ci

5、需要區分大小寫時使用utf8mb4_bin