选择正确的MySQL数据类型

良好的逻辑设计和物理设计是高性能的基石，MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
——《高性能MySQL（第3版）》

整数类型

1. 整数类型有：tinyint、smallint、meduimint、int、bigint，占用字节数、位数、表示范围如下：

   数据类型	字节（Byte）数	位（bit）数	表示范围	无符号（unsigned）表示范围
   tinyint	1	8	-2^(8-1) ~ 2^(8-1)-1	0 ~ 2^(8-1)
   smallint	2	16	-2^(16-1) ~ 2^(16-1)-1	0 ~ 2^(16-1)-1
   mediumint	3	24	-2^(24-1) ~ 2^(24-1)-1	0 ~ 2^(24-1)-1
   int（integer）	4	32	-2^(32-1) ~ 2^(32-1)-1	0 ~ 2^(32-1)-1
   bigint	8	64	-2^(64-1) ~ 2^(64-1)-1	0 ~ 2^(64-1)-1

2. 有符号和无符号占用相同的存储空间，并具有相同的性能，但表示范围不同。

3. 可以为整数类型指定宽度，如：int(4)，除了限制交互工具显示的字符个数外，与 int(10)并无区别。对于存储和计算来说也并无区别。

4. MySQL对于整数的计算一般使用64bit的bigint，32位操作系统也是如此。

浮点数类型

1. float和double都是浮点数，属于非标准数据类型。其中float表示单精度浮点数，double表示双精度浮点数。

2. 浮点数在内存中的存储主要分为三部分，分别是：符号位、指数位和尾数位。float和double的存储分别为：

   数据类型	字节数	符号位	指数位	尾数位	小数点后位数
   float	4	1	8	23	7
   double	8	1	10	53	16

3. 浮点数可以指定总位数和小数部分所占位数，如：float(7, 2)或double(7,2)表示 小数部分为2位的7位浮点数。

4. float和double在存储超过字段设置小数部分长度的小数时，小数部分采取四舍五入的方式进行保存。如：在float(7,2)的字段中保存999.009，字段所保存的值会变为999.01

5. 如果需存储整数部分超过字段设置整数部分长度，MySQL直接报错。

6. float和double只支持使用标准的浮点运算进行近似计算。

定点数类型

1. decimal和numeric用于存储精确的小数，属于标准数据类型。numeric的实现方式等同于decimal，decimal的使用方式基本适用于numeric。
2. decimal使用二进制字符串存储，每四个字节存9个数字。例如：decimal(18, 9)需要9个字节（包含小数点1个字节）。
3. decimal(5)相当于decimal(5, 0)。
4. decimal允许最多65个数字，即最多支持保存65位小数的值。
5. decimal通常比浮点类型占用更多的空间，并需要额外的空间和计算开销。因此，应该尽量只在对小数进行精确计算时才使用decimal。面对数据量比较大时，应考虑使用bigint或double代替decimal。

varchar和char类型

1. varchar和char是两种主要的字符串类型。varchar存储可变长度的字符串，char存储指定长度的字符串。
2. varchar比char更节省空间，varchar仅使用必要的空间，char则占用最大空间。varchar需要使用额外的1~2个字节记录字符串的长度。列的最大长度小于或等于255字节，则只是用1个字节表示，否则使用2个字节。
3. char存储时会删除所有的末尾空格，适合存储很短的字符串，或者所有值都接近同一个长度。char类型相较于varchar不容易产生碎片，在存储空间上也更有效率。
4. 更长的列会消耗更多的内存，尤其是使用内存临时表进行排序或操作时会特别糟糕。所以最好的策略是只分配真正需要的空间。
5. 在Memory引擎中只支持定长的行，char和varchar都会根据最大长度分配最大空间。

binary和varbinary类型

1. binary 与 varbinary和char 与 varchar很类似，不过binary和varbinary存储的是二进制字符串，存储的是字节码，而不是字符。
2. 二进制在进行数值比较时更有优势。

blob和text类型

1. blob和text都是为存储很大的数据而设计的字符串数据类型。blob采用二进制存储，text采用字符串方式存储。
2. blob没有排序规则或字符集，而text类型有字符集和排序规则。
3. 与blob属于同一组的字符串类型有：tinyblob、smallblob、blob、mediumblob、longblob；与text属于同一组的字符串类型有：tinytext、smalltext、text、mediumtext、longtext。text是smalltext的同义词。
4. blob和text类型只对每个列的最前sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减少max_sort_length的配置，或者使用ORDER BY SUBSTRING(column, length)。
5. mysql不能将blob和text列全部长度的字符串进行索引，也不能使用这些索引消除排序。
6. 应尽量避免使用blob和text类型。

enum类型

1. enum可以把一些不重复的字符串存储成一个预定义的集合，MySQL内部会将每个值在列表中的位置保存为整数（实际表中各行存储的都是字符串对应的索引位置）。并以数字 - 字符串映射关系为查找表查找数据。
2. enum字段是按照内部存储的整数而不是定义的字符串进行存储和排序的，所以在使用数字作为enum字段的常量时，双重性很容易导致混乱。
3. MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中。
4. enum字段字符串常量列表是固定的，添加或删除字符串必须使用alter table，MySQL中大部分alter table操作都是阻塞的，会导致服务停顿。因此，对于一系列未来经常改变的字符串，并不推荐使用enum类型。

日期和时间类型

1. MySQL支持的日期类型有：Date、Time、DateTime、TimeStamp、Year，存储的最小时间粒度为秒。

2. 每种时间类型都有各自的特定使用场景，输出格式和存储范围如下：

   类型	格式	存储范围	与时区有关
   time	hh:mm:ss	-838:59:59 ~ 838:59:59	否
   date	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	否
   year	YYYY	1901 ~ 2155	否
   datetime	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	否
   timestamp	YYYY-MM-DD hh:mm:ss	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07	是

3. Time类型小时部分被设定成可以大于24，便于存储两个事件之间的时间差。

4. Year类型可接受多种输入参数，字段设置不同类型的参数，产生不同的效果：

   输入参数	存储结果
   4位数字字符串	1901 ~ 2155
   4位数字	1901~ 2155
   0~69中1或2位数字字符串n	2000 + n ~ 2069

5. Datetime保存的是格式为YYYYMMDDhhmmss的整数，与时区无关，占用8个字节的存储空间。

6. Timestamp保存从1970-01-01 00:00:01以来的秒数，与时区相关，类似与unix时间戳，占用4个字节的存储空间，只能表示1970 ~ 2038年。

7. 从空间效率看，通常情况下使用timestamp更佳。

位数据类型

1. bit和set都是用位存储的字符串数据类型。
2. bit是tinyint的同义词，可以使用bit在一列中存储一个或多个true或false值。支持的最大长度是64位。
3. set内部以一系列打包的位的集合来表示。一般无法在set列上进行索引查找。
4. set的字段定义时，可选值之间是通过,分割的，所以可选值不能够包含,。
5. 当使用set作为字段类型时，应注意改变列的定义通常需要执行alter table操作（大部分的alter table 操作都会产生阻塞，导致服务停顿），对与大表来说代价颇高。