第5章：数据类型（上）—— 数值与字符串

本章目标

学完本章后，你将能够：

根据数据特征正确选择整数类型（TINYINT/SMALLINT/INT/BIGINT）并理解其取值范围
理解 UNSIGNED、ZEROFILL 修饰符的含义，以及 INT(11) 中括号数字的真正意义
区分 DECIMAL、FLOAT、DOUBLE 的精度差异，知道为什么金额计算必须用 DECIMAL
掌握 CHAR 与 VARCHAR 的区别，在正确的场景选择正确的类型
了解 TEXT 系列、BLOB 系列、ENUM 和 SET 的用途与陷阱

前置知识

第4章：会创建数据库、理解字符集概念
对计算机中二进制、字节的概念有基本了解（1 字节 = 8 位 = 256 种可能值）

5.1 数值类型总览

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
整数	INT / INTEGER	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295
整数	BIGINT	8	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	0 ~ 18,446,744,073,709,551,615
定点数	DECIMAL(M,D)	变长	取决于 M 和 D（M 最大 65）	取决于 M 和 D
浮点数	FLOAT(M,D) / FLOAT(p)	4	约 -3.40E+38 ~ 3.40E+38	取决于具体值
浮点数	DOUBLE(M,D) / DOUBLE(p)	8	约 -1.80E+308 ~ 1.80E+308	取决于具体值
位类型	BIT(M)	(M+7)/8	M 取值范围 1~64	同左

5.2 整数类型详解

五种整数类型

sql

-- 创建一张包含所有整数类型的表来感受
CREATE TABLE int_demo (
    a TINYINT,
    b SMALLINT,
    c MEDIUMINT,
    d INT,
    e BIGINT,
    f TINYINT UNSIGNED,
    g INT UNSIGNED
);

-- 查看表结构
DESC int_demo;
-- +-------+------------------+------+-----+---------+-------+
-- | Field | Type             | Null | Key | Default | Extra |
-- +-------+------------------+------+-----+---------+-------+
-- | a     | tinyint          | YES  |     | NULL    |       |
-- | b     | smallint         | YES  |     | NULL    |       |
-- | c     | mediumint        | YES  |     | NULL    |       |
-- | d     | int              | YES  |     | NULL    |       |
-- | e     | bigint           | YES  |     | NULL    |       |
-- | f     | tinyint unsigned | YES  |     | NULL    |       |
-- | g     | int unsigned     | YES  |     | NULL    |       |
-- +-------+------------------+------+-----+---------+-------+

存储空间的含义

1 字节（Byte）= 8 位（bit），2^8 = 256 种可能值。所以：

TINYINT（1 字节）：有符号 -128 到 127（中间有 0，共 256 个数），无符号 0 到 255
INT（4 字节）：2^32 = 4,294,967,296 种可能值，有符号约 ±21 亿
BIGINT（8 字节）：2^64 = 约 1844 亿亿，足够存储大多数计数场景

UNSIGNED 修饰符

UNSIGNED 表示无符号数（非负数），将原本分配给负数的编码空间都给了正数，所以正数范围翻倍：

sql

CREATE TABLE unsigned_demo (
    id TINYINT UNSIGNED,
    age TINYINT UNSIGNED,      -- 年龄不可能为负，适合 UNSIGNED
    score INT UNSIGNED          -- 分数不可能为负，适合 UNSIGNED
);

-- 插入边界值测试
INSERT INTO unsigned_demo VALUES (255, 150, 4294967295);  -- ✅ 都是合法的最大值

INSERT INTO unsigned_demo VALUES (256, 150, 100);
-- ERROR 1264 (22003): Out of range value for column 'id' at row 1
-- 256 超过了 TINYINT UNSIGNED 的 255 上限

INSERT INTO unsigned_demo VALUES (255, -1, 100);
-- ERROR 1264 (22003): Out of range value for column 'age' at row 1
-- UNSIGNED 列不接受负数

UNSIGNED 的陷阱：两个 UNSIGNED 列做减法时，如果结果为负数，MySQL 会报错或产出意想不到的大数值：

sql

