mysql过滤style,script,html标签函数
今天用elasticsearch搜索mysql导入的文章,高亮内容大片的html标签,并且很多还是截断的。网上看了看有说用mysql函数过滤的,但是大多效果不好,只过滤了部分数据。只好自己优化了下,效果还不错:
SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS del_script_tags;
CREATE FUNCTION `del_script_tags`($str mediumtext) RETURNS mediumtext
BEGIN
DECLARE $start, $end INT DEFAULT 1;
LOOP_SCRIPT: LOOP
SET $start = LOCATE("<script", $str, $start);
IF (!$start) THEN LEAVE LOOP_SCRIPT; END IF;
SET $end = LOCATE("</script>", $str, $start);
IF (!$end) THEN SET $end = $start; END IF;
SET $str = INSERT($str, $start, $end - $start + 9, '');
END LOOP;
SET $start = 1;
SET $end = 1;
LOOP_STYLE: LOOP
SET $start = LOCATE("<style", $str, $start);
IF (!$start) THEN LEAVE LOOP_STYLE; END IF;
SET $end = LOCATE("</style>", $str, $start);
IF (!$end) THEN SET $end = $start; END IF;
SET $str = INSERT($str, $start, $end - $start + 8, '');
END LOOP;
SET $start = 1;
SET $end = 1;
LOOP
SET $start = LOCATE("<", $str, $start);
IF (!$start) THEN RETURN TRIM(REPLACE(REPLACE($str, CHAR(10), ''),' ','')); 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;
使用该函数过滤即可:
select del_strip_tags('过滤字段') from table