Mysql自定义函数

Mysql 自定义函数怎么用

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;

回复讨论(解决方案)

select strip_tag('
aaa
')

update tbl_name set html=strip_tag(html)

select strip_tag('
aaa
')

update tbl_name set html=strip_tag(html)

mysql里执行下面代码报错

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;

不会错的，我刚刚测试了你的指令

你最好写到程序里

$sql =<<< 'SQL'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;SQL;mysql_query($sql) or die(mysql_error());

因为你使用了 php 的变量名表示，phpmyadmin 可能另做了处理

mysql_connect();mysql_select_db('test');$sql =<<< 'SQL'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;SQL;//mysql_query($sql) or die(mysql_error());$rs = mysql_query("select strip_tags('ab')");print_r(mysql_fetch_row($rs));

Array(    [0] => ab)

select strip_tag('
aaa
')

update tbl_name set html=strip_tag(html)

自定义函数可以存活多久?

永久，直到你删去他

永久，直到你删去他

我加了一句 DROP FUNCTION IF EXISTS `strip_tags`; 就报错了

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE FUNCTION `strip_tags`($str text) RETURNS text BEGIN DECLARE $start,' at line 2

    $sql =<<< 'SQL'DROP FUNCTION IF EXISTS `strip_tags`CREATE FUNCTION `strip_tags`($str text) RETURNS textBEGIN    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;SQL;

按理说后面要加一个分号的
DROP FUNCTION IF EXISTS `strip_tags`;

但是mysql_query 只能执行一条sql语句，所以它把整个语句当成一条sql语句了，当成一条sql显然是不对的。