mysql encode&decode

MySQL urlencode 函数、urldecode函数 、multiurldecode 函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

DELIMITER ;   DROP FUNCTION IF EXISTS urlencode;   DELIMITER |   CREATE FUNCTION urlencode (s VARCHAR (4096)) RETURNS VARCHAR (4096) DETERMINISTIC CONTAINS SQL BEGIN         DECLARE c VARCHAR (4096) DEFAULT '' ;         DECLARE pointer INT DEFAULT 1;         DECLARE s2 VARCHAR (4096) DEFAULT '' ;           IF ISNULL (s) THEN             RETURN NULL ;         ELSE         SET s2 = '' ;         WHILE pointer <= length(s) DO            SET c = MID(s,pointer,1);            IF c = ' ' THEN               SET c = '+' ;            ELSEIF NOT (ASCII(c) BETWEEN 48 AND 57 OR                  ASCII(c) BETWEEN 65 AND 90 OR                  ASCII(c) BETWEEN 97 AND 122) THEN               SET c = concat( "%" ,LPAD(CONV(ASCII(c),10,16),2,0));            END IF;            SET s2 = CONCAT(s2,c);            SET pointer = pointer + 1;         END while;         END IF;         RETURN s2; END ; | DELIMITER ;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

DROP FUNCTION IF EXISTS urldecode;   DELIMITER |   CREATE FUNCTION urldecode (s VARCHAR (4096)) RETURNS VARCHAR (4096) DETERMINISTIC CONTAINS SQL BEGIN         DECLARE c VARCHAR (4096) DEFAULT '' ;         DECLARE pointer INT DEFAULT 1;         DECLARE h CHAR (2);         DECLARE h1 CHAR (1);         DECLARE h2 CHAR (1);         DECLARE s2 VARCHAR (4096) DEFAULT '' ;           IF ISNULL (s) THEN            RETURN NULL ;         ELSE         SET s2 = '' ;         WHILE pointer <= LENGTH(s) DO            SET c = MID(s,pointer,1);            IF c = '+' THEN               SET c = ' ' ;            ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN               SET h1 = LOWER (MID(s,pointer+1,1));               SET h2 = LOWER (MID(s,pointer+2,1));               IF (h1 BETWEEN '0' AND '9' OR h1 BETWEEN 'a' AND 'f' )                   AND                   (h2 BETWEEN '0' AND '9' OR h2 BETWEEN 'a' AND 'f' )                   THEN                     SET h = CONCAT(h1,h2);                     SET pointer = pointer + 2;                     SET c = CHAR (CONV(h,16,10));                END IF;            END IF;            SET s2 = CONCAT(s2,c);            SET pointer = pointer + 1;         END while;         END IF;         RETURN s2; END ;    |   DELIMITER ;

当一个字符串经过多次 urlencode 处理之后，可以使用 multiurldecode来解码，它将多次调用 urldecode来解码，直到没有需要解码的字符

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

DELIMITER ;   DROP FUNCTION IF EXISTS multiurldecode;   DELIMITER |   CREATE FUNCTION multiurldecode (s VARCHAR (4096)) RETURNS VARCHAR (4096) DETERMINISTIC CONTAINS SQL BEGIN         DECLARE pr VARCHAR (4096) DEFAULT '' ;         IF ISNULL (s) THEN            RETURN NULL ;         END IF;               REPEAT            SET pr = s;            SELECT urldecode(s) INTO s;         UNTIL pr = s END REPEAT;         RETURN s; END ;    |   DELIMITER ;