[MySQL]数据类型(图文详解)

[MySQL]数据类型

1. 数据类型分类

2. 数值类型

说明：

• 数值类型在定义时不加unsigned限定的，都会被创建为有符号数。
• 只能将数值类型范围内的数据插入表中。
• 在C/C++语言中，数据类型不匹配时，编译器可能会进行截断、隐式类型转化等方式将不同类型的数据相互赋值，在MySQL中这是不被允许的，在MySQL中数值类型是一种约束，限制程序员插入更正确的数据，这样使得数据库中的数据更加可信。

2.1 bit类型

语法：

bit [M]; #M默认为1，最大为64

• bit是以比特位数定义的类型。
• 不指定位数时默认为1位，最大为64。
• 数据范围由位数决定。
• 在表中以ASCII码的形式显示。

bit类型示例：

2.2 tinyint类型

tinyint、smallint、int、bigint在使用上相同，仅仅是数据表示的范围不同。

语法：

tinyint [unsigned];

tinyint类型使用示例：

2.3 float类型

double类型的使用于float类型相同，只是精度不同，所占字节数不同。

语法：

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

• MySQL保存值时会四舍五入，数据能否存入取决于四舍五入后数据是否在数据范围内。
• 显示长度 = 整数部分位数 + 小数部分位数(定值)。
• 显示长度小于等于M。
• 无符号类型相比于相同显示长度、小数位数的float类型范围减半(除去负数部分)。
• float表示的精度大约是7位。

float类型示例：

案例： 小数：float(4,2)表示的范围是-99.99 ~ 99.99。

由于浮点类型的数据的是以二进制形式存储的，因此无可避免的会有精度损失，并且值得注意的是，整数部分也可能有精度损失。

2.4 decimal类型

语法：

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

• 用法与float类型相同。
• 精度比float类型更高。

decimal类型示例：

• decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略， 默认是10(不同版本MySQL可能有所不同)。

如果想要更高精度的小数，使用decimal更能满足需求。

2.5 char类型

语法：

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

• 能存储的字符长度固定，最大为255字符。
• MySQL中的字符指的是任意字符，比如一个汉字和一个字母都是一个字符。

char类型示例：

2.6 varchar类型

语法：

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

• 用法上与char相同。
• 不同于char是给定空间，varchar是需要多少空间给多少空间。
• 最大长度为65535字节。
• 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类型示例(一下为utf8编码)：

由于MySQL中表的一行最多只能存储65535个字节，因此在属性列中增加属性，varchar能在一行中能使用的空间就变小了。

如何选择定长字符和变长字符？

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

2.7 日期和时间类型

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

• timestamp所在的记录(行)有插入、修改操作，timestamp都自动更新。

日期和时间类型示例：

2.8 enum和set类型

enum语法：

enum('选项1','选项2','选项3',...);

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

set语法：

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

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

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

enum和set类型示例：

enum和set类型查找

为了查询含有代码的记录，而不是只有代码的记录，需要借助以下函数：

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

• 返回0代表sub不在str_list中，返回非0即使sub在str_list中的下标。
• sub中只有一个字符才有效，如果要查询多个字符需要逻辑与(and)关键字。

find_in_set示例：

用find_in_set解决set查询问题：