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

原创于 2025-08-23 16:19:59 发布 · 684 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #json #仙盟创梦IDE #东方仙盟 #数据库优化

数据库专栏收录该内容

35 篇文章

订阅专栏

在 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

SELECT
  guest_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：判断是否有豪华单间的入住记录（返回整行）
SELECT
  guest_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 解析数组为行（更清晰展示每条订单）
SELECT
  jt.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

SELECT
  guest_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

SELECT
  SUM(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

SELECT
  guest_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 类型的优势与注意事项

优势：
- 适合存储半结构化数据（如客人的动态入住记录、灵活的附加服务）。
- 无需预先定义所有字段（如案例 6 中的 special_request 可动态添加）。
- 支持嵌套和数组，能更自然地表达复杂关系（如一个客人对应多个入住记录）。
注意事项：
- 复杂查询（如案例 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