MySQL字段类型和字符集

常见字符串类型

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比较：

特性	VARCHAR	CHAR
存储方式	可变长度	固定长度
空间使用	实际长度+1-2字节	总是使用定义的长度
读取速度	稍慢	更快
适用场景	长度变化大的字符串	长度固定的短字符串
最大长度	65,535 字节	255 字符
空格处理	保留尾部空格	去除尾部空格

性能考虑

行格式影响：
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 类型要低

存储机制

可变长度存储：
- 仅存储实际内容，不预先分配固定空间
- 内容与行数据分开存储（行中只存储20字节的指针）
溢出存储：
- 当内容超过一定大小时，存储在溢出页中
- 行格式为 COMPACT 或 DYNAMIC 时处理方式不同
字符集支持：
- 支持所有字符集
- 最大长度以字符计算（不同字符集下字节限制不同）

TEXT 类型家族

类型	最大长度(字符)	最大字节数	长度字节
TINYTEXT	255	255	1
TEXT	65,535	64KB	2
MEDIUMTEXT	16,777,215	16MB	3
LONGTEXT	4,294,967,295	4GB	4

主要特点

大容量存储：最大可存储4GB文本数据 (LONGTEXT)
严格模式限制：在严格SQL模式下插入超长数据会报错
排序限制：只能使用前1024字节进行排序 (可配置)
默认值限制：不能有DEFAULT值 (除BLOB/TEXT列)

优点

 1. 超大容量：远超 VARCHAR 的存储能力
 2. 存储效率：只占用实际需要的空间
 3. 灵活性：适合存储不可预知长度的文本
 4. 专用优化：MySQL对TEXT类型有专门的存储处理

缺点

 1. 性能开销：比 CHAR/VARCHAR 有更高的IO开销
 2. 索引限制：必须使用前缀索引(最多1000字节)
 3. 内存使用：处理大文本可能消耗大量内存
 4. 功能限制：不能作为主键，不能有完整默认值

与 VARCHAR 比较

特性	TEXT 系列	VARCHAR
最大长度	TINYTEXT: 255B TEXT: 64KB MEDIUMTEXT: 16MB LONGTEXT: 4GB	最大 65,535 字节（受行大小限制）
存储方式	内容存储在溢出页（行内只存指针）	行内存储（短内容）或溢出页
索引支持	仅支持前缀索引（最多 1000 字节）	支持完整列索引
内存表支持	不支持	支持
默认值	不允许设置 DEFAULT 值	允许设置 DEFAULT 值
排序限制	默认只使用前 1024 字节排序	无限制
字符计算	最大字符数=字节限制/字符集字节长度	同 TEXT
IO 开销	较高（需访问溢出页）	较低（短内容存行内）
适用场景	长文本、富文本、大段描述	短到中等长度可变字符串

使用建议

适合使用 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行格式更适合大文本

最佳实践

合理选择子类型： 根据实际需要选择最小够用的类型
分离大文本： 考虑将大文本存储在单独的表中
延迟加载： 应用层实现大文本的按需加载
避免过度使用： 能用VARCHAR解决的不用TEXT
字符集选择： utf8mb4推荐用于完整Unicode支持

blob

是一个可以存储二进制文件的容器，主要用于存储二进制大对象，例如可以存储图片，音视频等文件，按存储容量大小不同来分类，可分为四类

类型	最大长度	存储需求	描述
TINYBLOB	255 字节	长度 + 1 字节	微小二进制对象
BLOB	65,535 字节 (64KB)	长度 + 2 字节	常规二进制对象
MEDIUMBLOB	16,777,215 字节 (16MB)	长度 + 3 字节	中等二进制对象
LONGBLOB	4,294,967,295 字节 (4GB)	长度 + 4 字节	大型二进制对象

存储机制

二进制存储：
直接存储原始字节数据，不进行字符集转换
与行数据分开存储（行中只存储20字节的指针）
溢出存储：
内容存储在单独的溢出页中 -DYNAMIC 行格式更高效处理大BLOB对象
无字符集：
不关联字符集，按原始字节存储

主要特点

二进制安全： 完全保留原始字节数据
大容量存储： 最大可存储4GB数据
无字符集转换： 适合存储非文本数据
严格模式限制：插入超长数据会报错
默认值限制： 不能有DEFAULT值

使用场景

