admin管理员组

文章数量:1027244

MySQL字段类型和字符集

常见字符串类型

char

  • 概述：char是固定长度的字符串类型，这意味着无论实际存储的字符串长度是多少，每条记录都占用相同的存储空间
  • 例如，char(10)，不论插入“abc"还是”abcdefghij“，每条记录都占用10个字符的空间
  • 填充机制：对于实际长度不足定义长度的字符，char 类型会在右侧用空格符填充
  • 空格处理：检索时，MySQL会自动去除尾部填充的空格

特点：

  • char 取值范围 0-255
  • char 类型在比较时会忽略尾部空格
  • 对于长度基本相同的字符串，CHAR 比 VARCHAR 更高效

优势：

  • 固定长度有助于数据对齐，提高数据检索和访问效率，每条记录长度固定，性能表现较为稳定
  • 固定长度使得内存分配和计算更简单高效
  • 适合短且长度固定的数据，如国家代码、性别标志、MD5 值等
  • 不像 VARCHAR 需要额外字节存储长度信息

劣势：

  • 对于长度不足的数据，填充空格会导致空间浪费，当存储大量短字符串时，整体存储效率较低
  • 不适合存储长度变化大的数据
  • 最大只能定义 255 个字符

示例：

代码语言：sql复制
-- 创建表使用 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 占用更多空间）

示例：

代码语言：sql复制
-- 创建表使用 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 类型要低

存储机制

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

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列)

优点

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

缺点

代码语言：bash复制
 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条件的列 内存表中的列

示例

代码语言：sql复制
-- 创建表使用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

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

类型

最大长度

存储需求

描述

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

典型用途

小图标

文档

高清图片

视频片段

行内存储

可能

很少

从不

从不

示例

代码语言：sql复制
-- 创建包含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

示例

代码语言：sql复制
-- 创建表
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：

代码语言：bash复制
	ENUM('') 允许空字符串
代码语言：bash复制
	ENUM 列也可以为NULL

2、数值上下文

代码语言：sql复制
-- ENUM在数值运算中使用索引值
SELECT gender+0 FROM users; -- 返回1,2等数字

3、排序规则

代码语言：sql复制
-- 按定义顺序排序，而非字母顺序
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

无限制

典型用例

用户权限、标签

性别、状态

自由文本

使用示例-

代码语言：sql复制
-- 创建表
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、数值运算：

代码语言：sql复制
-- 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

语法示例

代码语言：sql复制
-- 单精度浮点
FLOAT
FLOAT(p) -- p为精度位数

-- 双精度浮点 
DOUBLE
DOUBLE PRECISION

-- 精确小数
DECIMAL(10,2) -- 共10位，2位小数
DECIMAL(M,D)  -- M=总位数，D=小数位
NUMERIC(M,D)  -- DECIMAL的别名

注意事项-

代码语言：bash复制
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

语法示例

代码语言：sql复制
-- 创建包含各种时间类型的表
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);

注意事项-

代码语言：powershell复制
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

重要说明

  1. utf8mb4替代utf8：MySQL的utf8是阉割版(只支持3字节)，实际应使用utf8mb4
  2. 排序规则：每种字符集都有对应的排序规则(如utf8mb4_general_ci)
  3. 存储影响：
    • 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+新项目

配置建议

