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通用二进制安装

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址：