【MySQL】--- 数据类型

Welcome to 9ilk's Code World

(๑•́ ₃ •̀๑) 个人主页: 9ilk

(๑•́ ₃ •̀๑) 文章专栏： MySQL

🏠 数据类型分类

MySQL是一套整体的对外数据存取方案,既然要存取数据,而数据有不同的类型,因此MySQL也存在不同的数据类型,有不同的用途:

🏠 数值类型

📌 整数类型

MySQL中，整数类型包括tinyint,smallint,bigint等，他们分别占据不同的字节。
在MySQL中，整形可以指定有符号和无符号的，默认有符号。我们可以根据其所占字节数和符号位确定他们相应的取值范围。
可以通过UNSIGNED来说明某个字段是无符号的。

（1）有符号tinyint数值范围测试

create table if not exists t1(num tinyint);
insert into t1 values (-128); //最小值
insert into t1 values (127);  //最大值
insert into t1 values (0);    //中间值
insert into t1 values (1);
insert into t1 values (-1);

测试结果：

insert into t1 values (-129);
insert into t1 values (128);
insert into t1 values (130);

测试结果：

通过测试我们可以得到以下结论：

1. MySQL中进行特定类型数据插入时，如果插入超出指定类型的数据范围，MySQL会直接拦截。

2. 与语言的区别：在语言中，比如char a = 1234567;此时超出数据范围只会报警，因为会发生截断；而在MySQL中，如果我们插入特定类型不合法数据，它一般是直接拦截，MySQL不能帮我们进行截断，因为需要保证插入数据的完整性，否则用户不信任我们的插入数据。

3. 反过来，数据插入到MySQL中，那么插入的数据一定合法；因此MySQL中，一般而言，数据类型本身也是一种天然的约束！

4. 数据类型是一种约束，约束使用者尽可能进行正确插入；即使你不是一个很好的使用者，它也能保证数据插入的合法性。

5. 一个表中设置足够多约束能保证数据库中的数据是可预期的(比如表中tinyint类型数据一定是-128~127，不可能是129)，完整的(没有发生截断)。

6.补充细节：mysql中建立属性列一般是列名称在前类型在后；反过来就是C/C++的类型定义格式。

（2）tinyint无符号数值范围测试

create table if not exists t2(num tinyint unsigned);
insert into t2 values (0);   //最小值
insert into t2 values (255); //最大值
insert into t2 values (100); //中间值
insert into t2 values (-1);
insert into t2 values (256); //临界

测试结果：

我们可以通过unsigned说明某个字段是无符号的，遇到不合法的同样会发生拦截。
尽量不要使用unsigned,对于int类型可能存放不下的数据，int unsigned同样可能存不下，这样不如设计时将int提升为bigint。
但其实能不能用UNSIGNED取决于你的应用场景，比如你的年龄不能用负数表示。

Q ：为什么MySQL要提供这么多大小不一的整形类型？

答：其实这是在满足应用场景和节省资源之间做平衡。比如年龄你用tinyint就够了，因为人最多是100来岁，当然你也可以用更大的，只不过没有必要，而且数据量大会浪费空间。

📌 bit类型

（1）语法

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

可以定义1-64个bit位。

（2）示例

create table if not exists t3(
   id int,
   online bit(1)
);
// 使用一个比特位来表示用户是否在线。由于只有一个比特位，只能插入0或1
insert into t3 (id,online) values (123,0);
insert into t3 (id,online) values (123,1);
insert into t3 (id,online) values (123,3);
insert into t3 (id,online) values (123,5);

测试结果：

bit类型不能插入超出bit位个数最大能表示的数据。

（3）bit类型的显示问题

select * from t1

测试结果(MySQL5.0x版本)：

我们发现在显示的时候online这一列并没有显示什么，其实这是因为以前版本的MySQL显示位类型是以ascii码的形式呈现的,我们可以使用hex函数将bit类型数据转化成十六进制字符串显示。

验证按照ascii码形式显示：

alter table t3 modify online bit(10);
insert into t3 (id,online) values(123,'a');
insert into t3 (id,online) values(123,97);

测试结果：

但是在现代版本的MySQL中其实是直接以十六进制的形式显示bit类型的字段的：

总结：

1. bit类型可以自由去定义bit位，可以帮我们节省空间。

2. 以前版本的MySQL显示bit类型字段是以ascii码形式呈现的，我们可以使用hex函数转化成十六进制字符串。

3. bit类型最多取64个bit位，超出会报错。

🏠 小数类型

