mysql函数转义中文json(转换转义UTF-16（JSON实体）恢复正常UTF8 MySQL中)

时间：2021-12-23 09:44:06

DELIMITER @@ 
CREATE FUNCTION Unjson (instring TEXT CHARACTER SET utf8) 
RETURNS TEXT CHARACTER SET utf8 
BEGIN 
 
    DECLARE i INT DEFAULT 0; 
    DECLARE c VARCHAR(1); 
    DECLARE utfstr TEXT CHARACTER SET utf16 DEFAULT ""; 
    DECLARE outstring TEXT CHARACTER SET utf8 DEFAULT ""; 
 
    WHILE i < CHAR_LENGTH(instring) DO 
    SET i = i + 1; 
    SET c = SUBSTRING(instring, i, 1); 
    IF c = "\\" THEN 
     SET c = SUBSTRING(instring, i + 1, 1); 
     IF c = "u" THEN 
     SET utfstr = CONCAT(utfstr, UNHEX(SUBSTRING(instring, i + 2, 4))); 
     SET i = i + 5; 
     END IF; 
    ELSE 
     IF utfstr != "" THEN 
     SET outstring = CONCAT(outstring, CONVERT(utfstr USING utf8)); 
     SET utfstr = ""; 
     END IF; 
     SET outstring = CONCAT(outstring, c); 
    END IF; 
    END WHILE; 
    IF utfstr != "" THEN 
    SET outstring = CONCAT(outstring, CONVERT(utfstr USING utf8)); 
    END IF; 
 
    RETURN outstring; 
END@@ 
DELIMITER ;