初识mysql数据库之数据类型

网络天使莱娜酱

于 2023-07-06 16:07:38 发布

阅读量286

点赞数

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

本文链接：https://blog.csdn.net/Masquerena114514/article/details/131395967

版权

mysql数据库专栏收录该内容

17 篇文章 2 订阅

订阅专栏

一、数据类型

在文中，我们创建表时，都会在里面创建一些列，这些列在创建时都跟上了一些数据类型，这些数据类型后面有时还带有括号，那么这些数据类型是什么呢？而这些数据类型后面跟着的括号又有什么用呢？在这里就会为大家解答。

1. 数据类型分类

在mysql中也是存在很多数据类型的，但总体来看分为几个大类，如整型、浮点型等等，这些类型和C++中的类型有点相似，但又有区别。

总的分类如上图所示。虽然类型有很多，但我们可以将其看做四个大类。在这些类型中，每个类型后面的括号的含义都可能不同，所以括号的含义会在下面的内容进行讲解。

为了方便解释，大家可以先在mysql中创建一个test_db数据库，然后进入这个数据库。

接下来的所有操作都会在这个数据库中执行。

2. 数值类型

2.1 整数类型

整数类型中的典型类型就是上面5个。每个类型所占的字节和返回都不同。这里的整数类型就和C++中的整型很相似，都有有符号和无符号的区分。其中，int类型的取值范围更是和C++中的int类型完全相同。

为了让大家看到这些类型在mysql中的表现形式，就以tinyint为例子。

创建如下一个t1表：

执行“desc t1;”命令查看这个表的属性信息：

可以发现，tinyint类型后面默认跟了一个(4)，这个内容我们在创建表时并没有写。至于它的作用是什么，在这里不太好解释，会专门放到后文中的“表的约束”中讲解。此时可以暂时不管。

通过上面的类型图片可以知道，tinyint的取值范围为-128 ~ 127。因此，我们向num中插入-128、127这两个边界值看看能不能插入。

可以正常插入。既然边界值可以插入，那我们再尝试一下插入越界的值，比如-129和128。

可以看到，当我们插入越界的值时，mysql就报错，说插入的值超出的取值范围。这就说明，当我们向某个数据类型中插入越界的值时，mysql会直接拦截此次操作，并返回一条错误信息。

既然有符号的tinyint无法越界插入，那我们再来看看无符号的tinyint是否可行。创建如下一个t2表：

从上面的类型图中可知，无符号的tinyint的取值范围为0 ~ 255。在这里我们就分别插入0与255这两个边界值和-1与256这两个越界值看看会发生什么。

当插入的边界值时，可以插入成功。然后再来看越界值：

同样的，mysql直接拦截了越界的值，禁止插入。

通过上面的两个例子就可以知道，无论数据类型是有符号还是无符号的，只要插入的是不合法的数据，mysql在一般情况下就会拦截该操作。由此，我们可以得出另一个结论——如果已经有数据插入到mysql中了，那么该数据在插入时就一定是合法的数据。

那为什么会有这个机制呢？究其原因就是mysql不信任用户。它担心某些程序员可能因为某些原因，向mysql中插入了不合法的数据，所以mysql在插入数据时，都会对数据进行检查，判断其是否合法，合法则插入，不合法则直接拦截。

通过这种方式，就能保证数据库中的数据是可预期的，是完整的。

至于上面的如smallint，mediumint等等整数类型，它们在mysql中的表现是一样的，这里就不再赘述，如果大家有兴趣，可以去测试一下其他几个整数类型。

在后面的使用中，要使用哪个整数类型，整数类型是采用有符号的还是无符号的，完全取决于你的使用场景。例如如果你的数据是要记录一个人的年龄，那么使用一个tinyint的无符号类型就完全合适。

2.2 位字段类型

位字段类型的简述就如上图。从名字bit上大家应该都可以知道，bit类型代表的就是bit位，因此就被称为“位字段类型”。

为了方便测试，创建如下一个t3表：

查看它的详细属性：

可以看到，t3的online列的数据类型就为bit，括号中的值是我们创建表时指定的1。那么这个1的含义是什么呢？向里面插入0、1、2三个数字来进行测试。

