字符串类型

MySQL 中的字符串类型比较多也比较的复杂，各个字符串类型的差别不仅仅在存储时候的空间占用，对存取时候字段某位的 strip 和 padding 还有差异。对于类型 CHAR/VARCHAR/TEXT 是跟本地字符集相关的，这会影响到实际占用空间的字节数、字符比较等。

根据官方手册，CHAR/BINARY 及其衍生的类型的数据是存储在表的行内部(inline)的，而对于 BLOB 和 TEXT 类型，每一个字段只占用该行 9-12(1~4+8)个字节(用于数据的地址和长度)，实际的数据是存储在 Row Buffer 之外位置的。所以对于经常访问的字符串类型，而长度又不是特别的大，还是建议用 VARCHAR(M) 的数据类型，性能会比 TEXT 快不少。

CHAR(M)/VARCHAR(M)

长度限制参数 M 表示的是本地字符集的字符个数而不是 bytes 数目，比如对于 UTF8 编码，每个本地字符其实际占用的 byte 长度可能是 3 或 4 倍的本地字符长度。比如 VARCHAR(255)，如果每个本地字符占用两个字节，那么其需要的存储空间最大为 255x2+2。CHAR 的 M 被限制在最大 255，而 VARCHAR 的 M 理论上受限于 Row Size 的长度(65,535bytes)，且实际存储时候会附加 1~2 字节的前缀表示数据实际长度。如果 strict SQL 模式没有被打开，那么当插入数据超过声明长度限制的时候，数据将会被截断并产生警告信息，在 strict SQL 模式下将会出错。

CHAR 类型在存储的时候，会在右端 padding SPACE 到指定的 M 长度，当取该字段的时候，所有末尾的 SPACE 都将会被 strip 掉然后返回；VARCHAR 不会对进行 SPACE 进行 padding 以及 strip 操作，存储什么样的数据就会返回什么样的数据。对于 CHAR/VARCHAR/TEXT 类型，在进行字符串比较的时候，(SQL 语句参数中的字符串)结尾的空格都是不参与比较的，但是对于 LIKE 语句，检索的时候结尾的空格是考虑在内的。

BINARY(M)/VARBINARY(M)

BINARY/VARBINARY 在操作的时候，参考的是 byte streaming 而不是 charaset streaming，所以其长度限制参数 M 表示的是 byte 数目，在比较的时候也是直接的数字大小比较(而非本地字符集方式比较)。

BINARY 在插入的时候，会使用 0x00(而非 SPACE)padding 到长度 M，取值的时候不会进行 strip 尾部空字符的操作(意味着取出来的长度一定是 M)；VARBINARY 则是保证原样存取的。

BLOB/TEXT

分别有 TINY/MEDIUM/LONG 类型的衍生长度，BLOB 是 bytes streaming 类型的，而 TEXT 是基于 character streaming 本地字符集类型的，两者在存取的时候都不会进行 padding 和 strip 操作。BLOB/TEXT 的关系和之前的 VARBINARY/VARCHAR 是比较相似的，除了：BLOB/TEXT 不能够有 DEFAULT 值；BLOB/TEXT 在创建索引的时候必须要有 prefix length，而前者是可选的；给予 TEXT 索引需要有前缀长度，而且建立索引会自动 padding SPACE 到索引长度，所以如果插入的字符前面一样，只是尾部空字符长度不同，也是会产生相同的索引值。

字符编码

先说一下 utf8 的标准，早期是用 1 ～ 6 个 byte 来表示一个字符。所以最早的 MySQL 实现，一个 Char 是用 6 个 Bytes 去实现的。这是正确的做法。但是 MySQL 为了性能，希望用户使用等长度的字符列。也就是说，一个字符如果用不到 6 个 byte，存储里就会被填充空白符号。学过计算机的人都会明白等长字符，用数组的索引值去找到数据会非常快。

MySQL 计划在 4.1 版本支持 utf8。4.1 的早期开发版本用最多 6 个 byte 表示一个 utf8 字符，这是对的。但是 MySQL 不知道脑子里抽了哪根筋，在 2002 年 9 月 27 日，for no particular reason，搞出这么一个 commit，强制让 utf8 编码只能处理最多 3 个 byte 的序列。

在 Unicode 中，3 个 Byte 可以支持所有的 BMP（basic multi-lingual plane）的字符；但是无法支持 SMP（supplementary multi-lingual plane），包括 emoji（这是重灾区），一些生僻的 CJK 字符，一部分生僻的符号等。对于主要的文字（英文、欧洲各种语种、中文、日文……），3 个 byte 的 utf8 也算是够用。但是，多年之后，也许是苹果强力推 emoji，大家才发现 MySQL 的 utf8 其实并不那么 utf8。直到 2010 年，MySQL 的 5.5.3 版本的时候，才引入了 utf8mb4（从此刻开始，utf8 是“utf8mb3“的 alias）。

JSON

MySQL 为我们提供了原生的 JSON 类型的支持，其使用方式类似于普通的字符串类型：

mysql> CREATE TABLE user(id INT PRIMARY KEY, name VARCHAR(20), lastlogininfo JSON);
Query OK, 0 rows affected (0.27 sec)

# 向 user 表插入普通数据与 json 数据。mysql 会对插入的数据进行 JSON 格式检查，确保其符合 JSON 格式，若插的是不合法的数据，会出现 Invalid JSON text 错误。
mysql> INSERT INTO user VALUES(1 ,"lucy",'{"time":"2015-01-01 13:00:00","ip":"
192.168.1.1","result":"fail"}');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO user VALUES(2 ,"bobo",'{"time":"2015-10-07 06:44:00","ip":"
192.168.1.0","result":"success"}');
Query OK, 1 row affected (0.04 sec)

也可以使用 JSON_OBJECT() 函数：

mysql> INSERT INTO user VALUES(1 ,"lucy",JSON_OBJECT("time",NOW(),"ip","
192.168.1.1","result","fail"));
Query OK, 1 row affected (0.00 sec)

操作符

JSON 结构允许我们传入操作符，如果传入的不是一个有效的键，则返回 Empty set。该表达式可以用于 SELECT 查询列表，WHERE/HAVING, ORDER/GROUP BY 中，但它不能用于设置值。

mysql>  SELECT * FROM user WHERE lastlogininfo ->'$.time' > '2015-10-02';

# 等价于
mysql>  SELECT * FROM user WHERE JSON_EXTRACT(lastlogininfo,'$.time') > '2015-10-02';

比较 JSON 值采用两个级别。第一级是基于 JSON 类型的比较。如果类型不同，则取决于哪种类型具有更高的优先级。如果是相同的 JSON 类型，则是第二级，使用该类型的规则来比较。下面的列表显示了 JSON 类型的比较规则,从最高优先级到最低优先级。显示在一行的类型则是具有相同的优先级：

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

使用 JSON_TYPE()函数返回指定属性对应的类型名称：

$ mysql> SELECT JSON_TYPE(lastlogininfo->'$.ip') FROM user;

虚拟列

值得一提的是，可以通过虚拟列对 JSON 类型的指定属性进行快速查询。创建虚拟列：

mysql> ALTER TABLE user ADD lastloginresult VARCHAR(15)
    -> GENERATED ALWAYS AS (lastlogininfo->'$.result') VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用时和普通类型的列查询是一样的：

mysql> SELECT lastloginresult FROM user WHERE name='lucy';