mysql urldecode utf8_支持utf8的mysql urldecode funtion

不要吐槽我为什么用sql语句来做urldecode。。。

但是当你遇到这种情况而且搜到的sql都只支持英文时，这篇文章就有用了

参考文章：

http://psoug.org/snippet/url_decode_that_respects_utf8_characters_502.htm

http://www.w3schools.com/tags/ref_urlencode.asp

原方法在替换'%0A'时转换失败，加多一句处理，其他特殊符号均可参照表格用同样方法提前替换。

--------------------------------------------------

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 ;

分享到：

2012-03-08 01:41

浏览 10232

分类:数据库

评论