mysql 字段去除html,MySQL创建strip_tags函数删除html标签

创建函数strip_tags

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

BEGIN

DECLARE $start, $end INT DEFAULT 1;

LOOP

SET $start = LOCATE(", $str, $start);

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;

查看函数strip_tags

mysql> show function status \G

*************************** 1. row ***************************

Db: sijitao.net

Name: strip_tags

Type: FUNCTION

Definer: root@localhost

Modified: 2014-12-10 09:40:00

Created: 2014-12-10 09:40:00

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

mysql> show create function strip_tags \G

*************************** 1. row ***************************

Function: strip_tags

sql_mode:

Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `strip_tags`($str text) RETURNS text CHARSET utf8

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

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

测试函数strip_tags

mysql> select strip_tags('hello wo<>rld <again<.>

+----------------------------------------------------------------------+

| strip_tags('hello wo<>rld <again<.>

+----------------------------------------------------------------------+

| hello world again.                                                   |

+----------------------------------------------------------------------+

1 row in set (0.00 sec)

测试结果满足要求，已经自动帮我们删除html标签。

遇到的问题

我在创建函数的时候碰到了如下类似错误。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决办法是在创建函数之前执行下面这个命令。

mysql> set global log_bin_trust_function_creators=1;

参考网址：

http://stackoverflow.com/questions/7654436/what-is-the-mysql-query-equivalent-of-php-strip-tags

http://database.51cto.com/art/201010/229918.htm