MySQL编程进阶之：`JSON`数据类型与`BLOB`、`TEXT`的性能对比与适用场景。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，晚上好！我是今晚的主讲人，咱们今儿个就来聊聊MySQL里那几位“存储大户”——JSON、BLOB、TEXT，看看它们之间到底有啥恩怨情仇，以及在不同场景下谁更能打。

咱们先来热热身，简单介绍一下这三位：

JSON： 这位是后起之秀，专门用来存储JSON格式的数据。JSON格式嘛，相信大家都见过，长得像Python里的字典或者JavaScript里的对象，键值对的那种。好处是结构化，方便程序读写。
BLOB： (Binary Large Object) 这位老大哥，啥都能装，二进制数据、图片、视频、压缩包，只要是二进制的，它都照单全收。
TEXT： 这位也算老牌选手了，主要用来存储长文本数据，比如文章、评论、日志等等。

好了，热身结束，咱们进入正题，开始扒一扒它们的性能和适用场景。

第一回合：存储空间

存储空间，这可是真金白银啊！谁更省钱，谁就更有优势。

数据类型	说明
`JSON`	存储JSON数据时，MySQL会对JSON进行解析和优化，可能会进行一些压缩，但总体来说，存储空间取决于JSON数据的复杂程度。如果JSON数据比较简单，可能比`TEXT`更省空间；如果JSON数据非常复杂，可能比`TEXT`更占空间。此外，`JSON`类型有额外索引开销，用于路径查询优化。
`BLOB`	`BLOB`是二进制数据，存储空间取决于实际的二进制数据大小。没有额外的开销。一般来说，存储同样大小的二进制数据，`BLOB`是最省空间的。但是，如果数据压缩后存储在`BLOB`中，需要考虑压缩算法的效率。`BLOB`分为`TINYBLOB`、`BLOB`、`MEDIUMBLOB`、`LONGBLOB`，存储大小范围不同。
`TEXT`	`TEXT`存储文本数据，存储空间取决于文本数据的长度和字符集。如果使用UTF-8字符集，一个汉字占3个字节。`TEXT`也分为`TINYTEXT`、`TEXT`、`MEDIUMTEXT`、`LONGTEXT`，存储大小范围不同。如果存储的JSON数据是字符串格式，那么`TEXT`会直接存储字符串，不会进行解析和优化，因此存储空间可能比`JSON`更大。

简单来说，存储空间这玩意儿，得看你存的是啥。

如果是简单的JSON，JSON类型可能会更省。
如果是二进制数据，那BLOB绝对是首选。
如果是长篇大论的文本，TEXT也还行。

第二回合：查询性能

查询性能，这可是用户体验的关键！谁查得快，谁就能赢得用户的心。

数据类型	说明
`JSON`	`JSON`类型的优势在于可以对JSON数据进行部分查询和更新。MySQL提供了很多JSON相关的函数，比如`JSON_EXTRACT()`、`JSON_SET()`、`JSON_ARRAYAGG()`等等，可以方便地操作JSON数据。而且，MySQL 5.7.22之后，支持对JSON字段建立虚拟列索引，可以大大提高JSON字段的查询性能。但是，如果只是简单地查询整个JSON数据，而不需要进行任何的JSON操作，那么`JSON`类型的性能可能不如`TEXT`。此外，频繁地更新JSON字段的某个部分，可能会导致性能下降，因为MySQL需要解析整个JSON数据，然后进行更新，最后再重新存储。
`BLOB`	`BLOB`类型一般不用于查询，而是用于存储和读取二进制数据。如果需要对`BLOB`数据进行查询，通常需要先将`BLOB`数据转换成其他类型，比如字符串，然后再进行查询。这种方式的性能比较差。此外，`BLOB`字段不能建立索引，因此不能通过索引来提高查询性能。
`TEXT`	`TEXT`类型可以进行全文检索，但是需要建立全文索引。全文索引的建立和维护需要消耗一定的资源。如果只是简单地查询整个文本数据，`TEXT`类型的性能还是不错的。但是，如果需要对文本数据进行复杂的查询，比如模糊查询、正则表达式查询等等，`TEXT`类型的性能可能会比较差。此外，`TEXT`字段的长度比较大，可能会导致查询时需要读取更多的数据，从而影响性能。通常对TEXT类型的数据查询，需要限制返回的字段长度，避免不必要的数据传输。

简单来说，查询性能这玩意儿，得看你怎么查。

如果需要对JSON数据进行部分查询和更新，那JSON类型绝对是神器。
如果只是简单地存储和读取二进制数据，BLOB也还行。
如果需要对长文本数据进行全文检索，TEXT配合全文索引也能凑合用。

第三回合：数据操作

数据操作，这可是程序员的日常！谁操作起来更顺手，谁就能提高开发效率。

数据类型	说明
`JSON`	`JSON`类型提供了丰富的函数，可以方便地对JSON数据进行各种操作，比如提取JSON中的某个字段、更新JSON中的某个字段、合并两个JSON对象等等。这些函数都比较高效，而且使用起来也很方便。例如:
`BLOB`	`BLOB`类型主要用于存储和读取二进制数据，没有提供专门的操作函数。如果需要对`BLOB`数据进行操作，通常需要先将`BLOB`数据读取到应用程序中，然后使用应用程序提供的函数进行操作，最后再将操作后的数据写回`BLOB`字段。这种方式比较繁琐，而且性能比较差。例如，存储图片：需要读取图片文件，将内容转换成二进制流，然后存入`BLOB`。
`TEXT`	`TEXT`类型主要用于存储文本数据，提供了一些基本的字符串操作函数，比如`SUBSTRING()`、`CONCAT()`、`REPLACE()`等等。但是，这些函数的功能比较简单，而且性能也比较一般。如果需要对文本数据进行复杂的处理，通常需要使用应用程序提供的函数或者存储过程。`TEXT`类型的数据操作相对简单，例如，更新文本内容：直接使用`UPDATE`语句即可。

简单来说，数据操作这玩意儿，得看你操作的是啥。

如果需要对JSON数据进行各种花式操作，那JSON类型绝对是你的好帮手。
如果只是简单地存储和读取二进制数据，BLOB也还凑合。
如果需要对长文本数据进行一些基本的字符串操作，TEXT也勉强能用。

第四回合：适用场景

适用场景，这可是决定胜负的关键！谁能用对地方，谁就能发挥出最大的价值。

数据类型	适用场景
`JSON`	存储半结构化数据：比如用户信息、商品信息、配置信息等等。这些数据可能包含一些不确定的字段，使用`JSON`类型可以方便地存储这些数据，而不需要预先定义所有的字段。例如，存储用户扩展信息，用户可能有一些个性化的信息，这些信息不适合放到用户主表中，可以使用`JSON`类型来存储。 API接口数据：比如第三方API返回的数据，通常都是JSON格式的。使用`JSON`类型可以直接将这些数据存储到数据库中，而不需要进行额外的转换。
`BLOB`	存储二进制文件：比如图片、视频、音频、文档等等。存储压缩数据：比如将一些文本数据压缩后存储到`BLOB`字段中，可以节省存储空间。
`TEXT`	存储长文本数据：比如文章、评论、日志等等。存储HTML代码：比如网页内容、邮件内容等等。

简单来说，适用场景这玩意儿，得看你存的是啥。

如果是半结构化数据或者API接口数据，JSON类型绝对是最佳选择。
如果是二进制文件或者压缩数据，BLOB类型是当仁不让。
如果是长文本数据或者HTML代码，TEXT类型也能胜任。

实战演练

光说不练假把式，咱们来几个实战例子。

例子1：存储用户信息

假设我们要存储用户信息，包括用户名、年龄、性别、地址等等。但是，有些用户可能还有一些额外的个性化信息，比如爱好、职业、教育经历等等。这些信息不确定，使用JSON类型来存储就很方便。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('male', 'female'),
    address VARCHAR(255),
    extra_info JSON
);

-- 插入数据
INSERT INTO users (username, age, gender, address, extra_info) VALUES (
    '张三',
    20,
    'male',
    '北京市',
    '{"hobby": "篮球", "occupation": "程序员"}'
);

-- 查询数据
SELECT username, age, JSON_EXTRACT(extra_info, '$.hobby') AS hobby FROM users WHERE id = 1;

-- 更新数据
UPDATE users SET extra_info = JSON_SET(extra_info, '$.occupation', '高级程序员') WHERE id = 1;

例子2：存储图片

假设我们要存储用户头像，可以使用BLOB类型来存储。

CREATE TABLE user_avatars (
    user_id INT PRIMARY KEY,
    avatar MEDIUMBLOB
);

-- 插入数据
-- 这里需要使用编程语言来读取图片文件，然后将二进制数据插入到数据库中
-- 示例代码（PHP）：
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', 'password');
$user_id = 1;
$image_path = '/path/to/image.jpg';
$image_data = file_get_contents($image_path);

$stmt = $pdo->prepare('INSERT INTO user_avatars (user_id, avatar) VALUES (?, ?)');
$stmt->bindParam(1, $user_id, PDO::PARAM_INT);
$stmt->bindParam(2, $image_data, PDO::PARAM_LOB);
$stmt->execute();
?>

-- 查询数据
SELECT avatar FROM user_avatars WHERE user_id = 1;

-- 这里需要使用编程语言来将二进制数据转换成图片文件

例子3：存储文章

假设我们要存储文章内容，可以使用TEXT类型来存储。

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT
);

-- 插入数据
INSERT INTO articles (title, content) VALUES (
    'MySQL数据类型对比',
    '本文主要介绍了MySQL中JSON、BLOB、TEXT三种数据类型的性能对比和适用场景。'
);

-- 查询数据
SELECT title, SUBSTRING(content, 1, 100) AS content_preview FROM articles WHERE id = 1;

-- 更新数据
UPDATE articles SET content = REPLACE(content, 'MySQL', 'MariaDB') WHERE id = 1;

总结

好了，讲了这么多，相信大家对JSON、BLOB、TEXT这三位“存储大户”已经有了一定的了解。

记住，没有最好的数据类型，只有最适合的数据类型。在选择数据类型的时候，一定要根据实际的业务场景和需求，综合考虑存储空间、查询性能、数据操作等等因素，才能做出最佳的选择。

最后，给大家留几个思考题：

如果需要存储大量的JSON数据，并且需要频繁地对JSON数据进行部分查询和更新，应该如何优化性能？
如果需要存储大量的图片数据，并且需要支持图片的缩略图功能，应该如何设计数据库表结构？
如果需要存储大量的文章数据，并且需要支持全文检索功能，应该如何优化全文索引？

今天的讲座就到这里，谢谢大家！希望对大家有所帮助！如果各位观众老爷觉得讲的还行，赏个赞呗！

发表回复 取消回复

发表回复取消回复