在创建表时选择”合适的”数据类型能有效提升查询效率、节省存储空间。所谓的”合适”数据类型则需要根据业务场景进行选择,例如在存储IPv4地址时最少需要7个字符,最多需要15个字符,一般为了可读性都会选择VARCHAR(15)进行存储,如果不在乎IPv4地址的可读性,使用VARCHAR(15)需要使用更多的存储空间,在读取时也会影响查询效率。事实上IPv4地址是一个32位无符号的整数(IPv6是一个128位的无符号整数),IPv4的可选范围是0.0.0.0～255.255.255.255,用逗号隔开每个数的取值范围为0-256,也就是256个数,256是2的8次方,用二进制表示8位,IPv4由4个数字组成,所以存储IPv4地址最多是32位,通常1 Byte(字节)等于8位二进制(不同电脑可能有所差异),所以IPv4使用32位无符号整数存储最多需要4个字节(用int类型来存储),而且使用范围查询(between and)效率也更高,但缺点是可读性不好,写入或读取都有格式转换的开销,好在Mysql提供了IP转换函数。

1.整数类型

查看表空间SQL语句如下:

# 切换到information_schema数据库,information_schema 数据库保存了MySQL服务器所有数据库的信息。如数据库名、数据库的表、表栏的数据类型与访问权限等
use information_schema;
# table_schema 为查询的数据库名称,table_name 查询table_schema数据库中的数据表名
select 
 concat(round(sum(data_length/1024/1024/1024),2),'GB') as '表占用空间(GB)',
 concat(round(sum(data_length/1024/1024),2),'MB') as '表占用空间(MB)',
 concat(round(sum(data_length/1024),2),'KB') as '表占用空间(KB)',
 concat(round(sum(data_length),2),'B') as '表占用空间(B)'
 from tables where table_schema='test' and table_name='blog';

总结:

• 存储非负数值时,必须用 unsigned 修饰。
• tinyint类型一般存储小数值或枚举值。例如存储0为男,1为女,tinyint类型比Enum类型tinyint所占空间少,所以查询起来效率更高。

• 如果忽略可读性推荐使用int类型存储IPv4的地址,IPv4本质上是一个无符号32位的数值,IPv4的可选范围是0.0.0.0～255.255.255.255,用逗号隔开每个数的取值范围为0-256,也就是256个数,256是2的8次方,用二进制表示8位,IPv4由4个数字组成,所以存储IPv4地址最多是32位,按照字节二进制转换,1Byte等于8位二进制,所以存储IPv4地址最多需要4Byte,应该选择int地址存储,相较于varchar类型使用int存储IPv4地址所占用的空间更少,对于范围查询效率也更高(因为存储的是数值),但Mysql也提供了INET_ATON、INET_NTOA两个函数用于IP地址与无符号数值的转换。

  use test;
  create table ip(ip int unsigned not null);
  # INET_ATON()函数用于将字符串ip地址转换为32位无符号整数
  # INET_NTOA()函数用于将32位无符号整数的IP地址转为字符串
  insert into ip values(INET_ATON('192.168.110.100'));
  select * from ip; # 查询结果为:3232263780
  select INET_NTOA(ip) from ip; // 查询结果为:'192.168.110.100'

• 推荐使用bigint做为大数据量数值列的存储类型,例如几亿行的日志表的日志id,日志表随着时间或业务的膨胀会越来越多,int类型最多只能存储43亿,远远满足不了业务需求,对于大数据业务下,也会使用bigint存储用户。bigint另一个使用场景是存储大额度金额,因为Mysql的decimal类型会有精度问题。

  2.字符类型

  MySQL提供了CHAR和VARCHAR两种字符类型,其中CHAR类型是定长,VARCHAR类型是变长的。

• 当存储CHAR值时,MySQL会删除所有的末尾空格,例如使用CHAR存储"我叫z乘风 "实际上存储的是"我叫z乘风",存储字符值的末尾空格都会被删除。

