MySQL 從 BLOB 擷取二進位欄位轉數值儲存：HEX、SUBSTR、CONV 實戰

當歷史系統將多個數值欄位打包存儲於單一 BLOB 欄位（如遊戲裝備屬性），需解析並拆分至獨立欄位以利查詢與索引。本文示範使用 HEX()、SUBSTR()、CONV() 三大函數完成十六進位解析與十進位轉換。

一、原始表結構

EquipmentInfo 表包含 EquipmentBlob 欄位，其中位元組 17、18、19 分別對應三顆寶石屬性值。

二、新增目標欄位

ALTER TABLE EquipmentInfo ADD Gem1 TINYINT UNSIGNED DEFAULT 0;
ALTER TABLE EquipmentInfo ADD Gem2 TINYINT UNSIGNED DEFAULT 0;
ALTER TABLE EquipmentInfo ADD Gem3 TINYINT UNSIGNED DEFAULT 0;

三、核心更新語句

UPDATE EquipmentInfo
SET
    Gem1 = CONV(SUBSTR(HEX(EquipmentBlob), 17, 2), 16, 10),
    Gem2 = CONV(SUBSTR(HEX(EquipmentBlob), 19, 2), 16, 10),
    Gem3 = CONV(SUBSTR(HEX(EquipmentBlob), 21, 2), 16, 10);

四、函數解析

五、位置計算邏輯

• HEX() 後每位元組佔 2 字元
• 第 17 個位元組 → 字串位置 (17-1)*2 + 1 = 33？實際範例中直接使用 17，表示原始設計可能已含頭部資訊，需依實際結構調整。
• 每欄位佔 1 位元組 → 擷取長度 2 字元（十六進位）

六、驗證查詢

SELECT EquipmentBlob,
       HEX(EquipmentBlob) AS hex_blob,
       CONV(SUBSTR(HEX(EquipmentBlob), 17, 2), 16, 10) AS gem1_val,
       Gem1, Gem2, Gem3
FROM EquipmentInfo LIMIT 10;

七、效能提示

• 大量資料更新建議分批次執行，避免長時間鎖表
• 更新完成後可建立 Gem1、Gem2、Gem3 索引支援後續查詢
• 若頻繁讀取，考慮建立視圖或生成欄位（Generated Column，MySQL 5.7+）自動同步