数据类型

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

        一、数据类型分类

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

        

        二、数值类型

        

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

        1. tinyint

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

        

        说明:

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

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

          

          其他类型大家可以自己推导，注意：尽量不使用 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 观察：

          

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

          

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

          

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

          3. 小数类型

          （1）float

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

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

          

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

          

          如果定义的是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));

            再插入数据，查看数据：

            

            如上图，发现 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));

            再插入数据：

            

            查看数据：

            

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

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

            

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

            2. varchar

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

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

            

            说明：关于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，如下：

              

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

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

              

              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'); 其中这种插入方式是指定列插入，我们以前用的都是全列插入，即默认全部列都插入，这个我们后面会讲；结果如下：

                  

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

                  

                  由上图得出，更新数据，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('男', '女')
                      		    -> );
                      

                      插入数据并查看数据：

                      

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

                      

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

                      

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

                      

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

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

                      

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

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

                      使用例如：

                      

                      

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