02.MySQL数据库类型

一、💡 数据类型怎么选

核心原则

先确定数据保存的是数字、金额、文字、日期还是结构化内容，再选择能够准确表达业务含义、范围够用的类型。

本文以 MySQL 8.4 为依据，保留常见业务场景；其他版本使用前应核对对应文档。文中的建表、写入语句仅作为学习示例。

常见业务字段可以这样选择：

数据	推荐类型	示例
主键 ID	`BIGINT`	`10001`
数量、次数	`INT`	`3`
金额	`DECIMAL`	`54.70`
长度固定的字符串	`CHAR`	`CNY`
长度变化的字符串	`VARCHAR`	`机械键盘`
是否启用、是否删除	`TINYINT` / `BOOLEAN`	`0`、`1`
温度、测量值，允许近似	`FLOAT` / `DOUBLE`	`26.35`
生日、预约日期	`DATE`	`2026-10-23`
下单时间、支付时间	`DATETIME(3)`	`2026-10-23 14:30:00.123`
需要按会话时区转换的事件时刻	`TIMESTAMP(3)`	注意时区和取值范围
营业时间	`TIME`	`09:00:00`
仅保存年份	`YEAR`	`2026`
文章正文、商品详情	`TEXT` / `MEDIUMTEXT`	一段长文本
商品扩展属性	`JSON`	`{"color":"黑色"}`
原始字节数据	`VARBINARY` / `BLOB`	摘要、文件字节

二、🔢 整数类型：INT 和 BIGINT

INT 和 BIGINT 都用于保存没有小数部分的整数。

类型	常见用途
`INT`	商品数量、年龄、排序号、计数
`BIGINT`	数据库主键、订单号等可能持续增长的大整数

例如：

id BIGINT PRIMARY KEY,
quantity INT NOT NULL

如果数据可能超过 INT 的范围，就使用 BIGINT。主键通常会长期增长，因此业务表主键常用 BIGINT。

2.1 其他整数类型与范围

整数类型不只有 INT 和 BIGINT。默认有符号，可以保存负数；UNSIGNED 表示无符号，只能保存非负数。

类型	字节数	有符号范围	无符号范围
`TINYINT`	1	-128～127	0～255
`SMALLINT`	2	-32,768～32,767	0～65,535
`MEDIUMINT`	3	-8,388,608～8,388,607	0～16,777,215
`INT`	4	-2³¹～2³¹-1	0～2³²-1
`BIGINT`	8	-2⁶³～2⁶³-1	0～2⁶⁴-1

例如订单状态只有几个值，用 TINYINT 就够；库存数量虽然不能小于零，也要考虑扣减时的运算行为和应用类型映射，不能只为了扩大范围就全部加上 UNSIGNED。

看起来是数字，不一定该用整数

手机号、邮政编码、带前导零的工号和外部订单编号，通常使用 VARCHAR。它们是标识，不用来加减，可能包含前导零、字母或符号。

2.2 INT(11) 不是最多保存 11 位

INT(11) 中的 11 是历史上的显示宽度，不决定存储空间和取值范围，也不限制只能存 11 位数字。整数显示宽度在 MySQL 8.4 中已弃用，新设计直接写 INT、BIGINT 即可。

三、💰 金额类型：DECIMAL

3.1 为什么金额使用 DECIMAL

金额优先使用 DECIMAL，不要使用 FLOAT 或 DOUBLE。

DECIMAL 保存精确值；FLOAT 和 DOUBLE 保存近似值。例如在一些编程语言中：

0.1 + 0.2 = 0.30000000000000004

这种误差经过大量订单汇总、折扣分摊和税费计算后，可能造成对账差异。

3.2 DECIMAL 后面的数字是什么意思

amount DECIMAL(19, 4) NOT NULL

DECIMAL(19, 4) 表示：

总共最多保存 19 位数字。
小数点后最多保存 4 位。
小数点前最多保存 19 - 4 = 15 位。

常见选择：

场景	推荐类型
普通金额，只精确到分	`DECIMAL(18, 2)`
支付、财务、税费、折扣	`DECIMAL(19, 4)`
汇率、高精度单价	根据业务选择，例如 `DECIMAL(20, 8)`

Java 中使用 BigDecimal 对应数据库的 DECIMAL：

import java.math.BigDecimal;
 
// 使用字符串创建，避免先产生 double 浮点误差。
BigDecimal amount = new BigDecimal("54.70");

四、🔤 字符串类型：CHAR 和 VARCHAR

4.1 最核心的区别

先记住