📌 float

（1）语法

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

（2）示例

create table if not exists t5( id int, salary float(4,2) );
insert into t5 (id,salary) values(1,99.99); //最大
insert into t5 (id,salary) values(2,-99.99);//最小
insert into t5 (id,salary) values(3,12.34);//中间
insert into t5 (id,salary) values(4,99.999); //边界
//边界
insert into t5 (id,salary) values(4,99.999);
insert into t5 (id,salary) values(4,-99.999);
insert into t5 (id,salary) values(4,-999.99);
insert into t5 (id,salary) values(4,-100.0); //精度不满足2位 100.00长度超过4
//10.0其实是10.00 要满足精度是两位的规定
insert into t5 (id,salary) values(4,10.0);

测试结果：

float(4,2)表示的范围是-99.99 ~ 99.99，需要同时满足数据范围，长度是4和精度是2的条件。

insert into t5 (id,salary) values(5,23.45);
insert into t5 (id,salary) values(6,23.456);
insert into t5 (id,salary) values(6,23.454);

测试结果：

这说明实际上在进行浮点数存储时，尤其是double/float，存入时如果存的精度超出了规定精度，MySQL并不是直接拦截你，而是给我们采用四舍五入的方式存储数据。

insert into t5 (id,salary) values(6,99.991);
insert into t5 (id,salary) values(6,99.993);
insert into t5 (id,salary) values(6,99.994);
insert into t5 (id,salary) values(6,99.995);

测试结果：

前面三个插入符合预期因为四舍五入后是99.99，如果是99.995之类的就会变成100.00不合法，所以四舍五入之后不合法的会直接拦截。五入也是有条件的！

（3）无符号浮点数

create table if not exists t6( 
       id bigint,
   salary float(4,2) unsigned
);
insert into t6 (id,salary) values(1,0);        
insert into t6 (id,salary) values(1,99.99);
insert into t6 (id,salary) values(1,99.996);//不合法
insert into t6 (id,salary) values(1,999.9); //不合法
insert into t6 (id,salary) values(1,-99.9); //不合法
insert into t6 (id,salary) values(1,-0.01); //不合法

测试结果：

如果将一个float定义成unsigned，它就将负数的部分认为是非法数据，只保留[0,99.99];

（4）float精度损失

浮点数存储时是可能存在精度损失的：

alter table t5 modify salary float;
insert into t5 (id,salary) values(1,32273.123193);
insert into t5 (id,salary) values(1,3234.2389);
insert into t5 (id,salary) values(1,3232983032.328);
insert into t5 (id,salary) values(1,32329345.21);

测试结果：

浮点数在存取的时候精度太多是可能会存在精度损失的。float的精度大约是7位。

总结：

1. float定义时需要指明长度和精度，插入时需要满足数据范围，长度和精度。

2. 浮点数类型遇到精度超出规定精度的情况，并不会直接拦截你，而是会进行四舍五入，如果四舍五入后合法就插入数据，否则拦截。

3.浮点数无符号的取值范围是直接将有符号的负数部分视为不合法数据。

4.浮点数精度太多可能存在精度损失的问题。

📌decimal

（1）语法

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

decimal(5,2) 表示的范围是 -999.99 ~ 999.99。
decimal(5,2) unsigned 表示的范围 0 ~ 999.99。
float和decimal表示的精度不一样。
decimal整数最大位数m为65，支持小数最大位数d是30。如果d被忽略，则默认为0；如果m被省略，则默认是10。当然不同版本的MySQL可能会有差别。

（2）示例

create table if not exists t7(
    f1 float(10,8),
    f2 decimal(4,2)
);
insert into t7 (f1,f2) values (10.0,99.99);
insert into t7 (f1,f2) values (10.0,-99.99);
insert into t7 (f1,f2) values (10.0,-99.999); //不合法
insert into t7 (f1,f2) values (10.0,99.994);
insert into t7 (f1,f2) values (10.0,23.935);

测试结果：

decimal和float double是一样的，也是要合法以及会进行四舍五入。

alter table t7 modify f2 decimal (10,8); //设置decimal和float同样精度
insert into t7 (f1,f2) values(23.12345612,23.12345612);

测试结果：

float在精度过大时会帮我们做优化做调整，但decimal不会，它的精度更准；未来如果你的使用场景对精度要求不高使用float，精度要求高就可以使用decimal。

🏠 字符串类型

📌 char

（1）语法

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

（2）示例

create table if not exists t8(
  id int,
  name char(2)
);
insert into t8 (id,name) values (1,'a');
insert into t8 (id,name) values (1,'ab');
insert into t8 (id,name) values (1,'abc'); //超出长度

测试结果：

insert into t8 (id,name) values (1,'中');
insert into t8 (id,name) values (1,'中国');
insert into t8 (id,name) values (1,'中国人');

测试结果：

Q：一个汉字按我们之前在C/C++的经验来讲在特定编码下一个汉字有的不是两个字节吗，为什么我这里可以插入两个汉字，四个字节，三个汉字就不行了？

答：MySQL中的字符跟语言的不同，它真正代表的是一种符号，char(2)表示可以存放两个字符，要么字母要么汉字，一个汉字就是一个字符。

create table if not exists t9（id int,addr varchar(256));

测试结果：

最多是255个字符。注意单位是字符而不是字节。

总结：

1. char后面括号里填的是固定字符串的上限，一旦定义好之后会一次性把空间开辟好。

2. 使用多少由使用者决定，上限多少由L决定。

3.char不允许插入超过L长度的数据，且注意区分MySQL中的字符。

📌 varchar

（1）语法

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

（2）示例

create table if not exists t9( id int, name varchar(6));
insert into t9 (id,name) values(123,'中');
insert into t9 (id,name) values(123,'中国');
insert into t9 (id,name) values(123,'中国人');
insert into t9 (id,name) values(123,'中国人,');
insert into t9 (id,name) values(123,'中国人,加');
insert into t9 (id,name) values(123,'中国人,加油'); //6个字符
insert into t9 (id,name) values(123,'中国人,加油！');

测试结果：

（3）varchar的len

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

varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说y有效字节数是65532。
当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844（因为utf中，一个字符占用3个字节)。
如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。
不同版本的MySQL记录数据大小所采用的字节数不同，以及编码方式不同可能会导致len最大值不同。

博主本人是MySQL8.0x版本，语法中varchar最大长度单位是字节，而VARCHAR 列需要 2 字节存储长度信息(不同版本不同)，每个字符最多占 4 字节（utf8mb4 编码) ，则（65535-2)/4 = 16383。

（4）max相关问题以及其他字段的影响

Q : 为什么我们最大是16383却不能修改？

答：这里的16383实际上是它把那计数数据长度的字节也带上来了。

Q：为什么在之前版本的t9表无法修改carchar类型最大值为21844？

答：虽然表中其他类型的字段不会直接限制你修改varchar类型字段的长度上限，但他们会占用每行的总存储空间，这会间接可能影响你那调整的varchar字段的最大长度。当然不同版本的MySQL,由于存储引擎的不同，每个表中每行的存储空间可能不同。

（5）char和varchar差别

共同点 : 都能保存字符串,都有上限。

不同点 : char是定长的,一次是空间给你分配好;varchar是变长,你用多少给多少空间，上限代表你最多能用多少。

如何选择定长或变长字符串？

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

总结：char适合用于字符串长度较为固定,起伏不大(char类型不需要长度标识,空间一次性开辟,不需要维护计数器表明多少字段); varchar适用于字符串起伏较大(用多少给多少管理成本较大)。

总结varchar类型：

1. 对于varchar来讲，它有自己的长度上限，在上限范围内用多少给多少。

2. 做法就是在申请的众多字节中有1~3个字节用来表示有效字符长度，通过这种方式来确定实际字符是多少来实现变长。

3. max最大一定要包含1-3个字节记录数据长度。

4.如果这个表很干净一行内没用其他字段。varchar能到21844，但是有其他字段那这个值就会变小。

5.varchar适用于字符串起伏较大的场景。

📌 日期和时间类型

(1) 常用日期和时间类型

date :日期 'yyyy-mm-dd' ，占用三字节。(手动插入)
datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节。(定义好后允许我们手动插入)
timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节。(会自动刷新，插入时不用管但新版MySQL创建表时需要声明)

(2) 示例

create table if not exists t11( t1 date, t2 datetime, t3 timestamp );
insert into t11 (t1,t2) values ('2000-10-01','1949-10-01 08:00:00');

测试结果：

注：MySQL 5.6 及以上如果 TIMESTAMP 字段没有明确声明 DEFAULT 或 ON UPDATE 属性，则其默认值为 NULL，不会再自动更新,就不会出现以上现象! 需要通过显式配置 DEFAULT CURRENT_TIMESTAMP 和/或 ON UPDATE CURRENT_TIMESTAMP 来实现(创建表时或修改现有表)。

