MySQL的常用数据类型详解

MySQL系列

---

---

本篇及将会依据下表介绍MySQL的常用数据类型，文章涉及的sql语句，不做详细解释

---

一、数值类型

1.1 tinyint类型

• tingyint： 只允许插入1byte的数据(-128~127)。

• tinyind unsigned: 范围为0~255。

• 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号

此处使用有符号类型作为演示，声明无符号的可以直接在建表阶段（create table db_name 类型 unsigned）也可对类型修改(上篇文章介绍了)

从上图可以看到，当试图插入超出tinyint类型限制的数据时，MySQL会直接拦截并报错（而非像部分编程语言那样发生数据截断）。这一特性并非tinyint独有，而是MySQL数据类型的共性表现。

这一现象从侧面清晰地说明：凡是成功插入MySQL的数据，必然是符合类型规则的合法数据。而在MySQL中，数据类型本身就是一种基础的约束机制（下篇文章单独介绍）。通过这种机制，MySQL能够倒逼开发者在数据插入阶段就尽可能确保数据的正确性，最终使得存储在数据库中的数据具备明确的可预期性。

约束的核心价值在于：它为数据使用设立了硬性规则。即便使用者在操作中存在疏漏或不规范行为，MySQL也能通过约束机制有效拦截异常数据，从根本上保障存储数据的合法性与准确性。

1.2 bit类型

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

可以看到此处仍受类型限制，我们可以手动调整该类型的大小。

可以看到图中插入类型显示为16进制，这里由于显示受到MySQL版本以及客户端设置等多方面影响，不同主机下显示内容可能不同，所以此处不做过多解释，仅供参考。

• 位图结构最大是64bit： 当超过64后就会报错：
  

1.3 float类型

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

示例：

float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在存储时会存在精度丢失等问题（小数部分无法完全转化为二进制）。

可以看到当数据存储精度，超出指定精度，就会存在精度丢失问题，这里需要注意，该问题是由于二进制转化造成的，不要误以为存在四舍五入。

• 当精度无法满足我们的需求时可以手动调整

从图中可以发现该类型存储高精度数据时，存在很明显的进度丢失问题，所以在进行高精度存储时，我们会选择decimal

MySQL中的浮点数，允许被设置为无符号类型，可以自己尝试。

1.4 decimal类型

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

相较于float，decimal不会对大精度数据做处理，存储精度更高，其余功能基本相同

不同与float，在显示上若数据小数位不足，decimal会默认补0：

• float表示的精度大约是7位。
• decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10

二、字符串类型

2.1 char类型

• char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255（设置超出限制，MySQL拦截错）

mysql中字符的大小不同与其他语言，此处一字符可以存储字母、汉字（不同编码下汉字所占字节不同）等，每个字符为三字节。

从上图可以看到，char类型仍受范围约束。

2.2 varchar变长字符串

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

此处基本功能和char类型类似，不同的是varchar设置的字符数为最大允许插入字符，但是当插入数据小于所设字符时，实际所占长度会以插入字符长度为准，当大于时MySQL拦截并报错，在存储数据长度变动较大的场景下varchar可以有效节省空间。

特点：

上述提到，varchar类型的最大存储长度为65535字节。若字符集为utf8（每个字符占用3字节），理论上的最大字符数可按如下计算：
最大字符数 = 65535字节 ÷ 3字节/字符 = 21845字符。

但需注意：varchar会额外占用1 - 3字节用于存储字段的实际长度信息（具体字节数取决于定义的长度）。若按最大额外占用3字节计算，实际可用于存储字符的字节数为 65535 - 3 = 65532字节，因此最终可用的最大字符数为 65532 ÷ 3 = 21844字符。

这意味着在utf8字符集下，varchar实际可存储的最大字符数为21844，而非理论计算的21845。

定长类型通过预先开辟固定空间实现高效率但可能浪费磁盘空间，而变长类型则在自定义范围内按需动态分配空间以节省存储，却伴随效率上的损耗。

这就像我们使用的容器，有开辟的空间，和空间中的有效数据，但需要注意的时，不同环境下会出现偏差，这里仅做参考。

三、日期和时间类型

• date：仅存储日期，格式为 'yyyy-mm-dd'，占用3字节。
• datetime：存储日期+时间，格式为 'yyyy-mm-dd HH:ii:ss'，表示范围从 1000年到9999年，占用8字节。
• timestamp：本质是时间戳（从1970年开始计时），显示格式与 datetime 完全一致（'yyyy-mm-dd HH:ii:ss'），但占用4字节。

从图中可以看到时间戳我没有插入值，这也是合理的，毕竟不能再每次插入时重新计算时间戳插入表中，其实在MySQL中我们提供了默认设置，帮助用户再更新某行数据时，时间戳字段计算更新，在一些旧版本中时默认打开的，有些版本就需要在创建表时使用 DEFAULT CURRENT_TIMESTAMP说明

CREATE TABLE ttest7 (t1 date,t2 datetime,t3 timestamp DEFAULT CURRENT_TIMESTAMP
);

进行该设置后，再对该行进行操作，时间戳都会自动更新。

四、枚举和集合

• enum：枚举，" 单选 "类型：enum(‘选项1’,‘选项2’,‘选项3’,…)；该设定只是提供了若干个选项的值，插入数据仅能在选项中选择。
• set：集合，“多选”类型：set(‘选项值1’,‘选项值2’,‘选项值3’, …);该设定只是提供了若干个选项的值，最终数据可以选择多个选项值。

 create table ttest9(name varchar(5),gender enum ('男','女'),hobby set ('唱','跳','rap','篮球'));

从图中可以看到，set类型可以同时选择多个，但是仅支持选择提供的选项，enum类型只能在提供的选项中选择，且仅能选择一个，你可以尝试一下。

此外我们也可以使用下标索引的方式插入数据：

enum

可以看到我们使用1索引第一个选项以此类推，0比较特殊，感兴趣的可以自己搜一个，这里不做解释了。

set

仔细观察结果不难发现set类型不同于enum类型，这里的索引并不是以下标，而是采用的位图结构：

1->0001
2->0010
5->0101

该处对应顺序需要反过来

对enum和set类型的查找处理

对enum类型查找时可以，直接查找选项，也可以查找索引。

可以看到这里的查找仅能筛选出严格匹配的数据，对于其他包含查找信息的行，仅是因为顺序不一样，也会导致无法筛选出，使用数字也是一样，可以自己试试。
通过上示例来看，发现对set类型来说，当在一行数据中的set类型有多个值时，若直接查找其中的某一个值，是无法找到的：select是直接看所有元素的，并不会判断其中是否包含。如果想要达到查找所以包含行的目的，就需要配合函数（后面篇幅介绍）来使用：

• find_in_set： 在集合中找是否包含某元素，若查找到返回下标，反之返回0。

现在我们就可以结合find_in_set函数在表中查找：

文中有些地方没有演示，大家可以自己尝试，那么本篇就到这里了，文中哪里有错误可以私信博主，感谢支持。