【MySQL 05】数据类型

♬咖啡猫♬

于 2024-08-12 05:00:00 发布

阅读量1.5k

点赞数 52

分类专栏： MySQL 文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/shangguanxiu/article/details/141095568

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

🌈 一、数据类型的作用

如果向 MySQL 特定的类型中插入不合法的数据，MySQL 一般会将本次操作直接拦截。反过来讲，能被成功插入到 MySQL 中的数据一定合法。
在 MySQL 中，数据类型本身也是一种约束，这种约束约束的是使用者，让程序员尽可能正确的进行插入。

🌈 二、常见数据类型分类

数值类型

数据类型	大小	说明
BIT(M)	1 ~ 64 比特	位类型，用 M 指定位数，默认值位 1，范围 1 ~ 64。
TINYINT [UNSIGNED]	1 字节	范围：有符号(-128 ~ 127)；无符号(0 ~ 255)；默认为有符号
BOOL	1 字节	布尔类型，1 表示真，0 表示假
SMALLINT [UNSIGNED]	2 字节	范围: 有符号(-2¹⁵，2¹⁵-1)；无符号(0，2¹⁶-1)；默认为有符号
INT [UNSIGNED]	4 字节	范围: 有符号(-2³¹，2³¹-1)；无符号(0，2³²-1)；默认为有符号
BIGINT [UNSIGNED]	8 字节	范围: 有符号(-2⁶³，2⁶³-1)；无符号(0，2⁶⁴-1)；默认为有符号
FLOAT [(M,D)] [UNSIGNED]	4 字节	M 指定显示长度，D 指定小数的位数
DOUBLE[(M,D)] [UNSIGNED]	8 字节	表示比 float 精度更大的小数
DECIMAL [(M,D)] [UNSIGNED]	如果 M > D，则为 M + 2，否则为 D + 2	定点数 M 指定长度，D 表示小数的位数

文本、二进制类型

数据类型	大小	说明
CHAR(L)	0 ~ 255 字节	定长字符串，L 指定字符串长度，存储的容量无法超过指定的 L
VARCHAR(L)	0 ~ 65535 字节	变长字符串，L 指定字符串长度，存储的容量可以超过指定的 L
BLOB	0 ~ 65535 字节	存储二进制数据
TEXT	0 ~ 65535 字节	存储大文本数据，不支持全文索引和默认值

日期和时间类型

数据类型	大小	格式	用途
DATE	3 字节	YYY-MM-DD	日期值
DATETIME	8 字节	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4 字节	YYYY-MM-DD hh:mm:ss	时间戳

字符串类型

数据类型	说明
ENUM	枚举类型，在定义字段时指定取值范围，字段值只允许从成员中选取单个值，其所需的存储空间由定义 ENUM 类型时指定的成员个数决定
SET	集合类型，在定义字段时指定取值范围，字段时可从成员中选取一个或多个值，其所需的存储空间由定义 SET 类型时指定的成员个数决定

🌈 三、数值类型

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

⭐ 1. tinyint 类型

int 类型以 tinyint 为例测试类型的存储范围，其他所有 int 类型范围测试方法同理。

1. 有符号 tinyint 范围测试

创建一个包含 tinyint 类型字段名为 t1 的表，默认其为有符号类型。

tinyint 类型大小为 1 字节，有符号 tinyint 的取值范围为 -128 ~ 127，只要插入的数据在该范围都能成功插入。

插入数据时，如果插入的数据不在 -128 ~ 127 这个合法范围内，会直接发生报错。

2. 无符号 tinyint 范围测试

创建一个包含 tinyint 类型字段名为 t2 的表，在 tinyint 后面添加上 UNSIGNED 使其成为无符号类型。

无符号 tinyint 的取值范围为 0 ~ 255，只要插入的数据在该范围都能成功插入。

插入数据时，如果插入的数据不在 0 ~ 255 这个合法范围内，会直接发生报错。

⭐ 2. bit 类型

bit 语法格式

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

1. bit 类型的显示方式

创建一张名为 t3 的表，表中包含一个名为 id 类型为 int 的字段，和一个名为 a 类型为 bit(8) 的字段。

往表中的 id 字段和 a 字段各插入一个 10，数据插入之后会发现 a 字段显示出来的内容并不是 10，而是 10 对应的 16 进制值。

2. 比特类型的使用案例

