当前位置： 首页 > ai >正文

数据库优化提速(三)JSON数据类型在酒店管理系统搜索—仙盟创梦IDE

ai 2025/8/24 6:36:06

在 MySQL 中，JSONB 类型（MySQL 中实际为 JSON 类型，功能类似 PostgreSQL 的 JSONB，支持高效的 JSON 数据存储和查询）非常适合存储半结构化数据，例如酒店入住客人的复杂信息（包含客人基本信息、入住记录、附加服务等）。

下面以酒店入住客人信息为例，讲解如何设计 JSON 类型字段的表结构，以及如何进行查询操作。

一、设计含 JSON 字段的表结构

假设我们需要存储客人的基础信息（姓名、身份证等）和动态入住记录（多次入住的详情），可以设计一张表 hotel_guests，其中包含一个 JSON 类型的字段 guest_data 存储核心信息：

sql

CREATE TABLE hotel_guests (id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增ID（唯一标识）guest_data JSON NOT NULL,  -- JSON类型字段，存储客人所有信息create_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 记录创建时间
);

guest_data 字段的 JSON 结构示例（包含嵌套和数组）：

json

{"basic_info": {"name": "张三","id_card": "110101199001011234","phone": "13800138001","gender": "男"},"check_in_records": [  -- 数组：存储多次入住记录{"order_id": "ORD20240501001","room_number": "101","check_in_date": "2024-05-01 14:30:00","check_out_date": "2024-05-03 12:00:00","room_type": "豪华单间","total_price": 800.00,"services": ["早餐", "洗衣服务"]  -- 附加服务},{"order_id": "ORD20240610002","room_number": "202","check_in_date": "2024-06-10 15:00:00","check_out_date": "2024-06-12 11:00:00","room_type": "双床房","total_price": 600.00,"services": ["早餐"]}]
}

二、插入测试数据

向表中插入 3 条含 JSON 数据的记录（模拟 3 位客人的信息）：

sql

INSERT INTO hotel_guests (guest_data) VALUES
-- 客人1：张三（2次入住记录）
('{"basic_info": {"name": "张三","id_card": "110101199001011234","phone": "13800138001","gender": "男"},"check_in_records": [{"order_id": "ORD20240501001","room_number": "101","check_in_date": "2024-05-01 14:30:00","check_out_date": "2024-05-03 12:00:00","room_type": "豪华单间","total_price": 800.00,"services": ["早餐", "洗衣服务"]},{"order_id": "ORD20240610002","room_number": "202","check_in_date": "2024-06-10 15:00:00","check_out_date": "2024-06-12 11:00:00","room_type": "双床房","total_price": 600.00,"services": ["早餐"]}]
}'),-- 客人2：李四（1次入住，未退房）
('{"basic_info": {"name": "李四","id_card": "310101199505055678","phone": "13900139002","gender": "女"},"check_in_records": [{"order_id": "ORD20240502003","room_number": "301","check_in_date": "2024-05-02 16:00:00","check_out_date": null,"room_type": "行政套房","total_price": 1200.00,"services": ["接机服务", "早餐"]}]
}'),-- 客人3：王五（1次入住，含特殊需求）
('{"basic_info": {"name": "王五","id_card": "440101200010109012","phone": "13700137003","gender": "男"},"check_in_records": [{"order_id": "ORD20240504004","room_number": "101","check_in_date": "2024-05-04 10:00:00","check_out_date": "2024-05-06 11:30:00","room_type": "豪华单间","total_price": 800.00,"services": ["早餐"],"special_request": "需要婴儿床"  -- 额外的动态字段}]
}');

三、基于 JSON 字段的查询案例

MySQL 提供了丰富的 JSON 函数（如 ->、->>、JSON_EXTRACT、JSON_CONTAINS 等），用于查询 JSON 字段中的数据。以下是酒店场景中的常见查询需求：

案例 1：查询所有客人的姓名和电话（提取 JSON 中的基础字段）

需求：快速获取客人的姓名和联系方式，用于前台沟通。
核心语法：->> 用于提取 JSON 字段并转为字符串（-> 提取为 JSON 格式）。

sql

SELECT-- 提取 basic_info 中的 name 和 phone（->> 转为字符串）guest_data->>'$.basic_info.name' AS 客人姓名,guest_data->>'$.basic_info.phone' AS 联系电话,guest_data->>'$.basic_info.gender' AS 性别
FROM hotel_guests;-- 结果：
-- 客人姓名 | 联系电话    | 性别
-- 张三     | 13800138001 | 男
-- 李四     | 13900139002 | 女
-- 王五     | 13700137003 | 男
案例 2：查询 “张三” 的所有入住记录（按 JSON 字段筛选）

