Schema的优化和索引 - 选择最佳的数据类型 - 字符串类型

字符串类型

MySQL支持很多字符串类型，以及它们的许多变化类型。这些数据类型在4.1和5.0版本变化都比较大。可以说变得更复杂了。早在4.1版本中，每个字符串列都有自己的字符集和对于那些字符集的排序规则，或者叫做collation（校对）。

 

VARCHAR和CHAR

有两个主要的字符串类型就是VARCHAR和CHAR。它们都存储了字符值。不幸的是，很难准确解释这些值怎样存储在硬盘和内存中，因为这些都是有存储引擎实现的。我们假设你使用的是MyISAM和/或者InnoDB.如果不是，你最好查阅你使用存储引擎的相关文档。

 

让我们先看看VARCHAR和CHAR是怎样存储在硬盘上的。要注意的是存储引擎可能存储的CHAR或者VARCHAR在存入内存和硬盘上有所不同的，以及当服务器从存储引擎获得了这个值的时候，会把这个值转为另一个存储引擎的格式。下面就是两种类型的比较

 

VARCHAR：

 

VARCHAR存储了变长的字符串以及它是最常见的字符串数据类型。它占用的空间要少于定长的类型，因为它根据所需来决定需要的空间大小。特例就是MyISAM参数设为ROW_FORMAT=FIXED.这个参数使表的每一行使用固定大小的空间以及浪费大量的空间。

 

varchar使用了1或者2额外的字节记录值的长度：如果长度大约为255字节的话，大概使用1个字节，如果更多的话，那么就是2个字节。假如是latin1字符节，一个varchar(10)将会占用11字节的存储空间。一个varchar(1000)使用上限为1002字节。因为需要2字节存储长度信息。

 

VARCHAR会对性能有所帮助，因为它节约了空间。然而，因为行是变长，当你更新它们的时候，它们就会增长。这样会导致需要额外的一些工作。如果一行增长以及并不在原来的地址了。这些行为依赖于存储引擎。举个例子，MyISAM可能把行分为碎片。以及InnoDB可能切开页，把行填充到它的内部。其他的存储引擎可能在适当的地方不会更新数据。

 

当最大列长度比平均长度要大的时候就值得使用VARCHAR.当很少更新字段的时候，存储碎片就不是一个问题。以及当你使用复杂的字符集如UTF-8时，每个字符都使用变量的字节数存储。

 

在5.0以及最新的版本，当你存储和获取值的时候，MySQL会保留尾部的空格。在4.1和更早的版本，MySQL去掉尾部空格。

 

CHAR

CHAR是定长的：MySQL总是分配指定的内存空间去存储字符。当存储了一个CHAR值，MySQL去掉尾部空格。值附加了空格是比较的需要。

 

如果你存储的字符串是非常短的，用CHAR是非常有用的。举个例子，存储密码的MD5值使用CHAR是个好的选择。因为这个MD5一直是定长的。如果数据经常修改的话CHAR要优于VARCHAR。因为定长的行并不会有存储残片。对于非常短的列，CHAR要比VARCHAR高效。一个CHAR(1)用来存储Y和N值。这只会使用1个字节，但是一个VARCHAR(1)会使用两个字节。有个字节用来保存它的长度信息。

 

 

这个行为让人有点困惑，因此我们举一个例子。首先，我们创建一个有单独CHAR(10)列的表以及存储一些数值。

 

 

mysql> CREATE TABLE char_test( char_col CHAR(10));

mysql> INSERT INTO char_test(char_col) VALUES

-> ('string1'), (' string2'), ('string3 ');

 

当我们获取这个值的时候，尾部空格被去掉了。

 

 

mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;

+----------------------------+

| CONCAT("'", char_col, "'") |

+----------------------------+

| 'string1' |

| ' string2' |

| 'string3' |

+----------------------------+

 

如果我们保存相同的值到VARCHAR(10)的列。结果如下

 

mysql> SELECT CONCAT("'", varchar_col, "'") FROM varchar_test;

+-------------------------------+

| CONCAT("'", varchar_col, "'") |

+-------------------------------+

| 'string1' |

| ' string2' |

| 'string3 ' |

+-------------------------------+

 

数据的存储方式取决于存储引擎。每个存储引擎处理定长和变长并不是使用相同的方法。内存存储引擎使用的定长的行。因此它为每个值分配尽可能大的空间。即使这个字段是变长的也是如此。然而，附加和去除空格的行为时跨越存储引擎的。因为这是由MySQL自身来操作的。

 

CHAR和VARCHAR的兄弟类型是BINARY和VARBINARY.它们存储了二进制字符串。二进制字符串和传统字符串非常相似。但是他们存储字节而不是字符。附加也是不同的：MySQL给 BINARY值附加了\0而取代了空格。获取数据的时候也不会去掉它。

 

当你需要存储二进制数据以及希望MySQL把值作为字节去比较而不是字符的时候，这些类型是比较有用的。比较字符宽度的优势不仅仅是不区分大小写。MySQL逐个的比较二进制字符串的一个字节。比较依据就是每个字节的数值。作为结果来说，二进制比较 比 字符比较 简单的多。因此它们的速度更快。

 

 

BLOB和TEXT

BLOB和TEXT是为了分别存储超大的二进制数据和字符串。事实上，它们还有一些其他的属于同一种类的数据类型，如TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,以及LONGTEXT,以及二进制类型是TINYBOLB,SMALLBLOB,BLOB,MEDIUMBLOB和LONGBLOB.要注意的是BLOB就是SMALLBLOB,TEXT是SMALLTEXT.

 

不像其他的数据类型，MySQL用自身标识把每个BLOB和TEXT作为一个对象来处理。存储引擎专门的存储它们。当它们太大的时候，InnoDB会使用一个独立外部的存储区域来保存它们。一行中的每个值都需要1-4个字节的存储空间以及真正保存它们的地方实际上是外部的存储空间。

 

这两个类型唯一的区别就是，BLOB保存的二进制数据并没有校对规则或字符集，而TEXT保存的是字符串并有一个校对规则和字符集。

 

MySQL对BLOB和TEXT排序和其他类型有所不同：并不是对全长的String进行排序，而是根据列的max_sort_length字节来排序。如果你需要前几个字符进行排序。你可以降低max_sort_length 变量或者使用ORDER BY SUBSTRING(Column,length)