适合使用 BLOB 的情况：
存储图片、音频、视频等媒体文件
存储PDF、Word等文档文件
存储加密的二进制数据
存储序列化的对象数据
不适合使用 BLOB 的情况：
纯文本数据（应使用TEXT）
需要频繁查询的内容
需要作为索引列的完整内容

BLOB 与 TEXT 对比

特性	BLOB	TEXT
存储内容	二进制数据	文本数据
字符集处理	无	有
排序比较	按字节值	按字符集规则
索引支持	仅前缀索引	仅前缀索引
大小限制	相同等级类型大小相同	相同等级类型大小相同

BLOB 与 VARCHAR 对比

特性	BLOB	VARCHAR
存储内容	二进制数据	文本数据
最大长度	4GB (LONGBLOB)	65,535 字节
字符集	不适用	支持字符集
默认值	不允许设置	允许设置
索引	仅前缀索引	完整列索引
存储位置	溢出页	行内存储（短内容）

BLOB 子类型对比

类型对比项	TINYBLOB	BLOB	MEDIUMBLOB	LONGBLOB
最大容量	255B	64KB	16MB	4GB
长度字节	1	2	3	4
典型用途	小图标	文档	高清图片	视频片段
行内存储	可能	很少	从不	从不

示例

-- 创建包含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 的底层存储为整数，而返回的是字符串，在一些情况下，特别设计数值比较时，可能产生混淆

优点	缺点
✅ 节省存储空间	❌ 修改枚举值需要ALTER TABLE
✅ 数据完整性保障	❌ 不支持表达式作为枚举值
✅ 可读性强	❌ 不同字符集可能影响排序
✅ 查询效率高	❌ 迁移到其他数据库可能不兼容

与VARCHAR/SET的比较

特性	ENUM	VARCHAR	SET
存储内容	单选项	任意字符串	多选项
存储效率	高	低	中
输入验证	严格	无	严格
适用场景	单选值	自由文本	多选值
最大成员	65,535	无	64

示例

-- 创建表
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个不同元素

成员数量与存储空间-

成员数量	存储需求
1-8	1字节
9-16	2字节
17-24	3字节
25-32	4字节
33-64	8字节

优缺点-

优点	缺点
✅ 多值存储高效	❌ 修改选项需要ALTER TABLE
✅ 节省存储空间	❌ 最大只能有64个成员
✅ 内置查找函数	❌ 排序不如ENUM直观
✅ 数据完整性保障	❌ 迁移兼容性问题

与ENUM/VARCHAR的比较-

特性	SET	ENUM	VARCHAR
存储内容	多选值	单选值	任意字符串
存储方式	位掩码	整数索引	原始字符串
输入验证	严格	严格	无
最大成员	64	65,535	无限制
典型用例	用户权限、标签	性别、状态	自由文本

使用示例-

-- 创建表
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个选项

常用操作函数-

函数	描述	示例	返回值说明
`FIND_IN_SET()`	检查值是否存在于SET中	`FIND_IN_SET('tech', tags)`	返回位置索引（从1开始），未找到返回0
`CONCAT()`	连接SET值与新值	`CONCAT(tags, ',finance')`	返回合并后的字符串（自动去重）
`LENGTH()`	返回SET值的字节长度	`LENGTH(permissions)`	返回1-8之间的整数
`BIT_COUNT()`	计算选中的值数量	`BIT_COUNT(permissions)`	返回选中项的个数
`FIELD()`	获取值在SET定义中的位置	`FIELD('write', permissions)`	返回定义顺序（从1开始）
`REPLACE()`	替换SET中的值	`REPLACE(tags, 'tech', 'ai')`	返回替换后的新SET字符串
`INSERT()`	在指定位置插入值	`INSERT(tags, 2, 0, 'new')`	返回修改后的SET字符串
`LIKE`	模糊匹配SET字符串	`tags LIKE '%tech%'`	返回布尔值（1/0）

位运算操作（数值上下文）

操作符	描述	示例	说明
`&`	按位与	`permissions & 2`	检测是否包含第2个选项
管道符（表格中管道符会被识别成表格的边框，还没研究出来怎么转义，所以无法显示。。。）	按位或	`permissions 管道符 4`	添加第4个选项
`^`	按位异或	`permissions ^ 1`	切换第1个选项状态
`~`	按位取反	`~permissions`	反转所有选项状态

