【MySQL】数据类型

👦个人主页：@Weraphael
✍🏻作者简介：目前学习计网、mysql和算法
✈️专栏：MySQL学习
🐋 希望大家多多支持，咱一起进步！😁
如果文章对你有帮助的话
欢迎 评论💬 点赞👍🏻 收藏 📂 加关注✨

---

一、 数据类型一览表

二、整型家族

整型家族的用法大差不差，这里以tinyint类型为例

字段名 tinyint [unsigned]
# unsigned可加可不加，看需求
# 不加默认是有符号的

由于带符号的tinyint的数值范围是[-128,127]，接下来我向表中插入一些数据分别是边界值：-128、127、一些中间值：0、66

以上数值显然是可以插入成功的，要是我插入除[-128,127]以外的值会怎么样呢？

由上可得，如果插入的数值不在特定数据的范围内，mysql的做法是直接拦截！不允许插入！

而如果是在C/C++中，int类型转char类型会发生截断（隐式类型转化），反过来，如果有数据被成功插入到数据表中，那么一定说明数据是合法的、可预期的。

因此，在mysql中，数据类型本身也是一种 【约束】，这种约束会倒逼程序员（使用者）尽可能进行正确的插入。所以，约束其实是在约束使用者。另外，如果你不是一个合法的使用者，那么mysql也能保证数据插入的合法性。

三、位字段类型bit

【语法】

create table if not exists [表名](
	[字段名] bit(x)
	...
);
# x表示二进制的位数，x的范围[1,64]
# 如果x被忽略，默认为1

【使用场景】

如果我们有这样的值，只存放0或1，这时可以定义bit(1)（定义bool也是没问题的）。这样可以节省空间。就比如判断用户在不在线，在线是1，不在线0。

再分别向其中插入一些数据，查询时却没看到数据

# 插入
insert into t2 (id, online) values (1, 0);
insert into t2 (id, online) values (2, 1);
# 查询
select * from t2;

注意：bit字段在显示时，是按照ASCII码对应的值显示。如下，这是可以验证的

对于二进制数据类型，查询时可以使用hex()这个内置函数将查询结果转为十六进制显示

select id, hex(online) from t2;

四、浮点数类型

4.1 float

【语法】

float(m,d) [unsigned]
# m - 保留长度
# d - 小数精度位数
# 当然也可以省略(m,d)
# 省略是不指明显示位数和小数精度

【使用案例】

假定当前浮点数支持只保留4位，且小数精度为2位，数据范围为[-99.99, 99.99]

插入一些正常数据，如99.99，-99.99，66.66（在[-99.99, 99.99]之间的数）

插入一些在[-99.99,99.99]以外范围的数据，直接被拦截了。这也印证了 mysql的数据类型本身就是一种约束！

不仅如此，不管是float还是double，在mysql中都遵守四舍五入规则

除此之外，当插入的小数部分过长时，会导致精度丢失（float能保证的精度最多是 7 位）。如果想要追求超高精度，可以使用decimal类型

4.2 decimal

decimal的用法和float/double基本类似，主要的区别是：不会出现精度丢失问题

4.3 如何选择浮点类型的使用

首先 float类型占用的存储空间一定会比decimal小，这是因为decimal需要足够的空间来保持精确的小数位数。因此，如果对精度要求不是很高或者精度在7位，可以选择float；如果对精度的要求高，选择decimal，如银行等

五、字符串类型

5.1 char

char在MySQL中叫做 固定长度字符串

【语法】

字段名称 char(x)
# x是可存储的字符个数
# x最大可以为255
# 不加x的话默认是1

【使用案例】

这个字符串类型也是有约束的，当实际插入的字符串长度超过char的固定长度时，插入操作会被拦截。另外，如果建表时，设定的长度是大于255，建表必定失败

5.2 varchar

【语法】

字段名 varchar(len)
# len表示字符个数

不同于char，系统没有为varchar提供默认长度，也就是说，在建表时，需要指定字符个数

varchar至多支持插入总大小为65535字节的字符串，而在utf8中，一个字符占3个字节；因此 varchar最多能存的字符总数为65535 / 3 = 21845

【使用案例】

在以上案例中，我将name字段类型varchar设置的字符总数为32，所占空间32 * 3 = 96字节，可是我插入数据最多只插入2 * 3 = 6字节。

