阿源的知识库

切换日光/暗黑模式

数据类型、字符集与时区 ​

学习目标 ​

  • 能根据范围、精度和比较规则选择字段类型。
  • 能区分 NULL、空串、JSON null，以及 DATETIME 与 TIMESTAMP。
  • 能检查严格模式、utf8mb4 和会话 time_zone，避免隐式转换损坏数据。

核心知识点 ​

本节使用 MySQL 8.0 / InnoDB 和 learning_lab 实验库。整数按范围选 TINYINT/SMALLINT/INT/BIGINT，UNSIGNED 去掉负值范围；整数显示宽度不决定存储范围。DECIMAL(p,s) 是精确定点数，FLOAT/DOUBLE 是近似浮点数。CHAR/VARCHAR/TEXT 存字符，BINARY/VARBINARY/BLOB 存字节；DATE/TIME/DATETIME/TIMESTAMP 表示不同的日期与时间语义。TIME 也可表示时长，不能一律理解为一天内的钟点。

字符集决定编码能力，collation 决定比较与排序；utf8mb4_0900_ai_ci 的 ai/ci 表示重音与大小写不敏感。连接与列的编码都需要检查。时区是会话解释时间的规则，不能把服务器操作系统时区等同于应用时区。官方分类见 Data Types。

常用用法 ​

CREATE TABLE：明确类型及精度 ​

用于在独立实验表中同时观察常见类型的真实存储值。

