MySQL的JSON类型和虚拟列索引

假设我们需要一个学生表,其中学生可以选课,学生-课程是多对多映射

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(114),
);
CREATE TABLE student_course_mid (
id INT PRIMARY KEY,
student_id INT,
course_id INT
);
CREATE TABLE course (
course_id INT PRIMARY KEY,
name VARCHAR(114)
);

这是很常见的多对多的实现方式.但有时,可能一张表有很多个多对多字段,就会带来相当数量的中间表,看着很麻烦.

这时候,可以将该多对多字段序列化成JSON,比如:

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(114),
course_id_ls VARCHAR(100)
);
CREATE TABLE course (
course_id INT PRIMARY KEY,
name VARCHAR(114)
);

INSERT INTO students (student_id, name, course_id_ls)
VALUES
(1, '张三', '[1, 2, 3]'),
(2, '李四', '[2, 3, 4]'),
(3, '王五', '[1, 4]');

这样下来,表结构确实整洁了,但查询效率会下降很多.比如想查询course_id包括(2,3)的所有学生,需要先将JSON序列化,再分别查询.

MySQL引入了原生的JSON数据类型.存储所需的空间大致与LongBlob或LongText相同.

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(114),
courses JSON
);

JSON就是字面意义上的JSON,几乎可以存放任意类型的数据.比如上文提到的列表([1,2,3]),字典({"id":1, "name":"break"}),以及更多层的嵌套.同时,MySQL可以自动判断JSON是否合法:如果不合法会产生错误.

插入时,当作普通字符串插入即可.

例子：

在MySQL中创建一个表，其中包含一个JSON类型的列来存储订单信息:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_info JSON
);

插入订单信息(多层嵌套的JSON对象):

INSERT INTO orders (order_info) VALUES (
    '{"user": {"name": "John Doe", "email": "john@example.com"},
      "items": [{"product_id": 101, "quantity": 2}, {"product_id": 102, "quantity": 1}],
      "payment": {"method": "credit_card", "amount": 150.00}}'
);

查询特定用户的订单:

SELECT order_info ->> '$.user.name' AS user_name
FROM orders
WHERE order_info ->> '$.user.email' = 'john@example.com';

->> 是 MySQL 中用于从 JSON 对象中提取值的操作符。它是一个组合操作符，结合了 ->（用于提取 JSON 对象中的值）和 >（用于从 JSON 数组中提取值）的功能.->> 操作符会返回 JSON 值作为字符串

查询时,直接select * from table的话,和查询一个VARCHAR或者TEXT类型是相似的,直接返回字符串

EXPLAIN一下:

EXPLAIN
SELECT order_info ->> '$.user.name' AS user_name
FROM orders
WHERE order_info ->> '$.user.email' = 'john@example.com';

发现就是基本的全表扫描+where,效率肯定比较差

此时我们可以创建虚拟列并为虚拟列建立索引

ALTER TABLE orders
ADD COLUMN user_name VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(order_info, '$.user.name'))) VIRTUAL,
ADD COLUMN email VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(order_info, '$.user.email'))) VIRTUAL;

CREATE INDEX idx_user_name ON orders(user_name);
CREATE INDEX idx_email ON orders(email);

此时使用虚拟列进行查询

SELECT user_name
FROM orders
WHERE email = 'john@example.com';

再次EXPLAIN一下,发现按预期使用了索引:

此外,在查询时,可以用 JSON_TABLE，他可以将JSON 数据转换成关系型表格形式,从而更容易与其他 SQL 表操作

SELECT
    jt.user_name,
    jt.user_email,
    jt.product_id,
    jt.quantity
FROM
    orders o,
    JSON_TABLE(
        o.order_info,
        '$.items[*]' COLUMNS (
            product_id INT PATH '$.product_id',
            quantity INT PATH '$.quantity',
            user_name VARCHAR(255) PATH '$.user.name',
            user_email VARCHAR(255) PATH '$.user.email'
        )
    ) AS jt
WHERE
    jt.product_id = 101;

'$.items[*]'：JSON 路径表达式,表示从 order_info 字段的 items 数组中提取每个元素
COLUMNS：定义提取的列及其数据类型
product_id INT PATH '$.product_id：从 JSON 数组中提取 product_id 字段,并将其转换为 INT 类型

将JSON 数组转化为SQL数组类型,再创建索引,称为多值索引

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(114),
courses JSON,
key course_id_idx((cast(courses as unsigned array)))
);

member of 用于检查一个元素是否是JSON数组中的一部分