mysql 专业笔记 -- 第 2 章：数据类型

第 2.1 节：CHAR(n)

CHAR(n) 是一个固定长度为 n 个字符的字符串。如果它的字符集是 utf8mb4，则无论其中包含什么文本，它都会精确占用 4 * n 字节。

大多数使用 CHAR(n) 的场景涉及包含英文字符的字符串，因此应使用 ascii 字符集（latin1 也可以）。

country_code CHAR(2) CHARACTER SET ascii,
postal_code CHAR(6) CHARACTER SET ascii,
uuid CHAR(39) CHARACTER SET ascii,  -- 更多讨论见其他章节

第 2.2 节：DATE, DATETIME, TIMESTAMP, YEAR, TIME

• DATE 数据类型包含日期但不包含时间部分。其格式为 'YYYY-MM-DD'，取值范围为 '1000-01-01' 到 '9999-12-31'。
• DATETIME 类型包含时间，格式为 'YYYY-MM-DD HH:MM:SS'，取值范围为 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。
• TIMESTAMP 类型是一个整数类型，包含日期和时间，有效范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。
• YEAR 类型表示年份，取值范围为 1901 到 2155。
• TIME 类型表示时间，格式为 'HH:MM:SS'，取值范围为 '-838:59:59' 到 '838:59:59'。

存储需求

小数秒（从 5.6.4 版本开始）

请参阅 MySQL 手册：DATE、DATETIME 和 TIMESTAMP 类型，数据类型存储需求，以及时间值中的小数秒。

第 2.3 节：VARCHAR(255) — 是否使用 255

建议的最大长度

首先，我提到一些常见的字符串，它们总是十六进制或以其他方式限于 ASCII。对于这些，你应该指定 CHARACTER SET ascii（latin1 也可以）以避免浪费空间：

UUID CHAR(36) CHARACTER SET ascii                    -- 或压缩为 BINARY(16)
country_code CHAR(2) CHARACTER SET ascii
ip_address CHAR(39) CHARACTER SET ascii              -- 或压缩为 BINARY(16)
phone VARCHAR(20) CHARACTER SET ascii                -- 可能足够处理分机号
postal_code VARCHAR(20) CHARACTER SET ascii          -- （不是 'zip_code'）（不知道最大长度）
city VARCHAR(100)                                    -- 这个俄罗斯城镇需要 91 个字符：Poselok Uchebnogo Khoyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin
country VARCHAR(50)                                  -- 可能足够
name VARCHAR(64)                                     -- 可能足够；比某些政府机构允许的还要长

为什么不用 255？有两个原因反对在所有地方都使用 (255) 的常见做法。

1. 当一个复杂的 SELECT 需要创建临时表（用于子查询、UNION、GROUP BY 等）时，首选使用 MEMORY 引擎，它将数据放在 RAM 中。但 VARCHAR 在此过程中会转换为 CHAR。这使得 VARCHAR(255) CHARACTER SET utf8mb4 占用 1020 字节。这可能导致需要溢出到磁盘，从而变慢。
2. 在某些情况下，InnoDB 会查看表中列的可能大小，并决定表太大，从而中止 CREATE TABLE。

VARCHAR 与 TEXT

关于 *TEXT、CHAR 和 VARCHAR 的使用提示，以及一些最佳实践：

• 永远不要使用 TINYTEXT。
• 几乎永远不要使用 CHAR——它是固定长度的；每个字符占用字符集的最大长度（例如，utf8mb4 每个字符 4 字节）。使用 CHAR 时，除非你知道需要其他字符集，否则应使用 CHARACTER SET ascii。
• VARCHAR(n) 会在 n 个字符处截断；而 TEXT 会在某个字节数处截断。（但是，你真的想要截断吗？）
• *TEXT 可能会因为临时表的处理方式而拖慢复杂的 SELECT 查询。

第 2.4 节：INT 作为 AUTO_INCREMENT

任何大小的 INT 都可以用于 AUTO_INCREMENT。UNSIGNED 始终是合适的。

请注意，某些操作会“消耗”AUTO_INCREMENT 的 ID。这可能导致意外的间隙。例如：INSERT IGNORE 和 REPLACE。它们可能会在意识到不需要该 ID 之前预先分配一个 ID。这是 InnoDB 引擎的预期行为，也是设计使然，不应因此阻止使用它们。

第 2.5 节：其他类型

已经有单独的条目介绍“FLOAT、DOUBLE 和 DECIMAL”以及“ENUM”。关于数据类型的单独页面可能会显得冗长——我建议“字段类型”（或者应该叫“数据类型”？）作为一个概述，然后拆分为以下主题页面：

• 整数类型
• FLOAT、DOUBLE 和 DECIMAL
• 字符串（CHAR、TEXT 等）
• BINARY 和 BLOB
• DATETIME、TIMESTAMP 及相关类型
• ENUM 和 SET
• 空间数据
• JSON 类型（MySQL 5.7.8+）
• 如何表示货币，以及其他需要“改造”到现有数据类型中的常见“类型”

在适当的情况下，每个主题页面除了语法和示例外，还应包括：

• ALTER 时的注意事项
• 大小（字节）
• 与非 MySQL 引擎的对比（低优先级）
• 在 PRIMARY KEY 或二级索引中使用该数据类型的注意事项
• 其他最佳实践
• 其他性能问题

第 2.6 节：隐式/自动类型转换

SELECT '123' * 2;

为了与 2 相乘，MySQL 自动将字符串 '123' 转换为数字。

返回值：

246

数字转换从左到右进行。如果无法转换，结果为 0。

SELECT '123ABC' * 2;

返回值：

246

SELECT 'ABC123' * 2;

返回值：

0

第 2.7 节：数值类型介绍

MySQL 提供了多种不同的数值类型。它们可以分为以下几组：

第 2.8 节：整数类型

最小的无符号值始终为 0。

第 2.9 节：定点数类型

MySQL 的 DECIMAL 和 NUMERIC 类型用于存储精确的数值数据。建议使用这些类型来保持精确精度，例如用于货币。

DECIMAL

这些值以二进制格式存储。在列声明中，应指定精度（precision）和小数位数（scale）。

• 精度表示值为存储的有效数字位数。
• 小数位数表示小数点后存储的数字位数。

salary DECIMAL(5,2)

5 表示精度，2 表示小数位数。对于此示例，可以存储在此列中的值的范围是 -999.99 到 999.99。

如果省略小数位数参数，则默认为 0。

此数据类型最多可以存储 65 位数字。

DECIMAL(M,N) 占用的字节数大约为 M/2。

第 2.10 节：浮点数类型

FLOAT 和 DOUBLE 表示近似数据类型。

• REAL 是 FLOAT 的同义词。
• DOUBLE PRECISION 是 DOUBLE 的同义词。

尽管 MySQL 也允许使用 (M,D) 限定符，但请不要使用它。(M,D) 表示值最多可以存储 M 位总位数，其中 D 位在小数点后。数字将被四舍五入两次或被截断；这将带来更多麻烦而非好处。

由于浮点值是近似的，而不是精确存储的值，尝试在比较中将其视为精确值可能会导致问题。特别要注意，FLOAT 值很少等于 DOUBLE 值。

第 2.11 节：位值类型

BIT 类型对于存储位字段值很有用。BIT(M) 允许存储最多 M 位的值，其中 M 的取值范围是 1 到 64。

你也可以使用位值表示法指定值。

b'111'   -- 7
b'10000000' -- 128

有时使用“移位”来构造单个位的值很方便，例如 (1 << 7) 对应 128。

NDB 表中所有 BIT 列的最大总大小为 4096 字节。