【mysql】—— 数据类型详解
作者:mmseoamin日期:2023-12-20

序言:

本期我将大家认识关于 mysql 数据库中的基本数据类型的学习。通过本篇文章,我相信大家对mysql 数据类型的理解都会更加深刻。

【mysql】—— 数据类型详解,MySQL标志矢量图 - 设计之家,第1张

目录

(一)数据类型分类

(二)数值类型

1、tinyint类型

2、bit类型

3、小数类型

float类型

decimal

4、字符串类型

char

varchar

char和varchar比较

日期和时间类型

enum和set

总结


(一)数据类型分类

首先,在我们正式的学习数据类型之前,我们需要知道有哪些数据类型在mysql数据库中。因此,接下来我首先带大家认识具体有哪些数据类型。

💨 MySQL中的数据类型可以分为以下几个主要分类:

1️⃣数值类型:

  • 整数类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等。
  • 浮点数类型:FLOAT、DOUBLE等。
  • 定点数类型:DECIMAL。

    2️⃣字符串类型:

    • 固定长度字符串:CHAR。
    • 可变长度字符串:VARCHAR。
    • 大文本类型:TEXT。

      3️⃣日期和时间类型:

      • DATE:日期。
      • TIME:时间。
      • DATETIME:日期和时间。
      • TIMESTAMP:时间戳。

        4️⃣ 布尔类型:

        • BOOL、BOOLEAN。

          5️⃣枚举和集合类型:

          • ENUM:从预定义值列表中选择一个值。
          • SET:从预定义值集合中选择多个值。

            6️⃣二进制类型:

            • BLOB:用于存储二进制数据。

              7️⃣ JSON类型:用于存储和处理JSON格式的数据。

               8️⃣空间数据类型:用于处理地理空间数据。

              最后,我给大家整理总结了一张表,大家可以直观的去感受(对于其中红色的是需要大家掌握的,其余的等到需要用到的时候在学习不迟!!)

              【mysql】—— 数据类型详解,MySQL的数据类型都有哪些?它们分别用在什么的场景?_人山人海肉最可爱的博客-CSDN博客_数据类型有哪几种,第2张

              【小结】

              这些数据类型可以根据需求在MySQL表的列定义中使用。在选择数据类型时,需要考虑存储需求、数据的精度要求、操作和计算的效率等因素。此外,MySQL还支持一些特殊的数据类型和存储引擎相关的数据类型,如GEOMETRY、POINT、XML等,可以根据具体需求选择使用。


              (二)数值类型

              下面是一些常用的数据类型值:

              【mysql】—— 数据类型详解,MySQL常见数据类型详解_mysql中 数值数据类型包括2种,分别是( )数据类型和( )数据类型。_你走开``的博客-CSDN博客 ,第3张


              1、tinyint类型

              MySQL中的 TINYINT 类型是一种整数类型,用于存储小整数值,占用1个字节的存储空间。

              下面是关于TINYINT类型的详解

              数据范围和存储空间:

              • 有符号(默认):范围为-128到127,占用1字节的存储空间
              • 无符号:范围为0到255,占用1字节的存储空间

                用途:

                • TINYINT通常用于存储布尔值(0或1),表示真或假。
                • 也可以用于存储较小的整数值。

                  有符号和无符号:

                  • 默认情况下,TINYINT是有符号的,范围为 [-128,127]
                  • 可以使用 UNSIGNED 关键字来声明TINYINT为无符号,范围变为【0,255】

                    存储方式:

                    • TINYINT类型的值以二进制格式存储。对于有符号类型,最高位(最左边的位)表示正负号,‘0’ 表示正数,‘1’表示负数
                    • 在使用时,MySQL会根据需要将二进制值转换为可读的整数形式。

                      💨 示例:

                      • 数值越界测试:

                        ①创建一个TINYINT列:

                        【mysql】—— 数据类型详解,第4张

                         

                        ②插入数据:

                        【mysql】—— 数据类型详解,第5张

                         

                         ③查询数据:

                        【mysql】—— 数据类型详解,第6张

                        【小结】 

                        •  上述,我们已经成功的进行了相关的测试。

                          但是接下来,我在插入这样的数据,看最终是否还能成功的插入呢?

                          【mysql】—— 数据类型详解,第7张

                           【说明】

                          1. 在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
                          2. 可以通过UNSIGNED来说明某个字段是无符号的
                          • 无符号案例: ①创建一个TINYINT列:

                            【mysql】—— 数据类型详解,第8张

                             ②插入数据(注意范围):

                            【mysql】—— 数据类型详解,第9张

                             ③查询数据:

                            【mysql】—— 数据类型详解,第10张

                             

                             

                            【注意】
                            • 尽量不使用unsigned,对于int类型可能存放不下的数据,int unsigned同样可能存放不下,与其如此,还不如设计时,将int类型提升为bigint类型。
                              【小结】 总之,TINYINT类型是MySQL中的一种整数类型,适用于存储布尔值或较小的整数。 它占用较少的存储空间,查询和计算效率高。可以根据需要选择是否使用有符号或无符号的TINYINT类型。

                              2、bit类型

                              MySQL中的BIT类型是一种用于存储位字段(bit fields)的数据类型。

                              下面是关于BIT类型的详解

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

                                存储空间:

                                • BIT数据类型占用的存储空间取决于定义时指定的位数,最大位数为64

                                  位数限制:

                                  • BIT类型可以定义的位数可以是1到64之间的任意整数

                                    存储方式:

                                    • BIT类型的数据以二进制形式进行存储

                                      使用场景:

                                      • BIT类型通常用于表示布尔值或位掩码。
                                      • 可以用于存储二进制标志,每个位表示一种状态,如开/关、启用/禁用等。

                                        查询和计算操作:

                                        • 查询和计算BIT类型的数据需要使用相关的位运算操作符,如按位与(&)、按位或(|)、按位异或(^)等

                                          默认值:

                                          • BIT类型的默认值‘1’,也可以使用b'xxx'或0bxxx的形式指定具体的二进制值

                                            💨 示例:

                                            ①创建一个 bit 列:

                                            【mysql】—— 数据类型详解,第11张

                                             

                                             ②插入数据(注意范围):

                                            【mysql】—— 数据类型详解,第12张

                                              ③查询数据:

                                            【mysql】—— 数据类型详解,第13张

                                             【说明】

                                            1. 此时我们会发现很怪异的现象,a的数据10没有出现;
                                            2. 其实 bit 字段在显示时,是按照ASCII码对应的值显示,因此当我们插入 10之后没有任何显示

                                            【mysql】—— 数据类型详解,第14张

                                             

                                            【注意】

                                            如果我们有这样的值,只存放 0 或 1 ,这时可以定义 bit(1) 。这样可以节省空间

                                            【mysql】—— 数据类型详解,第15张

                                             

                                            【小结】

                                            总之,BIT类型是用于存储位字段的数据类型,可以指定位数范围,并以二进制形式进行存储。

                                            它适用于存储布尔值或位掩码,并可以使用位运算操作符进行查询和计算操作。

                                            需要注意的是:在操作BIT类型数据时,需要使用适当的位运算操作符来处理位级别的操作。


                                            3、小数类型

                                            float类型

                                            MySQL中的FLOAT类型是一种浮点数类型,用于存储单精度浮点数。

                                            下面是有关FLOAT类型的详解

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

                                              存储范围和精度:

                                              • FLOAT类型占用4字节的存储空间。
                                              • 可以存储的范围为 [-3.402823466E+38,-1.175494351E-38]、[0]、[1.175494351E-38,3.402823466E+38] 之间的浮点数。
                                              • 最多可存储7位小数。

                                                精度和舍入错误:

                                                • FLOAT类型是近似值类型,并且以二进制形式存储。
                                                • 存在舍入错误的可能性,这意味着在进行计算时可能会出现轻微的精度损失。
                                                • 比较FLOAT类型的值时,可能需要使用误差范围进行判断,而不是简单的等于比较。

                                                  默认值:

                                                  • FLOAT类型的默认值为 ‘0’

                                                    查询和操作:

                                                    • FLOAT类型可以使用算术运算符(如加法、减法、乘法、除法等)进行计算。
                                                    • 可以与其他数值类型进行混合运算,MySQL会自动进行类型转换。

                                                      存储方式:

                                                      • FLOAT类型的值以二进制格式存储,并使用IEEE 754标准表示。
                                                      • 在使用时,MySQL会根据需要将二进制值转换为可读的浮点数形式。

                                                        💨 示例1:

                                                        • 小数:float(4,2)表示的范围是-99.99 ~ 99.99,MySQL在保存值时会进行四舍五入。

                                                          【mysql】—— 数据类型详解,第16张

                                                           

                                                          💨 示例2:

                                                          • 如果定义的是float(4,2) unsigned 这时,因为把它指定为无符号的数,范围是 0 ~ 99.99

                                                            【mysql】—— 数据类型详解,第17张

                                                             

                                                            【小结】

                                                            1、总之,FLOAT类型是MySQL中的一种浮点数类型,适用于存储具有小数部分的数值。

                                                            2、它占用较少的存储空间,并提供了一定的范围和精度。

                                                            3、然而,在进行浮点数计算时,需要注意舍入误差和精度损失可能带来的影响。如果需要更高的精度,可以考虑使用DOUBLE类型。


                                                            decimal

                                                            MySQL中的DECIMAL类型是一种精确数值类型,用于存储固定精度的小数值

                                                            下面是关于DECIMAL类型的详解

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

                                                              【说明】

                                                              1. decimal(5,2) 表示的范围是 -999.99 ~ 999.99
                                                              2. decimal(5,2) unsigned 表示的范围 0 ~ 999.99
                                                              3. decimal和float很像,但是有区别: float和decimal表示的精度不一样

                                                              存储空间:

                                                              • DECIMAL类型的存储空间取决于定义时指定的精度和小数位数

                                                                精度和小数位数:

                                                                • DECIMAL类型需要指定精度和小数位数,例如DECIMAL(10, 2),其中10表示总位数,2表示小数位数;
                                                                • 精度范围为1到65,默认精度为10,默认小数位数为0

                                                                  范围:

                                                                  • DECIMAL类型可以存储从-10^(精度-小数位数)+1到10^(精度-小数位数)-1的数值

                                                                    存储方式:

                                                                    • DECIMAL类型使用定点表示法进行存储,以确保精确性

                                                                      精确计算:

                                                                      • DECIMAL类型的计算是精确的,不会有舍入误差

                                                                        使用场景:

                                                                        • DECIMAL类型适用于需要对小数进行精确计算的场景,如货币金额、精确统计数据等。
                                                                        • 由于DECIMAL类型的存储和计算是精确的,它比FLOAT和DOUBLE类型更适合处理需要高精度的小数值。

                                                                          💨 示例:

                                                                          【mysql】—— 数据类型详解,第18张

                                                                          【说明】

                                                                          1. float表示的精度大约是7位;
                                                                          2. decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略,默认为0.如果m被省略, 默认是10。

                                                                          💨 建议:如果希望小数的精度高,推荐使用decimal。

                                                                          【小结】

                                                                          1、总之,DECIMAL类型是MySQL中的一种精确数值类型,用于存储固定精度的小数值。

                                                                          2、它提供了精确的计算和存储,并适用于需要高精度和准确性的场景。

                                                                          3、在定义DECIMAL列时,需要指定精度和小数位数,以满足具体的业务需求。


                                                                          4、字符串类型

                                                                          char

                                                                          MySQL中的CHAR类型是一种固定长度的字符串类型,用于存储定长的字符数据。

                                                                          下面是关于CHAR类型的详解

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

                                                                            存储空间:

                                                                            • CHAR类型占用固定长度的存储空间,占用的字节数取决于定义时指定的长度

                                                                              长度限制:

                                                                              • CHAR类型需要在定义时指定长度,长度范围为1到255个字符,默认长度为1

                                                                                填充空格:

                                                                                • 如果存储的字符串长度小于定义的长度,将使用空格填充剩余的位置

                                                                                  查询效率:

                                                                                  • 由于CHAR类型是固定长度的,查询效率比可变长度的字符串类型(如VARCHAR)更高。因为在检索和比较时,不需要计算实际存储的字符串长度

                                                                                    存储方式:

                                                                                    • CHAR类型的字符串以定长的方式进行存储,不受实际存储内容的影响

                                                                                      使用场景:

                                                                                      • 适用于存储长度固定的字符串,如国家代码、性别、状态等。
                                                                                      • CHAR类型通常用于存储较短的字符串,而不适用于存储可变长度的大文本。

                                                                                        💨 示例:

                                                                                        【mysql】—— 数据类型详解,第19张

                                                                                         

                                                                                        【说明】
                                                                                        • char(2) 表示可以存放两个字符,可以是字母或汉字,但是不能超过2个, 最多只能是255

                                                                                          【mysql】—— 数据类型详解,第20张

                                                                                           

                                                                                          【小结】

                                                                                          1、CHAR类型是MySQL中的一种固定长度字符串类型,适用于存储定长的字符数据。

                                                                                          2、它占用固定的存储空间,效率较高,并且适合存储长度不变的字符串。

                                                                                          3、在定义CHAR列时,需要指定长度以满足具体的业务需求。


                                                                                          varchar

                                                                                          MySQL中的VARCHAR类型是一种可变长度的字符串类型,用于存储可变长度的字符数据。

                                                                                          下面是关于VARCHAR类型的详解

                                                                                          • 语法:
                                                                                            varchar(L): 可变长度字符串,L表示字符长度,最大长度65535个字节

                                                                                            存储空间:

                                                                                            • VARCHAR类型占用实际存储的字节数,根据存储的字符串长度而变化

                                                                                              长度限制:

                                                                                              • VARCHAR类型需要在定义时指定最大长度,长度范围为1到65535个字符,默认长度为1

                                                                                                存储效率:

                                                                                                • 由于VARCHAR类型是可变长度的,存储的实际字节数取决于存储的内容长度,对于较短的字符串,占用的存储空间较小

                                                                                                  查询效率:

                                                                                                  1. 由于VARCHAR类型是可变长度的,查询效率相对于CHAR类型可能稍低;
                                                                                                  2. 因为在检索和比较时,需要计算实际存储的字符串长度。

                                                                                                  存储方式:

                                                                                                  • VARCHAR类型的字符串以变长方式进行存储,只占用实际存储内容所需的空间

                                                                                                    使用场景:

                                                                                                    1. 适用于存储长度可变的字符串,如用户名、电子邮件地址、文章内容等。
                                                                                                    2. VARCHAR类型通常用于存储较长的字符串,尤其是长度不确定的情况。

                                                                                                    💨 示例:

                                                                                                    【mysql】—— 数据类型详解,第21张

                                                                                                     

                                                                                                    【说明】
                                                                                                    1. 关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:
                                                                                                    2. varchar长度可以指定为 [0,65535] 之间的值,但是有1 - 3 个字节用于记录数据大小,所以说有效字节数是 65532。
                                                                                                    3. 当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844[因为utf中,一个字符占 用3个字节];
                                                                                                    4. 如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符 占用2字节)

                                                                                                    【mysql】—— 数据类型详解,第22张

                                                                                                     

                                                                                                    【小结】

                                                                                                    1、总之,VARCHAR类型是MySQL中的一种可变长度字符串类型,适用于存储可变长度的字符数据

                                                                                                    2、它根据实际存储内容的长度来占用存储空间,效率较高,并适用于存储长度不确定的字符串。

                                                                                                     


                                                                                                    char和varchar比较

                                                                                                    我给大家整理了一张表格帮助大家理解记忆:

                                                                                                    【mysql】—— 数据类型详解,第23张

                                                                                                     

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

                                                                                                      日期和时间类型

                                                                                                      MySQL中提供了多种日期和时间类型,用于存储日期、时间和日期时间数据

                                                                                                      下面是关于MySQL日期和时间类型的详解

                                                                                                      1. DATE类型:用于存储日期值(年、月、日),格式为'YYYY-MM-DD'。范围从'1000-01-01'到'9999-12-31'。

                                                                                                      2. TIME类型:用于存储时间值(时、分、秒),格式为'HH:MM:SS'。范围从'-838:59:59'到'838:59:59'。

                                                                                                      3. DATETIME类型:用于存储日期和时间值,格式为'YYYY-MM-DD HH:MM:SS'。范围从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

                                                                                                      4. TIMESTAMP类型:与DATETIME类型类似,但范围限制为'1970-01-01 00:00:01'到'2038-01-19 03:14:07'。通常用于记录某条数据的创建或修改时间。

                                                                                                      5. YEAR类型:存储年份值,格式为'YYYY'。范围从1901到2155。

                                                                                                      6. INTERVAL类型:用于表示时间间隔或时间差,可以与日期或时间类型相加或相减。

                                                                                                      💨 示例:

                                                                                                      【mysql】—— 数据类型详解,第24张

                                                                                                       

                                                                                                      【小结】

                                                                                                      1、MySQL提供了多种日期和时间类型,可根据具体需求选择合适的类型来存储日期、时间和日期时间数据。


                                                                                                      enum和set

                                                                                                      MySQL中的ENUM和SET类型是用于存储具有固定取值的数据的列类型

                                                                                                      下面是关于ENUM和SET类型的详解

                                                                                                      • 语法:
                                                                                                        enum:枚举,“单选”类型;
                                                                                                        enum('选项1','选项2','选项3',...);

                                                                                                        【说明】

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

                                                                                                          【说明】

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

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

                                                                                                          💨 示例:

                                                                                                          有一个调查表 votes ,需要调查人的喜好, 比如(登山,游泳,篮球,武术)中去选择 ( 可以多选 ) , (男,女)[ 单选 ]

                                                                                                          【mysql】—— 数据类型详解,第25张

                                                                                                          •  插入数据:

                                                                                                            【mysql】—— 数据类型详解,第26张

                                                                                                            •  有如下数据,想查找所有喜欢登山的人:

                                                                                                              【mysql】—— 数据类型详解,第27张

                                                                                                              •  我们可以发现不能查询出所有,爱好为登山的人 集合查询使用 find_ in_ set 函数: find_in_set(sub,str_list) :
                                                                                                                1. 如果 sub 在 str_list 中,则返回下标;如果不在,返回0;
                                                                                                                2. str_list 用逗号分隔的字符串。
                                                                                                                mysql> select find_in_set('a', 'a,b,c');
                                                                                                                +---------------------------+
                                                                                                                | find_in_set('a', 'a,b,c') |
                                                                                                                +---------------------------+
                                                                                                                | 1 |
                                                                                                                +---------------------------+
                                                                                                                mysql> select find_in_set('d', 'a,b,c');
                                                                                                                +---------------------------+
                                                                                                                | find_in_set('d', 'a,b,c') |
                                                                                                                +---------------------------+
                                                                                                                | 0 |
                                                                                                                +---------------------------+

                                                                                                                查询爱好登山的人:

                                                                                                                【mysql】—— 数据类型详解,第28张

                                                                                                                 


                                                                                                                总结

                                                                                                                以上便是mysql数据类型的全部知识。感谢大家的支持与观看!!!

                                                                                                                【mysql】—— 数据类型详解,第29张