MySQL —— 数据类型

前言： 数据类型也是MySQL的一种约束。SQL它是强语言类型，就是说数据类型定义一个变量，这个变量必须满足这个数据类型的约束条件。最简单的就是 数据类型的取值范围，它定义的变量的值不可以超过数据类型的取值范围，这个就是数据类型的约束。

---

1. 数据类型的分类

数据类型大体可以分为三类：

1. 数值类型:整数类型，小数类型，位类型，bool类型
2. 字符串类型：char，varchar，enum，set……
3. 时间类型：year，time，date……

2. 数值类型

2.1 整数类型

• tinyint：带符号的范围是 -128~127，无符号的范围是0 ~ 255；默认情况是有符号。占一个字节。
• smallint：带符号的范围是 -2¹⁵ ~ 2¹⁵-1 ，无符号的范围是0 ~ 2¹⁶-1；默认情况是有符号。占两个字节。
• mediumint:它是占三个字节，带符号的范围是 -2²³ ~ 2²³-1，无符号的范围是0 ~ 2²⁴ - 1；默认情况有符号。
• int：它是占四个字节，带符号的范围是 -2³¹ ~ 2³¹-1，无符号的范围是0 ~ 2³² - 1；默认情况有符号。
• bigint：它占八个字节，带符号的范围是 -2⁶³ ~ 2⁶³-1，无符号的范围是0 ~ 2⁶⁴ - 1；默认情况有符号。

观察这些整数类型的名称，挺有趣的，tinyint(超小int)，smallint(小int)，mediumint(中不溜大的int)……

整数类型，主要是利用取值范围来进行约束。

举例子：

1. 假如我创建一个表t1，它只有一个字段，字段类型为tinyint：
   create table t1(id tinyint);
   然后我向表t1中插入数据127，看看情况：
   
   发现插入成功了，那么我再插入数据128，看看情况：
   
   发现插入失败，out of range了，越界。

2. 我再创建一个表t2，它只有一个字段，这个字段类型是无符号的tinyint：
   create table t2(id tinyint unsigned);
   我插入数据 -2 ：
   
   插入失败了。
   再插入数据256：
   
   发现也失败了。

---

所以综上来说，数值类型定义的变量，它必须在数值类型的数值范围内。

2.2 小数类型

