利用JSON数据类型优化关系型数据库设计

前言

在关系型数据库中，传统的结构化存储方式要求预先定义好所有的列及其数据类型。

然而，随着业务的发展，这种设计可能会显得不够灵活，尤其是在需要扩展单个列的描述功能时。

JSON数据类型的引入，为关系型数据库提供了存储非结构化数据的能力，打破了关系型与非关系型数据库之间的界限。

本文将深入探讨JSON数据类型的优势，并通过实际案例展示如何在业务中有效使用JSON类型。

一、JSON数据类型的优势

1. 灵活的数据结构

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，支持复杂的数据结构，包括对象和数组。

与传统的固定列结构不同，JSON类型允许字段的无限扩展，无需预先定义所有列。

这种灵活性非常适合存储动态或非结构化的数据。

2. 支持多种数据类型

JSON不仅支持字符串、整型、浮点数等基本数据类型，还支持嵌套的JSON对象和数组。

例如，可以存储图片的元数据、用户的登录信息或用户画像标签等复杂数据。

3. 高效的查询与索引

从MySQL 5.7版本开始，JSON类型支持函数索引和多值索引（Multi-Valued Indexes），这使得在JSON字段上进行高效查询成为可能。

通过虚拟列和索引，可以显著提升查询性能。

4. 简化表结构设计

使用JSON类型可以减少表的列数，避免频繁执行 ALTER TABLE操作来添加新列。

这对于需要频繁扩展字段的业务场景非常有用。

二、JSON类型的基本用法

1. JSON对象与数组

JSON对象是由键值对组成的无序集合，而JSON数组是由有序的值组成的列表。例如：

JSON对象：存储图片的元数据

{"Image": {"Width": 800,"Height": 600,"Title": "View from 15th Floor","Thumbnail": {"Url": "http://www.example.com/image/481989943","Height": 125,"Width": 100},"IDs": [116, 943, 234, 38793]}
}

JSON数组：存储多个地理位置信息

[{"precision": "zip","Latitude": 37.7668,"Longitude": -122.3959,"City": "SAN FRANCISCO","State": "CA"},{"precision": "zip","Latitude": 37.371991,"Longitude": -122.026020,"City": "SUNNYVALE","State": "CA"}
]

2. JSON类型的存储与查询

在MySQL中，JSON类型的数据可以通过 JSON_EXTRACT和 ->>等操作符进行查询。例如：

SELECT userId, loginInfo->>"$.cellphone" AS cellphone
FROM UserLogin;

三、实战案例：用户登录信息存储

1. 表结构设计

假设一个用户可以通过手机、微信、QQ等多种方式登录，我们可以使用JSON类型存储登录信息：

CREATE TABLE UserLogin (userId BIGINT NOT NULL,loginInfo JSON,PRIMARY KEY(userId)
);

2. 插入数据

插入用户登录信息：

INSERT INTO UserLogin VALUES 
(1, '{"cellphone": "13918888888", "wxchat": "破产码农", "QQ": "82946772"}'),
(2, '{"cellphone": "15026888888"}');

3. 查询数据

通过 ->>操作符提取JSON字段：

SELECT userId, loginInfo->>"$.cellphone" AS cellphone
FROM UserLogin;

4. 创建虚拟列与索引

为了优化查询性能，可以创建虚拟列并为其添加索引：

ALTER TABLE UserLogin 
ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");ALTER TABLE UserLogin 
ADD UNIQUE INDEX idx_cellphone(cellphone);

四、实战案例：用户画像标签存储

1. 表结构设计

在用户画像场景中，可以使用JSON数组存储用户的标签：

CREATE TABLE UserTag (userId BIGINT NOT NULL,userTags JSON,PRIMARY KEY(userId)
);

2. 插入数据

插入用户标签数据：

INSERT INTO UserTag VALUES 
(1, '[2, 6, 8, 10]'),  -- 80后、高学历、小资、有房、常看电影
(2, '[3, 10, 12]');   -- 90后、常看电影、爱外卖

3. 多值索引与查询

从MySQL 8.0.17开始，支持在JSON数组上创建多值索引：

ALTER TABLE UserTag
ADD INDEX idx_user_tags ((CAST(userTags->"$" AS UNSIGNED ARRAY)));

通过 MEMBER OF、JSON_CONTAINS等函数进行高效查询：

-- 查询常看电影的用户
SELECT * FROM UserTag 
WHERE 10 MEMBER OF(userTags->"$");-- 查询80后且常看电影的用户
SELECT * FROM UserTag 
WHERE JSON_CONTAINS(userTags->"$", '[2, 10]');