• 这又和char有什么区别呢？

varchar和char的最大区别是：varchar是变长字符串。它会根据实际数据长度动态分配存储空间，节省了存储空间，并且不会浪费空间。

• 那varchar是如何做到动态分配存储空间？

当定义一个varchar类型的字段时，该字段的实际存储空间为实际值长度加1~3个字节的长度前缀。因此，varchar只占用实际数据长度加上一定的额外存储空间。

也就是说varchar的长度其实是不能取到21845，这是可以验证的

• 为什么要用1~3个字节来存储长度信息?

目的是为了在读取数据时能够准确地知道每个varchar列中实际存储的字符串长度，从而正确地截取和处理数据。使得MySQL可以有效地管理和检索可变长度的字符串数据，而不会浪费过多的存储空间。

5.3 如何选择char和varchar

• 使用 char 的场景：

  • 当存储的数据确定长度都一样，可以考虑使用 char。比如邮政编码、银行卡号、身份证性别等信息。虽然相比于varchar会浪费空间，但是固定长度的字段在索引效率上可能更高。

• 使用 varchar 的场景：

  • 当存储的数据长度可变且长度较大时，通常选择 varchar。比如家庭住址、评论等。在不超过自定义范围的情况下，用多少开辟多少空间

六、日期类型

6.1 date

【语法】

字段 date

需要注意插入格式：‘yyyy-mm-dd’，如果不满足位数需要向高位补0。占4个字节

【使用案例】

6.2 datetime

如果想插入更为详细的日期信息，可以改用datetime，插入格式：'yyyy-mm-dd HH:ii:ss' ，占用八字节。

【语法】

字段 datetime

【使用案例】

6.3 timestamp

• MySQL中提供了时间戳类型timestamp，从1970-1-1 0:0:0开始计时的秒数，占八个字节。

• 时间戳最大的特点是：不需要手动插入时间，它是随着对表的更新操作来更新时间戳。并且显示的格式与datetime一样，

【使用案例】

假设有一位学生叫张飞，他要在某个博主的评论区说：“拼搏百天，我要上清华大学”

后来由于复习的不咋地，为了避免尴尬，又去修改自己的评论，那么对应的评论时间也要被修改成修改后的时间，这是就可以用到时间戳

使用update指令更新字段信息，可以触发 时间戳 的更新

update [表名] set column1=value1, ... where condition=xxx;
# column1：是要更新的列名。
# value1：是要设置的新值。
# condition：是用于指定要更新哪些行的条件。

七、枚举类型

7.1 enum

enum称为枚举类型，用于提供一批元素，可以选择其中一个（多选一）

【语法】

字段 enum(选项1,选项2...)

【使用案例】

除此之外，如果插入的数据不在枚举列表中，mysql就会拦截，则插入失败。

---

除了直接选择枚举中的元素值外，还可以通过下标的方式进行选择。注：下标从1开始

7.2 set

enum称为集合类型，也是用于提供一批元素，可以选择其中一个或者多个

【语法】

字段 set(选项1,选项2...)

插入元素和enum一样，可以用字面值。但在选多个元素时，以,分隔，并且中间不能有空格，还有不能插入除集合以外的元素

当然也可以使用下标来插入元素，但是这里的下标是有讲究的（类似于位图）。

比如说，这里的集合有6个元素，那么就对应6个比特位，一开始比特位上全为零：000000，如果选了，就从低位（右往左）开始标记成1，最后将其转化为十进制就是对应的下标

• 如果选了写代码，也就是000001，对应的下标也就是1（转十进制）
• 如果选了csgo和篮球，也就是101000，对应的下标也就是40

接下来我们可以测试一下：

7.3 查询

有关enum类型的查询

假设有关于enum类型的表及其数据如下：

现在假设我只想看男生的信息

select * from [表名] where 字段=value

---

有关set类型的查询

假设有关于set类型的表及其数据如下：

如果集合类型使用以上的sql语句，会导致筛出来的信息可能不完整。这里可以带大家来试验一下

比如我想筛选出喜欢写代码的

此条语句对于集合不能查询出所有爱好为xxx的人

这里推荐集合查询使用find_ in_ set函数

【语法】

find_in_set(value,集合) 
# 集合 - 用逗号分隔的字符串

注意：如果value在集合中，则返回对应的下标；如果不在，返回0