MySQL创建strip_tags函数删除html标签

创建函数strip_tags

<code><span class="kwd">CREATE</span> <span class="kwd">FUNCTION</span> <span class="pun">`</span><span class="pln">strip_tags</span><span class="pun">`($</span><span class="pln">str text</span><span class="pun">)</span><span class="pln"> RETURNS text
</span><span class="kwd">BEGIN</span>
    <span class="kwd">DECLARE</span> <span class="pun">$</span><span class="kwd">start</span><span class="pun">,</span> <span class="pun">$</span><span class="kwd">end</span><span class="pln"> INT </span><span class="kwd">DEFAULT</span> <span class="lit">1</span><span class="pun">;</span><span class="pln">
    LOOP
        </span><span class="kwd">SET</span> <span class="pun">$</span><span class="kwd">start</span> <span class="pun">=</span><span class="pln"> LOCATE</span><span class="pun">(</span><span class="str">"<"</span><span class="pun">,</span> <span class="pun">$</span><span class="pln">str</span><span class="pun">,</span> <span class="pun">$</span><span class="kwd">start</span><span class="pun">);</span>
        <span class="kwd">IF</span> <span class="pun">(!$</span><span class="kwd">start</span><span class="pun">)</span> <span class="kwd">THEN</span> <span class="kwd">RETURN</span> <span class="pun">$</span><span class="pln">str</span><span class="pun">;</span> <span class="kwd">END</span> <span class="kwd">IF</span><span class="pun">;</span>
        <span class="kwd">SET</span> <span class="pun">$</span><span class="kwd">end</span> <span class="pun">=</span><span class="pln"> LOCATE</span><span class="pun">(</span><span class="str">">"</span><span class="pun">,</span> <span class="pun">$</span><span class="pln">str</span><span class="pun">,</span> <span class="pun">$</span><span class="kwd">start</span><span class="pun">);</span>
        <span class="kwd">IF</span> <span class="pun">(!$</span><span class="kwd">end</span><span class="pun">)</span> <span class="kwd">THEN</span> <span class="kwd">SET</span> <span class="pun">$</span><span class="kwd">end</span> <span class="pun">=</span> <span class="pun">$</span><span class="kwd">start</span><span class="pun">;</span> <span class="kwd">END</span> <span class="kwd">IF</span><span class="pun">;</span>
        <span class="kwd">SET</span> <span class="pun">$</span><span class="pln">str </span><span class="pun">=</span> <span class="kwd">INSERT</span><span class="pun">($</span><span class="pln">str</span><span class="pun">,</span> <span class="pun">$</span><span class="kwd">start</span><span class="pun">,</span> <span class="pun">$</span><span class="kwd">end</span> <span class="pun">-</span> <span class="pun">$</span><span class="kwd">start</span> <span class="pun">+</span> <span class="lit">1</span><span class="pun">,</span> <span class="str">""</span><span class="pun">);</span>
    <span class="kwd">END</span><span class="pln"> LOOP</span><span class="pun">;</span>
<span class="kwd">END</span><span class="pun">;
</span></code>

查看函数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