mysql字符串不等于怎么优化_MySQL 数据类型优化（二：字符串） | 剑花烟雨江南...

MySQL 支持多种字符串类型，每种类型还有很多变化。

VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR 是两种最主要的字符串类型。不幸的是，很难解释这些值是怎么存储在磁盘和内存中的，因为这和存储引擎有关。下面的描述假设使用的存储引擎是 InnoDB 或者 MyISAM。如果是其他存储引擎，请参考相应的文档。

先看看 VARCHAR 和 CHAR 值通常在磁盘上怎么存储。请注意，存储引擎存储 CHAR 或者 VARCHAR 值的方式在内存和磁盘上可能不一样，所以MySQL 服务器从存储引擎读出的值可能需要转换为另一种存储格式。

VARCHAR

VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。有一种情况例外，如果MySQL 表使用 ROW_FORMAT = FIXED 的话，每一行都会使用定长存储，很浪费空间。

VARCHAR 类型需要使用 1 或者 2 个额外字节记录字符的长度：如果列的最小长度小于或等于255 字节，则只使用 1 个自己表示，否则使用 2 个字节。假设使用 latin1 字符集，一个VARCHAR(10) 的列需要11个字节的存储空间。VARCHAR(1000) 的则需要 1002 个字节。

VARCHAR 节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE 时可能使行变得比原来更长，这就要需要额外的工作。

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

InnoDB 则更为灵活，它可以把过长的 VARCHAR 存储为 BLOB。

CHAR

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

CHAR 适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR 非常适合存储密码的 md5 值，因为这是一个定长的值。

CHAR 类型的这些行为可能有点难理解，我们通过以下的例子说明。首先，我们创建一张只有一个 CHAR(10) 字段的表并且插入一些数据：

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

mysql > INSERT INTO char_test (char_col) VALUES

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

当检索这些值的时候，会发现 string3 末尾的空格被截断了：

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

如果用 VARCHAR(10) 字段存储相同的值，可以得到如下结果：

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串。Memory 引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。不过填充和截取空格的行为在不同存储引擎都是一样的，因为这是在 MySQL 服务器层处理的。

与 CHAR 和 VARCHRA 类似的类型还有 BINARY 和 VATBINARY ，它们存储的二进制字符串。二进制字符串跟常规字符串成非常类似，但是二进制存储的字节码而不是字符，填充也不一样。

慷慨是不明智的

使用 VARCHAR(5)和 VARCHAR(200) 存储‘hello’的空间开销是一样的，那么使用更短的列有什么优势吗？

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL 通常会分配固定大小的内存块来报错内部值。尤其是使用内存临时表进行排序或者操作时会特别糟糕，在利用磁盘临时表进行排序时也同样糟糕。

所以最好的策略是只分配真正需要空间。

BLOB 和 TEXT 类型

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

实际上，它们分别属于两组不同的数据类型家族：字符串类型是 TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；对应的二进制类型是 TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。BLOB 是 SMALLBLOB 的同义词，TEXT 是 SMALLTEXT 的同义词。

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

MySQL 对 BLOB 和 TEXT 列进行排序与其他类型是不同的：它对每个列的最前 max_sort_length 字节而不是整个字符串做排序。

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

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL 在内部会将每个值在列表中的位置保持为一个整数，并且在 .frm 文件中保持“数字 - 字符串”映射关系的“查找表”。

枚举最不好的地方是，字符串的列表是固定的，添加或删除字符串的必须使用 ALERT TABLE 。