第四单元 · 概念、类型、改表、分页 · 第 9 / 21 节

字段类型怎么选：数字、文字、日期

建 lesson09_types（6 行、8 列），用真实输出看清三件事：DECIMAL 精确而 DOUBLE 是近似值；CHAR 会补空格而 VARCHAR(20) 的 20 是字符不是字节；DATE / DATETIME / TIMESTAMP 各记什么、TIMESTAMP 为什么只到 2038。最后给出“先问精确、再问长度、再问时分秒”的选型顺序。

1 个步骤 · 进度只存本机

约 24 分钟。这一节建第四单元的第一张表，只解决一件事：同样是“存一个值”，为什么有的列用 INT、有的用 DECIMAL、有的用 VARCHAR，选错了会怎样。

SSH / Linux 终端MySQL 终端输出示例（只读）复制按钮只复制命令，不含 $ / mysql> 提示符和输出。

已完成 0 / 1 步

你现在在哪个提示符

应该在 mysql>。断了就先回到学习终端（PowerShell）：

SSH / Linux 终端bash

ssh root@8.148.204.152

SSH / Linux 终端bash

mysql-learning

进去以后确认当前库：

MySQL 终端sql

SELECT DATABASE();

输出示例（只读，不要粘贴执行）text不可复制

+------------+
| DATABASE() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

显示 itcast 就对了。如果是 NULL，执行 USE itcast;。

第一步：一张表里三种类型，各管一类值

这一步做完了

第 2 节建表时只用了 INT、VARCHAR(20)、DATE 三个类型，够用但不够选。这一节建一张“工具清单”，把数字、文字、日期三类都放进去，一次看清它们的差别。

先确认这张表还不存在：

MySQL 终端sql

SHOW TABLES LIKE 'lesson09_types';

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

Empty set 说明可以建。如果已经有这张表，说明你之前跑过本节，直接跳到「怎么选」那一段核对，不要重复建。

MySQL 终端sql

CREATE TABLE lesson09_types (
  id INT PRIMARY KEY COMMENT '编号',
  code CHAR(4) COMMENT '定长编号',
  name VARCHAR(20) COMMENT '商品名，按字符算',
  price DECIMAL(8,2) COMMENT '价格，两位小数',
  weight DOUBLE COMMENT '重量，近似值',
  made_on DATE COMMENT '生产日期',
  made_at DATETIME COMMENT '生产时刻',
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '行更新时间'
) COMMENT='本教程第四单元练习表：类型怎么选';

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 0 rows affected (0.03 sec)

MySQL 终端sql

INSERT INTO lesson09_types (id, code, name, price, weight, made_on, made_at) VALUES
  (1, 'A1  ', '螺丝刀', 19.90, 0.35, '2024-01-05', '2024-01-05 09:30:00'),
  (2, 'B22 ', '卷尺',   7.50, 0.12, '2024-02-11', '2024-02-11 14:05:30'),
  (3, 'C333', '锤子',  45.00, 0.80, '2024-03-02', '2024-03-02 08:15:45'),
  (4, 'D4  ', '扳手',  32.80, 0.45, '2024-03-18', '2024-03-18 16:40:10'),
  (5, 'E55 ', '钳子',  28.00, 0.30, '2024-04-01', '2024-04-01 11:20:00'),
  (6, 'F6  ', '美工刀',  9.90, 0.05, '2024-04-20', '2024-04-20 10:00:00');

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

6 行、8 列。code 那几行你写的是 'A1 '（后面两个空格）、'B22 '（一个空格）——先照抄，下面会看到 MySQL 怎么处理它们。表里已经有这 6 行时，再把 INSERT 跑一遍会得到 1062（主键重复），原来的 6 行不会被改坏。

先看表结构：

MySQL 终端sql

DESC lesson09_types;

输出示例（只读，不要粘贴执行）text不可复制

