CHAR与VARCHAR

CHAR属于固定长度的字符类型，VARCHAR属于可变长度的字符类型。

值 CHAR(4) 存储需求 VARCHAR(4) 存储需求
‘’ ‘    ’ 4个字节 ‘ ’ 1个字节
‘ab’ ‘ab ’ 4个字节 ‘ab ’ 3个字节
‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节
‘abcdefgh’ ‘abcd’ 4个字节 ‘abcd’ 5个字节

由于CHAR长度固定，所以它处理速度比VARCHAR要快很多，但是缺点是浪费存储空间，程序需要对行尾空格进行处理。对于长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。

不同的存储引擎对CHAR和VARCHAR的使用原则有所不同。

  • InnoDB

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

  • MyISAM

    建议使用固定长度的数据列代替可变长度的数据列。

  • MEMORY

    都使用固定长度的数据行存储，因此无论使用CHAR或者VARCHAR列都没有关系。两者都是作为CHAR类型处理。

TEXT与BLOB

BLOB能用来保存二进制数据，比如照片；
Text只能保存字符数据，比如一篇文章或者日记。

常见问题

  1. BLOB和TEXT值会引起一些性能问题，特别是在执行大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用optimize table 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

  2. 使用合成的(Synthetic)索引来提高文本字段(BLOB或TEXT)的查询性能。合成索引就是根据大文本字段的内容建立一个散列值，并将这个值存储在单独的数据列中，通过检索散列值就可以找到数据行(只能用于精确匹配的查询)。可以使用MD5()、SHA1()、CRC32()生成散列值。

  3. 在不必要的时候避免检索大型的BLOB或TEXT值。

  4. 把BLOB或者TEXT列分离到单独的表中。

浮点数与定点数

浮点数一般用于表示含有小数部分的数值。

当一个字段被定义为浮点类型后，如果被插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在Mysql中float、double(或real)用来表示浮点数。

定点数不同于浮点数，定点数实际上是以字符串的形式存放的，所以定点数可以更精确地保存数据。
如果实际插入的数值精度大于实际定义的精度，则Mysql会进行警告(默认的SQLModel下)，但是数据会按照实际精度四舍五入后插入；如果SQLModel是在Tranditional(传统模式)下，则系统会直接报错，导致数据无法插入。在Mysql中，decimal(或numberic)用来表示定点数。

在今后的关于浮点数和定点数的应用中，要考虑到：

  • 浮点数存在误差
  • 对于货币等对精度敏感的数据，应该应用定点数标书或存储。
  • 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较。
  • 要注意浮点数中一些特殊值的处理。

日期类型

Mysql提供的常用的日期类型有DATE、TIME、DATETIME、TIMESTAMP。日期选择原则：

  • 根据实际需要选择能够满足应用最小的存储的日期类型。这样不仅能节约存储，更能提高表的操作效率。

  • 如果要记录年月日时分秒，并且记录的年份比较久远，最好使用DATETIME,不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

  • 如果记录的日期需要要让不同时区的用户使用，那么最好是使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

总结

  • 对于字符类型，要根据存储引擎来进行相应的选择。

  • 对于精度要求较高的应用中，建议使用定点数来存储数值，以保证结果的正确性。

  • 对于含有TEXT和BOLG字段的表，如果经常做删除和修改记录的操作要定时执行optimize table功能对表进行碎片整理。

  • 日期类型要根据实际需要选择能够满足应用的最小存储的日期类型。