數據庫優化提速(三)JSON數據類型在酒店管理系統搜索

在 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 類型的優勢與注意事項

優勢：
- 適合存儲半結構化數據（如客人的動態入住記錄、靈活的附加服務）。
- 無需預先定義所有字段（如案例 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