CREATE TABLE t (a INT UNSIGNED, b INT UNSIGNED);
INSERT INTO t VALUES (1, 2);
SELECT a - b FROM t;
-- 如果 sql_mode 不严格，可能返回 4294967295（即相当于 -1 在 32 位无符号下的表示）
-- 这就是为什么很多团队规定不使用 UNSIGNED，而用更大的有符号类型

INT(11) 中的括号数字是什么？

这是一个最常见的误解：INT(11) 中的 11 不是指"11 位数"，也不是指"11 字节"！它是指显示宽度（Display Width），仅在配合 ZEROFILL 使用时才有意义。

sql

-- 显示宽度演示
CREATE TABLE display_width_demo (
    a INT,          -- 默认显示宽度（通常 INT 是 11）
    b INT(5),       -- 显示宽度为 5
    c INT(5) ZEROFILL  -- 显示宽度 5 + 零填充
);

INSERT INTO display_width_demo VALUES (123, 123, 123);

SELECT * FROM display_width_demo;
-- +------+------+-------+
-- | a    | b    | c     |
-- +------+------+-------+
-- |  123 |  123 | 00123 |  ← 只有 ZEROFILL 才有效果
-- +------+------+-------+

INSERT INTO display_width_demo VALUES (123456, 123456, 123456);
-- c 列的值 123456 超过了显示宽度 5，但不会截断或报错！因为它只是显示宽度
SELECT * FROM display_width_demo;
-- +--------+--------+--------+
-- | a      | b      | c      |
-- +--------+--------+--------+
-- |    123 |    123 |  00123 |
-- | 123456 | 123456 | 123456 |  ← 显示宽度 5 但值有 6 位，照常显示
-- +--------+--------+--------+

重要结论：INT(5) 和 INT(11) 在存储上没有任何区别，都是 4 字节，都存储 -2^31 到 2^31-1 范围内的整数。显示宽度只是一个已被废弃的概念（MySQL 8.0.17+ 中这个语法已被弃用）。

整数类型选择指南

数据特征	推荐类型	原因
年龄（0-200）	TINYINT UNSIGNED	1 字节足够
状态码（0-255）	TINYINT UNSIGNED	1 字节足够
学生人数（学校规模）	SMALLINT UNSIGNED	最大 6.5 万，2 字节
用户 ID、订单 ID	INT UNSIGNED	最大 43 亿，4 字节
日志/交易流水号	BIGINT	8 字节，极大量不会溢出
布尔值	TINYINT(1) 或 BOOLEAN	MySQL 内部就是 TINYINT(1)

5.3 AUTO_INCREMENT 与整数类型

AUTO_INCREMENT 属性让 MySQL 自动为列生成递增的数值（常用于主键）：

sql

