第6章：数据类型（下）—— 日期与特殊类型

本章目标

学完本章后，你将能够：

区分 DATE、TIME、DATETIME、TIMESTAMP、YEAR 五种时间类型的特性和适用场景
掌握 DATETIME 和 TIMESTAMP 的核心差异（范围、存储、时区处理）
使用 TIMESTAMP 的自动初始化和自动更新特性
了解 JSON 类型的基本用法和常见函数
理解 BOOLEAN、空间数据类型等特殊类型的本质和用途

前置知识

第5章：理解 MySQL 数值和字符串类型的基本概念
理解 Unix 时间戳的基本概念（1970-01-01 以来的秒数）

6.1 日期时间类型总览

类型	格式	范围	所需字节	支持到秒的小数部分
DATE	'YYYY-MM-DD'	'1000-01-01' ~ '9999-12-31'	3	否
TIME	'HH:MM:SS'	'-838:59:59' ~ '838:59:59'	3 + 小数	是（0~6位）
DATETIME	'YYYY-MM-DD HH:MM:SS'	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	5 + 小数	是（0~6位）
TIMESTAMP	'YYYY-MM-DD HH:MM:SS'	'1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC	4 + 小数	是（0~6位）
YEAR	'YYYY'	1901 ~ 2155（MySQL 5.7，8.0 已弃用）	1	否

小数秒精度：MySQL 5.6.4 开始支持到微秒（microsecond）。在类型后加 (fsp) 指定保留几位小数秒，fsp 取值 0~6。例如 DATETIME(3) 保留 3 位（毫秒），TIMESTAMP(6) 保留 6 位（微秒）。

6.2 DATE 类型

DATE 只存储日期（年月日），不存储时间。

sql

CREATE TABLE date_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    birth_date DATE,
    hire_date DATE
);

INSERT INTO date_demo (name, birth_date, hire_date) VALUES
    ('Alice', '1995-03-15', '2020-01-10'),
    ('Bob', '1998-07-22', '2021-06-01'),
    ('Charlie', '2000-12-01', '2023-03-15');

-- 按日期范围查询
SELECT * FROM date_demo
WHERE birth_date BETWEEN '1995-01-01' AND '2000-12-31';
-- +----+---------+------------+------------+
-- | id | name    | birth_date | hire_date  |
-- +----+---------+------------+------------+
-- |  1 | Alice   | 1995-03-15 | 2020-01-10 |
-- |  2 | Bob     | 1998-07-22 | 2021-06-01 |
-- |  3 | Charlie | 2000-12-01 | 2023-03-15 |
-- +----+---------+------------+------------+

-- 字符串与 DATE 的比较（MySQL 会自动转换）
SELECT * FROM date_demo WHERE birth_date >= '2000-01-01';

-- 严格格式检查（建议始终使用标准格式）
SELECT * FROM date_demo WHERE birth_date = '1995-3-15';
-- MySQL 能识别 'YYYY-MM-DD'、'YYYY/MM/DD' 等多种格式，
-- 但推荐一直使用 'YYYY-MM-DD'

DATE 类型的常见操作

sql

-- 日期差（计算工龄天数）
SELECT name, hire_date,
    DATEDIFF(CURDATE(), hire_date) AS days_worked
FROM date_demo;

-- 提取年月日
SELECT name,
    YEAR(birth_date) AS birth_year,
    MONTH(birth_date) AS birth_month,
    DAY(birth_date) AS birth_day
FROM date_demo;

6.3 TIME 类型

TIME 存储时间（时分秒），范围远比 '00:00:00' ~ '23:59:59' 大：

TIME 范围: '-838:59:59' ~ '838:59:59'

这意味着 TIME 不仅表示"一天中的时间"，还可以表示时间间隔或时长。

sql

CREATE TABLE time_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    work_start TIME,        -- 上班时间（如 09:00:00）
    work_hours TIME,        -- 工作时长（如 08:30:00，表示 8 小时 30 分钟）
    marathon_record TIME    -- 马拉松记录（如 02:15:30，表示 2 小时 15 分 30 秒）
);

