官方文档：https://dev.mysql.com/doc/refman/5.6/en/data-types.html

数值类型

1. 整数类型

• TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 分别使用 8、16、24、32、64 位存储空间。它们可以存储的值的范围从 -2(N-1) 到 2(N-1)-1，其中 N 是存储空间的位数。

• 整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍。

• 可以为整数类型指定宽度，例如 INT(11)，但这通常是没意义的，因为它不会限制值的合法范围，只是规定了 MySQL 的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1) 和 INT(11) 是相同的。

  2. 精确值类型

  | DECIMAL | 存储精确的数值数据值。这些类型用于需要保持精确精度的情况，例如货币数据。 | | —- | —- | | NUMERIC | 在MySQL中，NUMERIC 被实现为 DECIMAL |

DECIMAL 可以指定小数点前后所允许的最大位数来决定消耗的字节数。例如，DECIMAL(18,9) 表示存储的值将不会超过 18 位数字，且小数点后面有 9 位数字。每四个字节可存储 9 位数字，所以小数点前占用 4 个字节，小数点后占用 4 个字节，小数点本身占 1 个字节，共使用 9 个字节。

在标准SQL中，语法 DECIMAL(M) 等价于 DECIMAL(M,0)，M 的默认值是 10。如果刻度为 0，则十进制值不包含小数点或小数部分。

在 MySQL 中以二进制格式保存 DECIMAL 和 NUMERIC 的值。如果值太大超出了 BIGINT 的范围，也可以用 DECIMAL 存储整型。定点数表达法的缺点在于其形式过于僵硬，固定的小数点位置决定了固定位数的整数部分和小数部分，不利于同时表达特别大的数或特别小的数。

在实际生产中，并不推荐使用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为整型类型。比如，将资金类型使用分单位存储，而不是用元单位存储。如 1 元在数据库中用整型类型 100 存储。因为 DECIMAL 类型是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储，而金额字段可能达到数十万亿级别，用类型 DECIMAL 不好统一。更重要的是，DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型高效。因此，推荐使用 BIGINT 存储金额相关字段。

3. 浮点类型

FLOAT 和 DOUBLE 类型表示近似的数值数据值。MySQL 对单精度值（FLOAT）使用 4 个字节存储，对双精度值（DOUBLE）使用 8 个字节存储。

对于 FLOAT 类型，MySQL 支持可选的精度规范，例如 FLOAT(a)，但 FLOAT(a) 中的精度值仅用于确定存储大小。并且由于浮点值是近似值，而不是精确值，因此试图在比较中将它们视为精确值可能会导致问题。

MySQL 允许非标准语法：FLOAT(M,D) 或 DOUBLE(M,D)。(M,D) 表示可以存储最多 M 个数字的值，其中 D 个数字可以在小数点后。例如，定义为 FLOAT(7,4) 的列在显示时看起来像 -999.9999。MySQL在存储值时执行四舍五入，所以如果将 999.00009 插入到一个 FLOAT(7,4) 列中，大概结果是 999.0001。

注意，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出警告。MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。

4. BIT

BIT 是位数据类型，用来存储位值。BIT(m) 可以存储 m 个字节的值，m 的范围为 1～64 位。

日期时间类型

表示日期和时间的数据类型有以下几种：DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。注意，如果列的数据类型为 DATETIME 或 TIMESTAMP 的话，MySQL 可以自动为其初始化并更新属性值。

DATETIME 和 TIMESTAMP 的时区区别：
类型 TIMESTAMP 最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来，TIMESTAMP 保存时会根据当前时区转换为 UTC 格式进行保存，检索时再根据当前时区从 UTC 转回来。如果你的业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择。参数 time_zone 指定了当前使用的时区，默认为 SYSTEM 使用操作系统时区，用户可以通过该参数指定所需要的时区。

而 DATETIME 就是一个固定的字符串时间（仅对 MySQL 本身而言）表示。因此，使用 TIMESTAMP 需要考虑 Java 进程的时区和 MySQL 连接的时区；而使用 DATETIME 类型，则只需要考虑 Java 进程的时区。

如果你的项目有国际化需求，我推荐使用时间戳，并且要确保你的应用服务器和数据库服务器设置了正确的匹配当地时区的时区配置。其实，即便你的项目没有国际化需求，至少是应用服务器和数据库服务器设置一致的时区，也是需要的。

TIMESTAMP 的性能问题：
虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，需要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会存在性能抖动问题。为了优化 TIMESTAMP 的使用，强烈建议你使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区：

[mysqld]
time_zone = "+08:00"

