选择优化的数据类型 

简单的原则

通常尽可能小

选可以正确保存数据的最小类型。需要更小的内存，磁盘，CPU周期。

简单就好

选简单的数据类型。比如整型比字符型更简单，当比较时，字符集和校队规则使得字符比较更加复杂。

还有两个常用的例子，用内置类型而不是字符串来保存日期和时间（内置类型用到整型来保存，之后会说），使用整型来保存IP地址。

避免NULL

NULL使得索引和值比较复杂，如用 != 查询，即使该列值为null满足条件，查询结果也不会包括其中。null 列还需要额外的空间来标识。

 数字类型

有两种类型的数字，整数与实数

整数

TINYINT,SMALLINT,MEDIUMINT,INT.BIGINT。分别使用1，2，3，4，8字节空间，存储值的范围的计算方法和Java语言的int相同。

整数有可选的UNSIGNED属性，表示最高位不是符号位，这样使正数范围翻了两倍，最小值为0。

实数类型

实数是带有小数的数字，但不一定只保存小数，也可以用DECIMAL保存比BIGINT还大的整数。

MYSQL既有精确类型也有不精确类型。

FLOAT和DOUBLE采用标准的浮点数计算方法，是不精确类型。

DECIMAL用于保存精确的小数。例如：用 DECIMAL（18，9）指定小数点两边一共18位数字，第二个参数表示小数部分有精确的9位。小学计算可知整数部分有（18-9） = 9 位。

DECIMAL底层把数字打包到一个二进制字符串中，每4个字节保存9个数字。因为9位最多为9亿9千万999...，而4字节最多有21亿能保存，而3字节又太少了。

DECIMAL只是一个保存格式，在实际计算中会用DOUBLE类型。

DECIMAL虽然精确，但需要额外的空间和计算开销，所以可以考虑用BIGINT代替DECIMAL。假设要精确到万分之一，就把所有数据乘于一百万，这样可以避免DECIMAL的缺陷。当然用BIGINT也有缺点，当数据整数部分过大时，这样乘可能会导致溢出问题。

字符串类型

VARCHAR和CHAR是两种最主要的字符串类型。

VARCHAR用于保存变长的字符串，这样比定长的字符串仅使用需要的空间。但VARCHAR需要额外的1到2字节保存当前长度。这个比较复杂，接下来详细介绍一下。

先说一下MySQL的行格式，MySQL一行除了你自己建表时所设立的列，还包括变长字段长度列表，NULL值列表，记录头信息。（记录头信息不容易理解，和本篇无关）MySQL规定以上所有信息组成的一行不能超过65535个字节，也就是用2个字节能表示的最大大小，这个限制和MySQL查询效率有关，暂且不表。总之，VARCHAR的最大长度本质上和MySQL一行的最大长度有关。

变长字段长度列表就是用来保存像VARCHAR这种变长的数据类型的长度的。如果实际字节数大于255字节，则会用两个字节来保存长度，如果实际字节数小于等于255字节，只会用一个字节来保存长度。

NULL值列表是用来标识行的一列是否可以为空的，用一bit来标识，但是最低用一个字节来保存。所以如果有一列可以为空，则NULL列为1字节大小，其中7bit为0，一bit标1，如果有9列可以为空，而要用二字节的空间。

介绍完上面的信息就可以来说明VARCHAR的最大长度了，因 一行最多65535个字节，所以如果全用来保存一列VARCHAR的话显然能达到最大长度。记录长度信息显然占2个字节，如果可以为空的话VARCHAR理论最长字节65533，不可以为空的话理论最长65532字节。又因为编程时标识VARCHAR的长度是用字符数标识，最大字符占字节数为4个字节，所以理论VARCHAR的最大字符数为65533/4,取整为16383个。

CHAR类型就没VARCHAR那么复杂，CHAR类型是定长的，而且会默认删除字符串所有的末尾空格。最大长度为255.

大字符串类型

BLOB和TEXT是为了存储很大的字符串类型而设计的，分别采用二进制和字符方式保存。BLOD用来保存音频等二进制文件，而TEXT用来保存可读字符。虽然二者本质上相同，都是二进制数据，但二进制表示的东西却不同。

枚举类型和SET类型

如果预先知道要保存的数据值数量有限，可以使用枚举类型或SET类型来代替。如星期，月份等。这两个类型都会在表定义问题保存映射关系的查找表（枚举类型是数字到值，SET类型是位到值），这样只用保存空间更小的键就可以了，有效节省空间。两者的用法区别是枚举类型是多值中选一值，SET类型是多值中选多值。

ENUM（枚举类）



create table if enumTest(
     e ENUM('fish' 'vege' 'bob')
);
insert into enumTest(e) values ('fish');


上面的代码是一个使用枚举的例子，插入了一行名为fish的值。实际保存类型为整数。枚举最不好的地方是，如果要更改枚举类型必须用alter table语句，除非只在枚举类型的末尾添加字符串。

而且枚举类型虽然节省空间，但转化成实际的值还是有一定开销。

SET类型



create table setTest(
    perm  set('a' ,'b','c')
);
insert into setTest(perm) values('a,c');
select perm from setTest where find_in_set('a',perm)


上述例子简单展示了set类型的用法，创建表后插入了一行set值，值为（‘a，c’）。查询时用 find_in_set 函数。

和枚举类型一样，如果要更改必须要用alter table 语句。

时间和日期

MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP。我们依次来看看。两者底层保存都是整数，所以效率比字符串要高，推荐使用。

DATETIME使用年月日的格式保存日期，所以可以在较小空间内保存大范围的值。从1001年到9999年，精度为秒。它把日期封装在YYYMMDDHHMMSS的整数中，与时区无关。使用8字节空间。

显示的时候会加上合适的分隔符，如“2021-07-00 22 : 37 : 08”这样显示。

TIMESTAMP用4字节的空间保存从1970年1月1日午夜到现在的秒数。范围比DATETIME要小，从1970年到2038年，这是因为这个时间段大概十几亿秒，4字节可以正好保存。TIMESTAMP的显示依赖于时区，在显示的时候也会像DATETIME那样格式化，而不是单纯显示一长串秒数。

DATETIME列不能设置当前时间为默认值而TIMESTAMP可以。



t1 timestamp  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,


DEFAULT CURRENT_TIMESTAMP 说明插入该行数据时默认当前时间

ON UPDATE CURRENT_TIMESTAMP说明插入其他行时这行的TIMESTAMP也会刷新为当前时间

这两个参数可以单独修饰TIMESTAMP对象。

 选择标识符