71 lines
1.7 KiB
Plaintext
71 lines
1.7 KiB
Plaintext
DELIMITER $$
|
|
|
|
CREATE OR REPLACE PROCEDURE usp_parsejson (
|
|
IN _uuid VARCHAR(100),
|
|
IN _json TEXT
|
|
)
|
|
/*
|
|
CALL usp_parsejson('1234', '{ "id":"abc", "nmae": "Michael"}');
|
|
SELECT * from _jsondata WHERE guid = '1234';
|
|
|
|
DELETE FROM _jsondata WHERE guid = '1234';
|
|
*/
|
|
BEGIN
|
|
DECLARE _json_clean TEXT;
|
|
DECLARE _pair TEXT;
|
|
DECLARE _key VARCHAR(255);
|
|
DECLARE _value TEXT;
|
|
DECLARE _valuetype VARCHAR(20);
|
|
DECLARE _pos INT DEFAULT 1;
|
|
DECLARE _len INT;
|
|
DECLARE _curr INT DEFAULT 1;
|
|
DECLARE _next INT;
|
|
DECLARE _cnt INT DEFAULT 1;
|
|
|
|
-- 建立暫存表
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS _jsondata (
|
|
guid VARCHAR(100),
|
|
rowid INT,
|
|
fieldname VARCHAR(255),
|
|
contents TEXT,
|
|
fieldtype VARCHAR(20)
|
|
);
|
|
|
|
-- 清除左右大括號與空白
|
|
SET _json_clean = REPLACE(_json,'{','');
|
|
SET _json_clean = REPLACE(_json_clean,'}','');
|
|
|
|
-- 計算逗號數（也就是 key:value 數）
|
|
SET _len = LENGTH(_json_clean) - LENGTH(REPLACE(_json_clean, ',', '')) + 1;
|
|
|
|
WHILE _cnt <= _len DO
|
|
-- 取出第 cnt 個 key:value 配對
|
|
SET _pair = SUBSTRING_INDEX(SUBSTRING_INDEX(_json_clean, ',', _cnt), ',', -1);
|
|
|
|
-- 取出 key
|
|
SET _key = SUBSTRING_INDEX(_pair, ':', 1);
|
|
SET _key = REPLACE(TRIM(_key),'"','');
|
|
|
|
-- 取出 value
|
|
SET _value = SUBSTRING_INDEX(_pair, ':', -1);
|
|
SET _value = TRIM(_value);
|
|
|
|
-- 判斷型別
|
|
IF LEFT(_value, 1) = '"' AND RIGHT(_value, 1) = '"' THEN
|
|
SET _valuetype = 'string';
|
|
SET _value = REPLACE(_value,'"','');
|
|
ELSE
|
|
SET _valuetype = 'number';
|
|
END IF;
|
|
|
|
-- 寫入結果
|
|
INSERT INTO _jsondata (guid, rowid, fieldname, contents, fieldtype)
|
|
VALUES (_uuid, _cnt, _key, _value, _valuetype);
|
|
|
|
-- 下一筆
|
|
SET _cnt = _cnt + 1;
|
|
END WHILE;
|
|
END$$
|
|
|
|
DELIMITER ;
|