MySQL 数据类型

学习目标

掌握 MySQL 三大类数据类型（数值、字符串、日期时间）的具体分类与特性
理解不同数据类型在存储空间、精度及适用场景上的差异
学会根据业务需求选择最合适的数据类型以优化数据库性能
熟悉字符集与排序规则对字符串类型的影响
了解 JSON 等现代数据类型在 MySQL 中的应用

数值类型

MySQL 提供了丰富的数值类型，主要分为整数类型和浮点 / 定点数类型。合理选择数值类型不仅能节省存储空间，还能提高查询效率。

整数

整数类型用于存储没有小数部分的数值。MySQL 支持从单字节到八字节不等的多种整数类型，每种类型都有有符号和无符号之分。

类型	存储大小 (字节)	有符号范围	无符号范围	适用场景
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	中等规模 ID、天数统计
INT / INTEGER	4	-21 亿到 21 亿	0 到 42 亿	用户 ID、常规计数器
BIGINT	8	约 -922 京到 922 京	0 到 1844 京	分布式 ID、金融流水号

注意：在定义整数时，括号内的数字（如 INT(11)）仅影响显示宽度（配合 ZEROFILL 属性使用），并不限制存储数值的实际范围。

浮点与定点数

当需要存储带有小数部分的数值时，需根据对精度的要求选择浮点或定点类型。

FLOAT 和 DOUBLE：基于 IEEE 754 标准的近似值数据类型。FLOAT 占用 4 字节，DOUBLE 占用 8 字节。由于二进制浮点数的特性，它们不适合存储要求高精度的货币金额，可能会出现微小的精度丢失。
DECIMAL (或 NUMERIC)：精确的定点数类型。存储时以字符串形式保存数值，确保精度完全准确。定义格式为 DECIMAL(M, D)，其中 M 是总位数，D 是小数位数。这是存储货币金额的首选类型。

-- 示例：定义一个存储价格的字段，总共 10 位，其中 2 位小数
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2) NOT NULL
);

重点

整数类型根据数值范围选择，优先使用占用空间小的类型。
涉及金钱计算必须使用 DECIMAL 类型，严禁使用 FLOAT 或 DOUBLE。
INT(11) 中的 11 不代表存储上限，仅代表显示宽度。

字符串类型

字符串类型用于存储文本数据。MySQL 提供了多种字符串类型，主要区别在于长度限制、存储方式和字符集处理。

定长与变长

CHAR：定长字符串。定义时指定长度（如 CHAR(10)），无论实际存入多少字符，都会占用固定的存储空间。如果存入内容不足，右侧会用空格填充。检索时会去除尾随空格。适用于长度固定且较短的数据，如手机号、身份证前缀、哈希值。
VARCHAR：变长字符串。只占用实际内容所需的存储空间，外加 1 或 2 字节用于记录长度。适用于长度变化较大的文本，如用户名、邮箱地址、文章标题。

大文本

当需要存储大量文本或二进制数据时，使用 BLOB 或 TEXT 系列类型。它们的主要区别在于字符集和排序规则：

TEXT：用于存储非二进制字符串（字符数据），支持字符集转换。分为 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT。
BLOB：用于存储二进制大对象（Binary Large Object），数据按字节存储，不进行字符集转换。分为 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB。常用于存储图片、音频、视频文件或加密数据。

枚举与集合

ENUM (枚举)：允许列的值只能是预定义列表中的一个。例如 ENUM('Male', 'Female', 'Other')。内部存储为整数索引，节省空间但扩展性较差。
SET (集合)：允许列的值是预定义列表中的零个或多个值。例如 SET('Read', 'Write', 'Execute') 可以存储 'Read,Write'。

-- 示例：用户表中的性别和权限字段
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female', 'Secret') DEFAULT 'Secret',
    permissions SET('Create', 'Read', 'Update', 'Delete')
);

重点

长度固定的短字符串选 CHAR，长度多变的选 VARCHAR。
存储大段文本用 TEXT，存储二进制文件用 BLOB。
ENUM 和 SET 虽然节省空间，但在修改选项列表时维护成本较高，需谨慎使用。

日期和时间

处理时间数据是数据库开发的常见需求。MySQL 提供了多种日期时间类型，以满足不同精度的存储需求。

常用日期时间

类型	格式	范围	用途
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:00 UTC 到 2038-01-19 03:14:07 UTC	存储时间戳，自动转换为当前时区，适合记录行创建 / 更新时间
YEAR	YYYY	1901 到 2155	仅存储年份

DATETIME 与 TIMESTAMP

这是面试和实际开发中最容易混淆的两个类型：

时区敏感性：TIMESTAMP 受时区影响。存入时会将当前时区时间转换为 UTC 存储，取出时再转换回当前时区。DATETIME 原样存储，与时区无关。
存储空间：TIMESTAMP 占用 4 字节，DATETIME 占用 8 字节。
自动更新：TIMESTAMP 可以设置 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性，方便自动记录行的创建和修改时间。DATETIME 在 MySQL 5.6.5 之后也支持类似功能，但 TIMESTAMP 更为传统和常用。
范围限制：TIMESTAMP 受限于 2038 年问题（Unix 时间戳溢出），而 DATETIME 范围非常广。

-- 示例：自动记录创建和更新时间
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

重点

记录业务发生的绝对时间（如会议时间）推荐用 DATETIME。
记录系统行为时间（如日志、最后修改时间）推荐用 TIMESTAMP。
注意 TIMESTAMP 的 2038 年上限问题，长期归档数据建议使用 DATETIME 或 BIGINT。

JSON 数据类型

从 MySQL 5.7 版本开始，原生支持 JSON 数据类型。这使得 MySQL 能够像 NoSQL 数据库一样存储半结构化数据，同时保留了关系型数据库的事务和查询能力。

优势

自动验证：插入数据时会自动检查是否为合法的 JSON 格式，非法格式会报错。
二进制优化：MySQL 内部将 JSON 文档转换为二进制格式存储，读取速度更快，且无需重新解析文本。
灵活查询：支持使用 -> 或 JSON_EXTRACT 函数直接查询 JSON 内部的特定键值，甚至可以建立虚拟生成列（Generated Columns）并添加索引来优化查询性能。

使用示例

-- 创建包含 JSON 字段的表
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    preferences JSON
);

-- 插入 JSON 数据
INSERT INTO user_profiles (id, username, preferences) 
VALUES (1, 'Alice', '{"theme": "dark", "notifications": true, "language": "zh-CN"}');

-- 查询 JSON 中的特定字段 (提取 theme)
SELECT username, preferences->>'$.theme' AS theme_setting 
FROM user_profiles 
WHERE id = 1;

重点

JSON 类型适合存储结构不固定或经常变化的属性（如商品规格、用户配置）。
避免滥用 JSON，对于需要频繁关联查询、强一致性约束的核心业务数据，仍应使用规范的关系型字段。
利用虚拟列和索引可以解决 JSON 字段查询性能低下的问题。