mysql去除标签_mysql去掉html标签

SET GLOBAL log_bin_trust_function_creators=1;

DROP FUNCTION IF EXISTS fnStripTags;

DELIMITER |

CREATE FUNCTION fnStripTags( Dirty varchar(4000) )

RETURNS varchar(4000)

DETERMINISTIC

BEGIN

DECLARE iStart, iEnd, iLength int;

WHILE Locate( ' 0 And Locate( '>', Dirty, Locate( ' 0 DO

BEGIN

SET iStart = Locate( '', Dirty, Locate('

SET iLength = ( iEnd - iStart) + 1;

IF iLength > 0 THEN

BEGIN

SET Dirty = Insert( Dirty, iStart, iLength, '');

END;

END IF;

END;

END WHILE;

RETURN Dirty;

END;

|

DELIMITER ;

SELECT fnStripTags('this is a test, nothing more');

如果中文 乱码，设置入口参数编码和返回编码即可

DROP FUNCTION IF EXISTS fnStripTags;

DELIMITER |

CREATE FUNCTION fnStripTags( Dirty varchar(4000) CHARSET utf8)

RETURNS varchar(4000) CHARSET utf8

DETERMINISTIC

BEGIN

DECLARE iStart, iEnd, iLength int;

WHILE Locate( ' 0 And Locate( '>', Dirty, Locate( ' 0 DO

BEGIN

SET iStart = Locate( '', Dirty, Locate('

SET iLength = ( iEnd - iStart) + 1;

IF iLength > 0 THEN

BEGIN

SET Dirty = Insert( Dirty, iStart, iLength, '');

END;

END IF;

END;

END WHILE;

RETURN Dirty;

END;

|

DELIMITER ;

另外一种方式：

DROP FUNCTION IF EXISTS strip_tags;

DELIMITER |

CREATE FUNCTION `strip_tags`($str text) RETURNS text

BEGIN

DECLARE $start, $end INT DEFAULT 1;

LOOP

SET $start = LOCATE('

IF (!$start) THEN RETURN $str; END IF;

SET $end = LOCATE('>', $str, $start);

IF (!$end) THEN SET $end = $start; END IF;

SET $str = INSERT($str, $start, $end - $start + 1, "");

END LOOP;

END; https://forums.mysql.com/read.php?52,177343,177985#msg-177985