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("<", $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
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('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.');
+----------------------------------------------------------------------+
| strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>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