CHAR(5) 写入 3 个字符不会报错。5 表示最多接收 5 个字符，不是要求每次必须写满 5 个字符。

类型	保存方式	适合保存
`CHAR(n)`	固定长度，不足时在末尾补空格	币种代码、国家代码等固定长度内容
`VARCHAR(n)`	可变长度，按照实际内容保存	姓名、商品名、手机号、邮箱、地址

4.2 CHAR(5) 写入 3 个字符会怎样

创建两个最大长度都是 5 个字符的字段：

CREATE TABLE string_demo (
    char_value CHAR(5),
    varchar_value VARCHAR(5)
);

都只写入 3 个字符：

INSERT INTO string_demo VALUES ('ABC', 'ABC');

两者都能正常写入，不会报错。区别在内部保存方式：

CHAR(5)     按固定长度处理：'ABC  '  末尾补两个空格
VARCHAR(5)  按实际长度保存：'ABC'    不补空格

查询时，两者看起来都是 ABC：

SELECT char_value, varchar_value FROM string_demo;

char_value  varchar_value
ABC         ABC

这是因为 MySQL 默认会删除 CHAR 查询结果末尾的空格。

4.3 超过 5 个字符会怎样

写入内容	字符数	`CHAR(5)`	`VARCHAR(5)`
`'ABC'`	3	正常，末尾补空格	正常，按 3 个字符保存
`'ABCDE'`	5	正常，刚好写满	正常，达到最大长度
`'ABCDEF'`	6	严格模式下报错	严格模式下报错

INSERT INTO string_demo VALUES ('ABCDEF', 'ABCDEF');

严格模式下会出现类似错误：

ERROR 1406 (22001): Data too long for column 'char_value' at row 1

非严格模式可能把内容截断成 ABCDE 并产生警告。生产环境不要依赖自动截断，应开启严格模式并在应用层校验长度。

4.4 VARCHAR(100) 是 100 个字符，还是 100 个字节

CHAR(n) 和 VARCHAR(n) 的 n 表示字符数。例如 VARCHAR(100) 可以保存最多 100 个字符，并不是最多 100 个字节。

实际占用字节数取决于字符集。使用 utf8mb4 时，每个字符最多占 4 个字节，所以不能把字段长度无限增大；还要受行大小、索引长度等限制。

-- CHAR_LENGTH 看字符数；LENGTH 看字节数。连接使用 utf8mb4 时，结果为 2 和 6。
SELECT CHAR_LENGTH('键盘') AS char_count,
       LENGTH('键盘') AS byte_count;

五、📅 日期时间：DATE、DATETIME、TIMESTAMP、TIME 和 YEAR

5.1 先根据业务要保存什么来选

同样叫“时间”，生日、下单时刻和营业时间表达的含义并不一样。

类型	保存内容	示例	典型场景
`DATE`	年月日	`2000-05-20`	生日、入职日期、预约日期
`DATETIME`	年月日时分秒	`2026-10-23 14:30:00`	下单时间、预约的本地日期时间
`TIMESTAMP`	日期时间，读写时按会话时区转换	`2026-10-23 14:30:00`	登录、日志等事件时刻，需确认范围够用
`TIME`	时分秒，也可表示一定范围内的时长	`09:00:00`	每天营业开始时间、短时长
`YEAR`	年份	`2026`	出厂年份、毕业年份

不要为了统一写成字符串：日期类型可以校验日期并支持日期计算。生日只需要 DATE，没有必要额外补一个 00:00:00。

5.2 DATETIME 和 TIMESTAMP：长得一样，时区行为不同

核心区别

DATETIME 保存日期时间本身，不自动按会话时区换算；TIMESTAMP 写入时从会话时区转换到 UTC，读取时再转回当前会话时区。两者都不会保留原始时区名称。

例如北京时间 2026-10-23 14:30:00 的一次支付：

操作	DATETIME	TIMESTAMP
在 `+08:00` 会话写入	`2026-10-23 14:30:00`	对应 UTC 的 `2026-10-23 06:30:00`
在 `+00:00` 会话读取	仍显示 `2026-10-23 14:30:00`	显示 `2026-10-23 06:30:00`

可以用只读查询检查当前连接的设置：

SELECT @@session.time_zone AS session_time_zone,
       @@global.time_zone AS global_time_zone;

选择建议：普通业务表可以用 DATETIME，但应用与数据库连接必须约定时间含义，例如统一保存 UTC。需要数据库按会话时区转换时可以选 TIMESTAMP，同时检查其范围。跨时区预约若需要保留“当地几点”的含义，应另外保存时区标识，例如 Asia/Shanghai。

