MySQL之选择字段数据类型

MySQL支持的数据类型很多，选择正确的数据类型对于 获得高性能至关重要。在选择时有个简单的原则有助于做出更好的选择。

 

简单的原则：

A、通常最小的是最好的

     因为这样可以用更少的磁盘、内容、CPU缓存，大大减少IO开销。

B、简单就好

     简单的数据类型操作通常需要更少的CPU周期。例如，整型比字符操作代价更小，因为字符集和校对规则（排序规则）使它比整型更复杂。比如应该使用MySQL内建的类型而不是使用字符型来存储日期和时间。

C、尽量避免使用NULL

     NULL是列默认的属性，通常我们要指定为NOT NULL。有NULL的列值会使得索引、索引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间，在MySQL中也需要对它进行特殊处理，当可为NULL列做索引时，每个索引需要一个额外的字节，在MyISAM更有可能导致固定大小的索引变成可变大小索引，在InnoDB中使用单独的位(bit)存储NULL值。

 

1、整数类型

     几种整数类型：TINYINT(8位),SMALLINT(16位),MEDIUMINT(24位),INT(32位),BIGINT(64位)，它们的范围是-2的(n-1)次方到2的(n-1)次方-1。如果选择了UNSIGNED表示非负，它可以使整数最大值提高一倍，有符号和无符号使用相同的存储空间，具有相同的性能。

     为整型指定宽度，如INT(11)，对于存储来说INT(1)和INT(20)是相同的，它不会限制值的合法范围，只是规定了MySQL与客户端的交互应该显示多少位而已，比如你向INT(1)中插入了123456值，数据库中其实已经存入了123456，只是对于客户端查出来是1而已。

 

2、实数类型

     实数是带小数部分的数字，然而它不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数，FLOAT和DOUBLE类型支持使用标准的浮点运算进行四舍五入。DECIMAL用于存储精确的小数。FLOAT占用4个字节，DOBULE占用8个字节，它比DECIMAL占用空间更小、计算浮更更快，因此在非存储财务数据时，可以考虑其它的类型，如放大N倍后存为BIGINT。

 

3、字符串类型

     MySQL支持多种字符串类型，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则，这些东西很大程度上影响性能。

     A、VARCHAR和CHAR类型

          VARCHAR类型用于存储变长字符串，它会删除末尾的所有空格，它比定长字符串更省空间，因为它仅使用必要的空间（越短的字符串占用越少），VARCHAR会用1或2个额外字节记录字符串长度，VARCHAR节省了空间，因此对性能是有帮助的，但是因为行是变长的，所以每次UPDATE时会重新计算字符串长度，会比定长多做额外工作。因此不是说VARCHAR就一定能提升性能，同样要分使用场景，如果字符串的最大长度比平均长度大很多，且列的更新很少时则适合用VARCHAR。注意InnoDB会把过长的VARCHAR存储为BLOB，通常人们喜欢将IP地址存储为VARCHAR型，其实IP地址是32位二进制表示的,你可以转为十进制表示，因而不是字符串，用小数是将它分为四段便于阅读。

          CHAR是定长的，每个列无论字符串长度大小都会分配固定存储空间，CHAR适合存储字符很短，或者所有值接近一个长度时，如存储MD5码、SESSION_ID等，对于经常变更的数据CHAR也比VARCHAR好，再一个是对于非常短的列，CHAR比VARCHAR更节省空间，例如使用CHAR(1)和VARCHAR(1)来存储Y或N时，CHAR需要一个字符，而VARCHAR需要两个，因为还有一个长度字节

    B、BLOB和TEXT类型

         它们都是为存储大数据而设计的，BLOB是采用二进制/TEXT采用字符存储。MySQL不能将BLOB和TEXT列全部进行索引（只能做前面某长度的索引，因此在查询SQL时不能用LIKE前模糊匹配，那样就走不到索引了），也不能使用这些索引进行排序。

   技巧：使用枚举代替字符串类型，枚举可以把一些重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，MySQL在列中保存值为枚举中的位置整数。枚举最不好的是字符串是固定的，添加或删除必须使用ALTER TABLE。因此对于未来会改变的字符串，使用枚举不是一个好主意，除非能接受在枚举末尾添加元素，由于枚举有一个映射转换过程，所以枚举虽然能减少存储空间，但是也会增加一些额外开销。

 

4、时间和时期类型

          DATETIME：这个类型能保存1001到9999年，精度为秒，与时区无关，使用8个字节存储，存储格式封装为YYYYMMDDHHMMSS的整数，因此它是一种可排序的类型，显示时以ANSI标准定义的日期和时间表示方法显示。

          TIMESTAMP：它保存了从1970年1月1日午夜以来的秒数，也就是常说的时间戳，它使用4个字节来存储，依赖于时区。除了特殊情况外，通常我们应该尽量使用TIMESTAMP，因为它比DATETIME更省空间，现实中有些人喜欢使用int来存储时间戳（包括刚出来工作时的我），这并不会带来任何收益，用整数保存时间戳不方便，因为取出来之后还需要程序转换下，所以不推荐这样做。

 

5、位数据类型

     MySQL有少数几种存储类型使用紧凑的位存储数据。不管底层存储格式如何处理，从技术上说都是字符型。

     BIT：BIT(1)表示定义一个包含一位的长度，BIT(2)表示2个位，以此类推，BIT列最大长度是64位，MySQL把BIT当字符串处理，而不是数字，当检索BIT(1)的值时，结果是一个包含二进制0或1的字符串，而不是ASCII码的0或1，例如00111001，它的二进制等于57，检索它时得到是一个字符码为57的字符，也就是ASCII码57对应字符为9。一般在应用中尽量避免使用BIT，对于大部分应用来说最好是不用。