注意：所有函数操作都不会修改原始数据，而是返回新的结果。实际存储的SET值是用逗号分隔的字符串形式（如 'tech,science'），但在数值上下文中会被转换为位掩码数值进行计算。

注意事项-

1、空值与NULL：

SET('') 允许空字符串
SET 列也可以为NULL

2、数值运算：

-- SET在数值运算中使用位掩码值
SELECT tags+0 FROM articles; -- 返回位掩码数值

3、排序规则：

按数值（位掩码）排序，可能不符合预期

4、建议：

适合中等规模的固定选项集
选项名应简洁明确
避免频繁修改选项集合

MySQL基本整数类型

类型	存储空间	有符号范围	无符号范围	典型用途
TINYINT	1字节	-128 ~ 127	0 ~ 255	状态标志、布尔值(0/1)
SMALLINT	2字节	-32,768 ~ 32,767	0 ~ 65,535	小规模计数、年份
MEDIUMINT	3字节	-8,388,608 ~ 8,388,607	0 ~ 16,777,215	中等规模ID、用户数
INT/INTEGER	4字节	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295	标准整数、主键ID
BIGINT	8字节	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	0 ~ 18,446,744,073,709,551,615	大数据量ID、金融数值

MySQL 整数类型作用说明

类型	作用描述
INT	标准整数类型，用于存储常规整数数据，适合大多数整数存储需求
INT UNSIGNED	无符号整数，仅存储非负整数，提供更大的正整数范围
TINYINT	微小整数，用于存储非常小范围的整数值，常用作状态标志
SMALLINT	小型整数，用于存储较小范围的整数值
MEDIUMINT	中等整数，用于存储中等范围的整数值
BIGINT	大型整数，用于存储极大范围的整数值

补充说明

UNSIGNED：所有整数类型都可添加此属性，用于存储非负数
存储范围：类型名称前缀(TINY/SMALL/MEDIUM/)表示其存储能力大小
自动递增：这些类型都可用于AUTO_INCREMENT列

特殊属性

INT UNSIGNED -- 无符号整数（只存储非负数）
TINYINT(1) -- 常用于表示布尔值（0/1）

MySQL 浮点与精确数值类型作用说明

类型	作用描述	特点
FLOAT	单精度浮点数	<ul><li>4字节存储</li><li>约7位有效数字</li><li>存在精度损失</li><li>计算速度快</li></ul>
DOUBLE	双精度浮点数	<ul><li>8字节存储</li><li>约15位有效数字</li><li>比FLOAT精度高但仍可能损失精度</li><li>性能略低于FLOAT</li></ul>
DECIMAL(M,D)	精确小数	<ul><li>精确存储数值</li><li>M为总位数(1-65)</li><li>D为小数位数(0-30)</li><li>无精度损失</li><li>计算速度较慢</li></ul>

使用场景对比

场景	推荐类型
科学计算、不需要精确结果	FLOAT/DOUBLE
财务数据、货币计算	DECIMAL
工程测量、中等精度需求	DOUBLE
需要绝对精确的数值运算	DECIMAL

语法示例

-- 单精度浮点
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 日期时间类型作用说明

类型	作用描述	格式	范围	存储大小
DATE	存储日期值（不含时间）	`YYYY-MM-DD`	1000-01-01 到 9999-12-31	3字节
TIME	存储时间值（不含日期）	`HH:MM:SS` 或 `HHH:MM:SS`	-838:59:59 到 838:59:59	3字节
YEAR	存储年份值	`YYYY`	1901 到 2155 (4位格式)	1字节
DATETIME	存储日期和时间组合	`YYYY-MM-DD HH:MM:SS`	1000-01-01 00:00:00 到 9999-12-31 23:59:59	8字节
TIMESTAMP	存储时间戳（自动时区转换）	`YYYY-MM-DD HH:MM:SS`	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC	4字节

使用场景对比

场景	推荐类型
只存储日期（如生日）	DATE
只存储时间（如营业时间）	TIME
存储年份（如毕业年份）	YEAR
需要完整日期时间（如订单时间）	DATETIME
需要自动更新的时间戳	TIMESTAMP

语法示例

-- 创建包含各种时间类型的表
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 常见字符集作用说明