代码语言：sql复制
-- 推荐使用(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;

配置示例

代码语言：sql复制
-- 创建使用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;

关联查询中，如果关联的字段字符集不一样，会影响查询

注意事项-

代码语言：bash复制
> utf8mb4_unicode_ci能正确处理多语言排序（如中文按拼音）
> 
> general_ci系列性能更好但排序准确性较低
> 
> 区分大小写的查询应使用*_bin或*_cs规则
> 
> 排序规则影响GROUP BY、DISTINCT、ORDER BY等操作

设置字符集

服务器级设置-

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

代码语言：ini复制
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

#重启MySQL 服务生效
#影响所有新建数据库的默认字符集

-

数据库级设置-

创建时指定：

代码语言：sql复制
CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

修改现有数据库：

代码语言：sql复制
ALTER DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-

表级设置-

创建时指定：

代码语言：sql复制
CREATE TABLE mytable (
  id INT PRIMARY KEY
) DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci;

修改现有表：

代码语言：sql复制
ALTER TABLE mytable 
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-

列级设置-

创建时指定：

代码语言：sql复制
CREATE TABLE mytable (
  id INT,
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

修改现有列：

代码语言：sql复制
ALTER TABLE mytable 
  MODIFY COLUMN name VARCHAR(100) 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_bin;

-

优先级说明-

列级 > 表级 > 数据库级 > 服务器级

-

查看当前设置-

代码语言：sql复制
-- 查看服务器设置
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_ci5、需要区分大小写时使用utf8mb4_bin

MySQL字段类型和字符集

常见字符串类型

char

  • 概述：char是固定长度的字符串类型，这意味着无论实际存储的字符串长度是多少，每条记录都占用相同的存储空间
  • 例如，char(10)，不论插入“abc"还是”abcdefghij“，每条记录都占用10个字符的空间
  • 填充机制：对于实际长度不足定义长度的字符，char 类型会在右侧用空格符填充
  • 空格处理：检索时，MySQL会自动去除尾部填充的空格

特点：

  • char 取值范围 0-255
  • char 类型在比较时会忽略尾部空格
  • 对于长度基本相同的字符串，CHAR 比 VARCHAR 更高效

优势：

  • 固定长度有助于数据对齐，提高数据检索和访问效率，每条记录长度固定，性能表现较为稳定
  • 固定长度使得内存分配和计算更简单高效
  • 适合短且长度固定的数据，如国家代码、性别标志、MD5 值等
  • 不像 VARCHAR 需要额外字节存储长度信息

劣势：

  • 对于长度不足的数据，填充空格会导致空间浪费，当存储大量短字符串时，整体存储效率较低
  • 不适合存储长度变化大的数据
  • 最大只能定义 255 个字符

示例：

代码语言：sql复制
-- 创建表使用 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 占用更多空间）

示例：

代码语言：sql复制
-- 创建表使用 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 类型要低

存储机制

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

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列)

优点

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

缺点

代码语言：bash复制
 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条件的列 内存表中的列

示例

代码语言：sql复制
-- 创建表使用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

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

类型

最大长度

存储需求

描述

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

典型用途

小图标

文档

高清图片

视频片段

行内存储

可能

很少

从不

从不

示例

代码语言：sql复制
-- 创建包含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

示例

代码语言：sql复制
-- 创建表
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：

代码语言：bash复制
	ENUM('') 允许空字符串
代码语言：bash复制
	ENUM 列也可以为NULL

2、数值上下文

代码语言：sql复制
-- ENUM在数值运算中使用索引值
SELECT gender+0 FROM users; -- 返回1,2等数字

3、排序规则

代码语言：sql复制
-- 按定义顺序排序，而非字母顺序
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

无限制

典型用例

用户权限、标签

性别、状态

自由文本

使用示例-

代码语言：sql复制
-- 创建表
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、数值运算：

代码语言：sql复制
-- 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

语法示例

代码语言：sql复制
-- 单精度浮点
FLOAT
FLOAT(p) -- p为精度位数

-- 双精度浮点 
DOUBLE
DOUBLE PRECISION

-- 精确小数
DECIMAL(10,2) -- 共10位，2位小数
DECIMAL(M,D)  -- M=总位数，D=小数位
NUMERIC(M,D)  -- DECIMAL的别名

注意事项-

代码语言：bash复制
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

语法示例

代码语言：sql复制
-- 创建包含各种时间类型的表
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);

注意事项-

代码语言：powershell复制
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

重要说明

  1. utf8mb4替代utf8：MySQL的utf8是阉割版(只支持3字节)，实际应使用utf8mb4
  2. 排序规则：每种字符集都有对应的排序规则(如utf8mb4_general_ci)
  3. 存储影响：
    • 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+新项目

配置建议

代码语言：sql复制
-- 推荐使用(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;

配置示例

代码语言：sql复制
-- 创建使用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;

关联查询中，如果关联的字段字符集不一样，会影响查询

注意事项-

代码语言：bash复制
> utf8mb4_unicode_ci能正确处理多语言排序（如中文按拼音）
> 
> general_ci系列性能更好但排序准确性较低
> 
> 区分大小写的查询应使用*_bin或*_cs规则
> 
> 排序规则影响GROUP BY、DISTINCT、ORDER BY等操作

设置字符集

服务器级设置-

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

代码语言：ini复制
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

#重启MySQL 服务生效
#影响所有新建数据库的默认字符集

-

数据库级设置-

创建时指定：

代码语言：sql复制
CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

修改现有数据库：

代码语言：sql复制
ALTER DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-

表级设置-

创建时指定：

代码语言：sql复制
CREATE TABLE mytable (
  id INT PRIMARY KEY
) DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci;

修改现有表：

代码语言：sql复制
ALTER TABLE mytable 
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-

列级设置-

创建时指定：

代码语言：sql复制
CREATE TABLE mytable (
  id INT,
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

修改现有列：

代码语言：sql复制
ALTER TABLE mytable 
  MODIFY COLUMN name VARCHAR(100) 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_bin;

-

优先级说明-

列级 > 表级 > 数据库级 > 服务器级

-

查看当前设置-

代码语言：sql复制
-- 查看服务器设置
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_ci5、需要区分大小写时使用utf8mb4_bin

本文标签： MySQL字段类型和字符集

版权声明：本文标题：MySQL字段类型和字符集 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1747380431a2162747.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。