MySQL之数据类型

一、MySQL的数据类型

MySQL作为一款软件，其内部拥有属于自己的数据类型。MySQL数据库支持多种数据类型，以满足不同的数据存储需求。

MySQL中的主要数据类型大致可以分为5类：数值类型，文本二进制类型，字符串类型，日期和时间类型，特殊类型。

下面我们就来依次讲一讲这些数据类型。

二、数值类型

数值类型也被分成了三类：整数类型，浮点数类型，定点数类型等。

1、整数类型

整数类型主要包括以下数据类型：
类型大小(Bytes)范围（有符号）范围（无符号）TINYINT（unsigned）1(-128，127)(0，255)SMALLINT（unsigned）2(-32 768，32 767)(0，65 535)MEDIUMINT（unsigned）3(-8 388 608，8 388 607)(0，16 777 215)INT或INTEGER（unsigned）4(-2 147 483 648，2 147 483 647)(0，4 294 967 295)BIGINT（unsigned）8(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)BIT位类型。M指定位数，默认值1，范围1到64
~ tinyint

创建一个包含tinyint类型的表：

再往其中插入一些在指定范围里的数据和越界的数据：

我们发现，如果用户插入了不在数据类型指定范围的数据，即不合法的数据，MySQL一般都会直接拦截我们并报错，不让用户进行插入。 因此，我们可以认为，如果已经有数据成功插入到了表中，那么这些数据一定就是合法的。这样数据库就能保住数据的合法性。

~ BIT类型

我们创建一个表，来表示用户的在线状况。可以用0表示不在线，1表示在线，因为在线状态只有两种状态，所以我们可以考虑使用位类型。

然后再往其中插入一些在指定范围里的数据和越界的数据：

因为我们的online的bit位只设置了1位，所以只能表示0和1，当插入其他数据时，就无法插入。如果要插入更大的数据，可以设置更多位。

但是，如果我们去查看表中的数据的话，会发现online列的数据没有显示出来：

为什么会是这样的呢？其实，bit类型在显示的时候是按照acssii码值的形式来显示的。而0和1的acssii码值是不可显示的。所以我们是看不见的。怎么解决呢？我们可以使用hex来进行十六进制显示。如下：

证明bit类型是按照asccii码值的形式来显示的。

先将bit类型的大小扩大：

然后插入两个字母：

进行查看：

然后，我们根据A和a的acssii 码值进行插入：

接着，进行查看：

我们发现，虽然插入的是数字，但是显示出来的是acssii码值对应的字母。

当然，bit类型的最大位数是64，如果创建一个位数大于64的位类型是不被允许的：

2、浮点数类型

浮点数类型主要包括以下数据类型：
类型大小（bytes）说明FLOAT（M，D）（unsigned）4M指定显示长度，D指定小数位数DOUBLE（M，D）（unsigned）8M指定显示长度，D指定小数位数DECIMAL（M，D）（unsigned）M指定显示长度，D指定小数位数
~ float

我们首先创建一个包含float类型的表：

然后我们插入一些合法数据：数值的有效数字个数位数为4，小数点后有两位。

下面我们插入一个有五位数值的小数。发现也插入成功了。查看一下，MySQL对其进行了处理，就是四舍五入。

从上面的结果，我们可以得出一个结论：对于小数来说，MySQL会对超出位数的小数进行四舍五入，如果四舍五入的结果合法，也是可以插入的。

插入不合法的数据，MySQL会直接拦截我们的插入操作：

无符号浮点数

创建一个包含无符号float类型的表：

插入一些数据：

如果是无符号float，其范围就是0 ~ 99.99。

但是，对于float来说，其精度还是存在一些问题的：

比如插入下面的数据，精度就会发生损失：

正是因为float的精度不够高，我们就有了decimal类型。

~ decimal

decimal的用法和float一样，但是其精度要比float高。

下面我们创建一张表，其中包含float和decimal两种类型：

我们向其中插入相同的数据：

从上面的查询结果我们发现，float的精度已经丢失了，而decimal的数据任然保存的是原数据。

所以说，如果希望小数的精度高，推荐使用decimal。

4、布尔类型

类型说明bool使用0和1表示真和假

三、字符串类型