以上行为依据：MySQL 8.4：DATE、DATETIME 和 TIMESTAMP。

5.3 取值范围：为什么要注意 2038 年

以下列出秒精度下的正常值范围，不把零日期作为业务可用日期：

类型	取值范围
`DATE`	`1000-01-01`～`9999-12-31`
`DATETIME`	`1000-01-01 00:00:00`～`9999-12-31 23:59:59`
`TIMESTAMP`	UTC `1970-01-01 00:00:01`～`2038-01-19 03:14:07`
`TIME`	`-838:59:59`～`838:59:59`
`YEAR`	`1901`～`2155`，另允许 `0000`

比如合同到期时间可能是 2050-12-31，MySQL 8.4 的 TIMESTAMP 就不适合，应该考虑 DATETIME。

TIME 可以保存 36:00:00，所以它不只表示一天中的几点。但累计工时、播放耗时若可能很长，或需要直接汇总，通常用 INT / BIGINT 保存秒数或毫秒数，并把单位写进字段名，例如 duration_seconds。

5.4 DATETIME(3) 中的 3 是什么意思

这里的 3 表示秒后面的小数位数，与 DECIMAL 的总位数含义不同。

类型	精度	示例
`DATETIME` 或 `DATETIME(0)`	秒	`2026-10-23 14:30:00`
`DATETIME(3)`	毫秒	`2026-10-23 14:30:00.123`
`DATETIME(6)`	微秒	`2026-10-23 14:30:00.123456`

TIME、DATETIME、TIMESTAMP 都支持 0～6 位小数秒；省略时默认为 0。需要毫秒时，应同时保证应用传值和数据库字段都保留毫秒。

范围与精度依据：MySQL 8.4：日期时间类型语法。

5.5 创建时间和更新时间怎么写

下面是放在建表语句中的字段定义片段：

-- 插入时不传 created_at，数据库自动填入当前时间，保留毫秒。
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
 
-- 插入时自动赋值；本行其他字段实际发生变化时自动更新时间。
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
 
-- 未支付时没有支付时间，使用 NULL 表示，而不是伪造一个日期。
paid_at DATETIME(3) NULL

DEFAULT CURRENT_TIMESTAMP(3) 管默认创建值，ON UPDATE CURRENT_TIMESTAMP(3) 管自动更新。把其他字段更新成原值时，不会因此自动刷新时间；显式赋值也会影响行为。DATETIME 同样支持这两个属性，并非只有 TIMESTAMP 支持。

支付时间应该在支付成功时由业务写入，不能给 paid_at 加自动更新时间，否则修改商品备注也可能把支付时间改掉。

官方说明：MySQL 8.4：自动初始化与更新。

5.6 查询某一天的数据：包含起点，不包含终点

以订单明细的创建时间为例：

-- 包含 10 月 23 日零点，排除 10 月 24 日零点，兼容毫秒和微秒精度。
SELECT id, product_name, created_at
FROM order_item
WHERE created_at >= '2026-10-23 00:00:00'
  AND created_at <  '2026-10-24 00:00:00';

不要把一天的终点简单写成 23:59:59，否则可能漏掉 23:59:59.123。若字段存的是 UTC，应用还要先把业务时区的一天起止时间转换成 UTC，再作为查询边界。

六、🚦 布尔值和状态：TINYINT、BOOLEAN 与 ENUM

6.1 是否删除，为什么常用 TINYINT

常见写法是用 0 表示否，1 表示是：

-- 字段定义片段：类型之外，用约束保证只允许 0、1。
is_deleted TINYINT NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1))

MySQL 的 BOOL、BOOLEAN 是 TINYINT(1) 的别名，并不是独立的布尔存储类型。TINYINT(1) 也不会自动限制只能写 0 和 1；布尔判断中，0 为假，非零为真。因此需要应用校验，必要时像上面一样添加 CHECK 约束。

订单状态不止两个值时，可以用 TINYINT 保存码值，例如 0=待支付、1=已支付、2=已取消，并在字段注释、应用枚举或字典中明确含义。

6.2 ENUM：从固定选项中选一个

-- 字段定义片段：尺码只能从给定字符串中选择一个。
size ENUM('S', 'M', 'L', 'XL') NOT NULL

ENUM 适合值集稳定的单选字段。增加选项需要修改表定义；频繁扩展的业务状态可以考虑状态码加字典表。它默认按声明顺序排序，不是按字符串字母顺序排序。

多选还有 SET 类型，但用户角色、商品标签等存在独立关系的数据，通常更适合关联表，方便维护和查询。

七、🌡️ 浮点数：FLOAT 和 DOUBLE 什么时候用