可以发现，0和1可以插入成功，但是在插入2的时候却插入失败了，并且打印了错误信息，说插入的数据太大了。再仔细思考一下，bit是bit位，而一个bit为只能表示0和1这两个数字，并且我们刚刚在创建表时恰好在bit的后面添加了1。这就说明，bit后面的括号中带的值表示的意思是要创建多少个bit位。

我们再来查看一下列中保留的数据：

这就很奇怪了，为什么表中online一栏没有显示数据呢？其实是因为位类型的数据在mysql中一般以anscii码值的方式显示，而0和1在ascii表中刚好是不可见的数据。如果你确实想看到，可以执行如下命令直接查看数据对应的ascii值：

为了验证这一点，我们可以修改online的bit位个数，添加其他的值进去：

修改成功后，添加一个'a'进去：

可以看到，插入a就可以正常显示。至于这里为什么可以插入，那是因为我们修改了online使用的bit位，让其可以表示a。同时大家知道，a在ascii表中的值是97，那如果我们插入一个97呢？

可以看到，当我们插入97时，显示的依然是a。原因就不必多说了，就是因为97在ascii表中对应的值是a。

bit数据显示的问题解决了，我们再来看另外一个问题——bit的范围。上文中说了，bit的范围是1 ~ 64，这个范围不是指bit的数据可表示范围，而是可创建的bit位数。因此，我们创建如下一个t4表，里面的bit就创建65位：

可以看到，此时mysql就直接拦截了此次创建，并告诉我们online列的最大长度应该为64。这也就进一步证明了bit的括号中的值表示的是要申请的位数，且这个位数的最大值应该是64。

2.3 小数类型

小数类型，其实就是C++中的浮点数。它和浮点数一样，都是可以用于保存带有小数点的数据的。

（1）取值范围问题

这里的m表示的是显示的长度，d表示的精度。注意，mysql中的小数类型和C++的浮点型要区分开来。这里的显示的长度指的是这个数字的长度，例如float(4, 2)，表示的意思就是长度为4，精度为2的数字，精度必须严格显示2位，因此它的取值范围就是[-99.99, 99.99]。这一点和C++中的浮点型区分很大。

简单来讲，小数类型中整数部分的最大位数是m - d。

那么到底是不是这样呢？我们创建如下一个t5表来测试。

查看它的详细属性：

类型和我们创建表时填的一模一样。上文中说过了，float(4, 2)的取值范围是-99.99 ~ 99.99，因此我们先插入它的边界值看看是否可行：

可以正常插入。再插入100和-100这两个越界值看看是否可行：

可以看到，此时就无法插入了。既然越界的值无法插入，那如果我们插入没有越界的值，但是不带足够的小数点呢？

可以看到，如果插入的值的小数位不够2位，mysql在显示时就会自动补齐。这也就证明了上文严格保持2位的说法。

既然插入的数字的小数位数可以不足2位，那是否可以大于2位呢？执行如下命令进行测试：

可以看到，当小数位超过2位时，依然可以插入。但是插入的值却有所变化。其中23.456在显示时变为了23.46，而23.454则是23.45。这就说明，虽然插入数据的时候插入的值的小数位可以超过指定位数，但是mysql会按照“四舍五入”的方式帮我们进位到指定位数。

既然小数位可以超过指定位数，那我们是否可以在边界值上超过呢？添加99.994和99.995两个值：

很明显，99.994成功，99.995失败。原因不比多说，就是因为四舍五入的关系，99.995四舍五入和超过了取值范围，而99.994则没有。

有符号的小数类型测试完后，我们再来测试一下无符号的小数类型。创建如下一个t6表：

大家知道，无符号数是没有负数的。那无符号数的正数取值返回会如何变化呢？插入99.99和100进行测试：

可以看到，99.99可以正常插入，但是100依然无法插入。这就说明，无符号的浮点数的取值范围仅仅是去掉了有符号的浮点数的负数部分，正数部分没有变化。和无符号整数类型中的整数范围扩大不同。

（2）精度问题

float的括号中的值的含义理解之后，大家可以思考另一个问题，那就是浮点数的精度是否会有损失呢？

修改t5表中的float如下：

在这次，我们就不给float带括号了。如果不自己指定显示长度，mysql会默认让它的可显示范围很大，可以存储10多位的整数，这和mysql的版本有关。