+------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field      | Type         | Null | Key | Default           | Extra                                         |
+------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id         | int          | NO   | PRI | NULL              |                                               |
| code       | char(4)      | YES  |     | NULL              |                                               |
| name       | varchar(20)  | YES  |     | NULL              |                                               |
| price      | decimal(8,2) | YES  |     | NULL              |                                               |
| weight     | double       | YES  |     | NULL              |                                               |
| made_on    | date         | YES  |     | NULL              |                                               |
| made_at    | datetime     | YES  |     | NULL              |                                               |
| updated_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

Type 那一列就是这节的主角。下面按三类拆开看。

数字：`INT`、`DECIMAL`、`DOUBLE` 不是一回事

id 用 INT：整数，范围够放编号；price 用 DECIMAL(8,2)：总共 8 位数字，其中 2 位在小数点后，最大值 999999.99；weight 用 DOUBLE：二进制浮点数，存的是近似值。

“近似”到什么程度？让两个浮点数相减：

MySQL 终端sql

SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) AS 双精度和,
       CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) - CAST(0.3 AS DOUBLE) AS 双精度差;

输出示例（只读，不要粘贴执行）text不可复制

+---------------------+-----------------------+
| 双精度和            | 双精度差              |
+---------------------+-----------------------+
| 0.30000000000000004 | 5.551115123125783e-17 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

0.30000000000000004 —— 0.1 加 0.2 本该是 0.3，这里多出来一串尾巴；5.55e-17 就是那点误差。DOUBLE 用二进制分数表示小数，0.1 和 0.2 都只能存成无限接近的近似值，加起来的误差就露出来了。再看定点数：

MySQL 终端sql

SELECT CAST(0.1 AS DECIMAL(4,1)) + CAST(0.2 AS DECIMAL(4,1)) - CAST(0.3 AS DECIMAL(4,1)) AS 定点差;

输出示例（只读，不要粘贴执行）text不可复制

+-----------+
| 定点差    |
+-----------+
|       0.0 |
+-----------+
1 row in set (0.00 sec)

0.0，一位不差。DECIMAL 是按十进制整数存小数点位置的，0.1 就是 1/10，不会变成二进制近似值。所以**钱、价格、分数这类“差一分都不行”的值用 DECIMAL**；DOUBLE 留给“测量值、比例、科学计算”这类本来就允许误差的地方。

DECIMAL(8,2) 的 8 位是“总共 8 位”，不是“小数点前 8 位”：最大 999999.99，再大就放不进去了，这一点下一节「常见报错」会看到真实报错。

文字：`CHAR` 补空格，`VARCHAR` 按字符算

code 是 CHAR(4)（定长 4 个字符），name 是 VARCHAR(20)（最多 20 个字符）。第 1 行你存的是 'A1 '，取出来看看：

MySQL 终端sql

SELECT id, CONCAT('[', code, ']') AS 定长列, CHAR_LENGTH(code) AS 字符数, LENGTH(code) AS 字节数
FROM lesson09_types WHERE id = 1;

输出示例（只读，不要粘贴执行）text不可复制

+----+-----------+-----------+-----------+
| id | 定长列    | 字符数    | 字节数    |
+----+-----------+-----------+-----------+
|  1 | [A1]      |         2 |         2 |
+----+-----------+-----------+-----------+
1 row in set (0.00 sec)

方括号里只剩 A1，长度是 2。CHAR(4) 在存的时候把 'A1' 补空格补满 4 位，取回时又把尾部空格去掉，所以你看不到那两个空格——**定长列的长度是固定的；取回会去掉尾部空格，但在本库的 NO PAD 排序规则（utf8mb4_0900_ai_ci）下，比较时字面量末尾的空格仍然算数**。

再对比 name 这一列存中文：

MySQL 终端sql

SELECT id, name, CHAR_LENGTH(name) AS 字符数, LENGTH(name) AS 字节数
FROM lesson09_types WHERE id = 1;

输出示例（只读，不要粘贴执行）text不可复制

+----+-----------+-----------+-----------+
| id | name      | 字符数    | 字节数    |
+----+-----------+-----------+-----------+
|  1 | 螺丝刀    |         3 |         9 |
+----+-----------+-----------+-----------+
1 row in set (0.00 sec)