//创建表
CREATE TABLE t11 (
    t1 DATE,
    t2 DATETIME,
    t3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
//修改现有表
ALTER TABLE t11 MODIFY t3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

配置完后我们进一步测试:

update t11 set t1='1999-01-01'; //更新

这说明timestamp不用我们做任何操作，这个表只要有时间戳，如果对这一行记录任何一个字段做更改或插入，这个时间戳会被自动更新。

（3）应用场景

1. timestamp可以应用到下面的场景(论坛评论)

create table if not exists t12( content text, time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t12 (content) values ('这个世界好人多'); //评论更新时间
select * from t12; 
update t12 set content='哈哈哈'; //更改评论 此时会自动更新时间戳

2. datatime用来存储固定时间不修改,比如进入公司时记录你什么时候来公司正式入职,方便统计工龄。或者你王者荣耀中皮肤体验卡到期时间。

3. date通常只记录日期，比如记录重要的人的生日等场景，不用记录几时几分几秒。

📌 enum和set类型

（1）语法

enum：枚举，“单选”类型；

enum('选项1','选项2','选项3',...); 多选一创建时设置枚举类型就要填上枚举值。

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

set : 集合，“多选”类型

set('选项值1','选项值2','选项值3', ...); 多选多/多选一

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，....最多64个。比如问卷调查询问你的兴趣爱好，你可以选择一个或多个。

（2）示例

create table if not exists votes(
  username varchar(30),
  gender enum('男','女'),
  hobby set('代码','羽毛球','听音乐','篮球','游泳')
);
insert into votes values ('张飞','男','代码');
insert into votes values ('孙权','unknown','代码'); //只能插入男或女

测试结果：

对于不存在的常量值是不允许插入的。

insert into votes values ('孙权',1,'代码');
insert into votes values ('孙权',2,'代码');
insert into votes values ('孙权',3,'代码');
insert into votes values ('孙权',0,'代码');
insert into votes values ('孙权',-1,'代码');

测试结果：

枚举类型可以直接写枚举限定的常量(比如上面的男或女),也可以写常量对应的下标,这个下标从1开始,有几个只能到几。

 insert into votes values ('曹操',1,'羽毛球');
 insert into votes values ('曹操',1,'足球'); //报错
 insert into votes values ('曹操',1,'篮球,代码,游泳');

测试结果：

集合类型不允许插入不存在的；可以单爱好插入；可以以逗号作为分割符然后多爱好插入。

insert into votes (username) values ('曹操');

测试结果：

set和enum插入结果可以默认是NULL的。

insert into votes values ('刘表',1,0);
insert into votes values ('刘表',1,1);

测试结果：

注： NULL就是空表示什么都没有；而' '表示有东西但是是空串。

insert into votes values ('刘表',1,2);
insert into votes values ('刘表',1,3);
insert into votes values ('刘表',1,4);
insert into votes values ('刘表',1,7);

测试结果：

set类型插入数据可以用数字，但是这个数字绝对不是下标！而是想像成bit位。比如00001 第5个bit位代表有没有代码这个爱好。

代码代表第一个bit位，羽毛球音乐游泳篮球依次表示第2，3，4，5个bit位，bit位上的0/1表示是否有这个爱好。比如10010对应十进制是18，也就是这个人有羽毛球和篮球爱好。

(3) enum和set类型查找

select * from votes where gender='男';
select * from votes where gender=1;

测试结果：

查找时可以使用常量也可以使用下标查找。

select * from votes where gender=0;

不合法下标当然就查不到啥。

select * from votes where hobby='代码'; //也可以用数字

测试结果：

注：此时只能筛选出爱好只有代码的，它是严格按照bit位组成筛选的，但我们如果要筛选出爱好中有代码的，我们需要筛选函数。

select 1+1
select find_in_set('a','a,b,c'); 找a是否在这个集合 1表示true
select find_in_set('a,c','a,b,c'); 返回0 说明它用来找一个元素
select find_in_set('a,b','a,b,c'); 返回0
select find_in_set('b','a,b,c'); 返回2 也是true(非0) 但是是下标

测试结果：

find_in_set函数可以帮我们在集合中查找某个元素是否存在，存在则返回下标；它查找的过程是判断是否在集合中，而不是判断是否相等。

select * from votes where find_in_set('代码',hobby);
select * from votes where find_in_set('代码,羽毛球',hobby); //不能这样写
select * from votes where find_in_set('代码',hobby) and find_in_set('羽毛球',hobby);

测试结果：