MySQL数据类型

数据类型实际上也是对数据的一种约束。

一.数据类型分类

image-20230720071150319

二.数值类型

image-20230720071215832

三.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中，整型可以指定是有符号的和无符号的，默认是有符号的。
• 可以通过UNSIGNED来说明某个字段是无符号的。

无符号案例：

mysql> create table tt2(num tinyint unsigned);
mysql> insert into tt2 values(-1); -- 无符号，范围是： 0 - 255
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)

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

四.bit类型

基本语法：

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

image-20230720073936162

由于我们定义的位数为1，因此插入除了0， 1之外的数都会被拒绝，这里就不演示了。

此外，查看我们插入的数据发现无法看到bit类型对应的数据：

image-20230720074206330

这是由于bit是以ASCII码的形式表示的，若想显示，需要如下的指令格式：

image-20230720074327823

image-20230720074800319

五.小数类型

float类型

语法：

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

如12.34，则M=4, d=2。

image-20230720075958225

插入一定的数据：

image-20230720080604474

但插入的是10.0，在表中会显示10.00。如果小数超出了d的大小，那么就会四舍五入。

image-20230720080900364

需要注意的是，如果整数达到3位，或者四舍五入后整数达到三位，就会拒绝插入。比如99.995, 99.996都会四舍五入成100.00，这个位数就不满足约束。

定义无符号类型的float

image-20230720081911993

但是此时的上限还是99.99，不同的是无符号类型的数不能为负数，即范围为[0, 99.99]。

注：浮点数会有精度损失。

decimal类型

float会出现精度损失，但是decimal规避了这一点。

语法：

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

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

image-20230722195216920

说明：float类型大小大约是7位。

decimal整数最大位数m为65，支持小数最大位数d是30。如果d被省略，默认为0；如果m被省略，默认是10。因此，一旦需要的精度高，那么推荐使用decimal。当然，不同的sql版本在浮点数的规则已经精度会不同，但是我们应该以实验为准，来应对不同版本所对应的规则。

六.字符串类型

char类型：定长字符串

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

image-20230722202344373

image-20230722204146152

gbk编码，一个汉字占两个字节

utf8编码，一个汉字占三个字节

此时默认使用utf8编码，一个汉字就占用三个字节，但此时仍然可以插入两个汉字，这就一位着对于mysql，即便是汉字，一个汉字也仍代表一个符号。

image-20230722204330849

varchar类型：变长字符串

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

即便最大长度是65535个字节，但是多少个字符还是需要看编码的。

image-20230723151448829

对于varchar的边界范围，varchar(len),len到底是多大，这个len值，和表的编码密切相关：

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。需要注意的是，65532是建表时的最大字节数，也就是说，若要让varchar有这么大的空间，必不能包含其他类型的字段。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

验证一下：

image-20230723152142862

可见，含有其他字段，那么就不能达到21844个字符长度，因为一行就那么多，id占用了一部分。

image-20230723152318954

如果只包含varchar字段，那么就可以达到最大值。

char和varchar的比较

image-20230723153510414

varchar列下的+1，这一字节就是有效长度变量所占用的。

如何选择定长或变长字符串？

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

七.日期和时间类型

常用的日期有如下三个：

• date :日期 yyyy-mm-dd ，占用三字节
• datetime 时间日期格式 yyyy-mm-dd HH:ii:ss表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

image-20230723155612989

插入时，由于insert无需插入t3列的数据，所以不能将变量省略

image-20230723155932888

如果更改或插入t1、t2任意一列，t3都会改变。

image-20230723160255590

应用场景

• date和datetime和时间戳不同，不会随着数据更新而更改，但能够记录一些特殊的日期，比如入职年份，生日等关键时间点。
• 而timestamp的用处更为常见，对于评论区，一旦你要修改自己的评论，那么相应的评论时间都会随之改变，这就是timestamp的作用

我们利用文本代替评论区，观察时间戳的变化：

image-20230723161823933

八.enum和set类型

enum：枚举，“单选”类型；

enum(‘选项1’,‘选项2’,‘选项3’,…);

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

set：集合，“多选”类型;

set(‘选项值1’,‘选项值2’,‘选项值3’, …);set(‘选项值1’,‘选项值2’,‘选项值3’, …);

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

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

演示

1. 使用enum、set创建表

image-20230723175952651

image-20230723180256188

1. enum插入数据

image-20230723180824423

不在enum以及set枚举中的数据不会被插入在votes表中，也就是对于gender列，除了’男’、‘女’，其他的字符都无法被插入，set同理。

当然，作为枚举，插入对应的数字是被允许的，因为这对应的数字就代表着枚举时的各个属性。

image-20230723181056669

从此现象来说，插入的数字一定是从1开始映射的，有几个数就只能到几。因此0不能被插入，只有1和2能被插入。

1. set插入数据

与enum的唯一区别，set可以同时具有多个枚举属性：

image-20230723181623138

1. NULL与' '的区别

如果只像下面这样指定插入，其他属性就为空。

image-20230723183302710

我们知道，0在enum和set中不属于被枚举的数字，0在enum中插入会报错，但在set中插入中不会显示，实际上是个空字符串。

image-20230723183622200

1. set对应数字的插入

通过0对于enum、set的区别可以看出，set不是像enum那样的数字下标，因为如果是下标，插入0一定会报错而不是插入了空字符串。

image-20230723184220063

通过这个现象，更加确信了刚才的猜测。set数字插入一定有规律，不难发现：7的二进制位0111，那么从右到左有三个1，而上面的'代码'，'羽毛球'，'乒乓球'，正好处于前三个位置。

所以，set所插入的数字，实际上对应的一串二进制，而二进制数字1所对应的位置，就代表着映射的位置。那么可以看出，enum对应的数字相当于数组下标的映射，而set对应的数字实际上却是一个位图。

验证

那么验证一下结论是否正确，如果想把'代码'，'羽毛球'，'乒乓球'，'足球'，'游泳'都一起插入，那么此时的位图就需要为11111,其对应的十进制数字为25 -1=31，所以，下面插入31:

image-20230723185132233

九.enum和set类型查找

对于以下数据：

image-20230723193431662

如果我们想找到喜欢羽毛球的人，即hobby里面包括羽毛球的人，采用where子句直接像下面这样筛选是不行的：

image-20230723193702640

因为这样只能找到只喜欢羽毛球的人。所以，引入如下方法：

集合查询使用find_ in_ set函数：

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

image-20230723194934707

如果想找到喜欢代码和羽毛球的，sub是不能直接写两个属性的，这样查找为空，因为sub只对应一个属性，但是可以在条件上多加一些：

image-20230723195226205

如果想要找到全都喜欢的，直接hobby=所有属性，肯定是可以的；用and连接所有的条件也是可以的；最简洁的方式还是通过位图的映射：

image-20230723195433143

十.select 的其他作用

select除了之前的操作，其还可以执行表达式：

image-20230723194000268

select也可以执行函数，比如上述的find_in_set：

image-20230723194223653

这也可以验证该函数的功能，该函数只能找其中str_list中的一个元素：

image-20230723194442219