MySQL儲存函數錯誤[Err] 1064的調試記錄

MySQL儲存函數錯誤[Err] 1064的調試記錄

一，朋友建立儲存函數報錯。
 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的儲存函數或者預存程序麼有直接可以調試的工具，所以需要手工仔細分析查看，應用中盡量少用預存程序或者儲存函數。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：