螺丝刀 是 3 个字符、9 个字节（utf8mb4 下一个汉字 3 字节）。VARCHAR(20) 里的 20 是字符数，不是字节数，所以 20 个汉字能存，占 60 字节也没关系。CHAR(n) 的 n 也是字符数，只是它会补空格到 n。

怎么选：长度基本固定、都很短（编号、性别、省份代码）用 CHAR；长度差别大（姓名、商品名、地址）用 VARCHAR。拿不准就用 VARCHAR，它不会浪费补齐的空格。

日期：`DATE`、`DATETIME`、`TIMESTAMP` 各记什么

made_on 是 DATE（只有年月日），made_at 是 DATETIME（年月日时分秒），updated_at 是 TIMESTAMP（也是年月日时分秒，但范围只到 2038 年，还能自动记录行更新时间）。

MySQL 终端sql

SELECT id, made_on, made_at FROM lesson09_types ORDER BY id LIMIT 3;

输出示例（只读，不要粘贴执行）text不可复制

+----+------------+---------------------+
| id | made_on    | made_at             |
+----+------------+---------------------+
|  1 | 2024-01-05 | 2024-01-05 09:30:00 |
|  2 | 2024-02-11 | 2024-02-11 14:05:30 |
|  3 | 2024-03-02 | 2024-03-02 08:15:45 |
+----+------------+---------------------+
3 rows in set (0.00 sec)

DATE 输出 10 位，DATETIME 输出 19 位。再看 8 列的默认值和 EXTRA（哪些列会自己动），这次用系统表查，按列的原始顺序排：

MySQL 终端sql

SELECT COLUMN_NAME AS 列, COLUMN_TYPE AS 类型, COLUMN_DEFAULT AS 默认值, EXTRA AS 额外
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'itcast' AND TABLE_NAME = 'lesson09_types'
ORDER BY ORDINAL_POSITION;

输出示例（只读，不要粘贴执行）text不可复制

+------------+--------------+-------------------+-----------------------------------------------+
| 列         | 类型         | 默认值            | 额外                                          |
+------------+--------------+-------------------+-----------------------------------------------+
| id         | int          | NULL              |                                               |
| code       | char(4)      | NULL              |                                               |
| name       | varchar(20)  | NULL              |                                               |
| price      | decimal(8,2) | NULL              |                                               |
| weight     | double       | NULL              |                                               |
| made_on    | date         | NULL              |                                               |
| made_at    | datetime     | NULL              |                                               |
| updated_at | timestamp    | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+--------------+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

最后一行 updated_at 的 EXTRA 是 DEFAULT_GENERATED on update CURRENT_TIMESTAMP：插入时不写它，自动填当前时间；这行以后被 UPDATE，它自己跟着改。这就是“行更新时间”最省事的做法。代价是范围：TIMESTAMP 只到 2038-01-19，DATETIME 到 9999 年。存生日、入职日期这种“事实时间”用 DATE/DATETIME；存“这条记录什么时候被改的”用 TIMESTAMP。

三类选完，回到一句话：**数字先问要不要精确（要精确就 DECIMAL），文字先问长度是否固定（不固定就 VARCHAR），日期先问记不记时分秒（不记就 DATE）**。

常见报错怎么办

看到	意思	怎么办
`1264 Out of range value`	数值超出列定义的范围	看 `DECIMAL(总位数,小数位)`，或换成更大的类型
`1406 Data too long`	文字超过列长度	加长 `VARCHAR(n)`，或先截短再插
`1265 Data truncated`	值的格式对不上列类型（如把 `'abc'` 塞进数字列）	改成合法字面量，别指望 MySQL 帮你猜
`1292 Incorrect date/datetime value`	日期不合法或超出类型范围	检查 `2024-02-30` 这种不存在的日期，或 `TIMESTAMP` 的 2038 上限

说明刻意写错的例子：DECIMAL(8,2) 塞 7 位整数

MySQL 终端sql

INSERT INTO lesson09_types (id, code, name, price, weight, made_on, made_at)
VALUES (7, 'G7  ', '卷笔刀', 1234567.89, 0.05, '2024-01-01', '2024-01-01 00:00:00');

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1264 (22003): Out of range value for column 'price' at row 1

