【MySQL】数据类型

本篇博客由 CSDN@先搞面包再谈爱 原创，转载请标注清楚，请勿抄袭。

前言

我在前一篇讲表的操作的时候碰到了一些数据类型，但是没有正式讲这些类型，本篇就重点讲讲MySQL中的所有数据类型。

像C、C++、Java等语言都是有自己的数据类型的，MySQL也有自己的数据类型。

【注】本篇中如果出现我前面没有讲过的操作我会解释一下，如果讲过了我就不细说了，不懂的同学可以翻一翻我前面的博客。

正式开始

首先给出所有的类型：

其中红色的重点讲。分四大块来讲，就是最左边的数值类型，文本、二进制类型，时间日期，String类型。

先来说数值类型。

数值类型

其中有很多熟悉的，int、bool、float等。

从float开始往下的都是浮点数类型，上面的都是整数类型。

先看整数类型。

整数类型

从tinyint到bigint：

不同类型占用的字节数不一样，都是MySQL内部规定的。像tinyint就像char，smallint就像short……，还是一个字节8个bit位。

拿tinyint来说，如果写的时候直接写tinyint，那默认情况下就是有符号的，取值范围和C/C++中的完全一样，定义的时候在tinyint后面加上unsigned就表示是无符号的，光说不好理解，来写写。

建一个表：

表中就一列，类型为tinyint。

其中的细节：

show 一下：

插入点数据：

tinyint数据范围为-128 ~ 127，可以看到，插入边缘数据-128和127都没什么问题，插入中间数据也没问题。

但是如果我插入的数据超出范围：

会直接对我插入的数据就行拦截。

再来创建一个表，搞成无符号的：

无符号的tinyint的范围为0 ~ 255，可以看到边缘数据0 和 255都插入成功了，如果我插入-1和256：

也是会直接拦截我。

你也可以试试其他整数类型，超范围了都是会拦截的。

如果我们向MySQL特定的类型中插入不合法的数据，MySQL一般都是直接拦截我们，不让我们做对应的操作。反过来说，如果我们已经有数据被成功插入到了MySQL中，那么插入的数据一定时合法的。就比如说tinyint unsigned，那数据库中存放的就一定是0 ~ 255的这样的合法数据。如果你插入了一个256，在C语言中会发生截断，但这是直接拦截。

所以MySQL中一般而言，数据类型本身也是一种约束，后面博客我会详谈约束，这里先引一下。这里的约束是约束我们程序员，倒闭程序员，让程序员尽可能进行正确的插入，也就是约束MySQL的使用者，另外，如果你不是一个很好的使用者，MySQL也能保证数据插入的合法性，这样就能保证数据库中的数据是可预期的、完整的，没有发生什么截断或者隐式类型转换的事情。

tinyint就讲到这里，剩下的几个int各位自行测试一下，我这里就不再说了，都是一样的。

实际在创建表的时候，列类型尽量选择最合理的类型，比如说年龄就用tinyint unsigned就绝对够了，不要搞什么smallint这样偏大的类型，可以但没必要，浪费空间。在数据库这里可不要小看每一个字节，如果你表中有很多条记录的话，那就会浪费很多，假如说有一百多万个用户，每个用户一条记录，一条记录浪费一字节，那最后就会浪费一百多万字节，还是相当多的，所以要根据应用场景来选择最合适的类型来搞。

bit类型

bit类型，也就是位类型。

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

方括号里的可以忽略。

直接给例子吧：

这里搞了一位，那么能表示的数据就只有0和1，假如说online字段表示是否在线，插入数据：

插入的是0、1成功。

再插：

一旦不是0或1就会失败。这就是约束。

因为只有一个比特位。

显示一下：

可以看到这里显示不出来0和1，因为显示的时候是将0、1这两个数值按照ASCII来显示的，而0、1的ASCII是特殊的字符，显示不出来，想要显示出来的话可以按照其他格式来显示（下面的操作后续博客会讲到）：

