unsigned 是什么意思（PPT 第 34 页问题一）#

PPT 第 34 页的问答原文：

问：数值类型在定义的时候，后面加了 unsigned 关键字是什么意思？ 答：unsigned 表示无符号类型，表示只能取 0 及正数；不加默认是 signed，表示可以取负数。

“符号”指的就是正负号。加上 unsigned 之后，原来用来表示负数的那一半取值空间全部挪给正数，所以上限直接翻倍：tinyint 从 -128127 变成 0255，int 从约 ±21 亿变成 0~约 42 亿。

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

什么时候该加 unsigned？规律很简单——这个字段在业务上不可能为负：年龄、薪资、性别编号、职位编号、学号……课程里员工表的写法就是现成例子：

1
-- 课程 SQL脚本.sql 中 emp 表的片段
2
id int unsigned primary key auto_increment comment 'ID, 主键',
3
gender tinyint unsigned not null comment '性别, 1 男; 2 女',
4
job tinyint unsigned comment '职位, 1 班主任; 2 讲师; 3 学工主管; 4 教研主管; 5 咨询师',
5
salary int unsigned comment '薪资',

id、gender、job、salary 都不会是负数，所以统一加了 unsigned；其中 gender、job 的取值只有几个，用 tinyint 就够，没必要上 int。

选类型的原则：够用就好#

xlsx 上给出的选取原则原话是：

在满足业务需求的前提下，尽可能选择占用磁盘空间小的数据类型。

所以它给的示例字段是 age tinyint unsigned（年龄最大也就 100 多，1 个字节足够）、id int unsigned（表里的数据量用 4 个字节足够装下）。反过来，“为了保险全用 bigint”是典型的浪费——字段越宽，占的磁盘、读写时搬的数据都更多。

float / double 与 decimal：小数为什么还有两种#

这是最容易踩的一个坑，先用实测说话：

1
-- float 字段存 1234567.89
2
select ... ;   -- 查出来是 1234570        ← 精度丢了
3
-- decimal(10,2) 字段存 1234567.89
4
select ... ;   -- 查出来是 1234567.89     ← 一分不差
5
-- decimal(10,2) 字段存 1.999
6
select ... ;   -- 查出来是 2.00           ← 小数位四舍五入，而且不报错

char 与 varchar 的区别（PPT 第 34 页问题二）#

PPT 第 34 页的问答原文：

问：char 与 varchar 的区别是什么？什么时候用 char，什么时候用 varchar？ 答：char 是定长字符串，varchar 是变长字符串。 如果一个字段的长度是固定的，建议使用 char；如：身份证号、手机号。 如果一个字段的长度不是固定的，建议使用 varchar；如：用户名、姓名。

为什么长度固定时 char 反而更好？xlsx 把账算得很清楚：

字符串类型也有”越界”#

只要值的长度超过括号里允许的范围，插入就会失败：

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

日期写错了会怎样#

1
-- 用斜杠写日期，居然也能插进去
2
... values ('2020/01/01')     -- 插入成功，存成了 2020-01-01
3
-- 月份写成 13
4
... values ('2020-13-01')     -- ERROR 1292 (22007): Incorrect date value: '2020-13-01' for column 'birth' at row 1

一、知识回顾（读完直接做下面的实践题）#

