【MySQL数据库】：MySQL数据类型

目录

​编辑 数值类型

tinyint 类型

bit类型 

 小数类型

float类型

decimal类型 

 字符串类型

char类型

varchar类型

char和varchar区别

时间日期类型

enum和set类型

 enum和set类型数字插入的区别

enum和set查找

---

 

数据类型的作用

• 决定了存储数据时应该开辟的空间大小。
• 决定了如何识别一个特定的二进制序列。
• 决定了数据的取值范围。

 数值类型

tinyint 类型

有符号tinyint范围测试

创建一个表 test1，表当中包含一个tinyint类型的列，默认其为有符号类型。 

 由于tinyint类型占用1字节，因此有符号tinyint的取值范围为-128~127，插入该范围内的数据时都能成功插入。

 

 如果插入的数据不在-128~127范围内，那么插入数据时就会产生报错。

 无符号tinyint范围测试

创建一个表 test2 ，表当中包含一个tinyint类型的列，并指定其为无符号类型。

 由于tinyint类型占用1字节，因此无符号tinyint的取值范围为0~255，插入该范围的数据时都能成功插入。

如果插入的数据不在0~255范围内，那么插入数据时就会产生报错。

建议

除非场景要求数值类型必须是无符号，否则尽量不要使用无符号，因为有符号的数值类型存不下的数据，其对应的无符号类型同样可能存不下，这时应该直接将数值类型进行提升。

 总结

• 如果我们向mysql中特定的类型中插入不合法的数据， mysql一般是直接拦截我们，不让我们做对应的操作！！！
• 反过来，如果我们已经有数据被成功插入到mysql中了，一定是插入的时候是合法的！！！
• 所以mysql中一般而言，数据类型本身也是一种约束！！！ 

bit类型 

基本语法

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

创建一个表 test3，表当中包含一个int类型的id列和一个8位bit类型的a列。 

插入数据！！！#发现很怪异的现象，a的数据10没有出现

根本原因是因为bit类型在显示时，是按照ASCII码对应的值进行显示的，而在ASCII码表中10对应的是控制字符LF，表示换行的意思。 

如果向表中插入记录时指定id和a的值均为65，由于ASCII码表中65对应的是字符A，因此插入记录后查看表就会发现a的值显示的是A。

建议：

• 虽然MySQL提供了位类型bit，但一般不建议将数据类型设置成位类型，除非将来这个数据本身就只是给程序看的，并且数据本身非常占用资源。
• 因为查询位类型数据时，默认会按照ASCII码对应的值进行显示，这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的。

 小数类型

float类型

基本语法

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

 创建一个表 test4，表当中包含一个int类型的列 和 float(4,2)类型的列，默认其为有符号类型。

根据语法我们可以得出float(4,2)的取值范围是在-99.99到99.99之间的！！！

 由于MySQL在保存值时会进行四舍五入，因此实际可插入float(4,2)的范围为-99.994~99.994，如果插入的数据不在该范围内，那么插入数据时就会产生报错。

我们将float的限制去掉！！！

 我们再次插入一组数据，发现出现精度丢失的情况。

 建议：

• 可以用于表示小数数据，在一些对精度要求不是非常高的场景下较为常用。
• 精度相对较低，在进行一些复杂的数学计算或需要高精度的场景下可能会出现精度丢失的情况。
• 例如，进行一些复杂的金融计算时，可能不太适合直接使用 float 类型，而需要考虑更高精度的类型如 decimal 等。

decimal类型 

基本语法

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

创建一个表 test5，表当中分别包含一个int类型的列以及float(10,8)的列和一个decimal(10,8)的列。！！！

向表当中插入一条记录，指定float和decimal的值均为52.12345678！！！

 

 但最终查表时会发现decimal保持了数据的原貌，而float则会存在一定的精度损失。

建议：

• 能保证数值计算的准确性，减少精度丢失的风险。
• 对于需要精确表示货币金额等数据非常适用。
• 相比float等类型，可能会占用更多的存储空间。

 字符串类型

char类型

基本语法：

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

创建一个表，表当中包含一个char(255)的content列。

修改成为一个char(256)的contant列，修改失败，说明char插入能够存储的最大的字符为255。

修改成为一个char(6)的contant列！！！

char(6)中最多可存储6个字符，因此只要插入的字符个数不超过6个都是能够成功插入的。如果插入的字符个数超过了6个，那么在插入数据时就会产生报错。

 

注意：这里所说的字符并不只是指一个英文字母，一个汉字也是一个字符，因此只要插入的汉字个数不超过6个也是可以插入的。 

总结：

• 对于长度固定且已知的字符串数据，使用方便。
• 处理速度快。
• 如果实际字符串长度小于指定长度，会造成一定的存储空间浪费。

varchar类型

基本语法

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

• varchar的使用基本相同但是本质有差别！！！
• varchar相当于STL中的string，你要多少给你多少。
• char相当于数组一次性直接申请！！！

varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。

而varchar类型可指定的字符个数上限，与表的编码格式有关：

• 对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为 65532 / 3 = 21844 
• 对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为 65532 / 2 = 32766 

char和varchar区别

• char类型可存储字符上限为255，varchar类型可存储字符上限与表的编码格式有关。
• char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。
• varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。

如何去使用char和varchar呢？ 

varchar：

若要存储固定的 身份证号、电话号等等，使用 char 更合适；

而如果要存储用户的评论、家庭住址等等，长度不固定且差异较大，使用 varchar(200) 等更合适，可以避免大量的空间浪费。

• char：

• 长度固定：不管实际存储的内容长度如何，都会占用定义好的固定长度的存储空间。
• 存储效率高：因为长度固定，在某些操作时效率相对较高。
• 可能浪费空间：当实际内容较短时，会存在空间浪费。
• 长度可变：只占用实际内容长度所需要的存储空间加上一些额外用于记录长度的字节。
• 节省空间：在存储内容长度变化较大时，能更有效地利用空间。
• 操作相对复杂：由于长度不固定，在某些情况下可能会有一些额外的处理开销。

时间日期类型

常用的三种时间日期类型如下：

• date：日期格式为YYYY-MM-DD，占用三字节。
• datetime：时间日期格式为YYYY-MM-DD HH:MM:SS，占用八字节。
• timestamp：时间戳，格式为YYYY-MM-DD HH:MM:SS，占用四字节。

创建一个表test7，表当中包含date、datetime和timestamp三种时间日期类型的列。

查看表结构可以看到，timestamp类型的t3列是不允许为空的，它的默认值CURRENT_TIMESTAMP。 

我们添加时间的时候t3会自动更新时间戳！！！

timestamp类型常见的使用场景！！！

创建一个评论表，表当中包含评论人的昵称、评论的内容和评论的发布时间。

 向评论表中插入记录时，只需要指明评论人的昵称和评论的内容，评论的发布时间默认会设置成该记录的插入时间。

如果评论人修改了评论内容，那么就需要对评论表进行更新，更新表的同时评论的发布时间也会更新为修改表的时间。

enum和set类型

enum和set类型的区别如下：

• 在定义enum字段时需要提供若干个选项的值，在设置enum字段值时只允许选取其中的一个值。
• 在定义set字段时需要提供若干个选项的值，在设置set字段值时可以选取其中的一个或多个值。

比如人的性别只能从男和女中进行二选一，因此可以定义成enum类型，而人的爱好在提供的选项中可能存在多个，因此可以定义成set类型。

enum和set类型最常见在调查表案例！！！

 创建一个调查表，表当中包含被调查人的姓名、性别和爱好。

向表中插入记录时，被调查人的性别只能从男和女中进行二选一，被调查人的爱好可以从提供的若干个选项中进行多选一或多选多，多个爱好之间需要通过英文逗号隔开。

通过数字设置enum 

在插入记录时，除了通过指明男女来设置性别，还可以通过插入数字1和2来设置性别。

根本原因在于，MySQL出于效率考虑，在存储enum值时实际存储的都是数字，enum中提供的选项值依次对应数字1、2、3、…，最多65535个，因此在设置enum值时可以通过数字的方式进行设置。

通过数字设置set 

在插入记录时，除了通过指明多个选项来设置爱好，还可以通过数字的方式来设置。

因为MySQL存储set值时实际存储的也是数字，set中提供的选项值依次对应数字1、2、4、8、…，最多64个，因此在设置set值时可以通过数字的方式进行设置。

 enum和set类型数字插入的区别

• enum数字插入利用的是下标！！！
• set类型数字插入利用的是位图，例如1就是第一个，2就是第二个，3就是第一个加第二个，4就是第三个，5就是第三个加第一个。

建议：

• 虽然enum和set可以通过数字的方式进行设置，但严重不推荐这种做法，因为这样的SQL可读性太差，导致后期维护成本变高。

enum和set查找

如果想要筛选出调查表中所有女同志的信息，那么直接在筛选时指明gender='女'即可，因为enum类型的值只能多选一。

但如果要筛选出调查表中爱好包含篮球的人的信息就比较麻烦了，如果继续使用上述方式，那么最终筛选出来的是爱好仅为敲代码的人的信息。

这时需要借助find_in_set(str,strlist)函数，该函数的作用是查询strlist中是否包含str，如果包含则返回str在strlist中的位置（从1开始），否则返回0。

通过select可以对find_in_set函数进行验证，依次查找集合a,b,c中是否包含字符a、b、d，这时在查找字符a和b时就会得到其在集合中的下标，而在查找字符d时就会得到0值。

这时就可以通过select搭配find_in_set函数，来筛选出爱好包含篮球的人的信息了。

 此外，我们还可以继续添加筛选条件，比如筛选出爱好中包含篮球，但爱好又不仅仅是篮球的人的信息！！！