CREATE TABLE auto_demo (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO auto_demo (name) VALUES ('Alice'), ('Bob'), ('Charlie');

SELECT * FROM auto_demo;
-- +----+---------+
-- | id | name    |
-- +----+---------+
-- |  1 | Alice   |
-- |  2 | Bob     |
-- |  3 | Charlie |
-- +----+---------+

AUTO_INCREMENT 只能用于整数（或浮点数，但不推荐）类型
每个表最多只能有一个 AUTO_INCREMENT 列
AUTO_INCREMENT 列必须被索引（通常是 PRIMARY KEY 或 UNIQUE KEY）
默认从 1 开始递增，可手动指定起始值：

sql

ALTER TABLE auto_demo AUTO_INCREMENT = 1000;
-- 之后插入的行将从 1000 开始

删除某行后，它的 AUTO_INCREMENT 值不会被回收重用（这是设计特性，不是 bug）

sql

-- 可以通过 LAST_INSERT_ID() 获取最后一次插入的 AUTO_INCREMENT 值
SELECT LAST_INSERT_ID();

5.4 DECIMAL —— 精确定点数

DECIMAL(M, D) 以字符串的形式存储数字（不是二进制浮点数），因此可以精确保存带小数的数值。这是金额计算的不二选择。

语法与参数

M（精度）：有效数字的总位数，最大 65
D（标度）：小数部分的位数，最大 30，且 D <= M
例如 DECIMAL(10, 2) 表示：总共 10 位数字，其中 2 位小数，整数部分最多 8 位。范围：-99999999.99 到 99999999.99

sql

CREATE TABLE money_demo (
    price DECIMAL(10, 2),
    tax_rate DECIMAL(5, 4)
);

INSERT INTO money_demo VALUES (12345.67, 0.1375);
SELECT * FROM money_demo;
-- +----------+----------+
-- | price    | tax_rate |
-- +----------+----------+
-- | 12345.67 |   0.1375 |  ← 精确存储，没有浮点误差
-- +----------+----------+

存储大小

DECIMAL 的存储空间取决于 M 值：

每 9 个十进制数位使用 4 个字节
剩余的不足 9 位对应的字节数：(1-2 位=1字节, 3-4 位=2字节, 5-6 位=3字节, 7-9 位=4字节)
例如 DECIMAL(18, 2) 需要 9 字节左右

5.5 FLOAT 与 DOUBLE —— 浮点数的精度陷阱

基本概念

FLOAT 和 DOUBLE 基于 IEEE 754 标准，用二进制方式近似表示十进制小数。

类型	字节数	有效数字（约）	MySQL 中 FLOAT(p) 的语法
FLOAT	4	~7 位	当 p=0~24 → FLOAT(4字节)；p=25~53 → DOUBLE
DOUBLE	8	~15 位

精度陷阱演示

这是每个程序员都应该知道的一个经典演示：

sql

CREATE TABLE precision_demo (
    float_val FLOAT,
    double_val DOUBLE,
    decimal_val DECIMAL(10, 2)
);

-- 插入 0.1, 0.2, 0.3
INSERT INTO precision_demo VALUES (0.1, 0.1, 0.1);
INSERT INTO precision_demo VALUES (0.2, 0.2, 0.2);
INSERT INTO precision_demo VALUES (0.3, 0.3, 0.3);

-- 计算 0.1 + 0.2
SELECT
    0.1 + 0.2 AS raw_sum,
    CAST(0.1 + 0.2 AS DECIMAL(10, 10)) AS precise_sum;

-- 来看看存储的值到底是什么
SELECT
    float_val,
    double_val,
    decimal_val,
    float_val + 0.00000001 AS float_plus  -- 这个微小的值可能看不到效果
FROM precision_demo
WHERE float_val = 0.1;

-- 关键的演示：0.1 + 0.2 是否等于 0.3？
SELECT
    0.1 + 0.2 = 0.3 AS float_equality,
    CAST(0.1 AS DECIMAL(10,1)) + CAST(0.2 AS DECIMAL(10,1))
        = CAST(0.3 AS DECIMAL(10,1)) AS decimal_equality;
-- +----------------+-------------------+
-- | float_equality | decimal_equality |
-- +----------------+-------------------+
-- |              0 |                 1 |
-- +----------------+-------------------+
-- 0 表示 FALSE: FLOAT 下 0.1+0.2 ≠ 0.3 !
-- 1 表示 TRUE:  DECIMAL 下 0.1+0.2 = 0.3

为什么 0.1 + 0.2 != 0.3 在浮点数中？

0.1 在二进制中是无限循环小数（就像 1/3 在十进制中是 0.3333...），计算机只能截断到有限位（FLOAT 约 7 位有效数字，DOUBLE 约 15 位），因此产生了微小的舍入误差。当误差累积到一定程度，等式就不成立了。

真实世界的后果

看一个金融场景的恐怖例子：

sql

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance FLOAT  -- ❌ 千万别这么做！
);

INSERT INTO accounts VALUES (1, 1000.00);

-- 存入 1000 笔 0.01 元
-- 在代码中，循环 1000 次 UPDATE accounts SET balance = balance + 0.01 WHERE id = 1;
-- 期望余额: 1010.00
-- 实际余额: 可能是 1010.0000000001 或 1009.9999999999
-- 经过更多操作后，误差会越来越大

-- ✅ 正确做法
ALTER TABLE accounts MODIFY COLUMN balance DECIMAL(15, 2);

铁律：凡是涉及金额、财务计算的列，必须使用 DECIMAL。FLOAT/DOUBLE 只能用在不需要精确计算的场景（如统计分析、科学计算、AI 模型的权重等）。