DECIMAL(8,2) 一共 8 位，小数占 2 位，整数最多 6 位，1234567 是 7 位，超了。想存这么大的数就得改成 DECIMAL(10,2) 之类——但先想清楚业务上真有这么大的价格吗。

说明刻意写错的例子：往 VARCHAR(20) 塞 21 个字符

MySQL 终端sql

INSERT INTO lesson09_types (id, code, name, price, weight, made_on, made_at)
VALUES (8, 'H8  ', 'abcdefghijklmnopqrstu', 1.00, 0.05, '2024-01-01', '2024-01-01 00:00:00');

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1406 (22001): Data too long for column 'name' at row 1

21 个字母超过 20。注意 1406 是严格模式（STRICT_TRANS_TABLES）下的行为：直接拒绝，不偷偷截断。要是关掉严格模式，它会截成 20 个字符再插进去并给一条警告——那才是更难查的坑。

说明刻意写错的例子：2 月 30 日

MySQL 终端sql

INSERT INTO lesson09_types (id, code, name, price, weight, made_on, made_at)
VALUES (9, 'I9  ', '测试', 1.00, 0.05, '2024-02-30', '2024-01-01 00:00:00');

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1292 (22007): Incorrect date value: '2024-02-30' for column 'made_on' at row 1

DATE 列不接受不存在的日期，1292 直接拒绝。这类错误在批量导入时最常见。

说明刻意写错的例子：TIMESTAMP 存 2039 年

MySQL 终端sql

INSERT INTO lesson09_types (id, code, name, price, weight, made_on, made_at, updated_at)
VALUES (10, 'J0  ', '测试', 1.00, 0.05, '2024-01-01', '2024-01-01 00:00:00', '2039-01-01 00:00:00');

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1292 (22007): Incorrect datetime value: '2039-01-01 00:00:00' for column 'updated_at' at row 1

DATETIME 能存 2039 年，TIMESTAMP 不能——它只到 2038-01-19。所以“未来某个时间点”不要用 TIMESTAMP 存。

练习

前三题动手，第四题只问选择。

1. 20 个汉字占多少字符、多少字节？用一条查询证明。

提示提示

CHAR_LENGTH() 数字符，LENGTH() 数字节，REPEAT('中', 20) 能造出 20 个汉字。

参考答案答案

MySQL 终端sql

SELECT CHAR_LENGTH(REPEAT('中', 20)) AS 字符数, LENGTH(REPEAT('中', 20)) AS 字节数;

输出示例（只读，不要粘贴执行）text不可复制

+-----------+-----------+
| 字符数    | 字节数    |
+-----------+-----------+
|        20 |        60 |
+-----------+-----------+
1 row in set (0.00 sec)

20 个字符、60 个字节。VARCHAR(20) 限制的是 20 个字符，所以这 20 个汉字放得下。

说明常见错

以为 VARCHAR(20) 只能存 20 字节，于是把中文名截到 6 个字——那是按字节算才会有的结论，在 utf8mb4 下不成立。

**2. 用一条语句说明 DOUBLE 存的小数是近似值，而 DECIMAL 不是。**

提示提示

把 0.1 和 0.2 都转成 DOUBLE 相加，再减 0.3；同样的事用 DECIMAL 做一遍，两个结果放在同一行对比。

参考答案答案

MySQL 终端sql

SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) - CAST(0.3 AS DOUBLE) AS 双精度差,
       CAST(0.1 AS DECIMAL(4,1)) + CAST(0.2 AS DECIMAL(4,1)) - CAST(0.3 AS DECIMAL(4,1)) AS 定点差;

输出示例（只读，不要粘贴执行）text不可复制

+-----------------------+-----------+
| 双精度差              | 定点差    |
+-----------------------+-----------+
| 5.551115123125783e-17 |       0.0 |
+-----------------------+-----------+
1 row in set (0.00 sec)