创建一张名为 t4 的表，表中包含一个名为 name 类型为 varchar(20) 的字段，和一个名为 gender 类型为 bit(1) 的字段。
- 由于 gender 性别在正常情况下只有 2 种，可以使用 1 位 bit 表示用以节省空间。

暂时将 1 定义为男，0 定义为女，在往 gender 字段插入数据时就可以用 1 位 bit 来表示男女。

如果插入 gender 字段的不是 0 / 1，MySQL 会拦截这次访问。

⭐ 3. float 类型

float 语法格式

float[(m, d)] [unsigned] : m 指定数字位数的总个数，d 指定小数点之后数字的个数，占用空间为 4 字节

1. 有符号 float 类型范围测试

创建一张名为 t5 的表，表中包含一个名为 num，类型为 float(4,2) 的字段，默认其为有符号类型。

由于 float(4, 2) 该字段能装 4 个数字，小数点后能装 2 个数字，因此该字段的取值范围为 -99.99 ~ 99.99，只要在这个范围的数字都能成功插入。

MySQL 在保存值时支持进行四舍五入，因此 float(4, 2) 字段的实际可插入范围为 -99.994 ~ 99.994，MySQL 会拦截不在该范围的数据插入操作。

2. 无符号 float 类型范围测试

创建一张名为 t6 的表，表中包含一个名为 num，类型为 float(4,2) 的字段，并在 float(4,2) 之后使用 unsigned 指定其为无符号类型。

无符号 float(4,2) 类型的取值范围并不是将 99.99 * 2 就完了，(4, 2) 已经限制了正数部分最高就只能取到 99.99，只是单纯的不让你存负数了而已。
- 又因为 float 支持四舍五入，因此实际可插入范围为 0 ~ 99.994，不在该范围内的数据插入操作都会被 MySQL 拦截。

⭐ 4. decimal 类型

在小数点后位数很多的时候，float 类型会发生精丢失，而 decimal 是一个精度更高的浮点数类型，可以很好的解决精度丢失的问题。

decimal 语法格式

decimal(m, d) [unsigned] : m 指定数字位数的总个数，d 指定小数点之后数字的个数

float 和 decimal 的精度对比

创建一张名为 t7 的表，表中分别包含一个 float(10,8) 的字段和一个decimal(10,8) 的字段。

分别往两个字段各自插入一段 32.42342321，decimal 能够保持数据的原貌，而 float 发生了精度丢失。

🌈 四、字符串类型

在不同编码中，一个字符所占的字节个数是不同的。
- utf8 中一个字符占3个字节，而 gbk 中一个字符占 2 个字节。
MySQL 限定字符的概念不是字节，而是字符个数，用户无需再关心编码细节。

⭐ 1. char 类型

char 类型使用格式

char(L): 固定长度字符串，L 表示字符个数，并不代表字节数

char 类型在指定 L 之后会一次性开辟出 L 个字符的空间。

char 类型使用案例

创建一张名为 t8 的表，表中包含一个名为 content，数据类型为 char(6) 的字段。

类型为 char(6) 的字段最多可以存储 6 个字符 (英文 / 汉字)，只要插入字符个数不超过 6 个都能插入成功，反之则会被 MySQL 拦截。

注意：char(L) 类型的 L 最多不能超过 255。

⭐ 2. varchar 类型

varchar 语法格式

varchar(L): 可变长度字符串，L 表示字符个数，并不代表字节数

varchar 允许在 L 规定的范围内灵活的开辟空间。
- 假设 L 是 6，varchar 一开始是不开辟空间的，只有在实际插入数据时再开辟空间，但插入的字符数不能超过 6。

1. varchar 类型使用案例

创建一张名为 t9 的表，表中包含一个名为 content，数据类型为 varchar(6) 的字段。

varchar(6) 最多可存储 6 个字符，只要插入的字符个数在 6 个及以下，本次操作就是合法的。

如果要插入的字符个数超过了 L 所指定的 6 个字符，则本次操作会被 MySQL 拦截。

2. varchar 类型可存储的字符个数

varchar 占用空间范围在 0 ~ 65535 字节，但其中有 1 ~ 3 字节用来记录数据大小，因此 varchar 类型的有效字节数最多是 65532 字节。
但这只是 varchar 类型的所占空间范围，L 具体最多能表示多长的字符串还要看表对应的编码格式。
- 对于 utf8 编码格式，一个字符占 3 个字节，因此 L 能够指定的最大字符串长度为 65532 / 3 = 21844。
- 对于 gbk 编码格式，一个字符占 2 个字节，因此 L 能够指定的最大字符串长度为 65532 / 2 = 32766。
建表时，如果定义的编码格式为 utf8，且 varchar(L) 中的 L 超过了 21844，则本次操作会被 MySQL 拦截下来。