这里是按照16进制显示的，也可以八进制显示：

修改一下bit位数：

从1位改成10位。

那么此时能表示的数就能多点了，最大能到$2^{10}-1$，也就是1023。插入：

显示：

不过这两个数还是显示不出来。

插入一个能显示出来的，比如说插入一个字符‘a’；

因为a的ASCII是97，所以能插入能显示。

如果我插入一个97，那么也会显示a：

所以这里显示的时候是按照ASCII来显示的。

bit只能表示正整数，如果插入负数会直接拦截：

前面说bit位数范围为1 ~ 64。这里测试一下：

默认情况下就是一位：

bit后面跟的1就指的是1位。注意bit后面的数是几就表示的是有几位。

浮点数类型

float

float[(m, d)] [unsigned] : m表示一共有多少位，d指定小数位数，占用空间4个字节。

也是方括号中的能省略。

上例子：

4表示的就是这个浮点数中一共有4位，2表示小数点后有2位，比如说43.91，4、3、9、1一共四位，小数点后面两位91。因为并没有带unsigned，那么这里范围就应该是-99.99 ~ 99.99。

插入点数据：

插入负数：

也是成功的。

如果我插入一些超出范围的：

都是失败的。

注意，如果插入数据在范围内，但小数位数你没有给够，会自动帮你补全：

这里补了一个0。

但是如果插入的数据在范围内，小数位数多了，会自动四舍五入（注意五入的时候前一位不同会有点区别，如果前一位是偶数，则不会入，如果前一位是奇数才会入，这一点取决于底层实现）：

5不入：

这里5前一位是4，偶数，不会进位。

这里5前一位是9，进位了。

再来试一个：

偶数，没有进位。

但如果进位的结果不在合法范围内，那也无法插入：

上面99.995进位的结果为100.00，超了，就插入失败。

所以四舍五入也是要在合法范围内的。

float数据的范围取决于定义列属性时的(m, n)的值。

在以前C/C++部分，定义一个浮点数总是会有符号的，因为总会有一位来表示符号位。但是mysql这里会区分有符号和无符号，看例子：

其中的salary就是无符号的。

插入个0：

会自动帮你补上两个小数0。

再插入点：

再来插入点负数：

都是失败的。

所以无符号的浮点数会把负数部分的数全部去掉，只留下0和整数部分的。

mysql中的float是否会有精度损失呢？
会的。

把刚刚的t6改一下：

插入：

可以看到默认情况下最大精度是六位。

再试点：

可以看到，会损失不少数的。

double也一样，虽然精度会多一点。这里就不再演示double了。

不过有一个类型来尽量避免精度损失，这个类型就是decimal。

decimal

刚刚float如果整体位数比较多，就会出现存的数据不太准的情况。这一点也和浮点数的存储原理有关系。不过decimal可以规避精度上的损失。二者用起来还是很相似的，看：

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

来演示一下：

这里搞了两列，一列是float，一列是decimal。

来插入点数据：

可以看到，其实和float差不多，甚至也有四舍五入。

再来插入点不行的：

也是位数过多。

我这里再来将decimal改一下精度：

先前插入的数据精度也会跟着变：

插入点数据：

可以看到float已经不准的，但是decimal依旧很准。

因此如果我们希望某个数据表示高精度，选择decimal。

decimal整数最大位数m为65。支持小数最大位数d是30。
如果d被省略，默认为0，如果m被省略，默认是10。不过也看版本，如果版本不同默认值也会有差异。

有一篇博客讲的很好，我贴到这里：【MySQL】浮点数精度问题

字符串类型

char

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

直接上例子：

name类型为char(2)，也就是能存放两个字符。

插入点数据：

abc不行，其他都行。

显示一下：

再插入点：

中国人没有插入进去。