5.6 BIT 类型

BIT(M) 存储位字段值，M 表示位数（1 到 64）：

sql

CREATE TABLE bit_demo (
    flags BIT(8)   -- 存储 8 位标志位，类似于编程中的 flags 变量
);

INSERT INTO bit_demo VALUES (b'10101010');
INSERT INTO bit_demo VALUES (0b1010);  -- 二进制字面量（MySQL 8.0+）

-- 读取时默认显示为二进制字符串
SELECT flags FROM bit_demo;
-- +-----------------+
-- | flags           |
-- +-----------------+
-- | 0xAA            |
-- | 0x0A            |
-- +-----------------+

-- 以十进制显示
SELECT flags + 0 FROM bit_demo;
-- +----------+
-- | flags + 0|
-- +----------+
-- |      170 |  -- 0b10101010 = 170
-- |       10 |  -- 0b1010 = 10
-- +----------+

-- 或以二进制字符串显示
SELECT BIN(flags) FROM bit_demo;
-- +------------+
-- | BIN(flags) |
-- +------------+
-- | 10101010   |
-- | 1010       |
-- +------------+

BIT 在实际开发中不常用，大多数场景下用 TINYINT + 位运算（&、|、^）替代更具可读性。

5.7 字符串类型总览

类型	最大长度	存储方式	适用场景
CHAR(M)	M 个字符（M <= 255）	固定长度	手机号、密码 MD5 值、固定格式编码
VARCHAR(M)	M 个字符（最多 65535 字节）	变长	用户名、邮箱、地址等大部分文本
TINYTEXT	255 字节	变长	短备注
TEXT	65,535 字节（64KB）	变长	文章内容
MEDIUMTEXT	16,777,215 字节（16MB）	变长	长文档
LONGTEXT	4,294,967,295 字节（4GB）	变长	超大文本
BINARY(M)	M 个字节	固定长度	二进制定长数据
VARBINARY(M)	M 个字节	变长	二进制变长数据
BLOB 系列	与 TEXT 系列对应	变长	图片、文件等二进制数据
ENUM	列表中所选项	内部为整数	状态/分类等有限选项
SET	列表中 0 个或多个	内部为位图	标签等多选场景（不推荐）

5.8 CHAR vs VARCHAR —— 全面对比

这是数据库设计中最基础也是最重要的类型选择问题。

核心差异

维度	CHAR(M)	VARCHAR(M)
存储方式	固定长度，始终占用 M 个字符的空间	变长，只占用实际内容的空间 + 1或2字节长度标识
空间效率	M 较小时浪费空间，但对很短的固定值高效	对变长内容节省空间
读写性能	更快（无需计算实际长度）	稍慢（需要读取长度标识，更新时可能产生碎片）
尾部空格	存储时保留，检索时自动去除	存储时保留，检索时也保留
最大长度	最多 255 个字符	最多 65535 个字节（但受行大小限制）
长度标识	不需要	当 M <= 255 字节时用 1 字节标识；> 255 时用 2 字节

尾部空格处理的差异

sql

CREATE TABLE spacer (
    c CHAR(10),
    v VARCHAR(10)
);

INSERT INTO spacer VALUES ('hello ', 'hello ');

-- 查找尾部空格
SELECT
    c,
    v,
    CHAR_LENGTH(c) AS char_len,
    CHAR_LENGTH(v) AS varchar_len
FROM spacer;
-- +-------+--------+----------+-------------+
-- | c     | v      | char_len | varchar_len |
-- +-------+--------+----------+-------------+
-- | hello | hello  |        5 |           6 |
-- +-------+--------+----------+-------------+
-- CHAR(10) 的尾部空格在检索时被自动删除了！
-- VARCHAR(10) 保留了尾部空格

VARCHAR 的长度前缀开销

sql

-- VARCHAR(200)：需要 1 个字节存储长度信息（因为 200 <= 255）
-- VARCHAR(1000)：需要 2 个字节存储长度信息（因为 1000 > 255）
-- 这个 1 或 2 字节是额外的开销

