【MySQL】数据库的数据类型
作者:mmseoamin日期:2023-12-25

数据类型

  • 一、数据类型分类
  • 二、数值类型
    • 1. tinyint
    • 2. bit
    • 3. 小数类型
      • (1)float
      • (2)decimal
      • 三、字符串类型
        • 1. char
        • 2. varchar
        • 3. varchar 和 char 比较
        • 四、日期和时间类型
        • 五、 enum 和 set

          一、数据类型分类

          mysql 中的数据类型分类如下,其中红色字体的为常用的类型:

          【MySQL】数据库的数据类型,在这里插入图片描述,第1张

          二、数值类型

          【MySQL】数据库的数据类型,在这里插入图片描述,第2张

          mysql 中表的建立属性列:列名称 数据类型,例如 num int,它与我们平时写的语言数据类型是倒过来的!

          1. tinyint

          接下来我们创建一个只有 tinyint 类型的表,然后分别插入 1 和 128,会发现 128 插入失败,因为越界插入了,超出类型范围:

          【MySQL】数据库的数据类型,在这里插入图片描述,第3张

          说明:

          • 在 MySQL 中,整型可以指定是有符号的和无符号的,默认是有符号的;
          • 可以通过 unsigned 来说明某个字段是无符号的;

            例如下图例子,我们创建一个无符号的表,插入 -1 会失败,因为 tinyint 的无符号范围是 0~255:

            【MySQL】数据库的数据类型,在这里插入图片描述,第4张

            其他类型大家可以自己推导,注意:尽量不使用 unsigned,对于 int 类型可能存放不下的数据,int unsigned 同样可能存放不下,与其如此,还不如设计时,将 int 类型提升为 bigint 类型。

            2. bit

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

            接下来我们创建一个 t3 表,数据类型分别为 int 和 八位的 bit 位:create table t3(id int, a bit(8));

            接下来插入一个数字 1 观察:

            【MySQL】数据库的数据类型,在这里插入图片描述,第5张

            我们发现现象,a 的数据 1 没有出现任何东西,其实 bit 字段在显示时,是按照 ASCII码 对应的值显示,例如我们插入 65,其对应的ASCII码 值位 ‘A’:

            【MySQL】数据库的数据类型,在这里插入图片描述,第6张

            如果我们有这样的值,只存放0或1,例如性别,只有男和女,这时可以定义 bit(1),这样可以节省空间:

            【MySQL】数据库的数据类型,在这里插入图片描述,第7张

            如上图,当插入 2 的时候已经越界插入了,因为只有一位 bit 位,只有 0 和 1.

            3. 小数类型

            (1)float

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

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

            【MySQL】数据库的数据类型,在这里插入图片描述,第8张

            如上 -99.992 最后一位四舍五入变为 -99.99 插入:

            【MySQL】数据库的数据类型,在这里插入图片描述,第9张

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

            (2)decimal

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

            例如:

            • decimal(5,2) 表示的范围是 -999.99 ~ 999.99
            • decimal(5,2) unsigned 表示的范围 0 ~ 999.99

              注意:decimal 和 float很像,但是有区别:float 和 decimal 表示的精度不一样。

              例如我们创建一个表:create table t6 (id int, salary1 float(10, 8), salary2 decimal(10, 8));

              再插入数据,查看数据:

              【MySQL】数据库的数据类型,在这里插入图片描述,第10张

              如上图,发现 decimal 的精度更准确,因此如果我们希望某个数据表示高精度,可以选择 decimal.

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

              三、字符串类型

              1. char

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

              例如我们先创建一个表:create table t7 (id int, name char(2));

              再插入数据:

              【MySQL】数据库的数据类型,在这里插入图片描述,第11张

              查看数据:

              【MySQL】数据库的数据类型,在这里插入图片描述,第12张

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

              如果我们在上面的表格中插入大于两个字符的数据,如下,是会越界插入的:

              【MySQL】数据库的数据类型,在这里插入图片描述,第13张

              这也就说明了一个问题,如果我们向 mysql 特定类型中插入不合法的数据,mysql 一般都是直接拦截我们,不让我们做对应的操作!反过来,如果我们已经有数据被成功插入到 mysql 中,一定插入的时候是合法的!所以,mysql 中,一般而言,数据类型本身也是一种约束,所谓约束,就是倒逼用户尽可能进行正确的插入,约束的是用户,这样就能保证数据库中的数据是可预期的,完整的。

              2. varchar

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

              例如:create table t8 (id int, name varchar(6)); 表示 name 可以存放 6 个字符,插入数据如下:

              【MySQL】数据库的数据类型,在这里插入图片描述,第14张

              说明:关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:

              • varchar 长度可以指定为 0 到 65535 之间的值,但是有 1 - 3 个字节用于记录数据大小,所以说有效字节数是 65532;
              • 当我们的表的编码是 utf8 时,varchar(n) 的参数 n 最大值是 65532/3=21844 (因为 utf 中,一个字符占用3个字节),如果编码是 gbk,varchar(n) 的参数 n 最大是 65532/2=32766(因为 gbk 中,一个字符占用2字节);

                我们可以验证一下,我们可以创建一个表,里面包含 varchar 类型并设置大小为 21844,因为字符集默认为 utf8,如下:

                【MySQL】数据库的数据类型,在这里插入图片描述,第15张

                上图验证了 utf8 确实是不能超过 21844.

                我们可以再查看一下当前表的字符集,可以使用指令:show create table t8\G 其中 \G 代表行显示:

                【MySQL】数据库的数据类型,在这里插入图片描述,第16张

                3. varchar 和 char 比较

                例如有 char(4) 和 varchar(4) 两个类型:

                • 当我们存储 ‘abcd’ 的时候,char(4) 占用 4*3=12 个字节;varchar(4) 占用 4 * 3 + 1 = 13个字节;
                • 当我们存储 ‘A’ 的时候,char(4) 占用 4*3=12 个字节;varchar(4) 占用 1 * 3 + 1 = 4个字节;
                • 当我们存储 ‘Abcde’ 的时候,它们都会报错。

                  那么我们该如何选择定长或变长字符串?

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

                  四、日期和时间类型

                  常用的日期有如下三个:

                  • date:日期 ‘yyyy-mm-dd’ ,占用三字节;
                  • datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ,占用八字节;
                  • timestamp:时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致,占用四字节。

                    接下来创建表:create table birthday (t1 date, t2 datetime, t3 timestamp);

                    插入数据:insert into birthday(t1,t2) values('2000-1-1','2000-1-1 01:1:1'); 其中这种插入方式是指定列插入,我们以前用的都是全列插入,即默认全部列都插入,这个我们后面会讲;结果如下:

                    【MySQL】数据库的数据类型,在这里插入图片描述,第17张

                    接下来我们尝试更新数据:update birthday set t1='2000-2-2';

                    【MySQL】数据库的数据类型,在这里插入图片描述,第18张

                    由上图得出,更新数据,t3 列时间戳会更新成当前时间。

                    五、 enum 和 set

                    • enum 语法:

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

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

                      • set 语法:

                          	set:集合,“多选”类型;
                          	set('选项值1','选项值2','选项值3', ...);
                        

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

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

                        例如,有一个调查表 votes,需要调查人的喜好, 比如(看书,游泳,篮球,足球)中去选择(可以多选),(男,女)[单选]:

                        		mysql> create table votes(
                        		    -> name varchar(20),
                        		    -> hobby set('看书', '游泳', '篮球', '足球'),
                        		    -> gender enum('男', '女')
                        		    -> );
                        

                        插入数据并查看数据:

                        【MySQL】数据库的数据类型,在这里插入图片描述,第19张

                        我们可以根据指定信息来查看表中的数据,假设我们再插入一些数据:

                        【MySQL】数据库的数据类型,在这里插入图片描述,第20张

                        假设我们需要筛选出 gender 为女的信息,可以使用语句:select * from votes where gender=2;

                        【MySQL】数据库的数据类型,在这里插入图片描述,第21张

                        假设我们继续添加数据如下:

                        【MySQL】数据库的数据类型,在这里插入图片描述,第22张

                        注意插入多个 hobby 的语法如下:insert into votes values('Jack', '篮球,看书', 2); 注意多个 hobby 使用逗号分割时不要使用空格分开。

                        假设需要筛选出所有爱好有篮球的 name,我们可以:

                        【MySQL】数据库的数据类型,在这里插入图片描述,第23张

                        如上图,但是并不是我们想要的结果,因为 Smith 和 Jack 的 hobby 中也有篮球的选项,所以我们需要使用集合查询,集合查询使用 find_ in_ set 函数。

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

                        使用例如:

                        【MySQL】数据库的数据类型,在这里插入图片描述,第24张

                        【MySQL】数据库的数据类型,在这里插入图片描述,第25张

                        下面我们使用 find_in_set 筛选出所有 hobby 中有篮球选项的 name:select * from votes where find_in_set('篮球', hobby);

                        【MySQL】数据库的数据类型,在这里插入图片描述,第26张