注意，在utf8中，一个汉字占用3个字节，而GBK中一个汉字占两个字节，而这里 中国 都占用6个字节了还能插入，这就可以说明char中的单位是字符，而非字节，mysql中的字符和C/C++语言中的字符概念是不一样的，以前语言上是一个字符对应一个字节，而汉字是一个字符，所以‘中’是一个字符，所以‘中国’就是两个字符，而不是看6个字节，两个字符就能插入。但是‘中国人’是3个字符，就插入不了。

刚刚介绍的时候说了最大长度是255，这里里试一下建256的行不：

看来是不行，不过下面提示了说可以用BLOB或者TEXT来替代。

char先到这。

varchar

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

最长为65536个字节，比char大。

演示：

插入数据：

到 ！停。因为加上！是7个字符。那这里和插入有什么区别呢？等会再说，先把varchar讲完。

再来修改一下t9，刚刚说了varchar最大字节数为65535，那能把长度改成65535吗？

看来是不行，不过提示了一个max = 21845，这是啥？
这里字符集用的是utf8，一个字符3字节，$21845\ast 3=65535$，所以这里varchar最大能插入的字符个数是21845，试试：

还是不行，为啥？
上面解释的是一行不能太大，啥是一行呢？
一行其实就是一条记录，一条记录的大小最大就是65535个字节，上面t9中有两列，一列是int，4字节，一列是varchar，如果给成21845的话就会占用65535个字节，这样总计就是65539个字节，再调小一点：

可以看到21842能成功，21844、21843都不行。一下子少了3个字符，也就是9字节，但是int也才4字节呀，为啥得多给9字节才行？

这里就要说一说char和varchar的区别了。
char在 C/C++中类似一个定长数组，比如说char(6)就像char arr[6]一样，申请了就一定会开6个字节的空间，但是你用多少不会关心的，就算你只需要用1个字节，但是还是会开6个字节的空间，那这样就会有一定程度的浪费。

但是varchar就像C++中的string一样，空间是动态开辟的，比如varchar(1000)，这里就当是3000个字节，那就相当于给string的capacity开到了3000一样，size依旧还是0，只有在插入数据的时候才会实际开辟空间。比如你只需要1个字符，那么只会给你开一个字符的空间，剩余的空间不会真正开出来的，但是上限还是1000个字符，上限和有多少用多少是不冲突的。但虽然string更省空间一点，但是其中还会有相应的字段来保存size和capacity是多少。对应的varchar也是，其中需要有字段来保存当前数据的长度，而这个字段也会占用空间。

varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字
节数是65532。1 ~ 3个字节完全是取决于其中数据长度的大小的，如果数据比较短，用一个字节就能表示整个长度。但如果数据比较长，字节短了表示不出来，那么就要多个字节来表示，最长的时候就是3个字节就能表示。

所以就能回答前面的问题了，多给9个字节其实并不是给int的，而是其中记录长度的字段占用的，但是varchar中是以字符为单位的，总共给三个字符，两个字符（6字节）用来表示int的4个字节，一个字符（3字节）用来表示数据的长度。

再来给个例子：

这里t10只有一个name，所以可以开的更大一点：

不过21845还是开不了，因为一行最大不能超过65535：

char和varchar比较

看图：

解释一下varchar占用字节，其实刚刚也说过了，数据短的时候用一个字节就可以表示总长度了，这里abcd只有4个字节，用1个字节完全可以表示，所以就是13个字节，A同理。

Abcde，char和varchar都开的是4，肯定没法存。

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

如果数据确定长度都一样，就使用定长（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 完全一致，占用四字节

时间戳是啥意思我就不说了，不懂的同学百度一下。
时间戳会自动更新，日期和日期时间需要我们手动添加，光讲理论不好说，得看例子。

例子：

这里时间戳默认值为当前时间戳，附加列中说更新的时候会自动将这个时间更新成当前时间戳，还是得看例子，光看理论不好说。

这里是指定列插入数据（指定t1和t2，需要加括号，我前面的插入都是全列插入，可以手动加括号，但不加方便一点），t3不用我们手动插入，在插入的时候初始值会自动更新成你当时插入操作的时间。