-- 实际占用的总空间 = 实际字符串长度 + 长度前缀字节数（1 或 2）

-- 行大小限制
-- MySQL 每个行（所有列加起来）最大为 65535 字节
-- 所以你不能创建 VARCHAR(65535)（因为还有长度前缀的开销）
CREATE TABLE max_test (c VARCHAR(65535));
-- ERROR 1118 (42000): Row size too large.

为什么 VARCHAR(255) 特别常见？

VARCHAR(255) 是一个常见的"通用"选择，原因：

255 是 VARCHAR 能用 1 字节长度前缀的最大值（2^8 - 1 = 255）
比 256 的 VARCHAR 少 1 字节开销（1字节 vs 2字节长度前缀）
对于大多数短文本（用户名、邮箱等）来说足够长
历史上许多 ORM 框架和开发者的"惯性"选择

但这不是魔法数字！如果你确实需要更长的文本（比如地址可能需要 500 字符），就设置更大的值——多 1 字节的长度前缀开销在绝大多数场景下可以忽略。

选择指南

sql

-- ✅ 使用 CHAR 的场景
mobile CHAR(11),           -- 中国手机号固定 11 位
id_card CHAR(18),          -- 中国身份证号固定 18 位
md5_hash CHAR(32),         -- MD5 值固定 32 个字符
country_code CHAR(2),      -- 国家代码（CN、US 等）
gender CHAR(1),            -- 性别（M/F）
yesno CHAR(1),             -- 是/否（Y/N）

-- ✅ 使用 VARCHAR 的场景
username VARCHAR(50),       -- 用户名（变长）
email VARCHAR(100),         -- 邮箱（变长）
title VARCHAR(200),         -- 标题（变长）
address VARCHAR(500),       -- 地址（可能很长）

-- ✅ 使用 TEXT 的场景
content TEXT,               -- 文章内容（通常几千到几万字）
description MEDIUMTEXT,     -- 产品详细描述
log_data LONGTEXT           -- 超大日志/数据

5.9 TEXT 系列详解

TEXT 类型与最大长度

类型	最大字节数	约合字符数（utf8mb4）
TINYTEXT	255	~63
TEXT	65,535（64KB）	~16,383
MEDIUMTEXT	16,777,215（16MB）	~4,194,303
LONGTEXT	4,294,967,295（4GB）	~1,073,741,823

TEXT vs VARCHAR 的差异

sql

-- 差异1：TEXT 不能有 DEFAULT 值（VARCHAR 可以）
CREATE TABLE vt1 (
    v VARCHAR(100) DEFAULT '默认值',   -- ✅
    t TEXT DEFAULT '默认值'            -- ❌ ERROR 1101: BLOB/TEXT can't have a default value
);

-- 差异2：TEXT 大值会存储在行外（off-page）
-- 当 TEXT 的值超过一定大小时（InnoDB 约 768 字节），有 20 字节的指针留在行内，
-- 实际数据存储在额外的溢出页中。这对于查询性能有影响。

-- 差异3：TEXT 列需要指定前缀长度才能建索引
CREATE TABLE vt2 (
    id INT PRIMARY KEY,
    v VARCHAR(200),
    t TEXT
);

CREATE INDEX idx_v ON vt2(v);       -- ✅ VARCHAR 可以直接全文索引
CREATE INDEX idx_t ON vt2(t);       -- ❌ ERROR 1170: BLOB/TEXT column used in key spec without a key length

CREATE INDEX idx_t_prefix ON vt2(t(100));  -- ✅ 指定前缀长度才行

TEXT 存储位置示意

当 TEXT 列数据 <= 768 字节时：
┌──────────────────────────────────────────────┐
│ 行数据 [id][其他列][varchar列][TEXT内联存储]  │
└──────────────────────────────────────────────┘

当 TEXT 列数据 > 768 字节时：
┌───────────────────────────────────────┐   ┌─────────────────┐
│ 行数据 [id][...][20字节TEXT指针]      │──→│ TEXT数据溢出页   │
└───────────────────────────────────────┘   └─────────────────┘

5.10 BINARY / VARBINARY / BLOB 系列

这类类型用于存储二进制数据（不是文本！）：

sql