sql
USE learning_lab;
-- 初始：lab_types 不存在；实验表不改变 users/orders。
CREATE TABLE lab_types (
  id BIGINT UNSIGNED PRIMARY KEY,
  amount DECIMAL(12,2) NOT NULL,
  label VARCHAR(32) NOT NULL,
  token VARBINARY(8) NOT NULL,
  birthday DATE NULL,
  duration TIME NULL,
  event_local DATETIME(6) NOT NULL,
  event_instant TIMESTAMP(6) NULL,
  extra JSON NULL,
  note TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO lab_types VALUES
 (1,19.90,'学习😀',X'0041','2000-01-02','01:30:00',
  '2026-10-05 09:00:00.123456',NULL,'{"level":2,"tag":null}',NULL);
-- 关键变化：写入 id=1，保留金额两位小数、二进制零字节及六位时间精度。
SELECT id, amount, label, HEX(token), birthday, duration, event_local FROM lab_types;
-- 输出：1 | 19.90 | 学习😀 | 0041 | 2000-01-02 | 01:30:00 | 2026-10-05 09:00:00.123456。

DECIMAL(12,2) 可容纳 10 位整数和 2 位小数；不要依赖数据库替应用做金额舍入决策。VARCHAR(32) 长度以字符计，索引和存储空间仍受字节数约束。BINARY 为定长，VARBINARY 为变长；大文本/大二进制使用 TEXT/BLOB 时，还需评估传输和查询负担。

SELECT：验证 DECIMAL 与整数计算 ​

用于区分精确十进制运算、整数除法与普通除法。

sql
-- 输入：金额 19.90、加数 0.10，以及整数 5 和 2。
SELECT CAST(19.90 AS DECIMAL(12,2)) + CAST(0.10 AS DECIMAL(12,2)) AS total,
       5 DIV 2 AS quotient, 5 / 2 AS ratio;
-- 关键变化：计算十进制和与两种除法；total=20.00，quotient=2，ratio=2.5000（默认配置）。
-- 输出：total=20.00、quotient=2，默认配置下 ratio=2.5000。

普通除法显示小数位会受 div_precision_increment 影响；SQL 算术类型推导不等于 Java 的运算规则。

SELECT：比较 utf8mb4 与 COLLATE ​

用于在具体字符串上验证编码长度及不同排序规则的相等语义。

sql
-- 输入：utf8mb4 字符 😀，以及大小写不同的 A/a。
SELECT CHAR_LENGTH(_utf8mb4'😀') AS chars, LENGTH(_utf8mb4'😀') AS bytes,
       _utf8mb4'A' COLLATE utf8mb4_0900_ai_ci = _utf8mb4'a' AS insensitive,
       _utf8mb4'A' COLLATE utf8mb4_0900_as_cs = _utf8mb4'a' AS sensitive;
-- 关键变化：按字符与字节计数并比较；输出 1 | 4 | 1 | 0。
-- 输出：chars=1、bytes=4、insensitive=1、sensitive=0。

唯一键也遵循列的 collation；“用户名字符串不同”不一定能同时插入。列使用 utf8mb4 但连接乱码时仍可能写坏内容，连接参数与 character_set_client/connection/results 要一起检查。连接字符集官方说明。

SET：限定 sql_mode 的实验范围 ​

用于在单个实验连接中验证严格模式对超范围值的拒绝行为。

sql
USE learning_lab;
-- 初始：lab_strict 不存在，保存本连接的原始 SQL mode。
SET @old_mode = @@session.sql_mode;
CREATE TABLE lab_strict (n TINYINT NOT NULL) ENGINE=InnoDB;
-- 风险：SET SESSION 改变当前连接的数据校验规则，仅用于独立实验连接，完成后必须恢复。
SET SESSION sql_mode='STRICT_TRANS_TABLES';
INSERT INTO lab_strict VALUES (127);
SELECT n FROM lab_strict;
-- 关键变化：严格模式生效，合法上界 127 被保留；输出 127。
-- 输出：n=127。
SET SESSION sql_mode=@old_mode;

在恢复之前手动执行 INSERT INTO lab_strict VALUES (128); 应报超范围错误，表仍只有 127；这是刻意失败的单语句实验，请勿作为自动批处理的一部分。非严格模式可能调整值并产生警告，严格模式也不代表每种精度损失都必然报错；执行后检查警告。本例临时简化 mode，生产应用应保留团队规定的完整模式集。Server SQL Modes。

SET：验证 time_zone 与 TIMESTAMP ​

用于对照同一个绝对时刻在不同会话时区下的显示和 DATETIME 的稳定字面值。

sql
USE learning_lab;
-- 初始：lab_types 的 id=1，event_instant 为 NULL。
SET @old_tz = @@session.time_zone;
-- 风险：SET time_zone 改变当前连接的 TIMESTAMP 解释，独立实验结束后恢复，不能遗留到连接池。
SET SESSION time_zone='+00:00';
UPDATE lab_types SET event_instant='2026-10-05 01:00:00.123456' WHERE id=1;
SET SESSION time_zone='+08:00';
-- 关键变化：TIMESTAMP 显示转换为北京时间，DATETIME 保留原值。
SELECT event_local, event_instant FROM lab_types WHERE id=1;
-- 输出：两个字段都显示 2026-10-05 09:00:00.123456。
SET SESSION time_zone=@old_tz;

TIMESTAMP 写入时从 session 时区转换到 UTC，读取时反向转换；DATETIME 不做此转换，适合明确约定的本地业务时间。MySQL 8.0 TIMESTAMP 上限在 2038 年，不能代替任意远期日期；命名时区如 Asia/Shanghai 需要已加载时区表，固定偏移不体现夏令时。Time Zone Support。

SELECT：提取 JSON 值 ​

用于确认 JSON 文档内部值的类型并区分 JSON null 与 SQL NULL。

sql
USE learning_lab;
-- 初始：lab_types.id=1 的 extra={"level":2,"tag":null}。
SELECT JSON_EXTRACT(extra,'$.level') AS level,
       JSON_TYPE(JSON_EXTRACT(extra,'$.tag')) AS tag_type,
       JSON_EXTRACT(extra,'$.missing') IS NULL AS missing
FROM lab_types WHERE id=1;
-- 关键变化：读取文档路径；结果 level=2，tag_type 字符串为 NULL，missing=1。
-- 输出：level=2、tag_type='NULL'（文本）、missing=1。

JSON 列验证文档合法性，不能把任意文本当 JSON 写入。缺失路径返回 SQL NULL；JSON null 是文档值，JSON_TYPE 返回字符文本 'NULL'。更完整的查询与修改将在 JSON 篇展开。

SELECT：判断 NULL 而非等号比较 ​

用于区分缺失值、空字符串和普通比较产生的未知结果。

sql
-- 输入：SQL NULL 和空字符串 ''。
SELECT NULL = NULL AS equality, NULL IS NULL AS missing,
       NULL <=> NULL AS null_safe, '' IS NULL AS empty_missing,
       COALESCE(NULL,'未填写') AS display_text;
-- 关键变化：比较结果分别为 NULL、1、1、0，展示文本为 未填写。
-- 输出：equality=NULL、missing=1、null_safe=1、empty_missing=0、display_text=未填写。

WHERE 只保留 TRUE 的行，= NULL 不会匹配缺失值。<=> 是 MySQL 的 NULL 安全相等运算，COALESCE 可为展示提供默认值，但不能据此假定原字段已被更新。

易混点 ​

空串是长度为零的已知字符串，NULL 表示缺失或未知；JSON null 又是另一层语义。utf8mb4 保存四字节字符，collation 决定比较结果，不能只改编码而忽视唯一键规则。DATETIME 记录日历字面值，TIMESTAMP 保存按时区解释的时刻；应用端要统一时间约定。

课后小问 ​

  1. 两个连接时区不同，DATETIME 会不会显示不同？

    答：同一已存储 DATETIME 字面值不随 session 时区转换；TIMESTAMP 通常会。

  2. 开启严格模式能否省略应用校验？

    答：不能。严格模式约束数据库转换，业务范围、金额舍入及时间语义仍需应用明确校验。

本节小结 ​

  • 金额选 DECIMAL，整数和字符串需按业务范围选择。
  • 字符编码、排序规则、NULL 语义影响数据比较。
  • SQL mode 与时区属于连接规则，需要检查并恢复实验变更。

快速回顾 ​

  • HEX(token) 用十六进制检查二进制字节。
  • IS NULL 匹配缺失值，<=> 提供 NULL 安全比较。
  • @@session.time_zone 决定 TIMESTAMP 的输入输出解释。