1. 数据类型分三类：数值类型、字符串类型、日期时间类型（PPT 第 33 页只有这一句概述，三张详细表格来自课程配套的 MySQL数据类型.xlsx）
2. 整数类型按占用空间从小到大：tinyint(1 字节，有符号 -128127 / 无符号 0255)、smallint(2 字节)、mediumint(3 字节)、int(4 字节，约 ±21 亿 / 无符号约 42 亿)、bigint(8 字节)
3. unsigned 表示无符号类型，只能取 0 及正数；不加默认是 signed（可以取负数）。加 unsigned 后上限翻倍（tinyint：127 → 255）。插入超出范围的值报 ERROR 1264 (22003): Out of range value for column ...
4. 小数类型：float(4 字节，单精度近似值)、double(8 字节，双精度近似值)、decimal（精确小数值，金额用）；decimal(5,2) 里的 5 = 整个数字长度、2 = 小数位个数
5. 实测证据：float 字段存 1234567.89 查出来是 1234570（精度丢失）；decimal(10,2) 存同一个数分毫不差；decimal(10,2) 存 1.999 得到 2.00（小数位四舍五入，不报错）——所以金额字段必须用 decimal
6. char 是定长字符串（char(10) 存 A 占 10 个字符，优势性能略高、劣势浪费空间）；varchar 是变长字符串（varchar(10) 存 A 只占 1 个字符，优势节约空间、劣势性能略低）
7. 选择规律：长度固定的字段用 char——身份证号 char(18)、手机号 char(11)；长度不固定的字段用 varchar——用户名 varchar(50)、姓名 varchar(10)。char 补的空格在检索时会被去掉，所以实测 char(11) 和 varchar(11) 存 'ab' 查出来的 char_length 都是 2（差别在存储层面）
8. 字符串类型越界报 ERROR 1406 (22001): Data too long for column 'xxx' at row 1（如 varchar(10) 里塞 11 个字符）；长文本用 text 系列，二进制数据用 blob 系列
9. 日期时间类型：date（3 字节，YYYY-MM-DD）、time（3 字节，HH:MM）、year（1 字节，YYYY）、datetime（8 字节，YYYY-MM-DD HH:MM）、timestamp（4 字节，时间戳，范围只到 2038-01-19）；只要日期用 date，日期时间都要用 datetime
10. 日期实测：'2020/01/01' 这种斜杠写法能插入（MySQL 接受），但值不合法（如 '2020-13-01'）报 ERROR 1292 (22007): Incorrect date value
11. 选类型的总原则（xlsx 原话）：在满足业务需求的前提下，尽可能选择占用磁盘空间小的数据类型——比如年龄用 age tinyint unsigned、id 用 int unsigned

二、裸写题#

• 2-1 给一组字段挑类型 要给一张”商品表”定字段。请为下面每个字段选出合适的数据类型（只写类型即可，括号里的长度/精度一并写出来），并说明理由：

  字段	用途说明
  id	每条商品的编号，做唯一标识，从 1 开始自动往后发，不会是负数
  name	商品名称，最长 50 个字符
  price	售价，要求精确到分
  stock	库存数量，最大几万件，不会是负数
  is_on_sale	是否上架，只有”上架 / 下架”两种
  pic_path	商品图片的存放路径，最长 255 个字符
  detail	商品详情，可能是很长的一段文字
  create_time	商品上架的具体日期和时间（精确到秒）

  提示（先自己想，实在想不出再点开）

  一级 · 思路：先给每个字段归到”数值 / 字符串 / 日期时间”三大类里的哪一类，再在同一类里挑”装得下、又不浪费”的那一个——数量只有几种的字段别用 int，固定长度的字符串想想要不要定长，金额要精确 二级 · 方法：整数用 tinyint / smallint / int（不会为负就加 unsigned，但 id 做标识时还要能自动发号）；金额用 decimal(总长度,小数位)；字符串长度固定用 char(n)、不固定用 varchar(n)、很长用 text；日期时间用 date / datetime 三级 · 骨架：id ____ unsigned primary key auto_increment、is_on_sale ____ unsigned、price ____(10,2)、name ____(50)、detail ____、create_time ____

  参考答案（做完再点开）

  字段	类型	理由
  id	int unsigned	标识用整数，业务上不为负；表里数据量用 4 个字节足够（够用就好）
  name	varchar(50)	商品名称长度不固定，用变长字符串省空间
  price	decimal(10,2)	金额必须用精确类型，2 位小数精确到分（实测 float 存 1234567.89 会变成 1234570）
  stock	int unsigned	库存是整数且不为负；几万件用 int 足够（也可以 mediumint unsigned）
  is_on_sale	tinyint unsigned	只有两种取值（1 上架 / 2 下架），1 个字节就够，范围 0~255
  pic_path	varchar(255)	路径长度不固定，上限给到 255（课程 emp 表的 image varchar(255) 就是这么写的）
  detail	text	可能是很长的一段文字 → 用长文本类型 text（xlsx 里 text 的定位就是”长文本数据”，0-65 535 bytes）
  create_time	datetime	日期和时间都要，格式 YYYY-MM-DD HH:MM

  一句总原则收尾：能满足需求的前提下选占用空间小的类型——所以这里没有一处用 bigint 或 double。