INSERT INTO time_demo (name, work_start, work_hours, marathon_record) VALUES
    ('Alice', '09:00:00', '08:30:00', '02:15:30'),
    ('Bob', '10:00:00', '06:00:00', '03:10:45'),
    ('Charlie', '08:30:00', '09:15:00', NULL);

-- TIME 的加减（计算下班时间）
SELECT name,
    work_start,
    work_hours,
    ADDTIME(work_start, work_hours) AS work_end
FROM time_demo;
-- +---------+------------+------------+----------+
-- | name    | work_start | work_hours | work_end |
-- +---------+------------+------------+----------+
-- | Alice   | 09:00:00   | 08:30:00   | 17:30:00 |
-- | Bob     | 10:00:00   | 06:00:00   | 16:00:00 |
-- | Charlie | 08:30:00   | 09:15:00   | 17:45:00 |
-- +---------+------------+------------+----------+

TIME 为什么范围这么大：因为 MySQL 设计 TIME 的时候考虑了它同时作为"一天中的时间点"和"一段时间间隔"这两种语义。负数 TIME 表示减去的时长。

6.4 DATETIME 类型

DATETIME 存储"日期 + 时间"的组合，范围覆盖从公元 1000 年到 9999 年。

sql

CREATE TABLE datetime_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_time DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 插入时自动设为当前时间
);

INSERT INTO datetime_demo (event_name, event_time) VALUES
    ('产品发布会', '2026-09-15 14:00:00'),
    ('团队会议', '2026-07-10 09:30:00'),
    ('系统维护', '2026-08-01 02:00:00');

-- DATETIME 不存储时区信息
-- 你存 '2026-07-10 09:30:00'，不管在哪个时区连接 MySQL，取出来都是 '2026-07-10 09:30:00'

-- 支持到微秒
CREATE TABLE datetime_precision (
    t DATETIME(6)  -- 保留 6 位小数秒（微秒）
);
INSERT INTO datetime_precision VALUES ('2026-07-09 14:30:00.123456');
SELECT t FROM datetime_precision;
-- +----------------------------+
-- | t                          |
-- +----------------------------+
-- | 2026-07-09 14:30:00.123456 |
-- +----------------------------+

DATETIME 的存储空间演变

MySQL 版本	DATETIME 存储	说明
MySQL 5.6.3 之前	8 字节	固定 8 字节
MySQL 5.6.4 之后	5 + (小数秒) 字节	不含小数秒时为 5 字节

MySQL 5.6.4 的优化将 DATETIME 的整数部分（年月日时分秒）打包到了 5 个字节中，比以前节省了 3 字节。

6.5 TIMESTAMP 类型

TIMESTAMP 是 MySQL 中最"特殊"也最"重要"的日期类型。它存储的是 Unix 时间戳（从 1970-01-01 00:00:01 UTC 到现在的秒数）。

核心特点

范围有限：'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC（即 32 位有符号整数最大能表示的时间）
时区感知：存储时转换为 UTC，检索时转换回会话时区
自动初始化和自动更新：支持 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP
存储高效：4 字节（不含小数秒），比 DATETIME（5 字节）略小

sql

CREATE TABLE timestamp_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- 插入时自动设为当前时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 每次更新自动刷新
);

-- 插入数据（只指定 name，created_at 和 updated_at 自动生成）
INSERT INTO timestamp_demo (name) VALUES ('记录1');
INSERT INTO timestamp_demo (name) VALUES ('记录2');

SELECT * FROM timestamp_demo;
-- +----+--------+---------------------+---------------------+
-- | id | name   | created_at          | updated_at          |
-- +----+--------+---------------------+---------------------+
-- |  1 | 记录1  | 2026-07-09 14:30:25 | 2026-07-09 14:30:25 |
-- |  2 | 记录2  | 2026-07-09 14:30:26 | 2026-07-09 14:30:26 |
-- +----+--------+---------------------+---------------------+

