mysql数据类型的选择

mysql数据选择相信大家都应该知道，如果选择不好，会大大影响效率的。

要知道mysql都有什么数据类型，可以使用mysql帮助文档，输入 ? data types 即可，如下图：

再次输入 ? tinyint 可以看到 tinyint 类型在有符号的情况下范围是 -128~127，在无符号的情况下是 0 ~ 255。

所以查询数据类型的信息可以使用帮助文档来。下面就分析一下数据类型的使用。

一般情况下选择可以正确存储数据的最小数据类型。越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。简单的数据类型的操作通常需要更少的CPU周期。例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。这也是很多开发者把ip地址(如 192.168.0.1)转为无符号整型来存储的原因。

尽量避免NULL:尽量制定列为NOT NULL，除非真的需要NULL类型的值。因为可能为NULL列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。

整型数据介绍。
根据上面的分析，如果能确定整型的范围不存在负数的话，就应该设置成无符号类型，如果能确定整型的范围不会是负数并且最大值不超过 255，那么就应该选择最小的 无符号 tinyint。而不是int 或者其它。对应在创建表时，指定的数据类型大小，如 tinyint(3),int(16)等，只是用来显示，就是说显示出来的是按照设定好的大小呈现出来，但是实际存储依然是按照 tinyint 和int的实际大小来存储，也就是说 tinyint(1) 和 tinyint(8)实际存储的大小都是8位，所占的空间是相等的。

浮点型数据类型
浮点型数据类型有 real、float、double，这三种数据类型也被称作不确定数据类型，为啥不确定，就是后面保留的小数点值不确定。下面看一个例子：

CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));

insert into test values(131072.32,131072.32);

select * from test;

+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
#从上面的例子中我们看到c1列的值由131072.32变成了131072.31，这就是浮点数的不精确性造成的。

所以在存在使用比较运算符 =、<、> 、<=、>= 等时，要避免使用浮点数比较，在统计需要准确类型时，比如银行统计账号资金等，也不合适使用浮点数。而应该使用确定类型，比如 decimal。

字符型数据类型
字符型数据类型分为 char 和 varchar。
char是定长的，适合存储很短的字符串，或者所有值都接近同一个长度，例如MD5值。对于经常变更的数据，定长的char类型不容易产生碎片，在空间存储上也比varchar更有效率。
varchar类型用于存储可变长字符串，比char更节省空间。varchar需要使用1或2个额外字节记录字符串长度，如果列的最大长度小于或等于255字节，则使用1个字节表示，否则使用2个。

varchar适合使用该类型的情况：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了UTF-8这样复杂的字符集，每个字符的使用不同字节数进行存储。varchar还会保留末尾的空格。

当存储CHAR时MySQL会默认删除行尾空格。因为CHAR值通常需要用空格进行填充以方便比较。对于经常变更的数据CHAR比较VARCHAR要好，因为定长的CHAR类型不容易产生碎片。对于更短的列CHAR比VARCHAR要更加节省空间，因为VARCHAR总是需要一个额外的字节来存储长度。

对于使用InnoDB存储引擎，建议使用varchar数据类型来代替char。因为对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因 此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

时间数据类型
时间数据类型有 date、time、datetime、timestamp。date格式是YYYY-MM-DD，time的格式是hh:mm:ss，datetime就是 YYYY-MM-DD hh:mm:ss。timestamp是时间戳格式，timedate存储是8字节大小，而timestamp是4字节大小，所以，时间最好是用timestamp类型来存储。

BLOB和TEXT类型
BLOB和TEXT是用来存储很大的数据来设计的字符串数据类型，分别采用二进制和字符方式进行存储。
实际上他们时属于两种不同数据类型家族：

字符类型： TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT；

二进制类型：TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB。

与其他数据类型不同，它们被当作单独的对象进行处理。当BLOB和TEXT值太大时InnoDB会使用专门的外部区域来进行存储，此时每个值只需要1—4个字节在行内存储一个指针即可，然后在外部存储区域存储实际的值。

BLOB和TEXT的仅有的区别在于BLOB类型存储的时二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序时与其他类型时不同的，它只对每个列的最前max_sort_length字节而不是整个字符串做排序。
MySQL不能将BLOB和TEXT列全部长度进行字符串进行索引，也不能使用这些索引消除排序。