您可以创建如下功能
DELIMITER $$ DROP FUNCTION IF EXISTS `HTML_UnEnpre`$$ CREATE FUNCTION `HTML_UnEnpre`(X VARCHAr(255)) RETURNS VARCHAr(255) CHARSET latin1 DETERMINISTIC BEGIN DECLARE TextString VARCHAr(255) ; SET TextString = X ; #quotation mark IF INSTR( X , '"' ) THEN SET TextString = REPLACE(TextString, '"','"') ; END IF ; #apostrophe IF INSTR( X , ''' ) THEN SET TextString = REPLACE(TextString, ''','"') ; END IF ; #ampersand IF INSTR( X , '&' ) THEN SET TextString = REPLACE(TextString, '&','&') ; END IF ; #less-than IF INSTR( X , '<' ) THEN SET TextString = REPLACE(TextString, '<','<') ; END IF ; #greater-than IF INSTR( X , '>' ) THEN SET TextString = REPLACE(TextString, '>','>') ; END IF ; #non-breaking space IF INSTR( X , ' ' ) THEN SET TextString = REPLACE(TextString, ' ',' ') ; END IF ; #inverted exclamation mark IF INSTR( X , '¡' ) THEN SET TextString = REPLACE(TextString, '¡','¡') ; END IF ; #cent IF INSTR( X , '¢' ) THEN SET TextString = REPLACE(TextString, '¢','¢') ; END IF ; #pound IF INSTR( X , '£' ) THEN SET TextString = REPLACE(TextString, '£','£') ; END IF ; #currency IF INSTR( X , '¤' ) THEN SET TextString = REPLACE(TextString, '¤','¤') ; END IF ; #yen IF INSTR( X , '¥' ) THEN SET TextString = REPLACE(TextString, '¥','¥') ; END IF ; #broken vertical bar IF INSTR( X , '¦' ) THEN SET TextString = REPLACE(TextString, '¦','¦') ; END IF ; #section IF INSTR( X , '§' ) THEN SET TextString = REPLACE(TextString, '§','§') ; END IF ; #spacing diaeresis IF INSTR( X , '¨' ) THEN SET TextString = REPLACE(TextString, '¨','¨') ; END IF ; #copyright IF INSTR( X , '©' ) THEN SET TextString = REPLACE(TextString, '©','©') ; END IF ; #feminine ordinal indicator IF INSTR( X , 'ª' ) THEN SET TextString = REPLACE(TextString, 'ª','ª') ; END IF ; #angle quotation mark (left) IF INSTR( X , '«' ) THEN SET TextString = REPLACE(TextString, '«','«') ; END IF ; #negation IF INSTR( X , '¬' ) THEN SET TextString = REPLACE(TextString, '¬','¬') ; END IF ; #soft hyphen IF INSTR( X , '­' ) THEN SET TextString = REPLACE(TextString, '­','') ; END IF ; #registered trademark IF INSTR( X , '®' ) THEN SET TextString = REPLACE(TextString, '®','®') ; END IF ; #spacing macron IF INSTR( X , '¯' ) THEN SET TextString = REPLACE(TextString, '¯','¯') ; END IF ; #degree IF INSTR( X , '°' ) THEN SET TextString = REPLACE(TextString, '°','°') ; END IF ; #plus-or-minus IF INSTR( X , '±' ) THEN SET TextString = REPLACE(TextString, '±','±') ; END IF ; #superscript 2 IF INSTR( X , '²' ) THEN SET TextString = REPLACE(TextString, '²','²') ; END IF ; #superscript 3 IF INSTR( X , '³' ) THEN SET TextString = REPLACE(TextString, '³','³') ; END IF ; #spacing acute IF INSTR( X , '´' ) THEN SET TextString = REPLACE(TextString, '´','´') ; END IF ; #micro IF INSTR( X , 'µ' ) THEN SET TextString = REPLACE(TextString, 'µ','µ') ; END IF ; #paragraph IF INSTR( X , '¶' ) THEN SET TextString = REPLACE(TextString, '¶','¶') ; END IF ; #middle dot IF INSTR( X , '·' ) THEN SET TextString = REPLACE(TextString, '·','·') ; END IF ; #spacing cedilla IF INSTR( X , '¸' ) THEN SET TextString = REPLACE(TextString, '¸','¸') ; END IF ; #superscript 1 IF INSTR( X , '¹' ) THEN SET TextString = REPLACE(TextString, '¹','¹') ; END IF ; #masculine ordinal indicator IF INSTR( X , 'º' ) THEN SET TextString = REPLACE(TextString, 'º','º') ; END IF ; #angle quotation mark (right) IF INSTR( X , '»' ) THEN SET TextString = REPLACE(TextString, '»','»') ; END IF ; #fraction 1/4 IF INSTR( X , '¼' ) THEN SET TextString = REPLACE(TextString, '¼','¼') ; END IF ; #fraction 1/2 IF INSTR( X , '½' ) THEN SET TextString = REPLACE(TextString, '½','½') ; END IF ; #fraction 3/4 IF INSTR( X , '¾' ) THEN SET TextString = REPLACE(TextString, '¾','¾') ; END IF ; #inverted question mark IF INSTR( X , '¿' ) THEN SET TextString = REPLACE(TextString, '¿','¿') ; END IF ; #multiplication IF INSTR( X , '×' ) THEN SET TextString = REPLACE(TextString, '×','×') ; END IF ; #division IF INSTR( X , '÷' ) THEN SET TextString = REPLACE(TextString, '÷','÷') ; END IF ; #capital a, grave accent IF INSTR( X , 'À' ) THEN SET TextString = REPLACE(TextString, 'À','À') ; END IF ; #capital a, acute accent IF INSTR( X , 'Á' ) THEN SET TextString = REPLACE(TextString, 'Á','Á') ; END IF ; #capital a, circumflex accent IF INSTR( X , 'Â' ) THEN SET TextString = REPLACE(TextString, 'Â','Â') ; END IF ; #capital a, tilde IF INSTR( X , 'Ã' ) THEN SET TextString = REPLACE(TextString, 'Ã','Ã') ; END IF ; #capital a, umlaut mark IF INSTR( X , 'Ä' ) THEN SET TextString = REPLACE(TextString, 'Ä','Ä') ; END IF ; #capital a, ring IF INSTR( X , 'Å' ) THEN SET TextString = REPLACE(TextString, 'Å','Å') ; END IF ; #capital ae IF INSTR( X , 'Æ' ) THEN SET TextString = REPLACE(TextString, 'Æ','Æ') ; END IF ; #capital c, cedilla IF INSTR( X , 'Ç' ) THEN SET TextString = REPLACE(TextString, 'Ç','Ç') ; END IF ; #capital e, grave accent IF INSTR( X , 'È' ) THEN SET TextString = REPLACE(TextString, 'È','È') ; END IF ; #capital e, acute accent IF INSTR( X , 'É' ) THEN SET TextString = REPLACE(TextString, 'É','É') ; END IF ; #capital e, circumflex accent IF INSTR( X , 'Ê' ) THEN SET TextString = REPLACE(TextString, 'Ê','Ê') ; END IF ; #capital e, umlaut mark IF INSTR( X , 'Ë' ) THEN SET TextString = REPLACE(TextString, 'Ë','Ë') ; END IF ; #capital i, grave accent IF INSTR( X , 'Ì' ) THEN SET TextString = REPLACE(TextString, 'Ì','Ì') ; END IF ; #capital i, acute accent IF INSTR( X , 'Í' ) THEN SET TextString = REPLACE(TextString, 'Í','Í') ; END IF ; #capital i, circumflex accent IF INSTR( X , 'Î' ) THEN SET TextString = REPLACE(TextString, 'Î','Î') ; END IF ; #capital i, umlaut mark IF INSTR( X , 'Ï' ) THEN SET TextString = REPLACE(TextString, 'Ï','Ï') ; END IF ; #capital eth, Icelandic IF INSTR( X , 'Ð' ) THEN SET TextString = REPLACE(TextString, 'Ð','Ð') ; END IF ; #capital n, tilde IF INSTR( X , 'Ñ' ) THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ') ; END IF ; #capital o, grave accent IF INSTR( X , 'Ò' ) THEN SET TextString = REPLACE(TextString, 'Ò','Ò') ; END IF ; #capital o, acute accent IF INSTR( X , 'Ó' ) THEN SET TextString = REPLACE(TextString, 'Ó','Ó') ; END IF ; #capital o, circumflex accent IF INSTR( X , 'Ô' ) THEN SET TextString = REPLACE(TextString, 'Ô','Ô') ; END IF ; #capital o, tilde IF INSTR( X , 'Õ' ) THEN SET TextString = REPLACE(TextString, 'Õ','Õ') ; END IF ; #capital o, umlaut mark IF INSTR( X , 'Ö' ) THEN SET TextString = REPLACE(TextString, 'Ö','Ö') ; END IF ; #capital o, slash IF INSTR( X , 'Ø' ) THEN SET TextString = REPLACE(TextString, 'Ø','Ø') ; END IF ; #capital u, grave accent IF INSTR( X , 'Ù' ) THEN SET TextString = REPLACE(TextString, 'Ù','Ù') ; END IF ; #capital u, acute accent IF INSTR( X , 'Ú' ) THEN SET TextString = REPLACE(TextString, 'Ú','Ú') ; END IF ; #capital u, circumflex accent IF INSTR( X , 'Û' ) THEN SET TextString = REPLACE(TextString, 'Û','Û') ; END IF ; #capital u, umlaut mark IF INSTR( X , 'Ü' ) THEN SET TextString = REPLACE(TextString, 'Ü','Ü') ; END IF ; #capital y, acute accent IF INSTR( X , 'Ý' ) THEN SET TextString = REPLACE(TextString, 'Ý','Ý') ; END IF ; #capital THORN, Icelandic IF INSTR( X , 'Þ' ) THEN SET TextString = REPLACE(TextString, 'Þ','Þ') ; END IF ; #small sharp s, German IF INSTR( X , 'ß' ) THEN SET TextString = REPLACE(TextString, 'ß','ß') ; END IF ; #small a, grave accent IF INSTR( X , 'à' ) THEN SET TextString = REPLACE(TextString, 'à','à') ; END IF ; #small a, acute accent IF INSTR( X , 'á' ) THEN SET TextString = REPLACE(TextString, 'á','á') ; END IF ; #small a, circumflex accent IF INSTR( X , 'â' ) THEN SET TextString = REPLACE(TextString, 'â','â') ; END IF ; #small a, tilde IF INSTR( X , 'ã' ) THEN SET TextString = REPLACE(TextString, 'ã','ã') ; END IF ; #small a, umlaut mark IF INSTR( X , 'ä' ) THEN SET TextString = REPLACE(TextString, 'ä','ä') ; END IF ; #small a, ring IF INSTR( X , 'å' ) THEN SET TextString = REPLACE(TextString, 'å','å') ; END IF ; #small ae IF INSTR( X , 'æ' ) THEN SET TextString = REPLACE(TextString, 'æ','æ') ; END IF ; #small c, cedilla IF INSTR( X , 'ç' ) THEN SET TextString = REPLACE(TextString, 'ç','ç') ; END IF ; #small e, grave accent IF INSTR( X , 'è' ) THEN SET TextString = REPLACE(TextString, 'è','è') ; END IF ; #small e, acute accent IF INSTR( X , 'é' ) THEN SET TextString = REPLACE(TextString, 'é','é') ; END IF ; #small e, circumflex accent IF INSTR( X , 'ê' ) THEN SET TextString = REPLACE(TextString, 'ê','ê') ; END IF ; #small e, umlaut mark IF INSTR( X , 'ë' ) THEN SET TextString = REPLACE(TextString, 'ë','ë') ; END IF ; #small i, grave accent IF INSTR( X , 'ì' ) THEN SET TextString = REPLACE(TextString, 'ì','ì') ; END IF ; #small i, acute accent IF INSTR( X , 'í' ) THEN SET TextString = REPLACE(TextString, 'í','í') ; END IF ; #small i, circumflex accent IF INSTR( X , 'î' ) THEN SET TextString = REPLACE(TextString, 'î','î') ; END IF ; #small i, umlaut mark IF INSTR( X , 'ï' ) THEN SET TextString = REPLACE(TextString, 'ï','ï') ; END IF ; #small eth, Icelandic IF INSTR( X , 'ð' ) THEN SET TextString = REPLACE(TextString, 'ð','ð') ; END IF ; #small n, tilde IF INSTR( X , 'ñ' ) THEN SET TextString = REPLACE(TextString, 'ñ','ñ') ; END IF ; #small o, grave accent IF INSTR( X , 'ò' ) THEN SET TextString = REPLACE(TextString, 'ò','ò') ; END IF ; #small o, acute accent IF INSTR( X , 'ó' ) THEN SET TextString = REPLACE(TextString, 'ó','ó') ; END IF ; #small o, circumflex accent IF INSTR( X , 'ô' ) THEN SET TextString = REPLACE(TextString, 'ô','ô') ; END IF ; #small o, tilde IF INSTR( X , 'õ' ) THEN SET TextString = REPLACE(TextString, 'õ','õ') ; END IF ; #small o, umlaut mark IF INSTR( X , 'ö' ) THEN SET TextString = REPLACE(TextString, 'ö','ö') ; END IF ; #small o, slash IF INSTR( X , 'ø' ) THEN SET TextString = REPLACE(TextString, 'ø','ø') ; END IF ; #small u, grave accent IF INSTR( X , 'ù' ) THEN SET TextString = REPLACE(TextString, 'ù','ù') ; END IF ; #small u, acute accent IF INSTR( X , 'ú' ) THEN SET TextString = REPLACE(TextString, 'ú','ú') ; END IF ; #small u, circumflex accent IF INSTR( X , 'û' ) THEN SET TextString = REPLACE(TextString, 'û','û') ; END IF ; #small u, umlaut mark IF INSTR( X , 'ü' ) THEN SET TextString = REPLACE(TextString, 'ü','ü') ; END IF ; #small y, acute accent IF INSTR( X , 'ý' ) THEN SET TextString = REPLACE(TextString, 'ý','ý') ; END IF ; #small thorn, Icelandic IF INSTR( X , 'þ' ) THEN SET TextString = REPLACE(TextString, 'þ','þ') ; END IF ; #small y, umlaut mark IF INSTR( X , 'ÿ' ) THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ') ; END IF ; RETURN TextString ; END$$ DELIMITER ;
并执行以下查询：
SELECT HTML_UnEnpre('this is a <a>test, nothing more');