CREATE TABLE bin_demo (
    md5hash BINARY(16),         -- MD5 哈希（16 字节）：比 CHAR(32) 更省空间
    file_data MEDIUMBLOB,       -- 可以存 16MB 以内的文件
    avatar BLOB                 -- 头像图片（64KB 以内）
);

-- BINARY 尾部空格的处理：用 0x00 填充
INSERT INTO bin_demo (md5hash) VALUES (UNHEX('d41d8cd98f00b204e9800998ecf8427e'));
-- BINARY(16) 用 0x00 填充不足的位

BLOB 类型	最大字节数	对应的 TEXT 类型
TINYBLOB	255	TINYTEXT
BLOB	65,535	TEXT
MEDIUMBLOB	16,777,215	MEDIUMTEXT
LONGBLOB	4,294,967,295	LONGTEXT

BLOB vs 文件系统：虽然可以用 BLOB 存储图片和文件，但通常不推荐这样做。更好的方式是文件存储到文件系统或对象存储（OSS/S3），数据库只存文件路径。这样可以避免数据库膨胀、备份缓慢等问题。

5.11 ENUM 类型

ENUM 是"枚举"——从一个预定义的列表中选择恰好一个值：

sql

CREATE TABLE enum_demo (
    name VARCHAR(50),
    gender ENUM('male', 'female', 'other'),
    status ENUM('active', 'inactive', 'banned')
);

INSERT INTO enum_demo VALUES
    ('Alice', 'female', 'active'),
    ('Bob', 'male', 'active'),
    ('Charlie', 'other', 'inactive');

-- 可以按字符串查询
SELECT * FROM enum_demo WHERE gender = 'female';

-- 也能按索引值查询（'male'=1, 'female'=2, 'other'=3）
SELECT * FROM enum_demo WHERE gender = 1;  -- 等价于 gender = 'male'

-- 插入不在列表中的值会报错
INSERT INTO enum_demo VALUES ('Dave', 'unknown', 'active');
-- ERROR 1265 (01000): Data truncated for column 'gender' at row 1
-- 如果 sql_mode 不严格，则会是警告 + 插入空字符串 ''

ENUM 的内部存储

ENUM 内部用整数存储，值列表按声明顺序映射：

'male' → 1
'female' → 2
'other' → 3

每个 ENUM 值占 1 或 2 个字节（最多 65535 个枚举值）。

ENUM 排序是按索引值而非字符串

sql

-- 插入乱序数据
INSERT INTO enum_demo VALUES
    ('Zoe', 'other', 'inactive'),
    ('Andy', 'female', 'banned'),
    ('Bob', 'male', 'active');

-- 按 gender 排序
SELECT name, gender FROM enum_demo ORDER BY gender;
-- +---------+--------+
-- | name    | gender |
-- +---------+--------+
-- | Bob     | male   |    ← 索引 1
-- | Andy    | female |    ← 索引 2
-- | Charlie | other  |    ← 索引 3
-- | Zoe     | other  |    ← 索引 3
-- +---------+--------+
-- 结果是按索引 1,2,3 排序，不是按字母顺序！
-- 如果需要按字符串排序：ORDER BY CAST(gender AS CHAR) 或 ORDER BY FIELD()

ENUM 的陷阱与替代方案

sql

-- 陷阱1：添加或删除枚举值需要 ALTER TABLE（大表会锁表很久）
ALTER TABLE enum_demo MODIFY COLUMN gender
    ENUM('male', 'female', 'other', 'non_binary');

-- 陷阱2：可移植性差（ENUM 是 MySQL 特有的，PostgreSQL 和 SQLite 有不同实现）
-- 陷阱3：修改枚举值顺序会改变索引值，导致已有数据逻辑错误

-- ✅ 更好的替代方案：使用"查找表"（Lookup Table）+ 外键
CREATE TABLE genders (
    id TINYINT UNSIGNED PRIMARY KEY,
    label VARCHAR(20) NOT NULL
);
INSERT INTO genders VALUES (1, 'male'), (2, 'female'), (3, 'other');

