不要吐槽我为什么用sql语句来做urldecode。。。
但是当你遇到这种情况而且搜到的sql都只支持英文时,这篇文章就有用了
参考文章:
http://psoug.org/snippet/url_decode_that_respects_utf8_characters_502.htm
http://www.w3schools.com/tags/ref_urlencode.asp
原方法在替换'%0A'时转换失败,加多一句处理,其他特殊符号均可参照表格用同样方法提前替换。
------------------------------------
Sql代码:
DELIMITER $$
DROP FUNCTION
IF EXISTS `url_decode`$$
CREATE DEFINER = `root`@`%` FUNCTION `url_decode` (original_text TEXT) RETURNS TEXT CHARSET utf8
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 ;
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 result_text = CONVERT (
UNHEX(
REPLACE (encoded_text, "%", "")
) USING utf8
) ;
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 ;