带有 URL 解码的 MySQL SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6993754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 20:48:15 来源:igfitidea点击:
MySQL SELECT with URL Decode
提问by Rob
Is there a way to perform a MySQL query and have one of the columns in the output directly urldecode, rather than have PHP do it.
有没有办法执行 MySQL 查询并直接在输出中使用 urldecode 列之一，而不是让 PHP 来做。
For example this table 'contacts' would contain,
例如，此表“联系人”将包含，
------------------------------------ |name |email | ------------------------------------ |John Smith|johnsmith%40hotmail.com| ------------------------------------
SELECT * FROM `contacts`
Would output,
会输出，
John Smith | johnsmith%[email protected]
约翰·史密斯 | 约翰史密斯%[email protected]
Is there something along the lines of,
有没有类似的东西，
SELECT name, urldecode(email) FROM `contacts`
To output,
要输出，
John Smith | [email protected]
约翰·史密斯 | [email protected]
采纳答案by Rob
I managed to use a seperate line of PHP code to urldecode the query response back from the SQL statement before it was passed to the json_encode which is used for the JQuery autocomplete.
在将查询响应传递给用于 JQuery 自动完成的 json_encode 之前，我设法使用单独的一行 PHP 代码对来自 SQL 语句的查询响应进行 urldecode。
回答by Mistdemon
If anyone is still looking for a mysql decode function:
如果有人仍在寻找 mysql 解码功能：
DROP TABLE IF EXISTS urlcodemap;
CREATE TABLE `urlcodemap` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`encoded` VARCHAR(128) NOT NULL,
`decoded` VARCHAR(128) NOT NULL,
UNIQUE KEY urlcodemapUIdx1(encoded),
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- Table that contains the list of encode\decode.
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%20"," ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%21","!");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%22","""");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%23","#");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%24","$");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%25","%");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%26","&");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%27","'");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%28","(");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%29",")");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2A","*");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2B","+");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2C",",");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2D","-");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2E",".");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2F","/");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%30","0");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%31","1");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%32","2");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%33","3");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%34","4");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%35","5");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%36","6");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%37","7");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%38","8");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%39","9");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3A",":");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3B",";");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3C","<");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3D","=");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3E",">");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3F","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%40","@");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%41","A");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%42","B");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%43","C");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%44","D");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%45","E");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%46","F");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%47","G");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%48","H");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%49","I");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4A","J");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4B","K");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4C","L");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4D","M");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4E","N");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4F","O");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%50","P");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%51","Q");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%52","R");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%53","S");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%54","T");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%55","U");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%56","V");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%57","W");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%58","X");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%59","Y");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5A","Z");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5B","[");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5C","\");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5D","]");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5E","^");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5F","_");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%60","`");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%61","a");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%62","b");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%63","c");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%64","d");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%65","e");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%66","f");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%67","g");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%68","h");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%69","i");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6A","j");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6B","k");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6C","l");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6D","m");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6E","n");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6F","o");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%70","p");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%71","q");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%72","r");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%73","s");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%74","t");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%75","u");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%76","v");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%77","w");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%78","x");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%79","y");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7A","z");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7B","{");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7C","|");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7D","}");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7E","~");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%80","`");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%82","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%83","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%84","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%85","…");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%86","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%87","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%88","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%89","‰");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8A","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8B","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8C","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8E","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%91","‘");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%92","'");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%93","“");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%94","”");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%95","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%96","–");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%97","—");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%98","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%99","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9A","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9B","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9C","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9E","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9F","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A1","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A2","￠");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A3","￡");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A4","¤");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A5","￥");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A6","|");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A7","§");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A8","¨");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A9","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AA","a");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AB","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AC","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AE","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AF","ˉ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B0","°");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B1","±");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B2","2");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B3","3");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B4","′");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B5","μ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B6","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B7","·");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B8","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B9","1");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BA","o");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BB","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BC","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BD","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BE","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BF","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C0","à");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C1","á");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C2","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C3","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C4","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C5","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C6","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C7","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C8","è");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C9","é");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CA","");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CB","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CC","ì");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CD","í");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CE","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CF","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D0","D");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D1","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D2","ò");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D3","ó");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D4","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D5","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D6","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D7","×");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D8","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D9","ù");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DA","ú");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DB","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DC","ü");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DD","Y");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DE","T");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DF","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E0","à");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E1","á");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E2","a");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E3","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E4","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E5","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E6","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E7","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E8","è");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E9","é");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EA","ê");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EB","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EC","ì");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%ED","í");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EE","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EF","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F0","e");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F1","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F2","ò");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F3","ó");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F4","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F5","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F6","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F7","÷");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F8","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F9","ù");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FA","ú");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FB","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FC","ü");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FD","y");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FE","t");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FF","?");
DELIMITER $$
DROP FUNCTION IF EXISTS `URLDECODER`$$
CREATE FUNCTION `URLDECODER`(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) DETERMINISTIC
BEGIN
DECLARE X INT;
DECLARE chr VARCHAR(256);
DECLARE chrto VARCHAR(256);
DECLARE result VARCHAR(4096);
SET X = 1;
WHILE X <= (SELECT MAX(id) FROM urlcodemap) DO
SET chr = (SELECT `encoded` FROM urlcodemap WHERE id = X);
SET chrto = (SELECT `decoded` FROM urlcodemap WHERE id = X);
SET str = REPLACE(str,chr,chrto);
SET X = X + 1;
END WHILE;
RETURN str;
END$$
DELIMITER ;
Usage example
使用示例
SELECT urldecoder('http://testing.com/questions/7031469/is%20this%20working');
回答by Paul Kenjora
I feel like its important to answer the original question ( regardless of alternatives ), mainly because its been asked and is a valid question:
我觉得回答原始问题很重要（无论替代方案如何），主要是因为它被问到并且是一个有效的问题：
The short answer:You cannot.
简短的回答：你不能。
There is no native SQL function for decoding URL encoded strings.
没有用于解码 URL 编码字符串的本机 SQL 函数。
The long answer:You need to write a custom SQL function:
长答案：您需要编写自定义 SQL 函数：
Encode / Decode MySQL functions: http://www.dzone.com/snippets/urlencodeurldecode-mysql
编码/解码 MySQL 函数：http: //www.dzone.com/snippets/urlencodeurldecode-mysql
The best answer:Anything URL Encoded can be stored as UTF8.
最佳答案：任何 URL 编码都可以存储为 UTF8。
Change your tables to UTF8 by: dumping, replacing "latin1" with "utf8", and importing. Then run a script ( just once ) such as PHP, Python, or Rails to decode your column and store it back. And now you're ready to use your DB as it was intended. No overhead.
通过以下方式将您的表更改为 UTF8：转储，将“latin1”替换为“utf8”，然后导入。然后运行脚本（仅一次），例如 PHP、Python 或 Rails，以解码您的列并将其存储回去。现在您已准备好按预期使用您的数据库。没有开销。
Python code to do this:
执行此操作的 Python 代码：
import urllib
import MySQLdb
def decode():
con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="root", passwd="", db="hostip")
cursor = con.cursor()
cursor.execute("SELECT city, name FROM cityByCountry")
for row in cursor.fetchall():
print row
cursor.execute("UPDATE cityByCountry SET name=%s WHERE city=%s", (urllib.unquote(row[1]), row[0]))
if __name__ == "__main__":
decode()
回答by Roman
My solution is to declare Stored Function to decode url encoded string:
我的解决方案是声明存储函数来解码 url 编码的字符串：
DELIMITER $$
DROP FUNCTION IF EXISTS URL_DECODE $$
CREATE FUNCTION URL_DECODE (str text)
RETURNS text
DETERMINISTIC
BEGIN
DECLARE result text;
DECLARE ind INT DEFAULT 0;
SET result = REPLACE(str, '+', ' ');
WHILE ind <= 255 DO
SET result = REPLACE(result, CONCAT('%', LPAD(LOWER(HEX(ind)), 2, 0)), CHAR(ind));
SET result = REPLACE(result, CONCAT('%', LPAD(HEX(ind), 2, 0)), CHAR(ind));
SET ind = ind + 1;
END WHILE;
RETURN result;
END$$
DELIMITER ;
And than use it next way:
然后再使用它：
SELECT URL_DECODE('johnsmith%40hotmail.com')
回答by lsblsb
I needed a solution when selecting data records that may contain encoded URL special chars $ & + , / : ; = ? @(whitespace not included) and doing it like that (example code):
在选择可能包含编码的 URL 特殊字符$ & + , / : ; = ? @（不包括空格）的数据记录并这样做（示例代码）时，我需要一个解决方案：
SELECT * FROM `table` WHERE `field` REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`field`,'%24','$'),'%26','&'),'%2B','+'),'%2C',','),'%2F','/'),'%3A',':'),'%3B',';'),'%3D','='),'%3F','?'),'%40','@') LIKE '/example-request-uri?'
回答by PhoenixTech
My answer would be very similar to Mistdemon, but with the following changes:
我的答案与 Mistdemon 非常相似，但有以下变化：
(1) This line should be the top insert:
(1) 这一行应该是顶部插入：
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%25","%");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%25","%");
Otherwise a space comes out as %2520 instead of %20 since space is before %....
否则，空格会以 %2520 而不是 %20 的形式出现，因为空格在 %....
(2) If URL encoding for post, remove all the lines for A-Z, a-z, and 0-9. The post server can definitely read standard letters and numbers... and the whole thing does not look like gobbledy-gook.
(2) 如果是post的URL编码，去掉AZ、az、0-9的所有行。邮政服务器绝对可以阅读标准的字母和数字......而且整个事情看起来不像是gobbledy-gook。
回答by fela
Inspired by Mistdemon's answer I were using the following function:
受 Mistdemon 回答的启发，我使用了以下函数：
DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode`$$
CREATE FUNCTION `url_decode`(str VARCHAR(255) CHARSET utf8) RETURNS VARCHAR(255) CHARSET utf8 DETERMINISTIC
BEGIN
DECLARE X INT;
SET X = 128;
WHILE X < 192 DO
SET str = REPLACE(str, CONCAT('%C5%', HEX(X)), UNHEX(CONCAT('C5', HEX(X))));
SET str = REPLACE(str, CONCAT('%C4%', HEX(X)), UNHEX(CONCAT('C4', HEX(X))));
SET str = REPLACE(str, CONCAT('%C3%', HEX(X)), UNHEX(CONCAT('C3', HEX(X))));
SET X = X + 1;
END WHILE;
SET X = 32;
WHILE X < 127 DO
SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(HEX(X)));
SET X = X + 1;
END WHILE;
RETURN REPLACE(str, '+', ' ');
END$$
DELIMITER ;
SELECT url_decode('/pl/tagi/mi%C5%82o%C5%9B%C4%87');
It is good if you know what characters you can expect. In the above code it converts only single bytes and 2-bytes characters from C3, C4 and C5 ranges. For more characters you need more REPLACE iterations.
如果您知道可以期待哪些字符，那就太好了。在上面的代码中，它只转换来自 C3、C4 和 C5 范围的单字节和 2 字节字符。对于更多字符，您需要更多 REPLACE 迭代。
If you need to decode all utf8 characters you can use the following function. It is faster than previous one, but potentially more bugy if you have incorrectly encoded strings.
如果您需要解码所有 utf8 字符，您可以使用以下函数。它比前一个更快，但如果您的字符串编码不正确，则可能会出现更多错误。
DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode`$$
CREATE FUNCTION `url_decode`(str VARCHAR(255) CHARSET utf8) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE end INT;
DECLARE start INT;
SET start = LOCATE('%', str);
WHILE start > 0 DO
SET end = start;
WHILE SUBSTRING(str, end, 1) = '%' AND UPPER(SUBSTRING(str, end + 1, 1)) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F') AND UPPER(SUBSTRING(str, end + 2, 1)) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F') DO
SET end = end + 3;
END WHILE;
IF start <> end THEN
SET str = INSERT(str, start, end - start, UNHEX(REPLACE(SUBSTRING(str, start, end - start), '%', '')));
END IF;
SET start = LOCATE('%', str, start + 1);
END WHILE;
RETURN REPLACE(str, '+', ' ');
END$$
DELIMITER ;
SELECT url_decode('/bg/%D0%B8%D0%B3%D1%80%D0%B8%D1%82%D0%B5-%D0%BD%D0%B0-%D0%B3%D0%BB%D0%B0%D0%B4%D0%B0');
