Mysql中进行url汉子解密操作

Mysql 中进行url汉子解密操作

在实际业务中的get请求中的汉子会出现乱码操作,若不想在代码中操作,在数据库层面进行操作的话,具体如下函数。

get请求：

	http://127.0.0.1:8080/search/users?name=%E5%B0%8F%E6%98%8E%E5%90%8C%E5%AD%A6

汉子：

	小明同学

加密：

	%E5%B0%8F%E6%98%8E%E5%90%8C%E5%AD%A6

url_encode函数

函数作用

汉子加密操作

具体函数实现

DELIMITER $$
 
DROP FUNCTION IF EXISTS `url_encode`$$
 
CREATE FUNCTION url_encode(str VARCHAR(4096) CHARSET utf8mb4) RETURNS varchar(4096) CHARSET utf8mb4
BEGIN
  DECLARE sub VARCHAR(1) CHARSET utf8mb4;
  DECLARE val BIGINT DEFAULT 0;
  DECLARE ind INT DEFAULT 1;
  DECLARE oct INT DEFAULT 0;
  DECLARE ret VARCHAR(4096) DEFAULT '';
  DECLARE octind INT DEFAULT 0;
  IF str is NULL THEN
      RETURN NULL;
  ELSE SET ret = '';
      WHILE ind <= CHAR_LENGTH(str) DO
        SET sub = MID(str, ind, 1);
        SET val = ORD(sub);
        IF NOT (val BETWEEN 48 AND 57 OR 
                val BETWEEN 65 AND 90 OR 
                val BETWEEN 97 AND 122 OR 
                val IN (45, 46, 95, 126)) THEN
            SET octind = OCTET_LENGTH(sub);
            WHILE octind > 0 DO
              SET oct = (val >> (8 * (octind - 1)));
              SET ret = CONCAT(ret, '%', LPAD(HEX(oct), 2, 0));
              SET val = (val & (POWER(256, (octind - 1)) - 1));
              SET octind = (octind - 1);
            END WHILE;
        ELSE
          SET ret = CONCAT(ret, sub);
        END IF;
        SET ind = (ind + 1);
    END WHILE;
  END IF;
  RETURN ret;
END$$
 
DELIMITER ;

函数操作

SQL：

SELECT url_encode("小明同学");

结果：

%E5%B0%8F%E6%98%8E%E5%90%8C%E5%AD%A6

url_decode函数

函数作用

汉子解密操作

具体函数实现

DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode`$$
 
CREATE FUNCTION `url_decode`(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 ;

函数操作

SQL：

SELECT url_decode('%E5%B0%8F%E6%98%8E%E5%90%8C%E5%AD%A6');

结果：

小明同学