MySQL urlencode/urldecode 支持中文字符

MySQL urlencode/urldecode 支持中文字符

urlencode

DELIMITER ;
 
DROP FUNCTION IF EXISTS urlencode;
 
DELIMITER |
 
CREATE FUNCTION URLENCODE(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) CHARSET utf8
DETERMINISTIC
CONTAINS SQL
BEGIN
   -- the individual character we are converting in our loop
   -- NOTE: must be VARCHAR even though it won't vary in length
   -- CHAR(1), when used with SUBSTRING, made spaces '' instead of ' '
   DECLARE sub VARCHAR(1) CHARSET utf8;
   -- the ordinal value of the character (i.e. ñ becomes 50097)
   DECLARE val BIGINT DEFAULT 0;
   -- the substring index we use in our loop (one-based)
   DECLARE ind INT DEFAULT 1;
   -- the integer value of the individual octet of a character being encoded
   -- (which is potentially multi-byte and must be encoded one byte at a time)
   DECLARE OCT INT DEFAULT 0;
   -- the encoded return string that we build up during execution
   DECLARE ret VARCHAR(4096) DEFAULT '';
   -- our loop index for looping through each octet while encoding
   DECLARE octind INT DEFAULT 0;
 
   IF ISNULL(str) THEN
      RETURN NULL;
   ELSE
      SET ret = '';
      -- loop through the input string one character at a time - regardless
      -- of how many bytes a character consists of
      WHILE ind <= CHAR_LENGTH(str) DO
         SET sub = MID(str, ind, 1);
         SET val = ORD(sub);
         -- these values are ones that should not be converted
         -- see http://tools.ietf.org/html/rfc3986
         IF NOT (val BETWEEN 48 AND 57 OR     -- 48-57  = 0-9
                 val BETWEEN 65 AND 90 OR     -- 65-90  = A-Z
                 val BETWEEN 97 AND 122 OR    -- 97-122 = a-z
                 -- 45 = hyphen, 46 = period, 95 = underscore, 126 = tilde
                 val IN (45, 46, 95, 126)) THEN
            -- This is not an "unreserved" char and must be encoded:
            -- loop through each octet of the potentially multi-octet character
            -- and convert each into its hexadecimal value
            -- we start with the high octect because that is the order that ORD
            -- returns them in - they need to be encoded with the most significant
            -- byte first
            SET octind = OCTET_LENGTH(sub);
            WHILE octind > 0 DO
               -- get the actual value of this octet by shifting it to the right
               -- so that it is at the lowest byte position - in other words, make
               -- the octet/byte we are working on the entire number (or in even
               -- other words, oct will no be between zero and 255 inclusive)
               SET OCT = (val >> (8 * (octind - 1)));
               -- we append this to our return string with a percent sign, and then
               -- a left-zero-padded (to two characters) string of the hexadecimal
               -- value of this octet)
               SET ret = CONCAT(ret, '%', LPAD(HEX(OCT), 2, 0));
               -- now we need to reset val to essentially zero out the octet that we
               -- just encoded so that our number decreases and we are only left with
               -- the lower octets as part of our integer
               SET val = (val & (POWER(256, (octind - 1)) - 1));
               SET octind = (octind - 1);
            END WHILE;
         ELSE
            -- this character was not one that needed to be encoded and can simply be
            -- added to our return string as-is
            SET ret = CONCAT(ret, sub);
         END IF;
         SET ind = (ind + 1);
      END WHILE;
   END IF;
   RETURN ret;
END;
  
|
 
DELIMITER ;

urldecode

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_text = REPLACE(encoded_text, '%', '');
		SET unhex_text = UNHEX(rep_text);
        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 ;

Usage

mysql> SELECT URLENCODE('山东');
+---------------------+
| URLENCODE('山东')   |
+---------------------+
| %E5%B1%B1%E4%B8%9C  |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT urlDecode('%E5%B1%B1%E4%B8%9C');
+---------------------------------+
| urlDecode('%E5%B1%B1%E4%B8%9C') |
+---------------------------------+
| 山东                            |
+---------------------------------+
1 row in set (0.00 sec)

参考：
https://blog.hostlike.com/technology/database-dev/mysql/mysql-urlencode-urldecode-that-support-chinese-chars/
https://cxybb.com/article/weixin_33831673/91822470