【重学 MySQL】五十九、二进制字符串类型与 JSON 类型

在MySQL数据库中，二进制字符串类型与JSON类型各自具有独特的特点和用途。

二进制字符串类型

二进制字符串类型在MySQL中主要用于存储二进制数据。这类数据类型包括BINARY、VARBINARY以及BLOB系列（TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）。

1. BINARY和VARBINARY

   • BINARY(M)：为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定（M），则默认只能存储1个字节。如果存储的字段值不足M个字节，MySQL将在右边填充’0’以补齐指定长度。
   • VARBINARY(M)：为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制（65535字节），另外还要考虑额外字节开销（VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数）。VARBINARY类型必须指定（M），否则报错。

2. BLOB系列

   • BLOB是一个二进制大对象（Binary Large Object），可以容纳可变数量的数据。MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB四种类型，它们可容纳值的最大长度不同。
   • 这些类型通常用于存储二进制的大对象，如图片、音频和视频等。但在实际工作中，往往不会在MySQL数据库中使用BLOB类型直接存储这些大对象数据，而是将文件存储到服务器的磁盘上，并将文件的访问路径存储到MySQL中。
   • 在使用BLOB和TEXT字段类型时，需要注意以下几点以优化数据库性能：
     • 执行大量的删除或更新操作后，可能会留下很多空洞。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理。
     • 避免在不必要的时候检索大型的BLOB或TEXT值。例如，使用SELECT *查询可能不是最佳选择，除非能确定WHERE子句只会找到所需的数据行。
     • 可以考虑将BLOB或TEXT列分离到单独的表中，以减少主表的碎片并提升性能。

JSON类型

JSON（JavaScript Object Notation）是一种轻量级的、基于文本的、跨语言的数据交换格式。它易于阅读和编写，同时支持数组和对象等复杂数据结构。

1. JSON数据类型的支持

   • MySQL从5.7.8版本开始支持JSON数据类型。在此之前，只能通过字符类型（CHAR、VARCHAR或TEXT）来保存JSON文档。
   • JSON数据类型在插入时能自动校验文档是否满足JSON格式的要求，并优化了存储格式，无需读取整个文档就能快速访问某个元素的值。

2. JSON字段的增删改查操作

   • 插入操作：可以直接插入JSON格式的字符串，也可以使用MySQL提供的JSON_ARRAY()和JSON_OBJECT()等函数来构造JSON数组和对象。
   • 查询操作：可以使用JSON_EXTRACT()函数从JSON文档中提取指定路径的元素。MySQL 8.0及以上版本还支持更复杂的路径表达式和语法糖（如column->path和column->>path）。
   • 修改操作：可以使用 JSON_INSERT() 函数在指定位置插入新值（仅当该位置不存在值时才插入），也可以使用其他JSON函数进行更新和删除操作。

3. JSON数据类型的优势

   • JSON数据类型使得在数据库中存储和查询复杂数据结构变得更加容易和高效。
   • 它结合了索引和优化的存储格式，可以降低磁盘IO消耗并节省网络带宽。
   • JSON格式的数据易于在不同应用程序之间传递和解析。

综上所述，二进制字符串类型与JSON类型在MySQL中各自扮演着重要的角色。二进制字符串类型主要用于存储二进制数据，而JSON类型则提供了一种高效、灵活的方式来存储和查询复杂数据结构。根据具体的应用场景和需求，可以选择合适的数据类型来优化数据库的性能和可维护性。