MySQL JSON类型详解

一、MySQL JSON类型概述

MySQL 5.7版本及以上开始支持JSON数据类型，通过这种类型，用户可以存储和查询JSON数据。

JSON（JavaScript Object Notation）是一种轻量级的数据格式，数据结构简洁清晰，易于阅读和编写。

二、MySQL JSON类型长度

JSON类型的最大存储空间与LONGTEXT相同，即2^32-1个字符。

我们可以通过以下代码查询JSON类型所需的空间大小：

SHOW VARIABLES LIKE 'max_allowed_packet';

三、MySQL的JSON数据类型

在MySQL中，JSON类型有两种实现方式：

1、存储为字符串：将JSON数据存储为字符串类型，通过JSON函数进行处理。

CREATE TABLE person (
  id int PRIMARY KEY,
  info json
);
INSERT INTO person (id,info) VALUES(1,'{"name":"bob","age":18}');
SELECT info->>"$.name" AS name FROM person WHERE id=1;

2、存储为二进制：将JSON数据存储为二进制类型，通过JSON函数进行处理。相比较于存储为字符串类型，会占用更少的存储空间。

CREATE TABLE person (
  id int PRIMARY KEY,
  info json
) ROW_FORMAT=DYNAMIC;
INSERT INTO person (id,info) VALUES(1,'{"name":"bob","age":18}');
SELECT info->>"$.name" AS name FROM person WHERE id=1;

四、MySQL JOIN类型

在MySQL中，JSON类型也可以用于JOIN操作。相比较与传统关系型数据库，JSON类型的JOIN会更加灵活，且避免了复杂的数据转换操作。

CREATE TABLE person (
  id int PRIMARY KEY,
  info json
);
CREATE TABLE pet (
  id int PRIMARY KEY,
  person_id int,
  info json,
  FOREIGN KEY (person_id) REFERENCES person(id)
);
INSERT INTO person (id,info) VALUES(1,'{"name":"bob","age":18}');
INSERT INTO pet (id,person_id,info) VALUES(1,1,'{"name":"tom","type":"cat"}');
SELECT person.info->>"$.name" AS person_name, pet.info->>"$.name" AS pet_name FROM person JOIN pet ON person.id=pet.person_id;

五、MySQL INT类型

MySQL也支持在JSON类型中存储数字类型的数据，可以通过JSON的CAST函数将数组类型转换为INT类型。

CREATE TABLE demo (
  id int PRIMARY KEY,
  scores json
);
INSERT INTO demo (id,scores) VALUES(1,'[98,72,85]');
SELECT CAST(scores->"$[0]" AS SIGNED) AS score_1 FROM demo;

六、MySQL的JS类型

MySQL 5.7版本及以上也支持一些基本的JavaScript函数，可以在JSON类型中执行JavaScript代码。

SELECT JSON_OBJECT('score', 95, 'pass', IF(95 > 60, 'true', 'false')) AS result;

七、MySQL的NUM类型

MySQL没有NUM类型，但可以通过JSON类型来模拟实现，常见的方法是将每一个数字存储为字符串类型。

CREATE TABLE demo (
  id int PRIMARY KEY,
  nums json
);
INSERT INTO demo (id,nums) VALUES(1,'["1","2","3"]');
SELECT SUM(CAST(nums->"$[0]" AS SIGNED)) AS result FROM demo;

八、MySQL JSON类型的坏处

使用MySQL JSON类型的坏处主要有以下几点：

1、不支持全文索引：MySQL的JSON类型不支持全文索引，需要单独建立全文索引。

2、不支持标准化：MySQL的JSON类型会使得数据难以标准化，不同的开发者会对JSON的结构有不同的解读。

3、对于某些复杂的查询，性能会差于传统的关系型数据库，需要进行深入的优化和分析。