插入如下几个位数较多的数据：

插入成功后，再查看表中的内容：

可以看到，此时表中的数字就出现了精度损失，并且可以发现，插入的数字在第6位之后的值就全部显示为0，出现了明显的精度损失。这也就说明，float存储位数过多的数据时是可能出现精度损失的，可维持精度的位数大约在6位左右。

既然float可能会出现精度损失，那有没有精度不会损失的小数类型呢？答案是有的，那就是decimal。

（3）decimal

decimal和float同属于小数类型，括号中带的值也是一样的。但是decimal和float有一个最大的区别，那就是decimal所表示的精度更大。

同时decimal可表示的值也比float大。decimal整数最大位数是65，支持小数最大位数d是30。如果d被省略，默认d为0；如果m被省略，默认m为10。

为了方便对比float和decimal，创建如下一个t7表：

表创建好后将向其中插入下面一组数据：

可以发现，当插入的数据的位数过大时，float就会出现精度损失，但是decimal并没有。

因此，当未来我们遇到需要存储精度较高的数时，就可以使用decimal；而如果存储的数据允许一定程度的精度损失，就可以使用float。

3. 字符串类型

3.1 char

char是字符串类型中的一种，是固定长度的字符串。char的括号中带的值是可存储的字符长度，最大为255。

创建一个t8表进行测试：

在这个表中的字符长度为2，我们插入如下数据进行测试：

可以看到，当插入的abc时，由于字符个数超过了限定值，mysql就直接给拦截了。

既然字母可以插入，那我们再来插入中文看看：

通过上图可以知道，当插入中文时，可以将“你”和“你好”插入，但是无法插入“你好啊”。要知道，这里使用时utf8编码集，该编码集中一个中文要占3个字节，根据C++中一个字符长度表示一个字节的含义来看，2个字符长度应该是不能插入中文的才对啊。

因此，mysql中的字符长度与C++中字符长度含义是不同的。C++中的字符长度就是字节长度；但是mysql中的字符长度是显示的字符个数，无关字节。因此，在上图中，由于我们设置的字符长度为2，虽然“你好”应该占6个字节，但依然可以插入。而“你好啊”的字符长度为3，就无法插入了。

根据上面的关于char的说法中，char的最大长度为255，为了验证这一点，我们创建一个t8表，表中带有一个长度为256的char：

创建失败，mysql返回信息告诉我们char的最大值应该是255，这也就验证了char的最大字符长度。

3.2 varchar

varchar是变长字符串，括号里面的数字也是表示字符长度，这些点和char是一样的。但是varchar的最大长度要比char大，并且它是可变长度字符串。

（1）varchar的使用

为了方便测试，创建如下一个t9表：

由于varchar括号中的数字的含义和char是一样的，所以我们直接插入6个中文字符测试一下：

结果符合预期，当插入的字符为6个时候可以插入，但是当超过6个后，就无法插入了。

同时上文中说varchar的最大范围是65535，因此创建如下一个varchar为65535的表来测试：

可以看到，当我们要创建65535长度的varchar时，mysql直接拦截了，并且告诉我们name的最大值应该是21845。这其实是因为，varchar中的65535指的是字节个数，即虽然varchar的括号中的数字的含义和char相同，但是varchar中这些字符的字节相加不能超过65535。同时这个表使用的是utf8编码集，在utf8中，一个字符表示3个字节，因此mysql提示我们name的最大值应该是21845。

（2）varchar的变长问题

虽然合理，但是这有一个问题，那就是varchar和char有什么区别呢？难道就只是范围大一点吗？而且varchar是“变长字符串”的意思是什么呢？

varchar是变长字符串，并不是指varchar的字符长度可以自行变化，而是指varchar在使用时，它使用的空间大小是随着插入的字符数量变化的。

当使用char时，mysql是直接将char需要的空间一次性申请出来，例如char(6)就是直接申请6个字符长度的空间；而varchar则是使用多少申请多少，例如varchar(6)，如果varchar中指插入了一个字符，那么mysql就只会给varchar申请一个字符长度的空间，而不是直接申请6个字符长度的空间。只是用6来限制最多可申请的空间。