DOUBLE 那一列不是 0，DECIMAL 那一列是 0.0。只要比较里出现“差一点点”，金额就不能用 DOUBLE 存。

说明常见错

直接写 SELECT 0.1 + 0.2 - 0.3; 会得到 0.0——因为不带引号的小数字面量在 MySQL 里默认是 DECIMAL，不是 DOUBLE。必须显式 CAST(... AS DOUBLE) 才看得到浮点误差。

**3. 查出第 2 行的 code 实际存了几个字符、显示出来是什么。**

提示提示

用 CONCAT('[', code, ']') 把首尾标出来，空格就看得见了；长度用 CHAR_LENGTH。

参考答案答案

MySQL 终端sql

SELECT id, CONCAT('[', code, ']') AS 显示, CHAR_LENGTH(code) AS 字符数
FROM lesson09_types WHERE id = 2;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+-----------+
| id | 显示   | 字符数    |
+----+--------+-----------+
|  2 | [B22]  |         3 |
+----+--------+-----------+
1 row in set (0.00 sec)

你插的是 'B22 '（3 个字符 + 1 个空格），CHAR(4) 补齐到 4 位，取出来又把尾空格去掉，所以显示 [B22]、长度 3。

说明常见错

用 LENGTH(code) 判断“存了几个空格”——LENGTH 是字节数，在纯 ASCII 上恰好相等，换成中文就露馅了；数字符统一用 CHAR_LENGTH。

**4. price 为什么用 DECIMAL(8,2) 而不是 DOUBLE？用一句话回答。**

参考答案答案

价格要求十进制精确：DOUBLE 是二进制近似，累加、比较会出现 0.30000000000000004 这种尾差；DECIMAL(8,2) 固定两位小数，存 19.90 就是 19.90，SUM 出来也不带误差。(8,2) 同时给了上限 999999.99，超过就报 1264，这是保护而不是限制。

本单元验收

能说出 INT、DECIMAL(8,2)、DOUBLE 各适合存什么，并演示一次 DOUBLE 的尾差。
能解释 CHAR(4) 为什么要补空格、VARCHAR(20) 的 20 是字符还是字节。
能说出 DATE、DATETIME、TIMESTAMP 的区别，包括 TIMESTAMP 的 2038 上限和自动更新时间。
能查出 updated_at 的 EXTRA 是 DEFAULT_GENERATED on update CURRENT_TIMESTAMP，并解释它意味着什么。
SELECT COUNT(*), SUM(price) FROM lesson09_types; 得到 6 和 143.10。

做到这里

做到这里今天可以停。你现在能看着一个值就选出类型，也知道选错的四种典型报错长什么样。这张表 lesson09_types 有 6 行，后面的节不会改它。

下一节换一件事：表已经建好了，怎么加一列、改一列的类型、给列换个名字、再删掉一列。

来源与边界

本节对应 BV1Kr4y1i7ru 平台 part 8（原标题「08. 基础-SQL-DDL-数据类型及案例」），在 P01–P50 范围内；主题与同版讲义《MySQL-基础篇》2.3.2.2 数据类型（p20–22）、2.3.2.3 建表案例（p22–23）对应。讲义为公开镜像，仅作事实核对。

无逐字稿，本节未声称听过讲解。表名、6 行数据、全部查询与练习均为本教程原创；DOUBLE 尾差、CHAR 尾空格、TIMESTAMP 2038 上限这三段输出是在作者自己的隔离 MySQL 8.0.46 实例上实跑得到的，不是从讲义抄的结论。

字段类型怎么选：数字、文字、日期

你现在在哪个提示符

第一步：一张表里三种类型，各管一类值

数字：INT、DECIMAL、DOUBLE 不是一回事

文字：CHAR 补空格，VARCHAR 按字符算

日期：DATE、DATETIME、TIMESTAMP 各记什么

常见报错怎么办

练习

本单元验收

做到这里

来源与边界

数字：`INT`、`DECIMAL`、`DOUBLE` 不是一回事

文字：`CHAR` 补空格，`VARCHAR` 按字符算

日期：`DATE`、`DATETIME`、`TIMESTAMP` 各记什么