需求：根据客人姓名查询其所有入住详情（订单号、房间号、价格等）。
核心语法：JSON_EXTRACT 提取字段，结合 WHERE 条件筛选。

sql

SELECTguest_data->>'$.basic_info.name' AS 客人姓名,-- 提取 check_in_records 数组（保留 JSON 格式）guest_data->'$.check_in_records' AS 入住记录
FROM hotel_guests
-- 条件：姓名为“张三”（注意字符串需用单引号）
WHERE guest_data->>'$.basic_info.name' = '张三';-- 结果：
-- 客人姓名 | 入住记录（JSON数组，包含2条订单）
-- 张三     | [{"order_id": "ORD20240501001", ...}, {...}]
案例 3：查询所有 “豪华单间” 的入住记录（筛选 JSON 数组中的元素）

需求：统计所有入住过 “豪华单间” 的客人及订单信息。
核心语法：JSON_CONTAINS 判断数组中是否包含满足条件的元素，JSON_TABLE 解析 JSON 数组为行（MySQL 8.0+ 支持）。

sql

-- 方法1：判断是否有豪华单间的入住记录（返回整行）
SELECTguest_data->>'$.basic_info.name' AS 客人姓名,guest_data->>'$.check_in_records[*].order_id' AS 相关订单号
FROM hotel_guests
-- 条件：check_in_records 数组中存在 room_type = "豪华单间" 的元素
WHERE JSON_CONTAINS(guest_data->'$.check_in_records','{"room_type": "豪华单间"}','$'
);-- 结果：张三（1次）和王五（1次）住过豪华单间
-- 客人姓名 | 相关订单号
-- 张三     | ["ORD20240501001", "ORD20240610002"]
-- 王五     | ["ORD20240504004"]-- 方法2：用 JSON_TABLE 解析数组为行（更清晰展示每条订单）
SELECTjt.order_id AS 订单号,g.guest_data->>'$.basic_info.name' AS 客人姓名,jt.room_type AS 房间类型,jt.total_price AS 总金额
FROM hotel_guests g,
-- 将 check_in_records 数组解析为多行
JSON_TABLE(g.guest_data->'$.check_in_records','$[*]' COLUMNS (order_id VARCHAR(20) PATH '$.order_id',room_type VARCHAR(30) PATH '$.room_type',total_price DECIMAL(10,2) PATH '$.total_price')
) AS jt
-- 筛选房间类型为豪华单间
WHERE jt.room_type = '豪华单间';-- 结果：
-- 订单号         | 客人姓名 | 房间类型 | 总金额
-- ORD20240501001 | 张三     | 豪华单间 | 800.00
-- ORD20240504004 | 王五     | 豪华单间 | 800.00
案例 4：查询 “未退房” 的客人（筛选 JSON 中的 NULL 值）

需求：前台需要确认当前在店客人（check_out_date 为 null）。
核心语法：JSON_SEARCH 查找 NULL 值的路径，结合 IS NOT NULL 判断。

sql

SELECTguest_data->>'$.basic_info.name' AS 客人姓名,guest_data->>'$.check_in_records[*].room_number' AS 房间号,guest_data->>'$.check_in_records[*].check_in_date' AS 入住时间
FROM hotel_guests
-- 条件：存在 check_out_date 为 null 的入住记录
WHERE JSON_SEARCH(guest_data,'one',  -- 查找第一个匹配项NULL,   -- 匹配 NULL 值'$',    -- 从根路径开始'$.check_in_records[*].check_out_date'  -- 匹配的路径
) IS NOT NULL;-- 结果：仅李四未退房
-- 客人姓名 | 房间号   | 入住时间
-- 李四     | ["301"] | ["2024-05-02 16:00:00"]
案例 5：统计 “2024 年 5 月” 的总营收（聚合 JSON 中的数值）

需求：财务统计 5 月所有订单的总金额。
核心语法：JSON_TABLE 解析数组为行，结合日期函数和 SUM() 聚合。

sql

SELECTSUM(jt.total_price) AS '2024年5月总营收'
FROM hotel_guests g,
JSON_TABLE(g.guest_data->'$.check_in_records','$[*]' COLUMNS (check_in_date DATETIME PATH '$.check_in_date',total_price DECIMAL(10,2) PATH '$.total_price')
) AS jt
-- 筛选入住时间在2024年5月的订单
WHERE DATE_FORMAT(jt.check_in_date, '%Y-%m') = '2024-05';-- 结果：
-- 2024年5月总营收
-- 2800.00 （张三800 + 李四1200 + 王五800）
案例 6：查询有 “特殊需求” 的客人（动态字段查询）