（3）varchar的实际有效字节数

虽然上文中说varchar的数据最多可表示65535字节，但是在varchar中有1 ~ 3个字节会被用于记录数据大小，因此varchar中的有效字节数应该是65532。至于为什么会是1 ~ 3个字节，是因为varchar的有效字节数是到65535，所以数据量较少时，如只有几十个字节，就可以只用1个字节记录；而如果字节量较大，比如有60000字节，就需要3个字节记录。

当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532 / 3 = 21844（utf8中，1个字符占3个字节）；

当我们的表的编码是gbk时，varchar(n)的参数n最大值是65532 / 2 = 32766（gbk中，1个字符占2个字节）。使用其他的编码时也是一样的。

上文的图中mysql不是提示说varchar的最大字符数为21845么：

为了验证这一点，我们再创建一个表，表中varchar的字符数为21845：

可以看到，尽管我们将varchar的字符个数调整为了21845，mysql依然拦截了。注意，这里的报错是“行太长”，而不是“数据太长”。这就说明，在表中的每一行都是有长度限制的，如果你是修改一个表的varchar的长度，并且这个表中还有其他数据，此时varchar就可能无法修改为21844，需要进一步降低字符长度。这里就不再演示这种情况了。

我们再将21845调整为21844：

此时就可以成功创建了。验证了上文中说的utf8编码时varchar的最大字符数为21844。

最后，char和varchar的区别就可以看成如下图所示：

char由于是一次性开辟指定数量的空间，所以它的效率比较高，但可能有空间浪费。

varchar由于是用多少开多少，所以它的效率比较低，但空间利用率比较高。

至于char和varchar的使用，就需要根据具体情况而定。例如插入的数据长度是一样的，例如是手机号、身份证之类的数据，就可以用char；而如果是姓名、地址这类长度会变化的，就可以用varchar。

4. 日期和时间类型

在mysql中存在日期和时间类型，能够让我们很轻松的保存日期和时间。要注意，这里的日期和时间要区分一下，日期特指年月日，时间则特指时分秒。

常用的日期一般有date、datetime和timestamp三个类型。

在这里面，date中只有日期；datetime中则既有日期也有时间；timestamp则是时间戳，表示的内容也是日期和时间。

为了方便测试，创建如下一个t11表：

然后查看它的详细属性：

可以看到，date和datetime后面什么都没有，但是timestamp后面却带了两个说明，分别表示当前时间戳和更新时间戳。虽然不知道究竟有什么用，但我们可以尝试向这个表中插入数据看一下：

当数据插入成功后，我们查看表的内容后可以发现，明明我们并没有向d3插入数据，但是这个d3中却自己更新了一个时间，并且这个时间就是当前时间。

然后我们再尝试更新一下d1的数据：

可以看到，当我们更新了d1的时间后，d3的时间也自动更新了。这就说明，timestamp类型的时间是会随着表内的数据更新而自动更新的，无需用户自行操作。

这个数据类型就可以用于评论区。例如有人在某个软件上发表了评论，当过了一段时间后，这个想更新他的这个评论，此时就可以用这个数据类型来保存，让mysql自动更新时间：

5. enum和set

enum和set想必大家都不陌生，但是mysql中enum和set与C/C++中的enum和set完全不同，大家不要搞混了。

5.1 enum介绍

enum，即枚举，是一个“单选”类型；

使用形式：

在enum中提供了有若干个选项的值，但是用户最终只能从这些值里面选择一个值填入表中。

enum处于效率的考虑，这些值实际存储的是“数字”。enum中的每个选项都对应一个数字，这些数字可以看成是数组中的下标，只不过是从1开始，最多65535个。

5.2 set介绍

set，即集合，是一个“多选”类型；

使用方式：

在set中提供了若干个选项的值，用户可以从这些值里面选取任意多个值填入表中。

同样的，set也出于效率考虑，这些选项实际存储的也是“数字”。但是set中的数字并不是下标，而可以看成是比特位，所有选项在一开始都视为0组合而成的bit位，当哪一个位置被置1，就说明哪个选项被选了。

例如有三个选项，它们组成000，当变成001时，就是第一个选项被选了；当变成010时，就是第二个选项被选了；当变成111时，就是三个选项都被选了。