字符集	作用描述	支持字符范围	存储需求	兼容性
GBK	简体中文字符集	<ul><li>支持GB2312所有汉字</li><li>扩展的21003个汉字</li><li>ASCII字符</li></ul>	中文字符2字节英文1字节	中文环境专用
latin1	西欧字符集	<ul><li>ISO 8859-1字符</li><li>基本ASCII扩展</li><li>不支持亚洲文字</li></ul>	每个字符1字节	广泛兼容但功能有限
utf8	UTF-8 Unicode (旧版)	<ul><li>基本多语言平面字符</li><li>不支持4字节字符</li></ul>	1-3字节/字符	存在兼容问题
utf8mb4	完整UTF-8 Unicode	<ul><li>支持所有Unicode字符</li><li>包括emoji表情</li><li>支持中文/日文/韩文</li></ul>	1-4字节/字符	现代应用标准

使用场景对比

使用场景	推荐字符集
纯中文系统	GBK
传统西欧系统	latin1
需要存储emoji	utf8mb4
多语言网站	utf8mb4
新项目开发	utf8mb4

重要说明

utf8mb4替代utf8：MySQL的utf8是阉割版(只支持3字节)，实际应使用utf8mb4
排序规则：每种字符集都有对应的排序规则(如utf8mb4_general_ci)
存储影响：
- utf8mb4比GBK多占用存储空间(中文都是3字节)
- latin1存储效率最高但功能有限

MySQL常见字符集排序规则对照表

字符集	常用排序规则	说明
GBK	`gbk_chinese_ci` (默认)	中文拼音排序，不区分大小写
	`gbk_bin`	二进制比较，区分大小写
latin1	`latin1_swedish_ci` (默认)	瑞典语规则，不区分大小写
	`latin1_general_ci`	通用西欧规则，不区分大小写
	`latin1_general_cs`	通用西欧规则，区分大小写
	`latin1_bin`	二进制比较，区分大小写
utf8	`utf8_general_ci` (旧默认)	旧版Unicode排序，不区分大小写
	`utf8_unicode_ci`	Unicode标准排序，不区分大小写
	`utf8_bin`	二进制比较，区分大小写
utf8mb4	`utf8mb4_unicode_ci` (推荐)	完整Unicode标准排序
	`utf8mb4_general_ci` (旧默认)	简化版Unicode排序
	`utf8mb4_0900_ai_ci` (MySQL 8.0+)	基于Unicode 9.0的增强排序
	`utf8mb4_bin`	二进制比较

排序规则命名解析

后缀含义：
- ci : Case Insensitive (不区分大小写)
- cs : Case Sensitive (区分大小写)
- bin : Binary (二进制比较)
- ai : Accent Insensitive (不区分重音)

性能与准确性对比

排序规则	排序速度	准确性	适用场景
`*_general_ci`	快	一般	性能敏感场景
`*_unicode_ci`	中	高	多语言标准排序
`*_0900_ai_ci`	慢	最高	MySQL 8.0+新项目

配置建议

-- 推荐使用(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

MySQL字段类型和字符集

常见字符串类型

char

varchar

存储机制

主要特点

使用建议

示例：

和CHAR比较：

性能考虑

最佳实践

text

存储机制

TEXT 类型家族

主要特点

优点

缺点

与 VARCHAR 比较

使用建议

示例

性能考虑

最佳实践

blob

存储机制

主要特点

使用场景

BLOB 与 TEXT 对比

BLOB 与 VARCHAR 对比

BLOB 子类型对比

示例

最佳实践

性能注意事项

替代方案

enum

与VARCHAR/SET的比较

示例

注意事项

性能考虑

set-

存储机制-

成员数量与存储空间-

优缺点-

与ENUM/VARCHAR的比较-

使用示例-

常用操作函数-

位运算操作（数值上下文）

注意事项-

MySQL基本整数类型

MySQL 整数类型作用说明

补充说明

特殊属性

MySQL 浮点与精确数值类型作用说明

使用场景对比

语法示例

注意事项-

MySQL 日期时间类型作用说明

使用场景对比

语法示例

注意事项-

MySQL 常见字符集作用说明

使用场景对比

重要说明

MySQL常见字符集排序规则对照表

排序规则命名解析

性能与准确性对比

配置建议

配置示例

注意事项-

设置字符集

服务器级设置-

数据库级设置-

表级设置-

列级设置-

优先级说明-

查看当前设置-

最佳建议-

晚上不吃饭

引用和评论

深入解析InnoDB Undo Log：事务回滚与MVCC的核心机制

Open WebUI：开源AI交互平台的全面解析