【MySQL】数据类型【mysql当中各自经典的数据类型的学习和使用】

目录

数据类型

数据类型本身就是mysql当中天然的一种约束

1数据类型分类

数据类型一共分为数值类型、文本，二进制类型、时间日期类型、String类型

2.数值类型

2.1tinyint类型

数值越界测试：

要讲述tintint类型，我们肯定要建一个表，在里面弄一个字段，字段类型是tinyint类型、

create table t1(
num tinyint
)

要注意，如果在声明的字段的数据类型的时候，没有表明是unsigned(无符号)，那默认就是有符号的。由于tinyint只占一个字节，因此有符号的话，存储范围就是 -128~127

建表完毕如下图

我们知道该表的tinyint的存储范围是-128~127，现在我们往里面插入一些数据

insert into t1 values(-128),(127),(0);

插入范围内的数据会报错，那我们插入范围外的呢？

insert into t1 values(-129),(128);

如图所示，mysql会直接报错，说超出范围了、

我们再来看看无符号unsigned 的tinyint。

create table if not exists t2(
num tinyint unsigned
)

同样插入几个数据，我们发现在0~255的数据都可以插入进去，刚好对应unsigned tinyint的范围。

如果插入超出范围的数据，一样也会被拦截的。这是因为在mysql中，数据类型本身也是一种约束【约束指的是约束使用者，倒闭使用者在插入数据的时候要考虑数据的合法性。并且哪怕数据不合法，mysql也会拦截。】

如果是其他语言，大数据放到小范围里可能会发生数据的截断，但是在mysql当中不行，因为如果一个表格当中，有部分数据是被截断过的，那用户还能信任这个表格吗，当然是不行的，因此mysql当中的表，一定是数据合法才能插入。

对于其他数据类型也可以样子实验，这里就不实验。

2.2bit类型

**

bit[(M)]

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

实例：

我们创建一个表，online表示是否在线

create table t3(
    -> id int, 
    -> online bit(1) comment '在线状态'
    -> );

此时我们的字段online是bit类型，是位字段类型，只有一个比特位，应该只能插入0和1两种数据。

实际上也确实，我们插入下图所示的2给字段online，mysql会直接进行拦截。

我们插入四个数据

insert into t3(id, online) values(123, 0), (124, 0);
insert into t3(id, online) values(125, 1), (126, 1);

然后选择展示所有数据，我们会发现online字段的数据我们是没办法看见的。

为什么呢？
因为bit类型 是位类型，是通过ASCII码值来显示，但是此时的表我们可以认为，ASCII码值是不可见的。因此我们看不见online字段的数据。

要查看的话要输入下面这条指令：

select id, hex(online) from t3;

将online字段的数据以10进制显示出来

为了验证bit类型在显示的时候是按照对应的ASCII码值去显示的。

我们修改一个online字段，修改一下online字段的位数

alter table t3 modify online bit(10) comment '用户在线状态';

然后再插入一些数据

insert into t3(id, online) values(127, 6), (128, 11);
insert into t3(id, online) values(129, 'a')

然后再查表

我们发现原来存不进去的大于1的数据都能存进去了，因为10个位，可以以二进制的形式存下0~2的10次方之内的数。

并且我们插入’a’，实际上就是以存储了’a’的ASCII码值，显示的时候以ASCII码值显示，因此是a，怎么证明？

我们再插入一个’a’的ASCII码值就可以证明

insert into t3(id, online) values(129, 97)

2.3小数类型

2.3.1float

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

实例：创建一个字段 数据类型是float(4.2)

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入，并且没有标明是unsigned，因此是有符号float

如下图所示，插入了三个数据。

并且插入由于长度是是4，因此下图的情况不行

但是并不是超出长度就会被mysql拦截，因为MySQL在保存值时会进行四舍五入

比如下图

查表的数据，发现确实如此。

注意：float的无符号类型和前面学的int类型是有一些不一样的，比如范围的变动，对于float unsigned来说，范围大小只会变小。

实例：创建一个表t5，有一个字段salary是无符号float(4,2)类型，范围是0~99.99，其他的东西不会变

插入一些不同的数据

注意：浮点数类型是一定会存在精度损失现象的，无论是小数部分还是整数部分，在进行数据的存入的时候，数据的精度都有可能会丢失。

我们来验证一下，首先将原来的字段修改一下类型的长度和精度，使用默认的float。

我们再插入几个数据，看看表内的数据是否有精度损失的现象。

再试一个

2.3.2decimal

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

学习float的时候，我们知道会有精度丢失的情况出现，使用decimal就可以避免。

实例：

create table t6(
    -> id int,
    -> f1 float(10,8),
    -> f2 decimal(4, 2)
    -> );

我们对decimal类型做和float同样的越界测试。

结果如下：

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

我们发现和float的规则是一样的。变为无符号后的规则也是一样的。但是decimal有自己的优势，decimal的精度更高

验证：

先将字段属性改一下

alter table t6 modify f2 decimal(10,8);

再插入数据：

insert into t6(id, f1, f2) values(5, 23.12345612, 23.12345612);

总结：

1. float的精度大概是7位
2. decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10
3. 如果希望小数的精度高，推荐使用decimal

3.字符串类型

3.1char

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

