mysql从json中获取字段

原创

mob649e815da088 2024-08-26 04:18:48 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815da088的原创作品，请联系作者获取转载授权，否则将追究法律责任

从 JSON 中获取字段的 MySQL 实现指南

在现代开发中，处理 JSON 数据已经成为了常规操作，尤其是在数据库中。MySQL 提供了强大的 JSON 支持功能，所以你可以直接从 JSON 中提取数据。本文将引导你完成如何在 MySQL 中从 JSON 获取字段的全过程。

流程概述

以下是实现从 JSON 中获取字段的步骤：

步骤	描述
步骤 1	创建表并插入 JSON 数据
步骤 2	使用 JSON 函数提取数据
步骤 3	查询结果并验证结果

步骤详细说明

步骤 1：创建表并插入 JSON 数据

首先，你需要创建一个表，并插入一些包含 JSON 数据的记录。

-- 创建表，包含一个 JSON 类型的字段
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    data JSON -- 使用 JSON 类型存储数据
);

-- 插入示例数据
INSERT INTO users (name, data) VALUES
('Alice', '{"age": 25, "city": "New York"}'),
('Bob', '{"age": 30, "city": "Los Angeles"}'),
('Charlie', '{"age": 35, "city": "Chicago"}');

注释：在上述代码中，我们创建了一个名为 users 的表，包含一个 data 的 JSON 类型字段。然后插入了一些用户的信息，包括他们的年龄和城市。

步骤 2：使用 JSON 函数提取数据

一旦数据插入完毕，就可以使用 MySQL 的 JSON 函数来提取数据。

-- 从 JSON 字段提取年龄
SELECT name, JSON_EXTRACT(data, '$.age') AS age FROM users;

-- 从 JSON 字段提取城市
SELECT name, JSON_UNQUOTE(JSON_EXTRACT(data, '$.city')) AS city FROM users;

注释：JSON_EXTRACT 函数用于从 JSON 中提取字段。上面的代码首先提取 age 字段，然后提取 city 字段。JSON_UNQUOTE 用于去除结果中的引号，使输出更加友好。

步骤 3：查询结果并验证结果

执行上述查询语句后，你将获得结果。可以通过以下SQL语句来验证查询结果。

-- 查询所有用户的年龄和城市
SELECT 
    name, 
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.age')) AS age, 
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.city')) AS city 
FROM users;

注释：这条综合查询语句会展示所有用户的姓名、年龄和城市。

关系图 (ER 图)

使用以下 mermaid 语法，展示表 users 的结构：

erDiagram
    USERS {
        INT id PK "主键"
        VARCHAR name "用户姓名"
        JSON data "用户数据（JSON 格式）"
    }

序列图

我们可以使用以下 mermaid 语法描述用户查询 JSON 数据的流程：

sequenceDiagram
    participant User
    participant MySQL
    User->>MySQL: SELECT name, JSON_EXTRACT(data, '$.age') AS age FROM users;
    MySQL-->>User: name, age
    User->>MySQL: SELECT name, JSON_UNQUOTE(JSON_EXTRACT(data, '$.city')) AS city FROM users;
    MySQL-->>User: name, city