-- 更新数据时，updated_at 自动刷新
UPDATE timestamp_demo SET name = '记录1-修改' WHERE id = 1;

SELECT * FROM timestamp_demo WHERE id = 1;
-- +----+-------------+---------------------+---------------------+
-- | id | name        | created_at          | updated_at          |
-- +----+-------------+---------------------+---------------------+
-- |  1 | 记录1-修改  | 2026-07-09 14:30:25 | 2026-07-09 14:30:45 |
-- +----+-------------+---------------------+---------------------+
-- created_at 没变，updated_at 自动更新了！

一张表可以有多个 TIMESTAMP

MySQL 5.6 之前，一张表只能有一个 TIMESTAMP 列使用 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP。5.6+ 放宽了此限制：

sql

CREATE TABLE multi_timestamp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    description VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,       -- 可为 NULL，用于软删除
    confirmed_at TIMESTAMP NULL DEFAULT NULL      -- 另一个时间点
);

6.6 DATETIME vs TIMESTAMP 全面对比

这是 MySQL 中最经典的类型选择问题。

对比维度	DATETIME	TIMESTAMP
时间范围	1000-01-01 ~ 9999-12-31	1970-01-01 ~ 2038-01-19
存储大小	5 字节（无小数秒）+ 小数秒	4 字节（无小数秒）+ 小数秒
时区处理	不处理，存什么取什么	自动转换：存转 UTC，取转会话来时区
时区影响	切换到不同时区，值不变	切换到不同时区，显示的值会变
自动初始化	MySQL 5.6.5+ 支持 DEFAULT CURRENT_TIMESTAMP	原生支持
自动更新	MySQL 5.6.5+ 支持 ON UPDATE CURRENT_TIMESTAMP	原生支持
NULL 支持	可以 NULL	可以 NULL
2038 问题	不受影响	2038-01-19 之后崩溃
索引效率	5 字节	4 字节（略优）

时区行为的实际演示

sql

-- 创建测试表
CREATE TABLE tz_test (
    dt DATETIME,
    ts TIMESTAMP
);

-- 查看当前时区
SELECT @@session.time_zone;
-- +---------------------+
-- | @@session.time_zone |
-- +---------------------+
-- | SYSTEM              |
-- +---------------------+

-- 插入同一条时间数据
INSERT INTO tz_test VALUES ('2026-07-09 12:00:00', '2026-07-09 12:00:00');

-- 在当前时区查询
SELECT * FROM tz_test;
-- +---------------------+---------------------+
-- | dt                  | ts                  |
-- +---------------------+---------------------+
-- | 2026-07-09 12:00:00 | 2026-07-09 12:00:00 |
-- +---------------------+---------------------+

-- 切换到 UTC+0
SET time_zone = '+00:00';

-- 再次查询
SELECT * FROM tz_test;
-- +---------------------+---------------------+
-- | dt                  | ts                  |
-- +---------------------+---------------------+
-- | 2026-07-09 12:00:00 | 2026-07-09 04:00:00 |  ← TIMESTAMP 变了！
-- +---------------------+---------------------+
-- DATETIME 还是 12:00，但 TIMESTAMP 变成了 UTC 时间（假设原时区是 UTC+8）

选择建议：
记录业务发生的时间（如订单时间、日志时间）：用 DATETIME，避免时区混乱
记录数据行本身的元数据时间（如 created_at/updated_at）：用 TIMESTAMP，利用自动更新特性
需要存储 2038 年之后的时间：必须用 DATETIME
需要处理多时区应用：TIMESTAMP 更合适，因为它会自动转换

6.7 YEAR 类型（注意：已弃用）

YEAR 类型存储年份，占 1 个字节，范围 1901~2155。

重要提示：MySQL 8.0 已经弃用了 YEAR(2) 和 YEAR(4) 类型（此前的 MySQL 5.7 支持 YEAR(4) = 4 位年份，YEAR(2) = 2 位年份，后者因为 Y2K 问题被弃用）。官方建议使用 SMALLINT 代替。

sql

-- 如果必须使用 YEAR
CREATE TABLE year_demo (
    id INT,
    birth_year YEAR
);

INSERT INTO year_demo VALUES (1, 1995), (2, 2020);

-- ✅ 新项目建议
CREATE TABLE better_year_demo (
    id INT,
    birth_year SMALLINT UNSIGNED         -- 用 SMALLINT 替代 YEAR
);

6.8 JSON 类型

MySQL 5.7 引入了原生 JSON 数据类型。MySQL 8.0 大幅增强了 JSON 支持。

为什么需要 JSON 类型

传统关系型数据库中，如果要存储"非结构化数据"（如用户自定义属性、产品规格参数），通常有两种做法：

新建多列：每个属性一列 → 属性多了数不清，修改需要 ALTER
EAV 模式（Entity-Attribute-Value）：属性-值对存在单独的表中 → 查询复杂，性能差

JSON 类型提供了第三种方式：将任意结构的 JSON 文档存储在一列中。

sql

CREATE TABLE products_json (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON,      -- 产品属性（动态字段）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入 JSON 数据
INSERT INTO products_json (name, attributes) VALUES
    ('手机',
     '{"brand": "Xiaomi", "ram": "8GB", "storage": "256GB", "colors": ["black", "white", "blue"]}'),
    ('笔记本电脑',
     '{"brand": "Dell", "cpu": "i7-13700H", "ram": "16GB", "storage": "512GB SSD", "screen_size": 15.6}'),
    ('耳机',
     '{"brand": "Sony", "type": "over-ear", "wireless": true, "noise_cancelling": true, "battery_hours": 30}');

-- 确认存储成功
SELECT id, name, JSON_PRETTY(attributes) FROM products_json;

JSON 常用函数

sql

-- 1. JSON_EXTRACT / -> / ->> 提取值
-- 提取 brand 字段
SELECT
    name,
    JSON_EXTRACT(attributes, '$.brand') AS brand_json,   -- 返回 JSON 类型
    attributes->'$.brand' AS brand_arrow,                 -- -> 等价于 JSON_EXTRACT
    attributes->>'$.brand' AS brand_unquote              -- ->> 返回不带引号的文本
FROM products_json;

-- 2. 提取嵌套数组的元素
SELECT
    name,
    JSON_EXTRACT(attributes, '$.colors[0]') AS first_color  -- 提取数组第一个元素
FROM products_json
WHERE name = '手机';

-- 3. JSON_CONTAINS 检查是否包含
SELECT name FROM products_json
WHERE JSON_CONTAINS(attributes, '"16GB"', '$.ram');
-- 查找 RAM 为 16GB 的产品

-- 4. JSON_CONTAINS_PATH 检查路径是否存在
SELECT name FROM products_json
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.wireless');
-- 查找有 wireless 属性的产品

-- 5. JSON_SET / JSON_INSERT / JSON_REPLACE 修改 JSON
UPDATE products_json
SET attributes = JSON_SET(attributes, '$.ram', '32GB')  -- 修改 RAM 为 32GB
WHERE name = '手机';

UPDATE products_json
SET attributes = JSON_INSERT(attributes, '$.weight_grams', 180)  -- 添加新属性
WHERE name = '手机';

-- 6. JSON_REMOVE 删除属性
UPDATE products_json
SET attributes = JSON_REMOVE(attributes, '$.battery_hours')
WHERE name = '耳机';

-- 7. JSON_ARRAY 和 JSON_OBJECT 构造 JSON
SELECT JSON_OBJECT('name', 'Alice', 'age', 25, 'skills', JSON_ARRAY('SQL', 'Python', 'Java'));

-- 8. JSON_LENGTH 获取长度
SELECT name, JSON_LENGTH(attributes) AS attr_count FROM products_json;

-- 9. JSON_KEYS 获取所有键
SELECT name, JSON_KEYS(attributes) FROM products_json;

-- 10. JSON_TABLE 将 JSON 转为关系表（MySQL 8.0.4+）
SELECT jt.* FROM products_json,
JSON_TABLE(
    attributes,
    '$' COLUMNS(
        brand VARCHAR(50) PATH '$.brand',
        ram VARCHAR(20) PATH '$.ram',
        wireless VARCHAR(10) PATH '$.wireless'
    )
) AS jt
WHERE name = '手机';

JSON 列的索引

不能在 JSON 列上直接创建普通索引，但可以通过**生成列（Generated Column）**来间接索引 JSON 中的字段：

sql

-- 创建虚拟生成列
ALTER TABLE products_json
ADD COLUMN brand VARCHAR(50)
    AS (attributes->>'$.brand') STORED;    -- STORED：物理存储，VIRTUAL：不占空间
-- MySQL 8.0 之前只能用 VIRTUAL，8.0+ 主键/外键/索引需要 STORED

-- 在生成列上创建索引
CREATE INDEX idx_brand ON products_json(brand);

-- 现在查询 JSON 的 brand 字段就能用到索引
EXPLAIN SELECT * FROM products_json WHERE brand = 'Sony';

JSON 类型使用建议

适合用 JSON	不适合用 JSON
属性数量和结构经常变化的"配置"数据	需要频繁 WHERE/JOIN/ORDER BY 的"业务"字段
外部 API 返回的原始数据	有明确 Schema 的核心业务数据
日志/事件数据（非结构化）	需要引用完整性约束的数据
一次读写整个文档	需要部分更新的高性能场景

原则：不要把 JSON 当"万能口袋"。核心业务数据仍然应该建模为标准的关系表，JSON 适用于"附加属性"等灵活数据。

6.9 BOOLEAN / BOOL 类型

MySQL 没有真正的 BOOLEAN 类型！BOOL 和 BOOLEAN 是 TINYINT(1) 的同义词：

sql

CREATE TABLE bool_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    is_active BOOLEAN,         -- 实际是 TINYINT(1)
    is_deleted BOOL            -- 实际是 TINYINT(1)
);

INSERT INTO bool_demo (name, is_active, is_deleted) VALUES
    ('Alice', TRUE, FALSE),
    ('Bob', 1, 0),             -- 1/0 也可以
    ('Charlie', TRUE, 0);

SELECT * FROM bool_demo;
-- +----+---------+-----------+------------+
-- | id | name    | is_active | is_deleted |
-- +----+---------+-----------+------------+
-- |  1 | Alice   |         1 |          0 |
-- |  2 | Bob     |         1 |          0 |
-- |  3 | Charlie |         1 |          0 |
-- +----+---------+-----------+------------+
-- TRUE = 1, FALSE = 0

-- 甚至可以插入 2
INSERT INTO bool_demo (name, is_active) VALUES ('Dave', 2);
-- 不会报错！is_active = 2。因为底层就是 TINYINT(1)

建议：使用 TINYINT(1) 并加 CHECK 约束来模拟真正的 BOOLEAN（MySQL 8.0+）：
sql
is_active TINYINT(1) NOT NULL DEFAULT 0 CHECK (is_active IN (0, 1))
1

6.10 空间数据类型概览

MySQL 从 5.7 开始加强了对地理空间数据（GIS）的支持，支持 OpenGIS 标准。

空间类型

类型	说明	应用场景
GEOMETRY	任何空间类型的基类	通用存储
POINT	点（经纬度坐标）	商铺位置、用户定位
LINESTRING	线（由多个点连接）	道路、路径
POLYGON	多边形	区域、建筑物轮廓
MULTIPOINT	多点	多个独立位置
MULTILINESTRING	多线	多条路线
MULTIPOLYGON	多多边形	多个区域
GEOMETRYCOLLECTION	几何体集合	混合类型

基本用法

sql

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    coordinates POINT,               -- 经纬度
    area POLYGON,                    -- 服务范围
    SPATIAL INDEX (coordinates)      -- 空间索引
);

