字符串类型

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';