MySQL urlencode/urldecode 支持中文字符

迷南。 2022-09-16 11:09 261阅读 0赞

# 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)

![1][]

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

[1]: /images/20220828/a20ff44d90f049ba95983a9a0e18ea34.png