CREATE TABLE users_better (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    gender_id TINYINT UNSIGNED,
    FOREIGN KEY (gender_id) REFERENCES genders(id)
);
-- 添加新的性别选项只需要在 genders 表中 INSERT 一行，不需要 ALTER

5.12 SET 类型

SET 允许从一个预定义的集合中选择 0 个、1 个或多个值：

sql

CREATE TABLE set_demo (
    name VARCHAR(50),
    skills SET('Java', 'Python', 'Go', 'Rust', 'JavaScript')
);

INSERT INTO set_demo VALUES
    ('Alice', 'Java,Python'),      -- 多选用逗号分隔
    ('Bob', 'Go,Rust'),
    ('Charlie', 'Python,Go,JavaScript'),
    ('Dave', '');                   -- 空集合

SELECT * FROM set_demo;
-- +---------+--------------------+
-- | name    | skills             |
-- +---------+--------------------+
-- | Alice   | Java,Python        |
-- | Bob     | Go,Rust            |
-- | Charlie | Python,Go,JavaScript|
-- | Dave    |                    |
-- +---------+--------------------+

-- 使用 FIND_IN_SET 查询包含某技能的用户
SELECT * FROM set_demo WHERE FIND_IN_SET('Python', skills);
-- +---------+--------------------+
-- | name    | skills             |
-- +---------+--------------------+
-- | Alice   | Java,Python        |
-- | Charlie | Python,Go,JavaScript|
-- +---------+--------------------+

-- 用 LIKE 也可以（但有风险）
SELECT * FROM set_demo WHERE skills LIKE '%Python%';
-- 但如果有人 skills = 'CPythonOnly' 也会被误匹配！

-- 位运算查询（使用 SET 的内部位图存储）
SELECT * FROM set_demo WHERE skills & 1;  -- 等价于包含 Java（第 1 位）

SET 的内部存储：每个集合元素对应位图中的一个位，最多 64 个元素（64 位 = 8 字节）。

SET 的陷阱

sql

-- 陷阱1：和 ENUM 一样，修改集合定义需要 ALTER TABLE
-- 陷阱2：FIND_IN_SET 性能差（无法使用索引），数据量大时效率很低
-- 陷阱3：`skills LIKE '%Go%'` 可能错误匹配（'Go' 匹配到了 'Golang' 中的 Go）
-- 陷阱4：可移植性差

-- ✅ 更好的替代方案：多对多关系表
CREATE TABLE skills_lookup (
    id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    skill_name VARCHAR(30) UNIQUE NOT NULL
);
INSERT INTO skills_lookup (skill_name) VALUES ('Java'), ('Python'), ('Go'), ('Rust'), ('JavaScript');

CREATE TABLE user_skills (
    user_id INT,
    skill_id TINYINT UNSIGNED,
    PRIMARY KEY (user_id, skill_id),
    FOREIGN KEY (skill_id) REFERENCES skills_lookup(id)
);
-- 查询某用户的技能：JOIN
-- 添加新技能：INSERT 到 lookup 表
-- 使用索引高效查询

ENUM 和 SET 的最终建议：对于小型、稳定、不常变更的枚举值（如 性别、状态），可以用 ENUM（确保值不会轻易增减）。对于需要动态增删的选项（如 标签、技能），使用查找表方案更灵活。SET 在现代 MySQL 开发中很少使用，建议尽量避免。

常见错误

错误 1：用 FLOAT 或 DOUBLE 存储金额

现象：金融应用中金额字段使用了 FLOAT，运行几个月后发现总账对不上。

原因：浮点数无法精确表示十进制小数，累积误差导致不一致。

解决：金额字段统一使用 DECIMAL(M, 2)（两位小数），如 DECIMAL(12, 2) 可精确存储到亿级别的金额。

错误 2：TINYINT UNSIGNED 存负数

现象：INSERT INTO t (age) VALUES (-5); 报错 Out of range。

原因：UNSIGNED 列不能存负数。

解决：如果数据可能为负，不要用 UNSIGNED。

错误 3：INT(5) 只能存 5 位数

现象：以为 INT(5) 只能存 0-99999，结果存入了 100000 也没报错。

原因：括号里的数字是显示宽度，不是存储限制。INT 永远是 4 字节，范围永远是 ±21 亿。