日期自动更新机制：

timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；

这个特性是自动初始化和自动更新（Automatic Initialization and Updating）。自动更新指的是如果修改了其它字段，则该字段的值将自动更新为当前系统时间。它与 “explicit_defaults_for_timestamp” 参数有关。在 MySQL 5.6.5 版本之前，Automatic Initialization and Updating 只适用于 TIMESTAMP 类型，而且一张表中最多允许一个 TIMESTAMP 字段采用该特性。从 MySQL 5.6.5 版本后，Automatic Initialization and Updating 同时适用于 TIMESTAMP 和 DATETIME 类型，且不限制数量。

字符串类型

1. CHAR、VARCHAR

CHAR 和 VARCHAR 的区别：

• CHAR 列的长度固定为创建表时声明的长度。长度可以是 0～255 之间的任何值。在存储 CHAR 值时，如果长度不足创建时声明的长度，多余长度将用空格对其进行右补。在检索 CHAR 值时，MySQL 会删除所有的末尾空格。

• VARCHAR 列中的值是可变长度的字符串。长度可以指定为从 0～65535 的值。VARCHAR 的有效最大长度受最大行大小（65535 字符由所有列共享）和所使用的字符集的限制。当检索 VARCHAR 值时，MySQL 不会删除末尾的空格。

• VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度：如果列的最大长度小于等于 255 字节（字符数 * 字符集单个字符占用的字节数），则只使用 1 个字节的存储空间，否则使用 2 个字节。

CHAR 适合存储很短的字符串，或所有值都接近同一长度的数据。对于经常变更的数据，CHAR 比 VARCHAR 更好，因为定长的 CHAR 类型不容易产生内存碎片。对于非常短的列，CHAR 比 VARCHAR 在存储空间上也更有效率，因为 VARCHAR 还有一个记录长度的额外字节。

字符集：

在表结构设计中，除了将列定义为 CHAR 和 VARCHAR 用以存储字符以外，还需要额外定义字符对应的字符集，因为每种字符在不同字符集编码下，对应着不同的二进制值。常见的字符集有 GBK、UTF8，推荐把默认字符集设置为 UTF8MB4。否则，某些 emoji 表情字符无法在 UTF8 字符集下存储。在 MySQL 8.0 版本后，字符集默认为 UTF8MB4，而在 8.0 版本之前默认的字符集为 Latin1。

字符串截断：

如果没有启用严格 SQL 模式，并且向 CHAR 或 VARCHAR 列分配的值超过了该列的最大长度，则该值将被截断以适合该列，同时生成警告。对于非空格字符的截断，可以使用严格的 SQL 模式禁止值的插入。

• 对于 VARCHAR 列，在插入之前会截断超过列长度的末尾空格，并生成警告。
• 对于 CHAR 列，会默认截断插入值的多余末尾空格。 | 插入值 | CHAR(4) | 存储字节数 | VARCHAR(4) | 存储字节数 | | —- | —- | —- | —- | —- | | ‘ ‘ | ‘ ‘ | 4 bytes | ‘ ‘ | 1 bytes | | ‘ab’ | ‘ab ‘ | 4 bytes | ‘ab’ | 3 bytes | | ‘abcd’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes | | ‘abcdefgh’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |

2. BINARY、VARBINARY

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，但它们包含的是二进制字符串。即它们包含的是字节字符串而不是字符字符串，它们的长度是字节长度而不是字符长度。这说明它们没有字符集，并且排序和比较是基于列值字节的数值值来计算的。

3. BLOB、TEXT

BLOB 是一个二进制大对象，可以容纳可变数量的数据。

TEXT 类型也有四种

BLOB 用于存储二进制字符串（字节字符串），而 TEXT 列则被视为非二进制字符串（字符字符串）的存储方式，它是有字符集和排序规则的，这两种类型都用于存储大量数据，具体存储方式按存储引擎各有不同。

在大多数情况下，可以将 BLOB 列视为能够存储足够大数据的 VARBINARY 列。同样，也可以将 TEXT 列视为 VARCHAR 列。但是，BLOB 和 TEXT 在以下几个方面不同于 VARBINARY 和 VARCHAR：

• 保存或检索 BLOB 和 TEXT 列的值时不用删除尾部的空格。
• 对于 BLOB 和 TEXT 列的索引，必须指定索引前缀的长度。
• BLOB 和 TEXT 列不能有默认值。
• 排序时只使用该列的前 max_sort_length 个字节，max_sort_length 的默认值是1024。