建表时，如果定义的编码格式为 gbk，且 varchar(L) 中的 L 超过了 32766，则本次操作会被 MySQL 拦截下来。

⭐ 3. char 和 varchar 比较

实际存储	char(4)	varchar(4)	char 所占字节	varchar 所占字节
abcd	abcd	abcd	4 * 3 = 12	4 * 3 + 1 = 13
A	A	A	4 * 3 = 12	1 * 3 + 1 = 4
Abcde	×	×	数据超过长度	数据超过长度

1. char 和 varchar 的区别

char 类型可存储的字符上限就是 255 个字符。而 varchar 可存储的字符上限受表的编码格式所影响。
char(L) 在定义好后，会一次性开辟好 L 个字符的空间。而 varchar(L) 则是在以 L 为上限的情况下，用多少开辟多少。

2. char 和 varchar 的优缺点

char 是定长的，因此会比较浪费磁盘空间，但是效率高 (能直接访问定长的空间)。
varchar 是变长的，因此会比较节省磁盘空间，但是效率低 (需要维护字符串 )。

3. 如何选择 char 还是 varchar

在确定数据长度的情况下，可使用定长字符串 char (如：身份证号、手机号、银行卡号等)。
在不确定数据长度的情况下，可使用变长字符串 varchar (如：名字、地址、籍贯等)。

🌈 五、日期和时间类型

数据类型	大小	格式	用途
DATE	3 字节	YYY-MM-DD	日期值
DATETIME	8 字节	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4 字节	YYYY-MM-DD hh:mm:ss	时间戳

1. 三种时间类型对应的表结构

创建一张名为 t10 的表，表中分别包含 3 中类型为 date、datetime、timestamp 的字段。

2. 往各类型字段插入时间数据

🌈 六、enum 和 set 类型

⭐ 1. enum 和 set 类型的区别

enum 枚举 (单选) 类型：在定义 enum 字段时需要提供若干个选项的值，在设置 enum字段值时只允许选取其中的一个值。
```
enum('选项1', '选项2', '选项3', ..., '选项n');
```
set 集合 (多选) 类型：在定义 set 字段时需要提供若干个选项的值，在设置 set 字段值时可以选取其中的一个或多个值。
```
set('选项值1', '选项值2', '选项值3', ..., '选项值n');
```

⭐ 2. enum 和 set 类型的使用案例

创建一张名为 votes 的问卷调查表，表中包含被调查人的姓名、喜好(登山，游泳，篮球，武术等、性别 (男 / 女)。
- 在大多数情况下，人的性别只有两种，因此可以定义成 enum 类型。而人的爱好在提供的选项中可能存在多个，因此可以定义成 set 类型。

往表中插入数据时，被调查人的性别只能从男 / 女中选择 1 个，被调查人的爱好可以从提供的若干个选项中选择 n 个。
- 注意：多个爱好之间需要通过英文逗号隔开。

⭐ 3. 通过数字设置 enum 和 set

MySQL 在存储 enum 值时实际存储的都是数字，enum 中提供的选项值依次对应数字1、2、3、… n 最多 65535 个，因此在设置 enum 值时可以通过数字的方式进行设置。
MySQL 存储 set 值时实际存储的也是数字，set 中提供的选项值依次对应数字 1、2、4、8、…，最多 64 个，在设置 set 值时也能通过数字的方式。
虽然通过数字设置 enum 和 set 值很方便，但还是不推荐通过数字设置 enum 和 set 值，因为这样会导致可读性变差的情况。

1. 通过数字设置 enum

在往表中插入数据时，除了能够通过男 / 女指定用户的性别，还可以通过插入数字 1 / 2来设置性别，enum 的数字表示的是下标。

2. 通过数字设置 set

set 是通过比特位上对应位置的 1 往表中插入数的，set 的数字表示的是位图。
- 如：1 是第一个比特位为 1，就会将第一个值 ‘踢足球’ 设置，2 是第二个比特位为 1，就会设置成第二个值 “打游戏”。
- 如果设置的值有多个位为 1，比如说 3，就会设置第一个和第二个值 (踢足球和打游戏)，其余同理。