• 2-2 定长还是变长 下面 5 个字段，各自该用 char 还是 varchar？逐个说明判断依据。

  1. 身份证号（固定 18 位）
  2. 手机号（固定 11 位）
  3. 姓名（1~10 个字不等）
  4. 用户名（3~20 个字符不等）
  5. 学号（固定 10 位数字）

  做完再想一个”反例”：如果把手机号写成 varchar(11)，功能上还能存下 11 位手机号吗？那为什么课程里还是用 char(11)？

  提示（先自己想，实在想不出再点开）

  一级 · 思路：只看一件事——这个字段的长度是不是”每个值都一样”。长度固定的用定长字符串（省得每次算长度，性能略高）；长度会变的用变长字符串（省磁盘） 二级 · 方法：定长用 char(n)，变长用 varchar(n)，括号里的 n 是字符数上限 三级 · 骨架：身份证号 ____(18)、手机号 ____(11)、姓名 ____(10)、用户名 ____(20)、学号 ____(10)

  参考答案（做完再点开）

  1. 身份证号 → char(18)：每个人的身份证号都是 18 位，长度固定（xlsx 备注里的示例字段就是 idcard char(18)）
  2. 手机号 → char(11)：固定 11 位（xlsx 备注示例 phone char(11)）
  3. 姓名 → varchar(10)：1 个字到十几个字都有，长度不固定（PPT 第 34 页举的例子）
  4. 用户名 → varchar(50)：长度不固定，短的 3 个字符、长的十几个（课程 emp 表用的是 varchar(20)，user 表用的是 varchar(50)）
  5. 学号 → char(10)：固定 10 位（真实业务里如果学号长度不固定，就该改成 varchar）

  反例的答案：写 varchar(11) 功能上也能存下 11 位手机号，但长度固定的字段用定长字符串性能略高，而且 char 能顺带表达”这一定是个 11 位的值”这层含义，所以课程里选 char(11)。反过来，如果长度不固定的字段用了 char，每行都要按最大长度占空间，浪费磁盘。

• 2-3 这段建表语句里的 unsigned 是什么用意 下面是一段真实的建表语句，另外附上一条插入数据时的报错。请回答三个问题：

  1
  create table score(
  2
      id int unsigned primary key auto_increment comment 'ID',
  3
      age tinyint unsigned comment '年龄',
  4
      salary int unsigned comment '薪资'
  5
  );
  6
  
  7
  insert into score(age) values (-1);
  8
  -- ERROR 1264 (22003): Out of range value for column 'age' at row 1

  1. 三个字段都写了 unsigned，它是什么意思？不加的话默认是什么？
  2. 为什么插入 -1 会报错？这个错误码是多少、报错原文说的是什么？
  3. 同样的 tinyint，加了 unsigned 之后能存的最大值是多少？不加呢？
  提示（先自己想，实在想不出再点开）

  一级 · 思路：这题的关键词是”符号”——unsigned 决定这个数字字段能不能是负数，以及正数那一半的空间有多大 二级 · 方法：unsigned = 无符号（只能 0 和正数）；不加默认 signed = 有符号（可以取负数）；越界报错的关键词是 Out of range value 三级 · 骨架：tinyint 有符号范围 -128 ~ ____，无符号范围 0 ~ ____

  参考答案（做完再点开）

  1. unsigned 表示无符号类型，表示只能取 0 及正数；不加默认是 signed（有符号，可以取负数）。年龄、薪资这类字段业务上不可能为负，加上 unsigned 顺带把上限翻倍。
  2. 因为 age 是 tinyint unsigned，取值范围是 0~255，-1 落在这个范围之外，所以越界报错——ERROR 1264 (22003): Out of range value for column 'age' at row 1，关键词就是 Out of range value（值超出范围）。
  3. tinyint 不加 unsigned 的范围是 -128~127；加了 unsigned 是 0~255（原来表示负数的那一半空间挪给了正数，所以能存的最大值从 127 变成 255）。

  顺带一提：本机实测同一个错误在 tinyint unsigned 字段里塞 256 时也会报 ERROR 1264——两头越界都是同一个错误码。