类型说明char（size）固定长度字符串，最大为255varchar（size)可变长度字符串，最大长度65535blob二进制数据text大文本，不支持全文索引，不支持默认值
~ char

语法：

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

我们先创建一个包含char类型的表：

然后我们向其中插入一些数据：

从上面的结果中，我们发现，插入一个字符和两个字符都是可以行的，但是当插入三个字符就会出错。其实括号里面的数字的单位是字符，就代表能够插入的最大字符个数。char(2) 表示最多可以存放两个字符，可以是字母或汉字，但是不能超过2个。

因为最大长度是255，所以在创建表的时候，char括号里面的数字不能超过255。

**~ varchar **

语法：

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

我们先创建一个包含varchar类型的表：

然后我们向其中插入一些数据：

char和varchar

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

因为utf8编码中，一个字符占用3个字节，varchar(n)的参数n最大值是65532 / 3=21844。

说明：

如果数据确定长度都一样，就使用定长(char)。比如：身份证，手机号。

如果数据长度有变化，就使用变长(varchar)。比如：名字，地址，但是要保证最长的能存的进去。

定长的磁盘空间比较浪费，但是效率高。而变长的磁盘空间比较节省，但是效率低。

定长的意义是，直接开辟好对应的空间。变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

四、时间日期类型

常用的时间日期类型有三个：
类型说明date日期，'yyyy-mm-dd'（年-月-日） ，占用三字节datetime时间日期，'yyyy-mm-dd HH:ii:ss'（年-月-日 时：分：秒），表示范围从 1000 到 9999 ，占用八字节timestamp时间戳，从1970年开始起至当前时间的总秒数 yyyy-mm-dd HH:ii:ss。格式和 datetime 完全一致，占用四字节。
~ date

我们先创建一个包含date类型的表：这个表是用来记录人的生日的。因为生日我们一般只会记录出生年月日。

然后我们向其中插入一些数据并查看：

~ datetime和timestamp

datetime类型除了会记录年月日外，还会记录时分秒。timestamp表示从1970年开始起至当前时间的总秒数，会自动更新。

我们先创建一个包含date，datetime，timestamp类型的表：

然后我们向其中插入一些数据并查看：在写插入语句时，我们并没有给t3(timestamp)输入插入值，但是MySQL帮我们自动记录了t3的值，即当前时间。

当我们更新表中的t1的数据时，发现t3也自动变化了，因为当前的时间一定是不断变化的：

date类型一般用来记录不会变化的时间数据，比如：生日，账号注册时间。

timestamp类型可以用在评论区，该类型可以自动记录评论的发表时间。比如下面：

此时如果有用户发表了一条评论，在底层就相当于向user表中插入了一条记录，tim就是评论的发表时间：

五、ENUM类型

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

语法：

enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值。也就是在众多选项中，我们只能选择其中一个值插入。也就是多选一。

我们先创建一个包含enum类型的表，这个表是一个学生的基本信息表：

然后我们向其中插入一些数据并查看：

如果我们两个同时插入或者插入没有的选项，就会出错：

出于效率考虑，这些选项实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1，2，3，....最多65535个。

当我们插入枚举值时，也可以使用对应的数字编号进行插入：

注：不建议在添加枚举值的时候采用数字的方式，因为这样可读性较差。

六、SET类型

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

语法：

set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值。也就是在众多选项中，我们可以选择其中多个值插入。也就是多选多。

我们先创建一个包含set类型的表，这个表是一个调查学生的爱好信息的表：

然后我们向其中插入一些数据并查看：

如果我们插入没有的选项，就会出错：

出于效率考虑，这些选项实际存储的也是“数字”。但是，和enum不同的是，set是使用位图进行存储的。0表示选择这个选项，1表示不选择这个选项。比如：000001（1）表示只选篮球，000011（3）表示选择篮球和足球，000101（5）表示选择篮球和羽毛球，这些结果如下：

有了上面的数据后，这时我们想要查找喜欢篮球的同学：

但是，我们不能查询出所有爱好为篮球的人。 只能查出爱好只有篮球的人。

如果我们想要查询一个人，他的爱好中有篮球，我们就需要使用下面的函数了，集合查询使用find_ in_ set函数。

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

注：同样的，不建议在添加集合值的时候采用数字的方式，因为这样可读性较差。