• CHAR值会根据需要采用空格进行填充以方便比较,例如使用CHAR(6)指定存储6个字符若存储的字符不满6个则会采用空格进行填充。
• CHAR适合存储很短的字符串,或者所有值都接近同一个长度,如密码的MD5值、身份证号码。对于经常变更的数据,CHAR也比VARCHAR更好,因为CHAR不容易产生碎片(行间碎片)。
• 由于VARCHAR是变长的,根据存储的字符计算所占用的存储空间,在CHAR和VARCHAR相同存储空间下,若实际存储的字符小于指定的存储空间,VARCHAR对比CHAR更加节省存储空间,所以性能也会更好。例如使用CHAR(6)和VARCHAR(6)分别存储"我的",由于CHAR是定长的不满6个字符会使用空格进行填充,所以CHAR存储需要6个字符,而VARCHAR存储实际只需要2个字符。
• Char对英文字符(ASCII)占用1个字节,对一个汉字占用两个字节;而VARCHAR的存储方式是,对每个英文字符占用2个字节,汉字也占用2个字节,两者的存储数据都非unicode的字符数据。
• 由于行是可变的,使用VARCHAR存储在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,MyISAM会将行拆成不同的片段存储;InnoDB则需要分裂页来使行可以放进页内。所以VARCHAR适合存储字符串的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
• 使用VARHCHAR(5)和VARCHAR(100)存储”hello”所占用的空间是一样的,但更长的列会消耗更多内存,因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕,所以最好的策略是只分配真正需要的空间。 ```sql drop table if EXISTS example; create table example( charStr CHAR(10), varCharStr VARCHAR(10) ) insert into example(charStr,varCharStr) values(‘hello ‘,’hello ‘); select concat(“‘“,charStr,”‘“) as charStr,concat(“‘“,varCharStr,”‘“) as varCharStr from example;

  ‘hello’ ‘hello ‘ 结论1:char类型会去除字符串末尾的空格,而varchar不会。

set sql_mode=’’; select LENGTH(charStr),LENGTH(varCharStr) from example;

5 8 默认sql_mode模式下,char长度不包含末尾后的空格,需开启填补字符长度模式可查看char实际存储长度

sql_mode即sql执行模式,会影响 MySQL 支持的 SQL 语法和它执行的数据验证检查,默认为空

PAD_CHAR_TO_FULL_LENGTH 表示启用填补字符长度模式,开启此模式可查看char实际存储长度

set sql_mode=’PAD_CHAR_TO_FULL_LENGTH’;

查看sql_mode

select @@sql_mode;

select concat(“‘“,charStr,”‘“),length(charStr),concat(“‘“,varCharStr,”‘“),length(varCharStr) from example;

‘hello ‘ 10 ‘hello ‘ 8 结论2:如果存储的内容小于char指定的长度,则会采用空格进行填充

drop table if EXISTS example; create table example( charStr CHAR(3), varCharStr VARCHAR(3) ); insert into example(charStr,varCharStr) values(‘hello hahaha’,’hello hahaha’); select * from example;

hel hel 结论3:当实际存储长度 > 存储的长度时,就会截掉多余的内容进行存储

```

3.实数(浮点)类型

• MySQL提供了FLOAT、DOUBLE、DECIMAL三种浮点类型。
• DECIMAL表示高精度小数类型,CPU 原生支持浮点运算,但是不支持 DECIMAl 类型的计算,因此 DECIMAL 的计算比其他浮点类型需要更高的代价。
• FLOAT、DOUBLE存储精度丢失问题,存储值会进行四舍五入。例如:FLOAT(D,M)或DOUBLU(D,M) D表示保留整数位,M表示保留小数位存储值会进行四舍五入,所以禁止使用FLOAT、DOUBLE存储金额,虽然DECIMAL是高精度小数类型,但计算开销较大,推荐使用BIGINT存储大额度金额。
• FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽,例如 DECIMAL(18, 9) 表示总共 18 位,取 9 位存储小数部分,剩下 9 位存储整数部分。

4.日期类型

• MySQL提供DATETIME和TIMESTAMP两种日期时间类型。
• DATETIME能够存储1001年到9999年的日期和时间,精度为秒,使用8字节的存储空间,且与时区无关。默认情况下,MySQL 以一种可排序的、无歧义的格式显示 DATETIME 存储的值,例如”2008-01-16 22:37:08”,这是 ANSI 标准定义的日期和时间表示方法。
• TIMESTAMP和UNIX时间戳相同,能够保存从1970年1月1日午夜(格林威治时间)以来的秒数,使用 4 个字节,只能表示从 1970 年 到 2038 年。它和时区有关,也就是说一个时间戳在不同的时区所代表的具体时间是不同的。默认情况下,如果插入时没有指定 TIMESTAMP 列的值,会将这个值设置为当前时间。如果存储时间在1970年至2038年之间且在乎时区推荐使用TIMESTAMP类型,相比较DATETIME它所占用空间更少,效率更高。