• float：float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节
• double：double[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间8个字节
• decimal：decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

区别：

1. float和double都是浮点型，float可以表示的精度大概是7位，double可以表示的精度在10位以上，所以如果要求精度在10位以上，还得用浮点型的话，只能使用double。
2. decimal是定点型，decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。decimal它的精度比浮点型要高，误差小，所以要求精度很高，那就使用decimal。

例子：

1. 假如我定义一个表f1，它的字段类型是float(4,2)。意思就是总长度为4，小数点后为2位，默认是有符号的，所以就是 -99.99 ~ 99.99 。如果是无符号那就是 0 ~ 99.99：
   create table f1(money float(4,2));
   然后向f1中插入数据-99.998，再插入-99.993：
   
   发现-99.998插入失败，但是-99.993插入成功了，这是因为要保留小数点后俩位，如果插入的数据小数点后大于俩位，那么就要进行四舍五入，如果四舍五入后，超过范围，那就插入失败。

2. 再来验证一下无符号的情况，这次创建一个表f2，还和上面一样，不过就是无符号的float罢了：
   create table f2(money float(6,3) unsigned);
   注意：这次是float(6,3)，无非就是总长度是6位，小数点后3位，无符号的范围就是 0~999.999。
   插入值 -0.1 ：
   
   那么插入值 99.99：
   
3. 验证一下：decimal 比 float 精度高，我创建一个表f3，它有两个字段：
   create table f3(money1 float(10,8),money2 decimal(10,8));
   然后向表中插入一个高精度数，看看它俩的显示情况：
   
   float显示的明显与插入的数据有偏差，但是decimal和插入的数据完全一致。这就是高精度的体现。

2.3 位类型

• bit(M)：M指定位数，默认值为1，范围是1~64。

位类型的显示，是按照ASCII码对应的值显示，这点要注意，别到时候没显示出来，就以为没插入进去，其实插入进去了，只不过有可能，你插入的数值对照到ASCII码表，属于ASCII码控制字符，这就可能导致不显示，但可能会起到一些其他的控制效果。

比如：我创建一个表b1，它的字段有一个，并且类型是bit：

create table b1(sb bit(8));

然后我向表中插入数据10，并且查询表b1：

发现有点奇怪，没有显示。

那么我再插入数据107，并且查询表b1：

发现显示出来一个小k。因为107对照的ASCII表的就是k。而10对照的是\n。所以看到表中那个奇怪的| ，就是因为被换行了。

2.4 bool类型

• bool类型：用 1，0 表示真假。

但是它的类型其实是tinyint(1)，你可以向bool类型定义的变量插入tinyint约束的数据范围的值。

不过，大多数情况下，往bool类型定义的变量插入的值是true，false，这可以理解成宏，它俩的值其实就是 1，0。

比如：我创建一个表 bool1：
create table bool1(woqu bool);

然后我向表中插入 true，再插入false：

再查看一下，表中数据：

对吧，然后我们查看一下这个表的结构：

所以插入1，2，3，4，5…… 这种值都是可以的。

3. 字符串类型

• char：固定长度字符串，最大长度255。
• varchar：可变长度字符串，最大长度65535。
• set：set是一个字符串对象，可以有0或者多个值，其值来紫表创建时规定的允许的一列值。指定包括多个set成员的set列值时各个成员用逗号隔开。这样set成员本身不能包括逗号。
• enum：enum是一个字符串对象，其值来自表创建时在列规定中显示枚举的一列值。
• blob：二进制数据。
• text：大文本，不支持全文索引，不支持默认值。

---

3.1 char and varchar

• char(L): 这个L指的是字符长度，注意是字符不是字节。举个例子：“你”，“o”，这两个的字符长度都是1，但是所占字节却不相同。L是有范围的，它的最大长度是255。
• varchar(L): 可变长度字符串，L表示字符长度，它的最大字节是65535。

char是定长的意思就是 无论你写入的字符长度是否小于L，最终都会占用L个字符长度的字节大小。varchar是变长的意思就是 你写入的字符长度小于L的话，它会使用你写入字符长度大小的字节。

也就是说：char就是开辟定长L的空间；varchar就是有开辟L大小的空间，但是你用多少空间就给你多少空间，不会直接给你L大小的空间。

它俩的相同点就是，插入的字符长度都不能超过L。

---

varchar的注意点：

• varchar的L范围是和表的编码有关系的，不过它的字节最大是65535，其中有1~3个字节用于记录数据大小，所以有效的字节最大值是65532。
  比如：utf8，一个字符的大小是3字节，所以L的最大值就是65532/3=21844;gbk，一个字符的大小是2字节，所以L的最大值就是65532/2=32766。
• 要注意varchar保存数据，是会用1 ~ 3 个字节来记录数据大小的，这点不要忽略。

---

例子：

1. 创建一个表c1，其中有一个字段char，它的L为2：
   create table c1(nb char(2)) character set utf8;
   然后插入值"ww" 和"你好"：
   
   如果插入值"kkk":
   
   这就插入不成功。

---

2. varchar 普通的插入，这里就不掩饰了，不过要验证一下在gbk字符集下，L的最大值是不是32766:
   
   如果长度是32767 发现不可以创建这个表，那么我换成32766呢？
   
   这样就创建成功，说明我们之前给的范围是准确的。

---

3.2 set and enum

它俩其实就是俩集合，enum是从集合中取一个(单选)；set是从集合中取一个或者多个(多选)。

如果去看它定义，觉得有点复杂，其实用起来很简单。

• enum(‘选项1’,‘选项2’,‘选项3’,…);

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

• set(‘选项值1’,‘选项值2’,‘选项值3’, …);

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

比如：创建一个调查表 test1 ，里面的字段 有name varchar(30)，hobby set()，gender enum()。hobby可以多选，所以设为set类型；gender性别只能单选，所以是enum类型。

create table test1(name varchar(30), hobby set('篮球','足球','跑步','乒乓球'),gender enum('男','女')) character set utf8;

那么我现在插入数据：李四 足球，篮球，跑步 男；王五 乒乓球 男；刘花 跑步 女：

查询一下：

注意： set可以多选，它插入时的格式是’选项一，选项二，……'；

在这里补充一下查询的一点点东西，方便大家做实验：

(1) 假如我要查询名字是刘花的信息：

select * from test1 where name='刘花'；

(2)假如我要查询爱好有跑步的人，这怎么查询？直接用where？

只有刘花？李四也爱跑步。所以这里不能直接用where。而是利用一个专门在集合中进行查找的函数 find_ in_ set，这个函数名 起的真是 用意明显。

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

select * from test1 where find_in_set('跑步'，'hobby');

3.3 其余

• blob：二进制数据。
• text：大文本，不支持全文索引，不支持默认值。

关于这俩货，我还没用过，不过我可以推荐一篇很好的博文：

blob和text类型详解

4. 日期时间型

• year：只保存年份，支持插入整数或者字符
• timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节
• time：用的少
• date：日期 ‘yyyy-mm-dd’ ，占用三字节
• datetime：时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节

---

1. year的注意点：

(1)插入两位数：
00~69 将转换为2000 ~2069之间
70~99 将转换为1970 ~ 1999之间
比如：插入(‘71’)，或者(71)结果就是1971；
(2)插入0和’0’：
这是特殊情况，插入0 ：就是 0
插入’0’:2000

---

2. time

这个用的也不多，不过还是提供一篇博文：time详解

---

到这我们来进行一些实验操作：

创建一个表，里面有 date，datetime，timestamp 三种类型的字段：

create table birthday(t1 date,t2 datetime,t3 timestamp);

现在进行插几个值：

insert into birthday(t1,t2) values('2000-5-13','2000-5-13 5:20:25');

如果想要更新时间：

update birthday set t1='2023-2-23';

date是年月日，datetime是年月日时分秒，timestamp是时间戳：这个是会自动更新的，比如像刚才的修改数据操作，也会更新时间戳。