【MySQL】数据类型

思维导图

学习目标

       学习了数据库中常见的数据类型。

一、数据类型分类

1.1 数值类型

数据类型	说明
bit(size)	位类型。size指定位数，默认值为1，范围是从1到64
tinyint [unsigned]	带符号的范围是从-128 到 127，无符号的范围是从0 到 255，默认为有符号
smallint [unsigned]	带符号的范围是从-2^15 到 2^15 - 1，无符号的范围是从0 到 2^16 - 1
int [unsigned]	带符号的范围是从-2^31 到 2^31 - 1，无符号的范围是从0 到 2^32 - 1
bigint [unsigend]	带符号的范围是从-2^63 到 2^63 - 1，无符号的范围是从0 到 2^64 - 1
float(M, D) [unsigned]	M指定显示长度，D指定小数位数，占用4个字节
double(M, D) [unsigned]	表示比float精度更大的小数，占用8个字节
bool	使用0和1表示真和假
decimal(M, D) [unsigned]	M指定显示长度，D指定小数点的位数

1.2 文本、二进制类型

数据类型	说明
char(size)	固定长度字符串，最大为255
varchar(size)	可变长度字符串，最大长度为65535
blob	二进制数据
text	大文本，不支持全文索引，不支持默认值

1.3 时间日期

数据类型	说明
date	日期类型 ： yyyy - mm - dd
datetime	日期时间类型 ： yyyy - mm - dd  HH - mm - ss
timestamp	时间戳

1.4 String类型

数据类型	说明
enum	enum是一个字符串对象，其值来自表创建时在列规定中显示枚举的一列值
set	set是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各个成员之间用逗号间隔开，这样set成员值本身不能包含逗号。

二、数值类型

2.1 约束的概念

       如果我们向MySQL特定的类型中插入不合法的数据，MySQL一般都是直接进行拦截，不让我们做任何操作。如果我们有数据被成功插入到MySQL中，插入的时候一定是合法的。

       所以，在MySQL中，数据类型本身就是一种约束，保证了数据库中的数据是可预期的，完整的。

       这种约束，使得程序员必须进行正确的插入数据，如果你不是一个很好的使用者，MySQL也可以保证数据插入的合法性。

2.2 tinyint类型

语法：tinyint [unsigned]     mysql表中建立属性列：列名称  类型在后。

有符号的案列：

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

无符号的案列：

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不 下，与其如此，还不如设计时，将int类型提升为bigint类型。

2.3 bit类型

       语法： bit(size) ：位字段类型。size表示每个值的位数，范围从1到64。如果size被忽略，默认为1。

bit类型的注意事项：

       bit字段在显示时，是按照ASCII码对应的值显示。像上图所示的情况是很常见的，刚好对应的ASCII码值没有相对应的字符。我们可以通过插入数字97来验证：

       我们可以使用bit类型来进行节省空间：例如，我们只存放0或1，就可以进行定义bit(1)。 

2.4 浮点类型

2.4.1 float类型

语法：float(M,D) [unsigned] ：M指定显示长度，D指定小数点的位数，占用4个字节

       mysql在存储数据时，会进行四舍五入。如果顶一个无符号的浮点数，那么数据范围会缩小到原来的一半，具有精度损失。

2.4.2 decimal类型

       大致用法与float类似，精度比float大。float表示的精度大约是7位，而decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。

三、文本、二进制类型

3.1 char类型

语法：char(size) ：固定长度的字符串，单位为字符，最大长度值可以为255。

注意一下，不管是插入汉字还是其他符号，可以插入的长度永远都不超过size的大小。

3.2 varchar类型

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

关于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字节）。

3.3 char类型和varchar类型的比较

3.3.1 两者的对比

3.3.2 如何选择定长或者变长字符串

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

四、日期和时间问题

常用的日期有如下三个：

• date：日期：yyyy - mm - dd ，占用3个字节
• datetime：时间日期格式：yyyy - mm - dd   HH : ii : ss ，表示的范围从1000 到 9999，占用8个字节
• timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

       在添加数据时，timestamp会更新所插入数据的时间；同理，当进行修改数据时，timwstamp也会记录修改数据的时间。

 

五、String类型

5.1 enum类型

语法：enum：枚举，“单选”选项  enum（'选项1', '选项2', '选项3', ......）；

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

5.2 set类型

语法：set：集合，“多选”选项 set（'选项1','选项2','选型3',……）；

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

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

5.3 插入数据

insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2);

5.4 查询数据

5.4.1 使用 where hobby = '画画‘

5.4.2 使用find_in_set函数

       find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回 0 ；

str_list 用逗号分隔的字符串。

查询爱好篮球的人：

 我们还可以使用 and 挑选好几个拥有这个爱好的人。