需求：筛选有特殊要求（如需要婴儿床）的客人，便于客房准备。
核心语法：直接查询 JSON 中可能存在的动态字段（无需预先定义表结构）。

sql

SELECTguest_data->>'$.basic_info.name' AS 客人姓名,guest_data->>'$.check_in_records[*].special_request' AS 特殊需求
FROM hotel_guests
-- 条件：存在 special_request 字段且不为空
WHERE guest_data->>'$.check_in_records[*].special_request' IS NOT NULL;-- 结果：
-- 客人姓名 | 特殊需求
-- 王五     | ["需要婴儿床"]

四、JSON 类型的优势与注意事项

  1. 优势：

    • 适合存储半结构化数据（如客人的动态入住记录、灵活的附加服务）。
    • 无需预先定义所有字段（如案例 6 中的 special_request 可动态添加）。
    • 支持嵌套和数组，能更自然地表达复杂关系（如一个客人对应多个入住记录）。

  2. 注意事项：

    • 复杂查询（如案例 3）需要结合 JSON_TABLE 等函数，语法较关系型查询更复杂。
    • 索引优化有限（MySQL 支持 JSON 字段的部分索引，但效率不如传统字段）。
    • 不适合频繁更新 JSON 中的某个字段（建议整体更新或用 JSON_SET 函数）。

通过上述案例可以看出，JSON 类型在处理酒店客人这类包含动态、复杂信息的数据时非常灵活，尤其适合需要快速扩展字段或存储数组 / 嵌套结构的场景。

阿雪技术观

在科技发展浪潮中，我们不妨积极投身技术共享。不满足于做受益者，更要主动担当贡献者。无论是分享代码、撰写技术博客，还是参与开源项目维护改进，每一个微小举动都可能蕴含推动技术进步的巨大能量。东方仙盟是汇聚力量的天地，我们携手在此探索硅基生命，为科技进步添砖加瓦。

Hey folks, in this wild tech - driven world, why not dive headfirst into the whole tech - sharing scene? Don't just be the one reaping all the benefits; step up and be a contributor too. Whether you're tossing out your code snippets, hammering out some tech blogs, or getting your hands dirty with maintaining and sprucing up open - source projects, every little thing you do might just end up being a massive force that pushes tech forward. And guess what? The Eastern FairyAlliance is this awesome place where we all come together. We're gonna team up and explore the whole silicon - based life thing, and in the process, we'll be fueling the growth of technology

http://www.xdnf.cn/news/18462.html

相关文章：

  • python企微发私信
  • 【React ✨】从零搭建 React 项目：脚手架与工程化实战（2025 版）
  • 文字学的多维透视：从符号系统到文化实践
  • 2025年09月计算机二级MySQL选择题每日一练——第五期
  • Go语言实战案例-Redis连接与字符串操作
  • 井云智能体封装小程序：独立部署多开版 | 自定义LOGO/域名，打造专属AI智能体平台
  • IDEA控制台乱码（Tomcat）解决方法
  • IDEA相关的设置和技巧
  • 机器人 - 无人机基础(5) - 飞控中的传感器(ing)
  • CTFshow Pwn入门 - pwn 19
  • 《天龙八部》角色安全攻防全解析：从渗透测试视角看江湖成败
  • 【Golang】有关任务窃取调度器和抢占式调度器的笔记
  • STM32F1 USART介绍及应用
  • 开发指南134-路由传递参数
  • 支持蓝牙标签打印的固定资产管理系统源码（JAVA）
  • linux编程----网络通信（TCP）
  • LLM实践系列：利用LLM重构数据科学流程04 - 智能特征工程
  • 博士招生 | 英国谢菲尔德大学 招收计算机博士
  • 项目中优惠券计算逻辑全解析（处理高并发）
  • Unreal Engine UStaticMeshComponent
  • JUC之CompletionService
  • DFS序与树链剖分入门
  • 开发避坑指南(35)：mybaits if标签test条件判断等号=解析异常解决方案
  • 文件系统层面的可用块数量可用空间和比例
  • AI重塑职业教育：个性化学习计划提效率、VR实操模拟强技能，对接就业新路径
  • 拿到手一个前端项目，应该如何启动
  • 开发避坑指南(34)：mysql深度分页查询优化方案
  • Ubuntu解决makefile交叉编译的问题
  • Android Jetpack | Hilt
  • 机器人爆发、汽车换代，速腾聚创开始讲新故事