-- 插入坐标（北京市天安门附近）
INSERT INTO locations (name, coordinates) VALUES
    ('天安门', ST_GeomFromText('POINT(116.3975 39.9087)'));

-- 插入多边形（一个四边形区域）
INSERT INTO locations (name, area) VALUES
    ('王府井区域', ST_GeomFromText('POLYGON((116.40 39.91, 116.43 39.91, 116.43 39.90, 116.40 39.90, 116.40 39.91))'));

-- 空间查询：查找某个坐标点附近的位置（5 公里内）
SELECT name,
    ST_Distance_Sphere(coordinates, ST_GeomFromText('POINT(116.3975 39.9087)')) AS distance_meters
FROM locations
WHERE ST_Distance_Sphere(coordinates, ST_GeomFromText('POINT(116.3975 39.9087)')) <= 5000;

空间数据类型在日常 Web 开发中不常用，但在 LBS（Location Based Service）应用中非常重要。大多数团队倾向于使用专门的地理空间数据库（如 PostGIS/PostgreSQL），而不是 MySQL 的空间功能。

6.11 如何选择正确的数据类型：决策流程

当你设计一张表时，面对每个字段，问自己以下问题：

1. 这个字段存的是数字吗？
   ├── 是整数 → 估计最大值 → TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT
   │   └── 需要自动递增？→ 加 AUTO_INCREMENT
   │   └── 有没有负数？→ 有符号数 or UNSIGNED
   │
   ├── 是小数 → 需要精确吗？
   │   ├── 金额、税率 → DECIMAL(M,D)
   │   └── 统计分析、GPS 坐标 → FLOAT / DOUBLE
   │
   └── 不是数字

2. 这个字段存的是文本吗？
   ├── 固定长度？（手机号、MD5、身份证）→ CHAR
   ├── 变长但有限？（用户名、邮箱）→ VARCHAR
   ├── 可能很长？（文章内容、日志）→ TEXT/MEDIUMTEXT
   └── 二进制？（哈希值、加密数据）→ BINARY/BLOB

3. 这个字段存的是日期时间吗？
   ├── 只要日期？→ DATE
   ├── 只要时间？→ TIME
   ├── 日期 + 时间 + 不需要时区转换？→ DATETIME
   ├── 日期 + 时间 + 需要时区感知 + 需要自动更新？→ TIMESTAMP
   └── 只要年份？→ SMALLINT（不用 YEAR）

4. 这个字段存的是有限选项吗？
   ├── 单选项，固定不变？→ TINYINT + 查找表（或少数情况用 ENUM）
   ├── 多选项？→ 关联表（不要用 SET）
   └── 真/假、是/否？→ TINYINT(1) + CHECK 约束

5. 这个字段存的是半结构化/嵌套数据？
   └── JSON

6. 这个字段存的是地理坐标吗？
   └── POINT + SPATIAL INDEX

常见错误

错误 1：用字符串存日期

现象：

sql

-- ❌ 不推荐的做法
CREATE TABLE bad_dates (
    event_date VARCHAR(10)      -- 用字符串存日期 '2026-07-09'
);

原因：无法使用日期函数（DATEDIFF、DATE_ADD 等），无法按日期范围高效查询，没有格式校验（可能存入 '2026-13-32' 这种非法日期）。

解决：日期始终用 DATE、DATETIME 或 TIMESTAMP。

错误 2：混淆 DATETIME 和 TIMESTAMP 的时区行为

现象：数据库迁移到不同时区的服务器后，TIMESTAMP 列的值"变了"。

原因：TIMESTAMP 存储的是 UTC 时间，显示时根据会话时区转换。如果迁移后未调整时区设置，显示就会不同。

解决：如果业务时间与时区无关（如用户输入的日期），使用 DATETIME；如果需要自动处理多时区，使用 TIMESTAMP 并正确设置时区。

错误 3：忘记 TIMESTAMP 的 2038 问题

现象：将 TIMESTAMP 用于存储生日（如 2045-01-01），插入时报错或截断。

原因：TIMESTAMP 底层是 32 位有符号整数，最大只能表示到 2038-01-19 03:14:07 UTC。

