一，朋友创建存储函数报错。
CREATE DEFINER=`root`@`localhost` FUNCTION `stuff`(
f_old varchar(1000),f_start int,f_length int,f_replace varchar(1000)
) RETURNS varchar(2000) CHARSET utf8
BEGIN
return replace(f_old,substring(f_old,f_start,f_length),f_replace);
[SQL] create   FUNCTION  f_Int2IP (ip bigint)  RETURNS   varchar(15)
BEGIN
DECLARE   re   varchar(15) default '';
SELECT  concat('.',CAST(ip/id  as  char)),ip%id into re,ip
from(
SELECT  16777216   as id
UNION   ALL   SELECT   65536
UNION   ALL   SELECT   256
UNION   ALL   SELECT   1) a;
RETURN STUFF(re,1,1,'');
报错如下：
[Err] 1064 - 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 'varchar)),ip%id into re,ip
from(
SELECT  16777216   as id
UNION   ALL   SEL' at line 4
二、查看创建函数的功能是否开启：
先看下， log_bin_trust_function_creators有没有开启
mysql>  show variables like '%func%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
如果Value处值为OFF，则需将其开启。
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql>  show variables like '%func%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql存储函数，需要特殊的间隔符来区分，还要用DELIMITER来标示，还有select ... into ... 已经赋值了，set就是多余的，所以修改如下：
DELIMITER $$
DROP FUNCTION IF EXISTS test.stuff$$
CREATE FUNCTION test.`stuff`(
f_old VARCHAR(1000),f_start INT,f_length INT,f_replace VARCHAR(1000)
) RETURNS VARCHAR(2000)
BEGIN
RETURN REPLACE(f_old,SUBSTRING(f_old,f_start,f_length),f_replace);
END$$
DELIMITER $$
DROP FUNCTION IF EXISTS test.f_Int2IP$$
CREATE FUNCTION `test`.`f_Int2IP`(ip BIGINT)
RETURNS VARCHAR(2000)
BEGIN
DECLARE re VARCHAR(2000) DEFAULT '';
SELECT CONCAT('.',CAST(ip/id  AS  CHAR)) AS restr,ip%id INTO re ,ip
FROM(
SELECT  16777216   AS id
UNION   ALL   SELECT   65536
UNION   ALL   SELECT   256
UNION   ALL   SELECT   1) a;
RETURN stuff(re,1,1,',');
END$$
DELIMITER ;
四，执行如下，都OK。
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.stuff$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION test.`stuff`(
->  f_old VARCHAR(1000),f_start INT,f_length INT,f_replace VARCHAR(1000)
-> ) RETURNS VARCHAR(2000)
-> BEGIN
->   RETURN REPLACE(f_old,SUBSTRING(f_old,f_start,f_length),f_replace);
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.f_Int2IP$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION `test`.`f_Int2IP`(ip BIGINT)
->     RETURNS VARCHAR(2000)
->     BEGIN
-> DECLARE re VARCHAR(2000) DEFAULT '';
-> SELECT CONCAT('.',CAST(ip/id  AS  CHAR)) AS restr,ip%id INTO re ,ip
-> FROM(
->     SELECT  16777216   AS id
->     UNION   ALL   SELECT   65536
->     UNION   ALL   SELECT   256
->     UNION   ALL   SELECT   1) a;
-> RETURN stuff(re,1,1,',');
->     END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
后补一下带int的函数
delimiter $$
CREATE FUNCTION first_func(param1 varchar(5),parmam2 varchar(5),param3 varchar(10))
RETURNS TINYINT
BEGIN
RETURN 1;
PS：mysql的存储函数或者存储过程么有直接可以调试的工具，所以需要手工仔细分析查看，应用中尽量少用存储过程或者存储函数。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26230597/viewspace-1258484/，如需转载，请注明出处，否则将追究法律责任。

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员