再来一个：

来更新一下，我这里专门隔了好久搞的：

其中划线的sql语句就是将t1为’2000-10-01’的记录的t1修改成1999-01-01，可以看到更新之后时间戳直接变成了新的时间。

timestamp类型常用于论坛/评论区，比如说：

就把这里的t12表作为专门用来评论的。

当一个人通过客户端将消息发送出去，其实就是发到了服务器，服务器再将消息转到sql语句交给数据库，然后数据库就会记录下来这个人的言论和发表言论的时间：

如果过几天这个人把这条评论改了，那么也会显示成TA改评论的时间：

这样其他人就能看到这个人评论的时间。

datetime用于固定时间，比如说记录下你入职的时间，这样方便计算你的工龄、给你发工资的时间等等。或者是记录你获取身份证的时间，方便记录什么时候到期啥的。

就说这么些。

enum和set

枚举和集合。

其实就和C中的差不多。

理论：

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

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

enum是多选一，set可以多选一也可以多选多。

来点例子：

在初始建表的时候就要将选项设置好，后面在插入数据的时候再选择对应的选项。

插入点数据：

再来一个：

enum只能在给出的选项中单选一：

enum还可以给数字：

其他的不行：

这里就说明枚举类型可以写限定的常量(男/女)，也可以写限定常量的下标(从1开始)，有几个常量就有几个值。数值是几就表示第几个常量。

set可以在给出的选项中选多个：

注意，多个字段之间不能加空格，不然会出错。

再来一个：

enum和枚举都可以给成空：

后面讲约束的时候再详谈这一点。

同样的set也可以给成数字，但是和枚举的数字不太一样：

给0的时候是一个空串’ '，1是敲代码，2是扳手腕，3是敲代码和扳手腕。

注意空串’ ‘和null可不一样，null表示什么都没有，’ '表示有东西，但不过是空串。就好比你手上有一个空瓶子，最起码还能接点水喝，但你空手接不到水（手上捧的那点不算）。

再看一下votes：

插入0为空串，1为敲代码，2为扳手腕，3却不是健身，而是敲代码和扳手腕。

再插入一个7：

很明显，这些数字不是下标，五个爱好，把这些爱好想象成5个比特位00000，为1就代表有这个爱好。数值给1的时候就是00001，那么就是有敲代码这个爱好。给2就是00010，就是有扳手腕这个爱好 ……7就是00111，就是前面的三个。

那总共5个位，全部都有就是11111，这个值十进制就是31，试试：

所以枚举那里是下标，而集合这里是位图。

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

enum和set类型的查找

这里要用到where字句，后面博客会专门讲解，这里就先用一下。这里只要知道是用来筛选的就行了。

比如说：

还可以是数字：

但是范围之外的就不行：

再来一下查找爱好的：

能找到，不过有个小问题，有的人爱好不止一个，但是包含敲代码，能不能让这样的人在查找的时候也显示出来？

用数字来查找不太行：

还是严格匹配的才会显示。

要用一个mysql的筛选函数：find_in_set：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；

你没有听错，mysql中也有函数，我后面的博客也是会讲到的，这里先了解一下这个。

甚至select后面还可以跟表达式：

来个这个函数的例子：

再来个：

如果不在范围中：

就会显示0。

只能是单个的，双的不行：

故find_in_set只能查一个元素。

用一下：

这里是会把find_in_set的结果交给前面的select。

那如果想要找同时喜欢敲代码和健身的呢？
可以将(‘乒乓’, hobby)和(‘代码’, hobby)的结果拼到一块，像C语言中的条件判断一样：

就可以查到了。

这里的where就像条件判断一样，and就像C中的&&。

至此数据类型全部讲完，是挑着重点的讲的，一些类似的类型同理。

最后一句话，数据类型本来就是mysql中的一种天然约束，这种约束满足了才能进行插入，所以表中的数据是可预期的。

到此结束。。。