解决：需要存储 2038 年之后的时间，必须使用 DATETIME。

错误 4：JSON 滥用

现象：把所有字段都塞到一个 JSON 列中。

sql

-- ❌ 反模式
CREATE TABLE orders (
    id INT,
    data JSON   -- 包含了 customer_name, product_name, quantity, price, order_date...
);

原因：JSON 列中的数据无法使用外键约束、普通索引、高效聚合等关系型数据库的核心能力。查询 JSON 内的字段也远不如直接查列方便。

解决：核心业务字段用标准列，JSON 只用于确实需要灵活结构的附加信息。

错误 5：DECIMAL 设了太大的 M 和 D

现象：

sql

-- DELIMAL(65, 30) — 用了最大精度
price DECIMAL(65, 30)

原因：DECIMAL 越大，存储和计算消耗越大。对于价格来说，DECIMAL(10, 2)（最大 99,999,999.99）已经足够大多数场景。

解决：根据实际业务需求合理设置 M 和 D。金额场景 DECIMAL(12, 2) 可精确到千万级别。

本章练习

TIMESTAMP 自动更新：创建一张表 articles，包含 title、body、created_at（插入时自动生成）、updated_at（自动更新）。插入一条记录后，执行 UPDATE 更新 title，验证 created_at 不变而 updated_at 自动更新。
时区实验：创建一个包含 DATETIME 和 TIMESTAMP 列的表，插入相同的时间。用 SET time_zone = '+08:00' 和 '+00:00' 分别查看两个列的值，记录差异并解释原因。
2038 测试：尝试向 TIMESTAMP 列中插入 '2039-01-01 00:00:00'，观察 MySQL 报什么错误。然后换 DATETIME 列尝试，确认 DATETIME 没有此限制。
JSON 函数练习：创建一张 user_profiles 表，包含 id、username、profile（JSON 类型）。profile 存储 {"age": 25, "city": "Beijing", "hobbies": ["reading", "coding", "hiking"]}。使用 JSON 函数完成以下查询：(a) 提取 city 字段；(b) 查询 hobbies 中包含 "coding" 的用户；(c) 为所有用户的 profile 添加 "level": "beginner" 字段。
生成列与 JSON 索引：基于上一题的表，创建一个 STORED 生成列 city 从 profile JSON 中提取城市名，并在该生成列上建索引。对比有索引和无索引时的查询效率。
类型选择综合：假设你要设计一个"车票预订系统"，列出以下字段的推荐 MySQL 类型并说明理由：
- 车票 ID、发车时间、到达时间、票价、乘客姓名、乘客身份证号、座位号、是否已检票

第6章：数据类型（下）—— 日期与特殊类型 ​

本章目标 ​

前置知识 ​

6.1 日期时间类型总览 ​

6.2 DATE 类型 ​

DATE 类型的常见操作 ​

6.3 TIME 类型 ​

6.4 DATETIME 类型 ​

DATETIME 的存储空间演变 ​

6.5 TIMESTAMP 类型 ​

核心特点 ​

一张表可以有多个 TIMESTAMP ​

6.6 DATETIME vs TIMESTAMP 全面对比 ​

时区行为的实际演示 ​

6.7 YEAR 类型（注意：已弃用） ​

6.8 JSON 类型 ​

为什么需要 JSON 类型 ​

JSON 常用函数 ​

JSON 列的索引 ​

JSON 类型使用建议 ​

6.9 BOOLEAN / BOOL 类型 ​

6.10 空间数据类型概览 ​

空间类型 ​

基本用法 ​

6.11 如何选择正确的数据类型：决策流程 ​

常见错误 ​

错误 1：用字符串存日期 ​

错误 2：混淆 DATETIME 和 TIMESTAMP 的时区行为 ​

错误 3：忘记 TIMESTAMP 的 2038 问题 ​

错误 4：JSON 滥用 ​

错误 5：DECIMAL 设了太大的 M 和 D ​

本章练习 ​