MySQL自定义url编码转为utf8mb4函数【转】

mysql 没有将url编码转为utf8/utf8mb4的函数，需要自定义一个
note：url编码，是浏览器url中传输数据时，使用的一种编码方式。

DELIMITER $$

DROP FUNCTION IF EXISTS `urlDecode`$$

CREATE FUNCTION `urlDecode`(original_text TEXT CHARSET utf8mb4) RETURNS TEXT CHARSET utf8mb4
BEGIN  
    DECLARE new_text TEXT DEFAULT NULL;  
    DECLARE pointer INT DEFAULT 1;  
    DECLARE end_pointer INT DEFAULT 1;  
    DECLARE encoded_text TEXT DEFAULT NULL;  
    DECLARE result_text TEXT DEFAULT NULL;  
    DECLARE rep_text TEXT DEFAULT NULL;  
    DECLARE unhex_text TEXT DEFAULT NULL;  
   
    SET new_text = REPLACE(original_text,'+',' ');  
    SET new_text = REPLACE(new_text,'%0A','\r\n');  
   
    SET pointer = LOCATE('%', new_text);  
    WHILE pointer <> 0 && pointer < (CHAR_LENGTH(new_text) - 2) DO  
        SET end_pointer = pointer + 3;  
        WHILE MID(new_text, end_pointer, 1) = '%' DO  
            SET end_pointer = end_pointer+3;  
        END WHILE;  
   
        SET encoded_text = MID(new_text, pointer, end_pointer - pointer);  
        SET rep_tex = REPLACE(encoded_text, '%', '');
        SET unhex_text = UNHEX(rep_tex);
        SET result_text = CONVERT(unhex_text USING utf8mb4);  
        SET new_text = REPLACE(new_text, encoded_text, result_text);  
        SET pointer = LOCATE('%', new_text, pointer + CHAR_LENGTH(result_text)); 
                
    END WHILE;  
   
    RETURN new_text;  
  
END$$

DELIMITER ;

原文地址：https://www.cxyzjd.com/article/qq_23501739/102641155