我们来讨论下Mysql中的数据类型
一,整数类型
类型
占用空间(字节)
最小值
最大值
Signed/Unsigned
(有符号/无符号)
tinyint
1
-128
127
Signed
0
255(2^8 -1)
Unsigned
smallint
2
-2^15 -1
2^15 -1
Signed
0
2^16 -1
Unsigned
mediumint
3
-2^23 -1
2^23 -1
Signed
0
2^24 -1
Unsigned
int
4
-2^31 -1
2^31 -1
Signed
0
2^32 -1
Unsigned
bigint
8
-2^63 -1
2^63 -1
Signed
0
2^64 -1
Unsigned
1,Sign代表有符号位,表示可以存储正数和负数。
比如我建表时,Unsigned这一列没有勾选(使用Signed),表示这一列存储的值是有符号的。既能存放整数,又能存放负数
2,Unsign代表无符号位,表示只能存放正数。插入负数会报错
如果我建表时,Unsigned这一列勾选的话,表示这一列存储的值是无符号的。只能存放正数
3,有符号和无符号类型使用相同的存储空间
对于tinyint类型的字段,它占用一个字节。无论是Signed还是Unsigned都占用一个字节。
无符号位可以存储的范围是:0-255。一个字节的8位都用来存储数据
有符号位可以存储的范围是:-128~127。最高位用来表示正负,其余7位用来存储数据。
4,注意:
可以为整数类型指定宽度,但是没什么用。他不会限制值的合法范围。
比如: int(1) 和 int(20)是一样的。
二,实数类型
实数是带有小数部分的数字
类型
占用空间
精度
精确性
float
4
单精度
低
double
8
双精度
低,比float高
decimal
可变长度
高精度
非常高
1,float(M,D) double(M,D) decimal(M,D) :显示M位整数,其中D位在小数点后面
2,注意:
因为CPU不支持对Decimal的直接计算,所以Mysql服务器自身实现了Decimal的高精度计算。CPU直接支持原生浮点数(float和double)的计算,所以浮点数运算明显更快。
三,字符集
1,什么是字符集?
字符集是一组符号和编码的集合。
我们在建数据库,建表时都要选择字符集,甚至可以为每一列都能指定字符集。(优先级:列 > 表 > 库。如果列没有指定字符集,它就使用表的字符集)
2,常见的字符集有:utf8,gbk,latin1,utf8mb4 。。。等
mysql中查看字符集:show character set;
字符集
描述
排序规则
maxlen
utf8
utf-8 unicode
utf8-gengral-ci
3
utf8mb4
utf-8 unicode
utf8mb4-gengral-ci
4
latin1
…
…
1
字符集总是和排序规则成对配合使用的。
排序规则中:utf8-general-ci中的 ci 是 case-insensitive的缩写,表示不区分大小写。
maxlen:表示该字符集中一个字符最多占用多少个字节。比如对于utf8字符集,它存储字符串时,一个字符最多占用3个字节,还可能占用一个字节或两个字节。
四,字符串类型
类型
说明
N的含义
是否有字符集
最大长度
char(N)
定长字符
字符
有
255
varchar(N)
变长字符
字符
有
16384
binary(N)
定长二进制字节
字节
无
255
varbinary(N)
变长二进制字节
字节
无
16384
tinyblob
二进制大对象
字节
无
256
bolb
二进制大对象
字节
无
16K
mediumblob
二进制大对象
字节
无
16M
longblob
二进制大对象
字节
无
4G
tinytext
大对象
字节
有
256
text
大对象
字节
有
16K
mediumtext
大对象
字节
有
16M
longtext
大对象
字节
有
4G
1,Char
Char类型是定长的
优点:存储定长的值,比如char(1)存储性别,用单字符集只需要一个字节。
缺点:所有的列的长度必须相近
使用场景:适合存储很短的字符串,并且所有值的长度接近。比如存储MD5值。经常变更的数据也适合char来存储
2,Varchar
Varchar存储可变长度字符串。Varchar还需要1或2个额外的字节记录字符串的长度。
如果列的长度 <= 255字节,使用1个字节表示长度。
如果列的长度 > 255字节,使用2个字节存储字符串长度。
优点:Varchar可以节省空间,它只使用必要的空间
缺点:如果列突然变长,并且页内没有多余的空间,那么InnoDB会进行页分裂。数据更新时进行页分裂会降低更新的效率。
使用场景:列更新很少,列的最大长度比平均长度大很多。
3,Char和Varchar对比
Char在存储时,会删除所有的末尾空格(注意是末尾的空格)。但是在查询数据进行比较时,会用空格来填充(填充成一样的长度方便比较)
Varchar在存储时会保留末尾的空格,检索时也会保留末尾的空格。
4,实例
param1是char类型的,param2是varchar类型的。
添加数据:
insert into `teset` (`id`, `value`, `param1`, `param2`) values('1','1','aaa','aaa');
insert into `teset` (`id`, `value`, `param1`, `param2`) values('2','2','aaa','aaa');
insert into `teset` (`id`, `value`, `param1`, `param2`) values('3','-128','aaa','aaa');
查询数据:
对于第二行的数据,char类型的param1只有3个字节,说明它在存储时是把字符串后面的空格去掉了。
查询时虽然第二行的param1存储的是'aaa'但是查询 条件是'aaa ',但是也能查出来。说明在查询时,它是用空格填充了的。
5,BINARY和VARBINARY
它们存储的都是二进制字符串,二进制字符串存储的是字节码而不是字符。它们采用零字节(\0)来填充。在比较时也不会去掉填充值。
Mysql在比较BINARY字符串时,每次按一个字节,并且根据该字节的数值进行比较,所以二进制的比较比字符串简单并且高效。
6,BLOB和TEXT
6.1,BLOB用来存储很大的数据,采用二进制方式存储
TEXT用来存储很大的数据,采用字符的方式存储
6.2,Mysql把每个Blob和Text的值当做一个独立的对象来处理,如果Blob和Text的值太大,InnoDB会用外部存储区域来存储,行内存储外部区域的指针,这个指针需要1~4个字节。
6.3,Mysql对Blob和Text列进行排序时,只对每个列的最前max_sort_length字节做排序(而不是对整个字符串排序)。
如果只需要排序前面一小部分字符,可以使用 order by substring(column,length)
7,ENUM
7.1,可以使用枚举列代替常用的字符串类型。
Mysql在存储枚举时,会用整数来保存(而不是用字符串),然后在表的.frm文件中保存 “数字-字符串”映射关系的 “查找表”。
7.2,排序时,也是按照内部存储的整数来排序(而不是用字符串来排序)
比如:create table t(name varchar(30),sex ENUM(‘male’,’female’))engine = innodb。
Insert into t select ‘david’, ‘male’;
配置文件:set sql_mode = ‘strict_trans_table’;用来做约束检查,可以保证非法数据不能添加到数据库。此时插入非法数据会直接报错。
8, 日期和时间类型
8.1,Datetime可以保存大范围的值,从1001~9999,精度为秒。他把日期封装到YYYYMMDDHHMMSS的整数中,使用8个字节存储。
8.2,默认下Mysql以可排序的格式显示datetime值
8.3,Timestamp类型保存了从1970年1月1日午夜以来的秒数(它和UNIX时间戳相同),使用4个字节存储。只能表示从1970年到2038年。Timestamp的显示依赖 时区
8.4,默认情况下,mysql设置timestamp列的值为当前时间。Timestamp列默认为 NOT NULL
8.5,一般情况下建议使用Timestamp,因为它更节省空间
9,Json
9.1,使用Json类型可以替换Blob类型
Json可以做到数据的有效性检查,Blob无法在数据层做约束性检查
Json查询性能更高,不需要遍历所有的字符串才找到数据
支持部分属性索引,通过虚拟列的功能可以对Json中的部分数据进行索引
比如:create table user(uid int auto_increment,data json,primary key(uid)engine = innodb);
insert into user values(null,’{“name”:”david”,”address”:”shanghai”}’);
select json_extract(data,’$.name’),jsn_extract(data,’$.address’) from user ;
结果:
第一列 第二列
david shanghai
10,特殊类型数据
10.1,IPV4地址,它实际上是32位无符号整数,不是字符串。所以应该用无符号整数存储IP地址。
10.2,Mysql提供INET_ATON()和INET_NTOA()函数
比如:create table test(name varchar(30),ip int)engine = innodb。
insert into test(name,ip)values(“david”, INET_ATON("192.168.0.1"));
select name, INET_NTOA(ip) from test;