📑 目录

• 1. 📚 数据类型概述
  • 1.1 数据类型分类
• 2. 🔢 数值类型
  • 2.1 整数类型
    • 2.1.1 TINYINT 类型详解
    • 2.1.2 无符号类型（UNSIGNED）
  • 2.2 BIT 类型
  • 2.3 小数类型
    • 2.3.1 FLOAT 类型
    • 2.3.2 DECIMAL 类型
• 3. 📝 字符串类型
  • 3.1 CHAR 类型
  • 3.2 VARCHAR 类型
  • 3.3 CHAR 和 VARCHAR 比较
• 4. 📅 日期和时间类型
• 5. 🎯 ENUM 和 SET 类型
  • 5.1 ENUM（枚举）类型
  • 5.2 SET（集合）类型
  • 5.3 综合案例：调查表
  • 5.4 集合查询：FIND_IN_SET 函数
• 6. 📌 小总结
• 7. 🎯 经典面试题

---

1. 📚 数据类型概述

在 MySQL 中，数据类型是一种非常重要的约束机制。它不仅能保证数据库中存储的数据是可预期和完整的，还能倒逼程序员进行正确的数据插入。如果你不是一个很好的使用者，MySQL 也能通过数据类型保证数据插入的合法性。

💡 核心思想：数据类型本身也是一种约束。如果我们已经由数据被成功插入到 MySQL 中，那么插入的时候一定是合法的。

1.1 数据类型分类

MySQL 的数据类型主要分为以下几大类：

• 数值类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DECIMAL、BIT 等
• 字符串类型：CHAR、VARCHAR、TEXT、BLOB 等
• 日期时间类型：DATE、DATETIME、TIMESTAMP 等
• 枚举与集合类型：ENUM、SET
  

---

2. 🔢 数值类型

2.1 整数类型

MySQL 提供了多种整数类型，它们的存储空间和取值范围各不相同：

类型	存储空间	有符号范围	无符号范围
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

2.1.1 TINYINT 类型详解

数值越界测试：

-- 创建表
mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.02 sec)

-- 插入合法值
mysql> insert into tt1 values(1);
Query OK, 1 row affected (0.00 sec)

-- 越界插入，报错
mysql> insert into tt1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

-- 查看表中的内容
mysql> select * from tt1;
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

⚠️ 注意：如果我们向 MySQL 特定的类型中插入不合法的数据，MySQL 一般会拦截我们，不让我们做对应的操作。

2.1.2 无符号类型（UNSIGNED）

在 MySQL 中，整型可以指定是有符号的和无符号的，默认是有符号的。可以通过 UNSIGNED 来说明某个字段是无符号的。

无符号案例：

mysql> create table tt2(num tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

-- 插入负数，越界报错
mysql> insert into tt2 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

-- 插入最大值
mysql> insert into tt2 values(255);
Query OK, 1 row affected (0.02 sec)

mysql> select * from tt2;
+------+
| num  |
+------+
|  255 |
+------+
1 row in set (0.00 sec)

其他类型推导：

• num int unsigned：无符号 INT，范围 0 ~ 4294967295
• num smallint unsigned：无符号 SMALLINT，范围 0 ~ 65535

💡 建议：尽量不使用 unsigned。对于 int 类型可能存放不下的数据，int unsigned 同样可能存放不下。与其如此，还不如设计时，将 int 类型提升为 bigint 类型。

2.2 BIT 类型

基本语法：

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

举例：

mysql> create table tt4 ( id int, a bit(8));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt4 values(10, 10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tt4; -- 发现很怪异的现象，a的数据10没有出现
+------+------+
| id   | a    |
+------+------+
|   10 |      |
+------+------+
1 row in set (0.00 sec)

mysql> insert into tt4 values(65, 65);
mysql> select * from tt4;
+------+------+
| id   | a    |
+------+------+
|   10 |      |
|   65 | A    |
+------+------+

⚠️ 注意：bit 字段在显示时，是按照 ASCII 码对应的值显示的。所以 10 对应的是换行符（不可见），65 对应的是字符 ‘A’。

BIT 类型使用注意事项：

-- 如果只存放 0 或 1，可以定义 bit(1)，节省空间
mysql> create table tt5(gender bit(1));
mysql> insert into tt5 values(0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt5 values(1);
Query OK, 1 row affected (0.00 sec)

-- 当插入2时，已经越界了
mysql> insert into tt5 values(2);
ERROR 1406 (22001): Data too long for column 'gender' at row 1

💡 实用技巧：desc 表名 可以查看表中变量的类型；alter table t3 modify online bit(10) 可以修改字段类型。

2.3 小数类型

2.3.1 FLOAT 类型

语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

案例：

mysql> create table tt6(id int, salary float(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt6 values(101, -99.991); -- 多的这一点被拿掉了
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt6;
+------+--------+
| id   | salary |
+------+--------+
|  100 | -99.99 |
|  101 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

📐 范围计算：float(4,2) 表示的范围是 -99.99 ~ 99.99，MySQL 在保存值时会进行四舍五入。如果 float(6,3)，范围是 -999.999 ~ 999.999。

四舍五入规则：若插入 (101, 99.985)，则插入的时候是 99.99，但四舍五入后不能越界，否则报错。

无符号 FLOAT 案例：

mysql> create table tt7(id int, salary float(4,2) unsigned);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt7 values(100, -0.1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'salary' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into tt7 values(100, -0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt7 values(100, 99.99);
Query OK, 1 row affected (0.00 sec)

如果定义的是 float(4,2) unsigned，这时因为把它指定为无符号的数，范围是 0 ~ 99.99。

2.3.2 DECIMAL 类型

语法：

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

decimal(5,2) 表示的范围是 -999.99 ~ 999.99；decimal(5,2) unsigned 表示的范围是 0 ~ 999.99。

FLOAT 和 DECIMAL 的区别：

mysql> create table tt8 ( id int, salary float(10,8), salary2 decimal(10,8));
mysql> insert into tt8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt8;
+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
|  100 | 23.12345695 | 23.12345612 | -- 发现decimal的精度更准确
+------+-------------+-------------+

💡 总结：

• float 表示的精度大约是 7 位。
• decimal 是十进制的定点数，精度更高。
• 如果希望小数的精度高，推荐使用 decimal。
• decimal 整数最大位数 m 为 65，支持小数最大位数 d 是 30。如果 d 被省略，默认为 0；如果 m 被省略，默认是 10。

---

3. 📝 字符串类型

3.1 CHAR 类型

语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

案例：

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt9 values(101, '中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt9;
+------+--------+
| id   | name   |
+------+--------+
|  100 | ab     |
|  101 | 中国   |
+------+--------+

⚠️ 注意：char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过 2 个，最多只能是 255。

mysql> create table tt10(id int ,name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

3.2 VARCHAR 类型

语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

案例：

mysql> create table tt10(id int ,name varchar(6)); -- 表示这里可以存放6个字符
mysql> insert into tt10 values(100, 'hello');
mysql> insert into tt10 values(100, '我爱你，中国');
mysql> select * from tt10;
+------+--------------------+
| id   | name               |
+------+--------------------+
|  100 | hello              |
|  100 | 我爱你，中国        |
+------+--------------------+

关于 VARCHAR 长度的说明：

varchar(len) 中的 len 值，和表的编码密切相关：

• 当表的编码是 utf8 时，一个字符占用 3 个字节，varchar(n) 的参数 n 最大值是 65532/3=21844。
• 当表的编码是 gbk 时，一个字符占用 2 个字节，varchar(n) 的参数 n 最大值是 65532/2=32766。

📌 注意：varchar 最大长度到 65535 之间的值，但是有 1-3 个字节用于记录数据大小，所以说有效字节数是 65532。

-- 验证utf8确实是不能超过21844
mysql> create table tt11(name varchar(21845))charset=utf8;
ERROR 1118 (42000): Row size too large...

mysql> create table tt11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

3.3 CHAR 和 VARCHAR 比较

💡 选择建议：

• 如果数据确定长度都一样，就使用定长（char），比如：身份证、手机号、md5。
• 如果数据长度有变化，就使用变长（varchar），比如：名字、地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

---

4. 📅 日期和时间类型

常用的日期类型有如下三个：

类型	格式	占用字节	说明
DATE	‘yyyy-mm-dd’	3字节	日期
DATETIME	‘yyyy-mm-dd HH:ii:ss’	8字节	时间日期格式，范围 1000 ~ 9999
TIMESTAMP	‘yyyy-mm-dd HH:ii:ss’	4字节	时间戳，从 1970 年开始

案例：

-- 创建表
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 sec)

-- 插入数据
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2017-11-12 18:28:55 | -- 添加数据时，时间戳自动补上当前时间
+------------+---------------------+---------------------+

-- 更新数据
mysql> update birthday set t1='2000-1-1';
Query OK, 1 row affected (0.00 sec)

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2017-11-12 18:32:09 | -- 更新数据，时间戳会更新成当前时间
+------------+---------------------+---------------------+

💡 重点：timestamp 时间戳在插入时不用管，会自动更新为当前时间。当更新数据时，时间戳也会自动更新成当前时间。

---

5. 🎯 ENUM 和 SET 类型

5.1 ENUM（枚举）类型

语法：

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

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

5.2 SET（集合）类型

语法：

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

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

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

5.3 综合案例：调查表

创建一个调查表 votes，需要调查人的喜好，比如（登山，游泳，篮球，武术）中去选择（可以多选），（男，女）单选。

mysql> create table votes(
    -> username varchar(30),
    -> hobby set('登山','游泳','篮球','武术'), -- 使用比特位位置来和set中的爱好对应起来
    -> gender enum('男','女')); -- 使用数字标识的时候，就是正常的数组下标
Query OK, 0 rows affected (0.02 sec)

插入数据

insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2); -- 2 对应 '女'
mysql> select * from votes where gender=2;
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| Juse     | 登山,武术      | 女     |
+----------+---------------+--------+

有如下数据：

+-----------+---------------+--------+
| username  | hobby         | gender |
+-----------+---------------+--------+
| 雷锋      | 登山,武术      | 男     |
| Juse      | 登山,武术      | 女     |
| LiLei     | 登山           | 男     |
| LiLei     | 篮球           | 男     |
| HanMeiMei | 游泳           | 女     |
+-----------+---------------+--------+

5.4 集合查询：FIND_IN_SET 函数

问题：想查找所有喜欢登山的人，使用如下查询语句：

mysql> select * from votes where hobby='登山';
+----------+--------+--------+
| username | hobby  | gender |
+----------+--------+--------+
| LiLei    | 登山   | 男     |
+----------+--------+--------+

⚠️ 注意：不能查询出所有爱好为登山的人，因为 hobby='登山' 只能匹配到只喜欢登山的人，无法匹配到"登山,武术"这种组合。

正确做法：使用 find_in_set 函数。

find_in_set(sub, str_list)：如果sub在str_list中，则返回下标；如果不在，返回0；
str_list 用逗号分隔的字符串。

函数测试：

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

mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
|                         0 |
+---------------------------+

查询所有喜欢登山的人：

mysql> select * from votes where find_in_set('登山', hobby);
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| 雷锋     | 登山,武术     | 男     |
| Juse     | 登山,武术     | 女     |
| LiLei    | 登山          | 男     |
+----------+---------------+--------+

💡 理解：集合中的每一项是位图（bitmap），0001 中的 0 和 1 代表有或者没有。find_in_set 函数就是用来在位图中查找指定元素是否存在。

---

6. 📌 小总结

MySQL 的数据类型不仅是存储数据的容器，更是一种强大的约束机制。通过合理选择数据类型，我们可以：

1. 保证数据完整性：越界数据会被自动拦截
2. 优化存储空间：根据实际需求选择合适的数据类型
3. 提高查询效率：定长类型比变长类型效率更高
4. 规范数据格式：日期、枚举等类型确保数据格式统一

核心要点回顾：

• 整数类型：注意有符号/无符号的选择，尽量使用 bigint 替代 int unsigned
• 小数类型：需要高精度时使用 decimal，而非 float
• 字符串类型：定长用 char，变长用 varchar
• 时间戳 timestamp：自动更新，适合记录最后修改时间
• 枚举和集合：enum 单选，set 多选，查询集合用 find_in_set

---

7. 🎯 经典面试题

面试题 1：CHAR 和 VARCHAR 的区别是什么？如何选择？

解答：

• CHAR 是定长字符串，VARCHAR 是变长字符串。
• CHAR 最多 255 个字符，VARCHAR 最多 65535 个字节（受编码影响）。
• CHAR 直接开辟好对应空间，效率高但浪费空间；VARCHAR 用多少开辟多少，节省空间但效率低。
• 选择建议：数据长度固定（如身份证号、手机号）用 CHAR；数据长度变化大（如用户名、地址）用 VARCHAR。

面试题 2：FLOAT 和 DECIMAL 有什么区别？什么时候用 DECIMAL？

解答：

• FLOAT 是浮点数，精度约 7 位，存在精度丢失问题。
• DECIMAL 是定点数，精度更高，适合存储对精度要求高的数据。
• 使用场景：涉及金额、价格等需要精确计算的场景，必须使用 DECIMAL。

面试题 3：TIMESTAMP 和 DATETIME 有什么区别？

解答：

• DATETIME 占用 8 字节，范围 1000 ~ 9999 年，不会自动更新。
• TIMESTAMP 占用 4 字节，范围 1970 ~ 2038 年，插入和更新时会自动记录当前时间。
• 使用场景：需要记录数据最后修改时间时，使用 TIMESTAMP 非常方便。

面试题 4：如何查询 SET 类型中包含某个选项的数据？

解答：
不能直接使用 WHERE hobby='登山'，这样只能匹配到只包含"登山"的数据。应该使用 find_in_set 函数：

SELECT * FROM votes WHERE find_in_set('登山', hobby);

find_in_set 会返回子串在字符串列表中的位置（从 1 开始），如果不存在则返回 0。

面试题 5：MySQL 中 UNSIGNED 有什么注意事项？

解答：
虽然 UNSIGNED 可以让整数类型的取值范围翻倍，但官方建议尽量不使用 unsigned。因为当 int unsigned 也存不下数据时，不如直接升级为 bigint。此外，unsigned 在计算时可能会产生意外的类型转换问题。