• 2-4 排错：四个字段的四个坑 下面是”学生表”里的四个字段，同事写完之后在测试环境里报了一串错，还有两个”没报错但数据不对”的情况。请逐个说明原因和正确的写法：

  现象	相关字段定义	报错 / 表现
  现象 1	nickname varchar(2)	存 3 个字的昵称时报 ERROR 1406 (22001): Data too long for column 'nickname' at row 1
  现象 2	birth date	存 '2020-13-01' 时报 ERROR 1292 (22007): Incorrect date value: '2020-13-01' for column 'birth' at row 1
  现象 3	tuition float	存 1234567.89 不报错，但查出来变成 1234570
  现象 4	score decimal(10,2)	存 1.999 不报错，但查出来变成 2.00

  提示（先自己想，实在想不出再点开）

  一级 · 思路：四个现象分两类——前两个是”值不符合类型的限制”（会报错），后两个是”类型的精度特性”（不报错，但数据被改了）。改法都是从”这个字段真正要存什么”出发 二级 · 方法：字符串超长要么把长度上限开大（varchar(n) 的 n），要么改用能装长文本的类型；日期字段的值必须是真实存在的日期（月份只能是 1~12）；货币金额不要用浮点类型，要用精确类型 decimal(总长度,小数位)；decimal 小数位超出时会四舍五入 三级 · 骨架：nickname ____(20)、birth ____、tuition ____(10,2)

  参考答案（做完再点开）

  • 现象 1（1406 Data too long）：varchar(2) 只允许 2 个字符，3 个字的昵称超长。改法：把上限开大，例如 nickname varchar(20)。“数据太长”（Data too long）就是字符串类型的越界报错。
  • 现象 2（1292 Incorrect date value）：'2020-13-01' 里的月份 13 不存在，不是一个合法日期。改法：把值写成真实存在的日期（如 '2020-12-01'）；顺带说一句，实测 '2020/01/01' 这种斜杠格式反而能插入成功——格式宽松，但值必须合法。
  • 现象 3（float 丢精度）：float 是近似的单精度浮点数，位数一多就不准，实测存 1234567.89 查出来是 1234570。改法：金额字段改成 decimal(10,2)——实测同一个数用 decimal(10,2) 存，查出来就是 1234567.89，分毫不差。
  • 现象 4（decimal 四舍五入）：这其实不是 bug。decimal(10,2) 只保留 2 位小数，多出来的第三位自动四舍五入，所以 1.999 变成 2.00，而且不报错。要接受”以为存了 1.999，其实存的是 2.00”这件事——需要更高精度就把小数位开大（如 decimal(10,3)）。

  一句话总结这题：越界和非法值会报错（1264 / 1406 / 1292），精度问题不报错但会悄悄改数据（float 丢精度、decimal 四舍五入）——后者更危险，所以类型要在一开始就选对。

三、综合题#