要注意这里的L的单位是字符，如果L是2，代表能存储两个字符，和字符所占据的字节无关，如果这个字符是汉字，就能存储2个汉字，如果是英文，就能存储两个字母。尽管每个汉字在mysql的utf8规则中占3个字节，英文字母占1字节

我们来做一下测试：
先创建一个表t7，里面有两个字段，分别是id，和name。name的类型是char(2)

然后我们向里面插入数据：

我们发现，无论是中温暖还是英文，只要是插入了3个字符的情况，就会报错。

插入成功的数据如下：

说明：

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

如果在建表的时候，定义的字符长度超过了255，mysql直接不让建表。

3.2varchar

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

做个测试：

其实使用起来和char类型是没有什么区别的。

那varchar和char到底在什么地方不一样呢？其实就是在它的名字上，varchar是可变长字符串——也就是你要多少字节我就给你多少，在字符数合法的情况下。

说明：

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

varchar字节长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。

当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]

如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

下图这个情况是因为mysql一行的字节数有限制，id这个字段的int类型，也要占一定字节数，因此最多只能是21842

如果只是单纯的varchar类型我们最多只能插入21844，而不是21845，因为有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。

3.3char和varchar的对比

那如何决定用char还是varchar？

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

4.日期和时间类型

常用的日期有如下三个：

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

timestamp和datatime的区别就是timestamp会实时变化

实例：

先建一个表

这里t3有一个额外属性，是因为它是类型是timestamp，时间戳要根据时间实时变动

我们插入一个数据

insert into t9(t1, t2) values('2000-10-01', '1999-10-01 08:00:00');

我们发现，尽管我们只插入了两个数据，但是t3会自动插入数据。这个数据就是我进行插入操作的时间。

如果我再对该表的数据进行一次操作，那么t3的时间也会对应的改变。

因此，只要我们对这个表进行任何操作，这个timestamp类型的字段都会自动更新时间。

timestamp类型的字段的经典应用场景就是评论区的评论时间【评论的时间是根据发布评论的时间定的】

datetime类型的字段通常用在不怎么需要改变的时间【比如身份证的有效时间段】

5.enum和set

enum和set

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

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

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

• set：集合，“多选”类型；

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

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

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

实例：

有一个调查表votes，需要调查人的喜好， 比如从（游泳，篮球，足球等）中去选择(可以多选)，性别为（男，女）[单选]

create table if not exists votes(
    -> username varchar(30),
    -> gender enum('男','女'),
    -> hobby set('打代码', '打羽毛球', '打篮球', '踢足球', '游泳')
    -> );

5.1对enum和set进行插入

• 对enum类型进行测试：

我们插入数据

有一个特殊的插入:

对于枚举类型来说，存储的元素是有下标的，这个下标从1开始

• 对集合类型进行测试

对于集合元素来说，插入的元素一定要是set定义时有的，比如set只有打羽毛球和打篮球，但是插入如果不输入完整是插入不了的，如下图：

并且set和enum的区别就是，set可以多选。

并且你输入的元素顺序，和最终插入的顺序是没有关系的，只要你输入的是set中的元素，最后插入的时候，是按照定义时的set的顺序来插入的

这里还有一个需要讲清楚的：

hobby这个字段我们没有进行约束，默认是可以为空的，0代表的是’ ’

NULL 和 ’ '的区别就是:

假设有一个账户

NULL是根本没有账户

’ '是有账户，但是账户里什么都没有

对于集合类型的字段，我们也可以用数字的方式进行数据的插入:

要注意，这个和enum的下标式插入不一样。

集合类型如果用数字插入的话是"比特位插入法"。

为什么我们说是比特位插入法呢？我们来看一张图片：

因此我们来验证一下输入7是否是前三个元素存在

可以看到确实如此，要输入五个元素都存在的话，就输入31就行了。

因此枚举用数字插入就是下标，集合用数字插入就是比特位插入

5.2对enum和set进行查询

• 对enum类型进行查询

由于enum只能单选，所以查询时还是好查询的，既可以通过字符去查，也可以通过数字去查

• 对set类型进行查询

由于set是存在多选的情况，并且其数字查询不是下标查询，是看的比特位。

因此相对麻烦，我们来测试一下。

上图，我们查询了hobby集合中是否有人的爱好是打羽毛球，输出了三个。

但是这样子好像有点小小的问题，那就是有些人的爱好是多样性的，可能打羽毛球只是众多爱好的其中之一，但是这里没有查询到这类人群。

如果我们只用字符或者数字的方式去查看的话，可以说是很麻烦的。只能精确的按照每一种情况去进行查询。

如果我们想查询爱好中有羽毛球的这个需求的话，我们需要借助集合查询函数——find_ in_ set

find_in_set(sub,str_list)

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

在mysql中函数是可以直接使用的。

可以发现，就是查询一个元素是否存在在一个集合内，如果存在就返回这个元素在这个集合当中的下标，下标是从1开始。

既然知道了find_in_set函数如何使用，我们再来试试应用到上面的场景里

输入下面这条指令就可以查询有羽毛球这个爱好的所有人

select * from votes where find_in_set('打羽毛球', hobby);

如果想查询的是存在打羽毛球和打代码这两个爱好的人呢？

就要多加一个and

select * from votes where find_in_set('打羽毛球', hobby) and find_in_set('打代码',hobby);

这样查询到的就是存在这两个爱好的所有人。

如果要查询更多就再加and就行了。

注意：我们要灵活运用字符查询、数字查询、和函数查询