MySQL儲存函式錯誤[Err] 1064的除錯記錄

mchdba發表於2014-08-26
MySql儲存函式除錯
一，朋友建立儲存函式報錯。
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);
END


[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;
set 
RETURN STUFF(re,1,1,'');
END  

報錯如下： 
[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;
END

PS：mysql的儲存函式或者儲存過程麼有直接可以除錯的工具，所以需要手工仔細分析檢視，應用中儘量少用儲存過程或者儲存函式。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1258484/，如需轉載，請註明出處，否則將追究法律責任。

相關文章