前面说金额不适合浮点数，不代表浮点数没用。它适合允许近似误差的测量值、科学计算等。

类型	常见存储空间	选择方式
`FLOAT`	4 字节	可接受较低精度的测量数据
`DOUBLE`	8 字节	需要比 FLOAT 更高精度的近似数值
`DECIMAL(p, s)`	随精度变化	金额、需要按十进制精确表达的数据

例如传感器测量温度，可以考虑 DOUBLE；若业务规定必须精确到固定的小数位并据此结算，仍应选择 DECIMAL。不要用“只要有小数就选 DOUBLE”的规则。

八、📄 长文本、二进制和 JSON

8.1 商品详情、文章正文：TEXT 家族

短备注有明确上限时，可以用 VARCHAR(500)；文章正文、长商品详情可以考虑 TEXT 家族。

类型	最大内容长度，单位为字节
`TINYTEXT`	255
`TEXT`	65,535，约 64 KiB
`MEDIUMTEXT`	16,777,215，约 16 MiB
`LONGTEXT`	4,294,967,295，约 4 GiB

这里是字节数，不是字符数；中文和表情的实际字符容量取决于编码。实际传输还受 max_allowed_packet 等配置限制，不代表可以直接一次写入 4 GiB。

列表页只需要商品名称和价格时，不要顺手查询整段详情。普通索引和全文检索也有不同设计，不能把 TEXT 当成一个无限放大的 VARCHAR 使用。

8.2 图片、文件字节：BINARY、VARBINARY 和 BLOB

类型	含义	典型场景
`BINARY(n)`	固定长度字节串	固定长度摘要等，需注意补零字节行为
`VARBINARY(n)`	可变长度字节串，n 按字节计	短的原始二进制内容
`BLOB` 家族	较大的二进制内容	文件字节等

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 的容量级别分别对应 TEXT 家族。关键区别是：TEXT 有字符集和排序规则，BLOB 按字节处理。

实际项目中的商品图片、视频和大附件通常放在对象存储，数据库用 VARCHAR 保存对象标识或地址，方便独立管理文件容量和访问。

8.3 商品扩展属性：JSON

同样是商品，键盘有轴体、配列，衣服有颜色、尺码。少量不固定的扩展属性可以放在 JSON 字段中：

{"color": "黑色", "switch": "红轴", "keys": 87}

MySQL 的原生 JSON 类型会校验 JSON 格式，并允许通过路径读取属性。例如第九章订单案例中的商品快照：

-- ->> 提取指定路径的值并去除 JSON 字符串外层引号。
SELECT id, product_attrs->>'$.color' AS color
FROM order_item;

JSON 适合扩展内容，不建议把订单金额、状态、用户 ID 等稳定且经常筛选关联的字段全部塞进去。需要频繁查询某个属性时，应考虑独立列或合适的生成列／函数索引；JSON 列本身不能直接作为普通索引列。

8.4 地理位置：了解空间类型

MySQL 还提供 POINT、LINESTRING、POLYGON 等空间类型，可用于地点、路线和区域。只做普通订单业务时不必使用；涉及附近门店、配送区域等需求，再结合坐标系、SRID 和空间索引单独设计。

九、🛒 综合案例：订单明细表

下面沿用“购买机械键盘”的场景，补上状态、配送日期、商品属性快照和审计时间。示例面向 MySQL 8.4，可在独立学习库中练习。

