Mysql建表时选择数据类型的一些技巧

1. 选择数据类型的原则

1. 更小的通常更好
一般情况下，尽量使用可以储存数据的最小数据类型，更小的数据类型通常更快，因为占用更少的磁盘、内存和cpu。

2. 简单就好
简单数据类型的操作通常需要更少的CPU周期，可提高处理数据速度。
3. 尽量避免NULL
通常情况下最好指定列为NOT NULL，除非真的需要存储NULL。
可为NULL的列会使用更多的存储空间，当可谓NULL的列为索引时，每个索引记录需要一个额外的字节，在MyISAM中可能会导致固定大小的索引编程可变大小的索引。

2. 各种数据类型的对比选择

1. 整数类型

类型	大小
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

Mysql可以指定整数宽度，例如INT(11)，它不限制值的合法范围，只是规定了一些交互工具用来显示字符的个数，对于存储和计算来说，INT(1) 和INT(20)是一样的。

2. 实数类型

实数是带有小数部分的数字，它们可以存储小数，例如DECIMAL可以存储比BIGINT还大的整数。

浮点类型	大小
float	4
DOUBLE	8

计算方式

实数类型	计算类型
FLOAT	近似计算
DOUBLE	近似计算
DECIMAL	支持精确计算

浮点和DECIMAL都可以指定精度，对于DECIMAL列，可以指定小数点前后允许的最大位数，这也会影响到列的空间消耗。DECIMAL(18,9)小数点两边各存储9个数字，共使用9个字节：小数点前后的数字各4个字节，小数点本身占1个字节。
精度定义是非标准的，所以建议只指定数据类型，不指定精度

因为需要额外的空间和计算开销所以应该尽量只对小数进行精确计算才使用DECIMAL，例如存储财务数据，但在数据量特别大的时候，可考虑采用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设存储的财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT中，这样可避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

3. 字符串类型

varchar	char
可变长字符串	定长字符
仅使用必要空间	根据长度分配足够空间
保留末尾空格	剔除末尾空格

1. VARCHAR

   1. VARCHAR比定长类型节省空间，因为它只使用必要的空间。

   2. VARCHAR需要消耗1或2个额外字节记录字符串长度：如果列长度小于或255字节，则使用1个字节表示。

   3. 例如VARCHAR(10) 需要11个字节，VARCHAR(1000)需要1002个字节

   4. VARCHAR节省了存储空间，但在UPDATE时可能会使行变的比原来更长，就会导致需要额外的工作

   5. 使用varchar的一些常用场景

      1. 字符串列的最大长度比平均长度大很多
      2. 列的更新很少，所以碎片不是问题
      3. 使用UTF-8这样的复杂字符集，每个字符都使用不同的字节数来存储。
2. CHAR
   1. CHAR是定长的
   2. CHAR值会根据需要采用空格进行填充以方便比较
   3. CHAR适合存储很短的字符串，例如CHAR非常适用存储MD5值，对于非常短的列和经常变更的列，CHAR也比VARCHAR要好。

      MD5 并不是加密算法，而是摘要算法。加密算法是可逆的，摘要算法是理论上不可逆的。输入任意长度的明文给他，结果都是一个定长16 、32、64（取决于加密算法）。

   4. CHAR检索时会剔除末尾空格，例如存储进去’STRING ‘，检索的结果可能为’STRING’

4. BLOB和TEXT类型

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

事实上他们属于两组不同的数据类型家族

二进制	字符型
TINYTEXT	TINYBLOB
SMALLTEXT	SMALLBLOB
TEXT	BLOB
MEDIUMTEXT	MEDIUMBLOB
LONGTEXT	LONGBLOB

当BLOB和TEXT太大时，InnoDB会使用专门的“外部”存储区域来进行保存，此时每个值需要1~4个字节存储一个指针，然后在外部的存储区域存储实际的值。

BLOB和TEXT唯一的不同是BLOB存储的二进制数据，没有排序规则或者字符集。而TEXT类型有字符集和排序规则

Mysql不能将BLOB和TEXT列的全部长度的字符串进行索引，也不能使用索引来消除排序。

5. 使用ENUM代替字符串类型

有时候可以用枚举列代替常用的字符串类型。Mysql在存储枚举时非常紧凑，会在表中的frm文件中保存“数字-字符串”映射关系的“查找表”，查询出来的数据只有整数

如果使用数字作为ENUM的枚举常量，很容易导致混乱，避免这样做

可以在查询中使用FIELD( )函数显示的指定排序，但这也会导致Mysql无法利用索引消除排序。

枚举的缺点：字符串列表是固定的，添加或删除的字符串必须使用ALTER TABLE。

枚举和VARCHAR关联查询

以VARCHAR和VARCHAR的关联速度为常速做对比

1. ENUM和ENUM关联，关联速度很快
2. ENUM和VARCHAR关联，速度略慢（虽然效率低但可能可以节省I/O，可行性较高）