• 3-1 给”学员表”定类型并落地成建表语句 Tlias 系统里要新增一张学员表（这里只做”定类型”这件事，约束下一篇再练）。需求描述如下：

  1. 每个学员有一个编号，做唯一标识，从 1 开始自动往后发，不会是负数；
  2. 学员姓名最长 10 个字；
  3. 性别只有”男 / 女”两种（用 1、2 表示）；
  4. 手机号固定 11 位；
  5. 身份证号固定 18 位；
  6. 入学日期只要”哪一天”；结课时间要精确到秒；
  7. 学费要精确到分；
  8. 照片存的是文件路径（最长 255 个字符）；
  9. 备注可能是一段很长的文字。

  要求：① 先列出”字段名 → 类型 → 为什么选它”的清单（9 个字段都要）；② 把清单落成一条完整的 create table 语句（字段都加 comment）；③ 建完表之后，故意做两个实验：往手机号字段里塞 12 位数字、往学费字段里塞一个带 3 位小数的数，把两次的结果（成功/报错原文）记在练习文件里。

  涉及知识点

  知识点	在这里的应用
  数值类型与 unsigned	学员编号用 int unsigned；性别用 tinyint unsigned（只有两种取值，够用就好）
  decimal	学费精确到分，用 decimal(10,2)，不能用 float/double
  char / varchar	手机号、身份证号长度固定 → char；姓名、图片路径长度不固定 → varchar
  text	备注是很长的文字 → text
  日期时间类型	入学日期只要日期 → date；结课时间要日期时间 → datetime
  选取原则	全程遵循”满足需求的前提下选占用空间小的类型”

  提示（先自己想，实在想不出再点开）

  一级 · 思路：先把 9 个需求各自翻译成”字段名 + 一句话的存储要求”，再按三大类（数值 / 字符串 / 日期时间）归类；归类之后只在同类里挑最小的那一个，注意四个”特殊要求”——编号不为负、性别取值极少、金额要精确、固定长度的字符串 二级 · 方法：整数 int unsigned / tinyint unsigned；精确小数 decimal(10,2)；定长字符串 char(n)、变长 varchar(n)、长文本 text；只存日期 date、存到秒 datetime；建表时字段后面可以跟 comment '注释' 三级 · 骨架：create table student( id ____ unsigned primary key auto_increment comment 'ID', name ____(10) comment '姓名', gender ____ unsigned comment '性别', phone ____(11) comment '手机号', idcard ____(18) comment '身份证号', entry_date ____ comment '入学日期', graduate_time ____ comment '结课时间', tuition ____(10,2) comment '学费', image ____(255) comment '照片', remark ____ comment '备注' ) comment '学员表';

  参考答案（做完再点开）

  ① 字段清单：

  字段	类型	为什么
  id	int unsigned	编号是整数、不为负，4 个字节够用
  name	varchar(10)	姓名 1~10 个字，长度不固定
  gender	tinyint unsigned	只有两种取值，1 个字节足够（1 男 / 2 女）
  phone	char(11)	手机号长度固定 11 位
  idcard	char(18)	身份证号长度固定 18 位
  entry_date	date	只要”哪一天”
  graduate_time	datetime	要精确到秒
  tuition	decimal(10,2)	金额必须精确，2 位小数精确到分
  image	varchar(255)	路径长度不固定，上限 255
  remark	text	长文本

  ② 建表语句：

  1
  create table student(
  2
      id int unsigned primary key auto_increment comment 'ID, 主键',
  3
      name varchar(10) not null comment '姓名',
  4
      gender tinyint unsigned not null comment '性别, 1 男; 2 女',
  5
      phone char(11) not null comment '手机号',
  6
      idcard char(18) comment '身份证号',
  7
      entry_date date comment '入学日期',
  8
      graduate_time datetime comment '结课时间',
  9
      tuition decimal(10,2) comment '学费',
  10
      image varchar(255) comment '照片路径',
  11
      remark text comment '备注'
  12
  ) comment '学员表';

  ③ 两个实验的结果（本机实测，MySQL 9.0.1）：

  • 往 char(11) 的手机号里塞 12 位数字 → 报错，关键词是 Data too long（ERROR 1406 (22001)）——字符串类型装不下就报这个；
  • 往 decimal(10,2) 的学费里塞带 3 位小数的数（如 1.999）→ 不报错，值被四舍五入成 2.00。这说明 decimal 会”悄悄改数”，写业务代码时要么保证小数位不超、要么接受舍入。

  顺带对照一下：如果这里把学费写成 float，就不会有四舍五入，而是更麻烦的精度丢失——本机实测 float 存 1234567.89 查出来是 1234570。这就是”金额用 decimal”的实证。