CREATE TABLE order_item (
    -- 主键会持续增长，使用 BIGINT。
    id BIGINT PRIMARY KEY,
 
    -- 商品名称长短不一，使用 VARCHAR。
    product_name VARCHAR(100) NOT NULL,
 
    -- 商品数量是整数，使用 INT。
    quantity INT NOT NULL,
 
    -- 金额需要精确计算，使用 DECIMAL。
    unit_price DECIMAL(19, 4) NOT NULL,
    pay_amount DECIMAL(19, 4) NOT NULL,
 
    -- CNY、USD 等币种代码固定为 3 个字符，使用 CHAR。
    currency_code CHAR(3) NOT NULL,
 
    -- 示例约定：0=待支付、1=已支付、2=已取消。
    status TINYINT NOT NULL DEFAULT 0 CHECK (status IN (0, 1, 2)),
 
    -- 预计送达只关心哪一天；没有确定日期时允许为空。
    expected_delivery_date DATE NULL,
 
    -- 未支付时为空，支付成功时由业务明确赋值。
    paid_at DATETIME(3) NULL,
 
    -- 短备注有明确长度上限；扩展属性保留购买时的商品快照。
    remark VARCHAR(500) NULL,
    product_attrs JSON NULL,
 
    -- 创建与更新都保留毫秒；DATETIME 的时间含义由应用统一约定。
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
        ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

写入一条订单明细：

INSERT INTO order_item (
    id,
    product_name,
    quantity,
    unit_price,
    pay_amount,
    currency_code,
    status,
    expected_delivery_date,
    paid_at,
    remark,
    product_attrs
) VALUES (
    10001,
    '机械键盘',
    2,
    299.0000,
    598.0000,
    'CNY',
    1,
    '2026-10-25',
    '2026-10-23 14:30:00.123',
    '工作日配送',
    JSON_OBJECT('color', '黑色', 'switch', '红轴', 'keys', 87)
);

这条数据中：

10001 是主键，用 BIGINT。
机械键盘 长度不固定，用 VARCHAR。
2 是购买数量，用 INT。
299.0000 和 598.0000 是金额，用 DECIMAL。
CNY 固定为 3 个字符，用 CHAR(3)。
1 表示已支付状态，用 TINYINT，具体含义由业务约定。
2026-10-25 只有日期，用 DATE。
支付时间带毫秒，用 DATETIME(3)。
商品颜色和轴体是扩展快照，用 JSON。
插入时省略创建时间和更新时间，由数据库使用默认值填入。

可用下面的查询检查保存结果：

SELECT id, product_name, quantity, pay_amount, currency_code,
       status, expected_delivery_date, paid_at,
       product_attrs->>'$.color' AS color,
       created_at, updated_at
FROM order_item
WHERE id = 10001;

应看到商品为机械键盘、数量为 2、金额为 598.0000、状态为 1、颜色为黑色，支付时间保留 .123；创建与更新时间取实际插入时的当前时间。

十、📌 快速回顾

没有小数的普通数字使用 INT；长期增长的主键常用 BIGINT。
金额使用 DECIMAL，Java 中使用 BigDecimal。
不要使用 FLOAT 或 DOUBLE 保存需要精确结算的金额。
长度经常变化的字符串使用 VARCHAR。
长度始终固定的短字符串可以使用 CHAR。
CHAR(5) 写入 3 个字符不会报错；只有超过 5 个字符才会在严格模式下报错。
只有年月日用 DATE；日期加时分秒用 DATETIME，需要毫秒就写 DATETIME(3)。
TIMESTAMP 会按会话时区转换，MySQL 8.4 中还要注意 2038 年范围限制。
每天几点用 TIME；长期累计时长可用整数保存，并明确单位。
是否类字段常用 TINYINT / BOOLEAN，但类型本身不限制只能写 0、1。
长文本用 TEXT 家族；原始字节用二进制类型；不固定的结构化属性可用 JSON。
NULL 表示没有值或未知，不要把未支付时间写成零日期，也不要把未知金额默认当成零。

Afyke的个人博客

探索

02.MySQL数据库类型

一、💡 数据类型怎么选

二、🔢 整数类型：INT 和 BIGINT

2.1 其他整数类型与范围

2.2 INT(11) 不是最多保存 11 位

三、💰 金额类型：DECIMAL

3.1 为什么金额使用 DECIMAL

3.2 DECIMAL 后面的数字是什么意思

四、🔤 字符串类型：CHAR 和 VARCHAR

4.1 最核心的区别

4.2 CHAR(5) 写入 3 个字符会怎样

4.3 超过 5 个字符会怎样

4.4 VARCHAR(100) 是 100 个字符，还是 100 个字节

五、📅 日期时间：DATE、DATETIME、TIMESTAMP、TIME 和 YEAR

5.1 先根据业务要保存什么来选

5.2 DATETIME 和 TIMESTAMP：长得一样，时区行为不同

5.3 取值范围：为什么要注意 2038 年

5.4 DATETIME(3) 中的 3 是什么意思

5.5 创建时间和更新时间怎么写

5.6 查询某一天的数据：包含起点，不包含终点

六、🚦 布尔值和状态：TINYINT、BOOLEAN 与 ENUM

6.1 是否删除，为什么常用 TINYINT

6.2 ENUM：从固定选项中选一个

七、🌡️ 浮点数：FLOAT 和 DOUBLE 什么时候用

八、📄 长文本、二进制和 JSON

8.1 商品详情、文章正文：TEXT 家族

8.2 图片、文件字节：BINARY、VARBINARY 和 BLOB

8.3 商品扩展属性：JSON

8.4 地理位置：了解空间类型

九、🛒 综合案例：订单明细表

十、📌 快速回顾

十一、🔗 官方资料

目录