解决：如果想限制取值范围，使用 CHECK 约束（MySQL 8.0+）：CHECK (col BETWEEN 0 AND 99999)。

错误 4：VARCHAR 尾部空格被吃掉？

现象：用 CHAR 存了 "hello "，取出来变成 "hello"。

原因：CHAR 在检索时自动截断尾部空格。这是 SQL 标准规定的行为，不是 MySQL bug。

解决：如果需要保留尾部空格，使用 VARCHAR。

错误 5：不用 IF NOT EXISTS 导致的脚本失败

现象：自动化部署脚本中的 CREATE TABLE 失败，因为表已经存在。

解决：在脚本中使用 CREATE TABLE IF NOT EXISTS，确保脚本可重复执行。

本章练习

整数类型练习：创建一张表，包含 TINYINT、SMALLINT、INT、BIGINT 四列。分别插入它们的有符号和无符号的边界值（最大值和最小值），观察哪些插入成功，哪些报错。
DECIMAL vs FLOAT 精度对比：创建一张表用 FLOAT 和 DECIMAL(10, 2) 同时存储 0.1、0.2、0.3，然后分别计算 0.1 + 0.2 = 0.3 的布尔结果。解释你观察到的差异。
CHAR vs VARCHAR 尾部空格实验：创建一个 CHAR(10) 列和一个 VARCHAR(10) 列，插入相同的带尾部空格的值 'hello '，然后用 CHAR_LENGTH 函数测量两者的长度，解释差异。
ENUM 排序测试：创建一个 ENUM 列 ('low', 'medium', 'high')，插入多个值后执行 ORDER BY，观察排序是按照什么顺序的。尝试用 ORDER BY CAST(column AS CHAR) 改为字母排序。
类型选择练习：为一个"图书管理系统"设计 users 表和 books 表的列类型。考虑：用户 ID、姓名、邮箱、密码哈希、注册日期；图书 ID、书名、ISBN、价格、摘要、封面图片路径。说明每个列的选型理由。
SET 替代方案：设计一个"文章标签"功能（一个文章可以有多个标签），分别用 SET 方式（不推荐）和多对多关联表方式（推荐）实现。写出两种方式的建表 SQL，并比较它们的灵活性、查询效率和可维护性。

第5章：数据类型（上）—— 数值与字符串 ​

本章目标 ​

前置知识 ​

5.1 数值类型总览 ​

5.2 整数类型详解 ​

五种整数类型 ​

存储空间的含义 ​

UNSIGNED 修饰符 ​

INT(11) 中的括号数字是什么？ ​

整数类型选择指南 ​

5.3 AUTO_INCREMENT 与整数类型 ​

5.4 DECIMAL —— 精确定点数 ​

语法与参数 ​

存储大小 ​

5.5 FLOAT 与 DOUBLE —— 浮点数的精度陷阱 ​

基本概念 ​

精度陷阱演示 ​

真实世界的后果 ​

5.6 BIT 类型 ​

5.7 字符串类型总览 ​

5.8 CHAR vs VARCHAR —— 全面对比 ​

核心差异 ​

尾部空格处理的差异 ​

VARCHAR 的长度前缀开销 ​

为什么 VARCHAR(255) 特别常见？ ​

选择指南 ​

5.9 TEXT 系列详解 ​

TEXT 类型与最大长度 ​

TEXT vs VARCHAR 的差异 ​

TEXT 存储位置示意 ​

5.10 BINARY / VARBINARY / BLOB 系列 ​

5.11 ENUM 类型 ​

ENUM 的内部存储 ​

ENUM 排序是按索引值而非字符串 ​

ENUM 的陷阱与替代方案 ​

5.12 SET 类型 ​

SET 的陷阱 ​

常见错误 ​

错误 1：用 FLOAT 或 DOUBLE 存储金额 ​

错误 2：TINYINT UNSIGNED 存负数 ​

错误 3：INT(5) 只能存 5 位数 ​

错误 4：VARCHAR 尾部空格被吃掉？ ​

错误 5：不用 IF NOT EXISTS 导致的脚本失败 ​

本章练习 ​