我的DBA之路：MYSQL数据类型
我们来讨论下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;
