MySQL基础之数据类型- 惊觉

一、数值类型：整型家族

1.1 五种整型

类型	字节数	有符号范围	无符号范围（UNSIGNED）
tinyint	1	-128 ~ 127	0 ~ 255
smallint	2	-32768 ~ 32767	0 ~ 65535
mediumint	3	-8388608 ~ 8388607	0 ~ 16777215
int	4	-2147483648 ~ 2147483647	0 ~ 4294967295
bigint	8	-2^63 ~ 2^63-1	0 ~ 2^64-1

tinyint  【UNSIGNED】
smallint 【UNSIGNED】
mediumint【UNSIGNED】
int      【UNSIGNED】
bigint   【UNSIGNED】

相比 C/C++ 的整型家族，MySQL 的数值类型明显分得更细，主要目的是提高内存利用率 —— 这也要求操作者选择合适的数据类型：能用 tinyint 就不用 int，能用 int 就不用 bigint。

1.2 越界行为：MySQL 拒绝而不是截断

在 C/C++ 中，如果我们为 int 类型赋予一个超过本身大小的数据，会发生截断；但在 MySQL 中不会让你插入！从根本上避免了因为范围问题造成的数据异常问题。

补充：这是 MySQL 严格模式（sql_mode 含 STRICT_TRANS_TABLES，8.0 默认开启）的行为 —— 越界直接报错（ERROR 1264 Out of range）。如果关闭严格模式，MySQL 会截断并给出警告。所以生产环境务必保持严格模式。

1.3 UNSIGNED 的含义

如果带上了 unsigned，则表明无符号，那一个字节的 8 个比特位全部用来存储数据本身（不再分出最高位表示正负），范围整体向正方向平移，例如 tinyint unsigned 是 0 ~ 255。

1.4 位字段类型 bit (M)

bit[(M)]：位字段类型。M 表示每个值的位数，范围从 1 到 64；如果 M 被忽略，默认为 1。

表示占多少个 bit 位。

mysql> show create table test_bit\G
*************************** 1. row ***************************
       Table: test_bit
Create Table: CREATE TABLE `test_bit` (
  `C` bit(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

现在创建一个 test_bit，可以看到 C 的类型是 bit (1)，也就是只占了一个 bit 位。

mysql> insert into test_bit values ('a');
ERROR 1406 (22001): Data too long for column 'C' at row 1

我们明确：在 MySQL 中字符的值也是对应的 ASCII 码 ——'a' 的 ASCII 是 97，二进制需要 7 个 bit，超过 bit (1) 的 1 位，所以越界无法插入。

mysql> select * from test_bit;
+------------+
| C          |
+------------+
| 0x00       |
| 0x01       |
+------------+

如果想要以十进制显示，用 hex 函数：

select hex(C) from test_bit;

bit (M) 的范围是 0 ~ 2^M - 1：bit (1) 是 0~1，bit (8) 是 0~255，bit (16) 是 0x0000 ~ 0xFFFF（0~65535）。

二、浮点类型：float

2.1 float (m,d) 语法与含义

float[(m,d)] [unsigned]
m 表示显示的长度（总位数），d 表示小数点后的精度（小数位数）

例如 float(4,2) 表示总共显示 4 位，其中保留 2 位小数，整数部分占 2 位，范围是 -99.99 ~ 99.99。

2.2 【答疑】占 4 个字节，是每一位占一个字节吗？

不是。float 占 4 字节与 m,d 完全无关——m,d 只是对 "能存多少位数字" 的限制，底层存储走的是 IEEE 754 单精度浮点数标准：

float 的 4 字节（32 位）划分：
┌─────┬───────────┬─────────────────────────────┐
│ 1位 │   8位     │          23位               │
│ 符号 │  指数     │          尾数（有效数字）     │
└─────┴───────────┴─────────────────────────────┘

1 位符号位：正负；
8 位指数位：表示数量级（科学计数法里的 "10 的几次方"）；
23 位尾数位：表示有效数字。

所以它不是 "每一位占一个字节"（那样 4 字节只能存 4 个数字，显然不合理）。浮点数是把数字拆成 "符号 × 尾数 × 2^ 指数" 来紧凑表示的，32 位就能覆盖非常大的范围（约 ±3.4×10^38），有效十进制精度约为 7 位。m,d 只是显示 / 约束层面的限制，8.0.17 之后 MySQL 已弃用 FLOAT (M,D) 的显示宽度语法，建议直接写 float（用 decimal 来约束精度）。

2.3 精度与范围行为

如果插入的数大于范围，无法插入（严格模式下报 Out of range）；
如果小数精度更高（超过 d 位），会进行四舍五入。

例如 float (4,2) 最大是 99.99：插入 99.999999，四舍五入后是 100.00，超过范围 → 插入失败；而 99.989999 四舍五入后是 99.99 → 插入成功。

mysql> show create table test_float\G
*************************** 1. row ***************************
       Table: test_float
Create Table: CREATE TABLE `test_float` (
  `score` float(4,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

2.4 unsigned 对浮点的作用

从上面可以看出，unsigned 不会增加最大值，只是把范围变为 【0，99.99】（砍掉负数部分）。浮点类型加 unsigned 在 8.0.17 之后同样已弃用。

三、定点数：decimal

3.1 语法与范围

decimal(m,d) 【unsigned】
m：总位数（精度），d：小数点后的位数

decimal(4,2) 的范围是 -99.99 ~ 99.99，看起来和 float (4,2) 没什么区别。

3.2 为什么 decimal 更精确？

关键在于存储方式完全不同：

float 是二进制浮点数：按 "符号 + 指数 + 尾数" 的二进制科学计数法存储。像 0.6、0.7、0.8、0.9 这样的十进制小数，转成二进制后是无限循环小数（就像 1/3 在十进制里除不尽），只能截断存储，所以一定有误差；
decimal 是十进制定点数：MySQL 内部不经过二进制小数转换，而是把每一位十进制数字按十进制整数的方式存储（每 9 位十进制数字用 4 字节打包），相当于 "以字符串 / 整数形式保存的精确十进制数"。

对比一下实际效果：

+------------------------+------------------------+
| score (float)          | deci (decimal)         |
+------------------------+------------------------+
|     99.099998474121100 |     99.100000000000000 |
| 938281.187500000000000 | 938281.185901519087532 |
+------------------------+------------------------+

float 列里 99.1 存成了 99.099998474121100（二进制近似），decimal 列里则是精确的 99.100000000000000。float 追求 "快"（硬件直接支持浮点运算），decimal 追求 "准"（金额、账目等必须精确的场景）。

3.3 精度上限

float：大小固定 4 字节，最大有效精度约 7 位十进制数字（double 8 字节，约 15~16 位）；
decimal：总位数 M 最大 65 位，小数位数 D 最大 30 位（且 D ≤ M）。

四、字符串类型

4.1 char (L)：L 是字符数，不是字节数！

char(L)：L 表示的是字符的数目！！！

我们知道，中国汉字每一个所占的字节数是比较大的，在 Unicode 中，一个汉字占几个字节取决于编码方式：

UTF-8 下：常见汉字（BMP 平面）占 3 字节，生僻汉字（增补平面）可能占 4 字节；
UTF-16 下：多数汉字占 2 字节，部分占 4 字节；
UTF-32：固定 4 字节。

L 最大是 255——char 用一个字节（8 位，最大 255）来记录字符的数目。

我们这里使用的字符集是 utf8mb3（即 utf8 的旧名，最多每字符 3 字节）：

mysql> show create table test_char\G
*************************** 1. row ***************************
       Table: test_char
Create Table: CREATE TABLE `test_char` (
  `str` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

mysql> insert into test_char values('你好世界我这个世界');
Query OK, 1 row affected (0.01 sec)

"你好世界我这个世界" 是 9 个汉字，在 utf8mb3 下占 27 字节，明显大于 10 字节，但插入成功了 ——一定要记住：char 中 L 代表字符数目，不是字节数！ 只要字符个数不超过 10，不管每个字符占 1 字节还是 3 字节都能存。

4.2 varchar (L)：变长字符串，整行 65535 字节限制

varchar(L)：可变长度字符串，L 表示最大字符数

和 char 一样，L 表示字符数；但 varchar 有整行 65535 字节的硬限制 —— 准确说，MySQL 中一行所有列加起来的最大字节数是 65535（与字符集相关），varchar 的单列长度也受此约束。

在 utf8mb3（每字符最多 3 字节）下，理论上最多 21845 个字符（21845 × 3 = 65535）。

4.3 【答疑】65535 字节里为什么还要扣掉 "统计长度" 的内存？为什么是 2 字节不是 4 字节？

varchar 存储时，会在数据前面额外记录 "这个字符串实际有多长"，这个长度前缀用 1 字节还是 2 字节，取决于该表可能出现的最大行长度：

行最大长度 ≤ 255 字节  → 用 1 字节长度前缀
行最大长度 > 255 字节  → 用 2 字节长度前缀

为什么 2 字节就够？因为 2 字节 = 16 位，最大能表示 65535

所以精确的推算应该是：

整行字节上限：65535
扣掉长度前缀：2 字节
留给数据的字节：65535 - 2 = 65533 字节
utf8mb3 下每字符最多 3 字节：
65533 ÷ 3 = 21844.3 → 最多 21844 个字符

顺带一提：utf8mb4（每字符最多 4 字节）下 varchar 单列最多约 16383 字符（16383 × 4 = 65532）。

4.4 char vs varchar：不只是长度不同

对比项	char (L) 定长	varchar (L) 变长
存储方式	固定分配 L 个字符的空间（饿汉）	用多少给多少（懒汉）
底层	固定数组	变长数组 + 长度前缀
空间	浪费（不足补空格）	节省
尾部空格	取出时去掉尾部空格	保留
适用场景	定长数据（身份证号、手机号、MD5）	变长数据（姓名、文章、地址）

char 是固定数组，varchar 是变长数组，前者是 "饿汉"，后者是 "懒汉"。所以变长数据往往用后者；但像手机号、身份证号这种定长字段，用 char 反而更合适（省掉长度前缀，且免去变长管理的开销）。

以 utf8mb3（每字符最多 3 字节）为例对比 char (3) 和 varchar (3)：

表格

存储内容	char(3)	varchar(3)
存 'a'	固定 3×3 = 9 字节（不足补空格）	1×3 + 1（长度前缀）= 4 字节
存 'abc'	9 字节	3×3 + 1 = 10 字节

五、日期和时间类型

常用的日期类型有三个：

类型	格式	范围	占用字节	特点
date	'YYYY-MM-DD'	1000-01-01 ~ 9999-12-31	3	只记录年月日
datetime	'YYYY-MM-DD HH:MM:SS'	1000-01-01 ~ 9999-12-31	8	更详细的日期时间，与时区无关
timestamp	'YYYY-MM-DD HH:MM:SS'	1970-01-01 ~ 2038-01-19	4	时间戳，与时区有关（存 UTC，按会话时区显示）

date 类型：用来记录年月日；
datetime 类型：用来更为详细地记录；
timestamp 类型：这个是时间戳，每次进行修改（行更新）的时候会自动更新为当前时间。联系日常，我们常常能够看到某些视频、评论、信息的时间，其实都是可以通过这个实现的。

timestamp 的几个注意点：

2038 年问题：因为只有 4 字节（存储从 1970 年起的秒数），最大到 2038-01-19，超过就会溢出 —— 需要更远的日期请用 datetime；
自动更新：MySQL 5.6.5 之后可以精确控制，建表时写 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 才会在更新时自动刷新，不是所有 timestamp 列都自动变；
取当前时间常用 now()（等价 current_timestamp）。

六、enum 和 set

6.1 enum：枚举，"单选" 类型

enum('选项1','选项2','选项3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是 "数字"，因为这些选项的每个选项值依次对应如下数字：1, 2, 3, ...，最多 65535 个。当我们添加枚举值时，也可以添加对应的数字编号。

6.2 set：集合，"多选" 类型

set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，可以存储其中任意多个值；而且出于效率考虑，这些值实际存储的是 "数字"，因为这些选项的每个选项值依次对应如下数字：1, 2, 4, 8, 16, 32, ...（按位组合），最多 64 个。

说明：不建议在添加枚举值、集合值的时候采用数字的方式，因为不利于阅读。

mysql> show create table test_enum_set\G
*************************** 1. row ***************************
       Table: test_enum_set
Create Table: CREATE TABLE `test_enum_set` (
  `e` enum('100','200','300','400') DEFAULT NULL,
  `s` set('100','200','300','400') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

6.3 【答疑】数字下标到底怎么用？

mysql> insert into test_enum_set values (1, 3);
-- 等价于
mysql> insert into test_enum_set values ('100', '100,200');

mysql> select * from test_enum_set;
+------+---------+
| e    | s       |
+------+---------+
| 100  | 100,200 |
+------+---------+

从这里可以看出下标的使用方法（enum 和 set 都是从 1 开始编号的）：

enum：插入时选择的下标是几，插入的元素就是第几个选项。e = 1 → 第 1 个选项 '100'；
set：数字按二进制位组合，也就是位图，换句话说，每一种选项对应一个bit位。s = 3 的二进制是 011，表示同时选中第 1 位和第 2 位 → '100,200'。

set 的位图示意：

选项：'100'(1)  '200'(2)  '300'(4)  '400'(8)
值 3  = 1 + 2     → '100,200'
值 6  = 2 + 4     → '200,300'
值 7  = 1+2+4     → '100,200,300'

七、筛选与 NULL

7.1 where 语句

我们常用的筛选是 where 语句，例如：

select * from test_enum_set where e='100';

可以理解 where 就是 C/C++ 中的 if 语句：MySQL 中 "与" 用 and，"或" 用 or。

7.2 集合查询使用 find_in_set 函数

find_in_set(sub, str_list)
如果 sub 在 str_list 中，则返回下标（从 1 开始）；如果不在，返回 0。
str_list 是用逗号分隔的字符串。

例如：

mysql> select find_in_set('a','a,b');
+------------------------+
| find_in_set('a','a,b') |
+------------------------+
|                      1 |
+------------------------+

因为存在返回值（>0 表示存在），所以可以用于筛选：

select * from test_enum_set where find_in_set('200', s);

7.3 NULL 的语义（重要修正）：

NULL 表示 "未知 / 没有值"，它不是一个具体的数（不像 C/C++ 中 NULL 就是 0）；
NULL 参与任何算术运算（如 1 + NULL）结果都是 NULL；
NULL 参与任何比较运算（如 e = NULL）结果既不是 TRUE 也不是 FALSE，而是 NULL（被视为假）——所以不能用 = 判断 NULL，必须用 is NULL / is not NULL；
只要 find_in_set 的任一参数为 NULL，结果就直接返回 NULL（不是 0，也不是空串）。想要查找 NULL，用：

where s is NULL;
//反之用
where s is not NULL;

八、总结

整型家族：tinyint /smallint/mediumint /int/bigint，分得细是为了省内存；越界时 MySQL 严格模式直接拒绝插入（不截断）；unsigned 让全部比特位存数据，范围向正方向平移。
bit(M)：占 M 个 bit，范围 0 ~ 2^M - 1，M 取值 1~64，默认 1；显示用 hex () 看十六进制。
float(m,d)：m 是总位数（含小数），d 是小数位数，float (4,2) 范围 -99.99~99.99；底层 4 字节是 IEEE 754 单精度（1 符号 + 8 指数 + 23 尾数），与 m,d 无关，不是 "每一位占一个字节"；小数超精度会四舍五入，超范围拒绝插入。
decimal(m,d)：定点数，按十进制整数方式存储，不经过二进制小数转换所以精确；M 最大 65、D 最大 30；金额等需要精确的场景用 decimal，float 追求速度。
char(L)：L 是字符数不是字节数（汉字 3 字节照样按字符数算），最大 255；varchar(L)：变长，整行合计最多 65535 字节，长度前缀 1 字节或 2 字节（行上限 ≤255 用 1 字节，否则 2 字节），utf8mb3 下单列最多 21844 字符；char 定长去尾部空格，varchar 变长保留空格。
日期时间：date（3 字节）/datetime（8 字节，与时区无关）/timestamp（4 字节，1970~2038，与时区有关，可自动更新）。
enum / set：enum 单选，编号 1,2,3...，最多 65535 项；set 多选，按位 1,2,4,8...，最多 64 项；set 数字是位图组合，3 = '100,200'，不是 "下标 3 不插入"。
NULL：表示 "未知"，不能参与运算、不能用 = 比较，必须用 is NULL；find_in_set 任一参数为 NULL 就返回 NULL

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/bksczm/article/details/164743450

MySQL基础之数据类型