1、URLENCODE

DELIMITER ; DROP FUNCTION IF EXISTS urlencode; DELIMITER | CREATE FUNCTION URLENCODE(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) CHARSET utf8DETERMINISTICCONTAINS SQLBEGIN   -- 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 ;