【学习笔记】mysql中的数据类型（一）

本文属于读书笔记，大部分内容摘抄于《高性能MYSQL》，摘抄内容版权属于原作者。

整数类型

mysql中可用的整数类型有以下几种：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT，分别使用9,16,24,32,64位存储空间。他们可以存储的值的范围是从-2的n-1次方到2的n-1次方-1，其中N是存储空间的位数。

整数类型可选的unsigned属性，表示不允许附属，这大致可以使整数的上线提高一倍。有符号我无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

mysql可以为整数制定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了mysql的一些交互工具（例如mysql命令行客户端）用来显示字符的个数。对于存储和计算INT(1)和INT(20)是相同的

实数类型

实数是带有小数部分的数字。然而，他们不只是为了存储小数部分；也可以使用DECIMAL存储被BIGINT还打的整数。mysql既支持精确类型，也支持不精确类型。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。

DECIMAL类型用于存储精确的小数。因为CPU不支持DECIMAL的直接计算，所以在mysql 5.0 和更高版本中,mysql服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原声浮点计算，所以浮点运算明显更快。

浮点和DECIMAL类型都可以指定精度。对DECIMAL列，可以指定小数点前后锁允许的最大位数。这会影响列的空间消耗。mysql 5.0和更高版本将数字打包保存到一个二进制的字符串中（每四个字节存9个数字）。例如DECIMAL(18，9)小数点两边将各存储9个数字，一个使用9个字节。小数点的数字用4个字节，小数点后的数字用4个字节，小数点本身占用一个字节。

有多种方法可以指定浮点列所需要的精度，这会使得mysql悄悄选择不同的数据类型或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度。

浮点类型在存储同样范围的值的时候，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样。能选择的只是存储类型；mysql使用都变了作为内部浮点计算你的类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。

字符串类型

VARCHAR

varchar类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为她仅使用必要的空间（例如，越短的字符串使用越少的空间）。有一种情况是例外的，如果mysql使用row_format=fixed创建的话，每一行都会使用定长存储，这回很浪费空间。

varchar需要使用1或2个额外字符记录字符串长度：如果列的最大长度小于或等于255使用1个字节表示，否则使用2个字节。

varchar节省了存储空间，所以对性能也有帮助。但是由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要丛额外的工作。在这种情况下，不同存储引擎的处理方式是不一样的。myISAM会将行拆成不同的片段存储，InnoDB则需要分裂也来使行可以放进也内。其他的一些存储引擎也许从不在原数据位置更新数据。

下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

char的长度是定长的：mysql总根据定义的字符串长度分配足够的空间。当存储Char时，mysql会删除所有的末尾空格。char值会根据需要采用空格进行填充以方便比较。

char适合存储很多的字符串，或者所有值都接近同一个长度。对于非常短的列，char比varcha更好，因为定长的char类型不容易产生碎片。

BOLB和TEXT

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

实际上这两者属于不同的数据类型家族：字符串类型是TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT;对应的二进制类型是TINYBLOB,SMALLBLOB, BOLB,MEDIUMBLOB, LONGBLOB.

BOLB是SMALBLOB的同义词，TEXT是SMALLTEXT的同义词。

与其他类型不同，Mysql把每个BLOB和TEXT值当做一个独立的对象处理。存储引擎在存储时通常会作特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个执政，然后再外部存储区域存储实际的值。

BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型是有字符集和排序规则。

mysql对BLOB和TEXT列进行排序与其他类型是不同的：它值对没格列的最前max_sort_length字节而不是整个个字符串做排序。如果只需要排序前面一小部分字符可以减少max_sort_length的配置，或者使用ORDERBY SUSTRING(colum,length)

因为memory引擎不支持BLOB和TEXT类型，所以如果查询使用了BLOB或TEXT列并且需要使用隐式临时表，将不得不使用MYISAM磁盘临时表，即使只有几行的数据也是如此。这会才导致严重的性能开销。即使配置mysql将临时表存储在内存块石碑上，依然需要许多昂贵的系统调用。

最好的解决方式是尽量避免使用BLOB和TEXT类型。如果实在无法避免，没有一个技巧是在所有用到BLOB字段的地方都使用SUSTRING（column, length）将列值转换为字符串（在ORDER BY自居中也适用），这样就可以适用内存临时表了。但是要确保截取的字符串足够段，不会使用临时表的大小超过max_heap_table_size或tmp_table_size，超过以后mysql会将内存临时表转成MYISAM磁盘临时表。

使用枚举ENUM代替字符串类型

这个方法其实大家都懂，有时候可以适用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。

mysql在存储枚举时非常紧凑，会根据列表值的数量亚索到一个或者两个字节中。mysql在内部会将每个值在雷彪中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。

另外一个让人吃惊的地方是，既然我们都已经知道了枚举类型实际上存储内部的是整数而不是字符串，也就能理解枚举的排序是按照存储的整数进行排序的，而不是按照字符串。也就是按照你一开始定义枚举列的顺序。

枚举最不好的地方时，字符出纳列表时固定的，添加或删除字符串都必须使用ALTER TABLE。因此，对于一些列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表的末尾添加元素（对排序会产生影响，但这样就不必重建整个表来完成修改）。

因为mysql吧每个枚举值保存为整数，并且必须进行查找才能转换为字符出阿妹，所以枚举列还是有一些开销。通常枚举列都比较IAO，所以开销还可以控制，但也不能保证一直如此。在特定情况下把CHAR/VARCHAR列与枚举列进行关联可能会比直接关联CHAR/VARCHAR更慢。