目录
一、数据类型分类
二、数据类型
1、tinyint 类型
有符号 tinyint 范围测试
创建一个包含 tinyint 数据类型的表结构,默认 tinyint 是有符号的:
因为 tinyint 类型占用 1 字节,所以取值范围为 -128 ~ 127,插入的数据在该范围内,就可以成功插入进入:
如果插入的数据范围,超过了 -128~127,那么插入就会报错:
无符号 tinyint 范围测试
创建一个包含 tinyint 类型的表结构,并指定其为无符号类型:
因为 tinyint 类型占用 1 字节的存储空间,因此无符号 tinyint 的取值范围为 0~255,插入的数据在这个范围内都可以成功插入:
如果插入的数据不在 0~255 之间,那么插入的数据时就会报错误:
注意:
尽量不要使用 unsigned,对于 tinyint 类型可能存放不下的数据,tinyint unsigned 同样可能存储不下,还不如设计的时候,将数据类型进行提升。
2、bit 类型
基本语法:
bit [(M)]:位字段类型。M表示每个值的位数,范围 1~ 64,如果 M 被忽略,默认为 1。
创建一个包含 int 类型,以及 8 位bit类型的表结构:
向表中插入,a 值为 10,11两条记录,发现 a 字段显示的并不是 10,11:
因为 bit 类型在显示时,对应是按照 ASCII码值进行显示的,其中 10 对应的控制字符为 LF,表示换行符号,11 对应的控制字符串为 VT,表示垂直制表符。如果向表中插入,bit 值为 97,其中对应ASCII表为 字符 a,就可以显示出来了:
如果我们有这样的值,只存放0或者1,这时可以定义 bit (1),这样可以节省空间。
此时插入2时,就已经越界了!
3、float 类型
基本语法:
float[(m,d)] [unsigned]:M指定显示长度,d指定小数位数,占用空间4个字节。
有符号 float 范围测试
创建一个包含 float (4,2) 类型,默认其是有符号的表结构:
因为float (4,2) 的取值范围为 -99.99 ~ 99.99,因此插入的数据范围在这个范围内都可以成功插入:
此外,MySQL在保存值时,会采用四舍五入的原则存储数据,实际 float (4,2) 的范围为 -99.994 - -99.994,如果插入的数据的范围超过这个范围,插入的数据记录就会报错:
无符号 float 范围测试
创建一个 包含 float (4,2)类型,并指定为无符号的表结构:
无符号的 float 类型取消了负数部分,因此 float (4,2) 的取值范围为 0~99.99,实际可插入的范围为 0~99.994,如果插入的记录超过这个范围就会报错:
4、decimal 类型
decimal类型与 float类型存储的数据类型一致,都是小数类型,但是decimal存储的精度要远远高于float类型。
创建一个包含 float (10,8) 和 decimal (10,8) 类型的表结构:
向表中插入一条记录,指定 num1 与 num2 两列都是 12.12345612,最终发现decimal 保持了数据原有的形态,而 float 类型造成了一定量的精度丢失:
说明:float表示的精度大约是 7 位。
• decimal 整数的最大位数m为65,支持小数的最大位数d为30,如果d被省略,默认为0,如果m被省略,默认为10。
建议:如果 希望小数的精度更高,推荐使用decimal。
5、char 类型
创建一个包含 char (6) 类型的表结构:
因为 char (6) 最多可以存储 6 个字符,不是字节,因此只要插入的字符个数不超过 6 个就可以插入成功,如果字符数量超过了6个就会报错:
注意:
• 这里的字符,不是指一个英文字母,一个汉字也表示一个字符。
• 不同编码中,一个字符所占的字节数量是不同的,比如:utf-8 中一个字符所占用 3 个字节,gbk中一个字符所占用2个字节。
6、varchar 类型
创建一个包含 varchar (6) 类型的表结构:
因为 varchar(6)最多可以存储6个字符,因此只要插入的数据在6个字符范围之内,都可以被插入成功,如果超过了 6 个字符就会报错,这里是跟 char 类型是一致的:
varchar 类型最多可以占用 65535字节,其中有1 ~3 字节用来记录数据大小,因此varchar类型的有效存储字节数为 65532 字节。
而 varchar 类型可指定的字符个数的上限,与表的编码有关:
• 对于 utf-8 来说,一个字符占用 3 个字节,因此 varchar (L)中最大可指定为 65532 / 3 = 21844,如果超过这个长度就会报错:
• 对于 gbk 来说,一个字符占用 2 个字节,因此 varchar (L)中最大可指定为 65532 / 2 = 32766,如果超过这个长度就会报错:
char 类型 与 varchar 类型的区别:
• char 类型可存储的上限为 255,varchar 类型可存储的上限与表的编码格式有关。
• char(L) 定义后,无论存储的字符串长度是否到达L,都会开辟用于存储L个字符的定长空间,如果存储的字符串长度超过L就会报错。
• varchar(L) 定义后,会根据存储字符串的长度需要开辟空间,并且需要使用1 ~ 3 字节的空间用于表示存储字符串的长度以及其他控制信息,如果存储的字符串长度超过L就会报错。
char类型和varchar类型的优缺点:
• char类型的数据是定长的,因此磁盘空间比较浪费,但是效率高。
• varchar类型的数据类型是变长的,因此磁盘空间比较节省,但是效率低(需要先读取存储字符串的长度,再访问指定长度的空间)。
7、日期类型
常见的三种时间日期类型:
• date:日期格式为 yyyy-mm-dd,占用三字节
• datetime:时间日期格式为 yyyy-mm-dd hh:mm:ss 占用8个字节
• timestamp:时间戳,格式为 yyyy-mm-dd hh:mm:ss 占用4个字节
创建一个包含,date类型,datetime类型,timestamp类型的表结构:
查看表结构可得,timestamp类型的数据是不允许为 NULL的:
因此插入数据时,不对 timestamp 列插入,它会自动更新成为时间戳:
8、enum类型、set类型
enum和set类型区别:
• 在定义enum字段时需要提供若干个选项的值,在设置enum字段值时,只允许选取其中的一个值(比如:性别的选取)。
• 在定义set字段时,需要提供若干个选项的值,在设置set字段值时,可以选取其中的一个或者多个值(比如:兴趣爱好的选取)。
创建一个包含 enum 类型和 set 类型的表结构:
以数字设置enum值:
以数字设置set值 (set的值实际是以bit位的方式进行存储的,插入时只需要将对应bit位置1即可):
enum和set查找
想在表中筛选出所有女性的信息,只需要使用 where 语句,进行判断即可:
如果要筛选出,hobby中篮球爱好者,使用上述方法是筛选不出来的:
此时我们需要使用 find_in_set 函数:
find_in_set (sub,str_list):如果sub 在str_list中,则返回下标;如果不在,返回0,str_list用逗号分割的字符串。
如果还要增加爱好中有敲代码者,只需要后面追加 and 条件即可: