{
  "device_type": "temperature_humidity_sensor",
  "model": "TH-S200",
  "readings": {
    "temperature_celsius": 23.5,
    "humidity_percent": 62.8
  },
  "battery_level": 87,
  "status": "online"
}

SELECT * FROM device_telemetry WHERE payload LIKE '%temperature_humidity_sensor%';

SELECT *
FROM device_telemetry
WHERE payload->>'$.device_type' = 'temperature_humidity_sensor';

SELECT *
FROM device_telemetry
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.device_type')) = 'temperature_humidity_sensor';

SELECT *
FROM device_telemetry
WHERE JSON_VALID(payload)
  AND payload->>'$.device_type' = 'temperature_humidity_sensor';

SELECT *
FROM device_telemetry
WHERE JSON_VALID(payload)
  AND COALESCE(payload->>'$.device_type', '') = 'temperature_humidity_sensor';

-- ? 錯誤：類型不匹配（字符串 vs 整數(shù)）
WHERE payload->>'$.battery_level' < 50;

-- ? 正確方式一：轉(zhuǎn)換為數(shù)值
WHERE CAST(payload->>'$.battery_level' AS UNSIGNED) < 50;

-- ? 更嚴(yán)謹(jǐn)（防止非數(shù)字）：
WHERE JSON_VALID(payload)
  AND CAST(
        CASE 
          WHEN payload->>'$.battery_level' REGEXP '^[0-9]+$' 
          THEN payload->>'$.battery_level' 
          ELSE '0' 
        END AS UNSIGNED
      ) < 50;

WHERE CAST(payload->>'$.readings.temperature_celsius' AS DECIMAL(5,2)) > 23.0;

ALTER TABLE device_telemetry
ADD COLUMN extracted_device_type VARCHAR(64) 
  GENERATED ALWAYS AS (payload->>'$.device_type') VIRTUAL;

CREATE INDEX idx_device_type ON device_telemetry(extracted_device_type);

SELECT * 
FROM device_telemetry 
WHERE extracted_device_type = 'temperature_humidity_sensor';

ALTER TABLE device_telemetry ADD COLUMN device_type VARCHAR(64);
-- 應(yīng)用層寫入時同時填充 device_type 和 payload
CREATE INDEX idx_device_type ON device_telemetry(device_type);

-- 1. 創(chuàng)建表
CREATE TABLE device_telemetry (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    device_id VARCHAR(36) NOT NULL,
    event_time DATETIME(3) NOT NULL,
    payload TEXT NOT NULL
);

-- 2. 插入測試數(shù)據(jù)
INSERT INTO device_telemetry (device_id, event_time, payload) VALUES
('d8a3b1e4-5c2f-4f8a-9e1d-0a2b3c4d5e6f', '2026-01-10 14:23:11.456',
 '{"device_type":"temperature_humidity_sensor","model":"TH-S200","readings":{"temperature_celsius":23.5,"humidity_percent":62.8},"battery_level":87,"status":"online"}'),
('a1b2c3d4-e5f6-7890-1234-567890abcdef', '2026-01-10 15:01:33.120',
 '{"device_type":"smart_lock","model":"LOCK-X9","event":"unlock_success","user_id":"U10045","method":"fingerprint","battery_level":45,"status":"locked_after_5s"}');

-- 3. 安全查詢
SELECT * FROM device_telemetry
WHERE JSON_VALID(payload)
  AND payload->>'$.device_type' = 'temperature_humidity_sensor';

-- 4. 添加生成列（優(yōu)化）
ALTER TABLE device_telemetry
ADD COLUMN extracted_device_type VARCHAR(64) AS (payload->>'$.device_type') VIRTUAL;
CREATE INDEX idx_device_type ON device_telemetry(extracted_device_type);

-- 5. 高效查詢
SELECT * FROM device_telemetry WHERE extracted_device_type = 'temperature_humidity_sensor';