MYSQL-数据类型（data type）

运维小路

发布于 2025-09-30 15:37:54

1860

文章被收录于专栏：运维小路运维小路

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL（本章节）

PostgreSQL

MongoDB

Redis

Etcd

前面我们介绍数据库的基本操作，里面有一个创建表的过程，其中就涉及到数据类型。

-- 创建一个 users 表，包含 id, name, email 和 age 字段
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自增长
  name VARCHAR(100) NOT NULL,        -- 可变长字符串，非空
  email VARCHAR(100) UNIQUE,         -- 唯一约束
  age INT DEFAULT 0                  -- 默认值 0
);

当然这里只列出部分数据类型，实际我们在前面讲解shell的时候，也有提到过：SHELL编程-数据类型，这也是学习各种编程语言必须学习的一个步骤，虽然我除了shell外，并没有学会其他编程语言（指具有当程序员的能力）。

所以下面针对MYSQL的数据类型做一个简单的介绍，并不是要大家记住，这个活大概率都是开发的，但是我们要对他有一个基本的认识。

一、数值类型

数值类型用于存储数字，包括整数和浮点数。

1. 整数类型

这些类型用于存储没有小数部分的数字。它们的主要区别在于存储大小和范围。

类型	长度 (字节)	有符号范围 (Signed)	无符号范围 (Unsigned)	用途
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	中等范围数值
INT	4	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295	最常用的整数类型，如 ID、数量
BIGINT	8	非常大 (±9.22×10¹⁸)	0 到 18,446,744,073,709,551,615	极大范围数值，如科学计算、大数据

属性：

UNSIGNED：指定只存储非负数，可以扩大正数的范围。
ZEROFILL：用零填充不足的位数（例如 INT(5) ZEROFILL，数字 18 会显示为 00018）。注意：此特性在 MySQL 8.0 中已不推荐使用。
如果不指定则会默认有符号。

2. 浮点数类型（近似值）

用于存储近似值的数值，计算可能会有微小误差。

类型	长度 (字节)	用途	注意
FLOAT	4	单精度浮点数	精度大约为 7 位小数
DOUBLE	8	双精度浮点数	精度大约为 15 位小数

在计算机领域里面有个很有意思的话题就是1+1=3。

3. 定点数类型（精确值）

用于存储精确值的数值，如金额、价格等。计算是精确的。

类型	语法	用途
DECIMAL	DECIMAL(M, D)	精确的小数。M 是总位数（精度），D 是小数点后的位数（标度）。例如 DECIMAL(10, 2) 可以存储 12345678.12。

二、日期和时间类型

用于存储日期、时间或组合。

类型	格式	范围	用途
DATE	YYYY-MM-DD	'1000-01-01' 到 '9999-12-31'	只存储日期，如生日、发布日期
TIME	HH:MM:SS	'-838:59:59' 到 '838:59:59'	只存储时间，或持续时间
DATETIME	YYYY-MM-DD HH:MM:SS	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	存储日期和时间，与时区无关
TIMESTAMP	YYYY-MM-DD HH:MM:SS	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC	时间戳，存储自 Unix 纪元以来的秒数，与时区有关，存入和查询时会自动转换。占用空间比 DATETIME 小（4字节 vs 8字节）。
YEAR	YYYY	1901 到 2155	只存储年份

DATETIME vs TIMESTAMP：

范围： DATETIME 范围更大。
时区： TIMESTAMP 会根据服务器的时区进行转换，而 DATETIME 不会。
空间： TIMESTAMP 更节省空间。
自动更新： TIMESTAMP 可以设置为在行创建或更新时自动设置为当前时间（DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP）。

三、字符串类型

用于存储文本和二进制数据。

1. 短文本字符串

类型	最大长度	描述	用途
CHAR(M)	255 字符	定长字符串。即使存入的字符串小于 M，也会占用 M 个字符长度的空间。	存储长度固定的数据，如 MD5 哈希值（32位）、国家代码（如 'CN', 'US'）。查询速度比 VARCHAR 快。
VARCHAR(M)	65,535 字符	变长字符串。只用必要的空间+1或2个字节来记录长度。	最常用的类型，存储长度变化的数据，如姓名、标题、描述。

CHAR vs VARCHAR：

空间： VARCHAR 通常更节省空间。
性能： CHAR 由于长度固定，在查询和更新时速度稍快。
选择：如果长度变化不大或总是固定，用 CHAR；否则用 VARCHAR。

2. 长文本字符串 (BLOB/TEXT)

当需要存储超过 65KB 的数据时使用这些类型。它们都有对应的二进制版本（BLOB）和文本版本（TEXT）。TEXT 类型有字符集和排序规则，而 BLOB 存储的是二进制字节串。

类型	最大长度	用途
TINYTEXT / TINYBLOB	255 字节	短文本或二进制数据
TEXT / BLOB	64 KB	普通长度的文本或二进制数据
MEDIUMTEXT / MEDIUMBLOB	16 MB	中等长度的文本或二进制数据，如论文、图片
LONGTEXT / LONGBLOB	4 GB	极长的文本或二进制数据，如书籍、视频文件

注意：由于性能考虑，应避免在数据库中存储大量二进制文件（如图片、视频）。通常的做法是将文件存储在文件系统或对象存储中，而在数据库中只存储文件的路径（字符串）。

3. 枚举和集合类型

类型	描述	用途
ENUM('val1', '', 'val3'...)	枚举。只能从预定义的列表中选择一个值。	存储固定选项的值，如性别 (ENUM('男', '女', '其他'))、状态 (ENUM('draft', 'published', 'archived'))
SET('val1', 'val2', 'val3'...)	集合。可以从预定义的列表中选择多个值（组合）。	存储多选项，如用户标签 (SET('运动', '音乐', '读书', '电影'))

注意：虽然 ENUM 和 SET 节省空间，但修改可选值需要修改表结构，不够灵活。在实际开发中，有时会用 VARCHAR 或关联表的方式来替代，以增加灵活性。

四、JSON 类型 (MySQL 5.7.8+)

类型	描述	用途
JSON	专门用于存储 JSON 格式的数据	存储结构化的半结构化数据，如配置项、API 响应、动态字段。MySQL 提供了丰富的函数来操作 JSON 数据（如 JSON_EXTRACT, JSON_SEARCH）。

优点：自动验证格式，优化存储格式，提供高效的查询路径。

如何选择数据类型？

最小化原则：在满足业务需求的前提下，选择占用空间最小的数据类型。例如，如果年龄不会超过 255，就用 TINYINT UNSIGNED 而不是 INT。
简单原则：数字比字符串操作更快。例如，存储 IP 地址，可以用 INT UNSIGNED 配合 INET_ATON() 和 INET_NTOA() 函数，而不是用 VARCHAR(15)。
避免 NULL：尽量定义字段为 NOT NULL。因为 NULL 值会使索引、索引统计和值比较都变得更复杂。可以用默认值（如空字符串、0）来代替。
谨慎使用大文本和二进制类型：如 BLOB 和 TEXT，它们可能会严重影响查询性能。如果必须使用，最好将它们分离到单独的表中。