使用 BLOB、TEXT 等大字段可能会导致严重的性能问题，比如导致产生磁盘临时表。MySQL 的临时表分为内存临时表和磁盘临时表，其中内存临时表使用 MySQL 的 MEMORY 存储引擎，磁盘临时表使用 MySQL 的 MyISAM 存储引擎。由于 MEMORY 存储引擎不支持 BLOB 和 TEXT 类型，所以如果有查询使用了 BLOB 或 TEXT 列且需要隐式使用临时表来进行排序，那么将不得不使用磁盘临时表，磁盘比内存慢得多，这会导致很严重的性能问题。

JSON 类型

MySQL 支持 JSON 对象和 JSON 数组两种类型，JSON 对象除了支持字符串、整型、日期类型，JSON 内嵌的字段也支持数组类型。需要注意是，JSON 类型是从 MySQL 5.7.8 版本开始支持的功能，而 8.0 版本解决了更新 JSON 的日志性能瓶颈。如果在生产环境中使用 JSON 数据类型，强烈推荐使用 MySQL 8.0 版本。与将 JSON 格式的字符串存储在字符串列中相比，JSON 数据类型提供了以下优点：

• 自动验证存储在 JSON 列中的 JSON 文档，无效的文档会产生错误。
• 存储在 JSON 列中的 JSON 文档被转换为允许对文档元素进行快速读访问的二进制格式。读取时，二进制格式的结构使 MySQL 能够直接通过键或数组索引查找子对象或嵌套值，而不需要读取文档中的所有值。
• MySQL 提供了一组 SQL 函数来支持对 JSON 值的操作，比如创建、操作和搜索。
• JSON 列是不能直接索引的，但可以通过在 JSON 列内的值上创建索引来提高查询性能，底层是通过虚拟列上的函数索引实现。

虚拟列索引使用：

ALTER TABLE User ADD COLUMN phone VARCHAR(50) AS (loginInfo->>"$.phone");
ALTER TABLE User ADD UNIQUE INDEX idx_phone(phone);

上述 SQL 先创建了一个虚拟列 phone，然后在这个虚拟列上创建一个唯一索引 idx_phone，这时再通过虚拟列 phone 进行查询，就可以看到优化器使用 idx_phone 索引了

1. JSON 标准化

在一个 JSON_OBJECT 中，如果出现了相同的 key，则最终的 value 值为第一个 key 关联的值，即前面的会覆盖后面的（first key wins）。为了使查找更有效，MySQL 还对 JSON 对象的键进行排序，因此查询结果不能保证和写入时的顺序是一致的。

JSON 数组包含一个由逗号分隔并用 [ 、 ] 字符括起来的值列表，允许不同类型的元素。

JSON 对象包含一组 K、V 键值对，用逗号分隔，用 { 、} 字符括起来，JSON 对象中的键必须是字符串，值允许不同类型的元素。允许在 JSON 数组元素和 JSON 对象键值中进行嵌套。

2. 搜索 JSON 值

2.1 路径表达式

MySQL 提供了 JSON 路径表达式，对于查询 JSON 文档的部分内容或修改 JSON 文档都非常有用。路径表达式使用一个 $ 字符来表示 JSON 文档，后面可以追加选择器，选择器依次表示文档中更具体的部分。

• .[*] 计算 JSON 对象中所有成员的值
• [*] 计算 JSON 数组中所有元素的值。举例：[ 3, {“a” : [5, 6], “b” : 10}, [99, 100]]
  • $[0] 的值为 3
  • $[1] 的值为 {“a” : [5, 6], “b” : 10}
  • $[1].a 的值为 [5, 6]
  • $[1].b 的值为 10
  • $[2] 的值为 [99, 100]
  • $[2][0] 的值为 99
  • $[3] 的值为 NULL

使用通配符的路径，可以进行计算，得到一个包含多个值的数组

2.2 JSON_CONTAINS

JSON_CONTAINS(target，condidate，[path])

通过返回 1 或 0 指示给定的候选 JSON 文档是否包含在目标 JSON 文档中。这里注意：第二个参数是不接受整数的，无论 JSON 元素是整型还是字符串

2.3 JSON_EXTRACT

JSON_EXTRACT(json_doc，path，[path...])

返回 JSON 文档中的数据，这些数据是从文档中与路径参数匹配的部分中选择的。如果参数为空或者文档中没有找到值的路径则返回 NULL。

3. 扩展阅读

虚拟列加索引优化：https://www.percona.com/blog/2016/03/07/json-document-fast-lookup-with-mysql-5-7/
如何正确使用JSON类型：https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong