delimiter // create FUNCTION f_Int2IP (ip bigint) RETURNS varchar(15) BEGIN DECLARE re varchar(15) default ''; SELECT concat('.',cast(ip/id as varchar)),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 // delimiter ;
尝试一下:delimiter $$ create FUNCTION f_Int2IP (ip bigint) RETURNS varchar(15) BEGIN DECLARE re varchar(15) default ''; SELECT concat('.',cast(ip/id as varchar)),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$$ delimiter;
DELIMITER // 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 // DELIMITER ;CONVERT()一般可用于比较出现在不同字符集中的字符串。 CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name) CAST() 和CONVERT() 函数可用来获取一个类型的值,并产生另一个类型的值。 这个类型 可以是以下值其中的 一个: BINARY[(N)] CHAR[(N)] DATE DATETIME DECIMAL SIGNED [INTEGER] TIME UNSIGNED [INTEGER] CAST 能转换的类型如以上
[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] 1221 - Incorrect usage of UNION and INTO 有进展,找到一个错误cast类型值错,但是还有这个问题,看看怎么回事
还有,SELECT concat('.',cast(ip/id as varchar)),ip%id into re,ip 为什么还要往ip里面赋值,ip不是传入的参数吗?
这个不是本质问题,请继续探讨,顺便请仔细看下into语句和union语句,错误说这块错了 [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] 1221 - Incorrect usage of UNION and INTO STUFF是我自己返照mssql定义的一个内置函数,内容如下 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
mysql> show variables like '%func%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 1 row in set (0.00 sec)mysql>看下你的创建函数选项是否已经打开了?
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 re; RETURN STUFF(re,1,1,''); END$$ DELIMITER ; 执行结果如下: 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.00 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 re; -> RETURN STUFF(re,1,1,''); -> END$$ Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ; mysql> mysql>
create FUNCTION f_Int2IP (ip bigint) RETURNS varchar(15)
BEGIN
DECLARE re varchar(15) default '';
SELECT concat('.',cast(ip/id as varchar)),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
//
delimiter ;
create FUNCTION f_Int2IP (ip bigint) RETURNS varchar(15)
BEGIN
DECLARE re varchar(15) default '';
SELECT concat('.',cast(ip/id as varchar)),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$$
delimiter;
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
//
DELIMITER ;CONVERT()一般可用于比较出现在不同字符集中的字符串。 CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
CAST() 和CONVERT() 函数可用来获取一个类型的值,并产生另一个类型的值。 这个类型 可以是以下值其中的 一个: BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
CAST 能转换的类型如以上
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] 1221 - Incorrect usage of UNION and INTO
有进展,找到一个错误cast类型值错,但是还有这个问题,看看怎么回事
还有,SELECT concat('.',cast(ip/id as varchar)),ip%id into re,ip 为什么还要往ip里面赋值,ip不是传入的参数吗?
[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] 1221 - Incorrect usage of UNION and INTO
STUFF是我自己返照mssql定义的一个内置函数,内容如下
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
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)mysql>看下你的创建函数选项是否已经打开了?
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 re;
RETURN STUFF(re,1,1,'');
END$$
DELIMITER ;
执行结果如下:
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.00 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 re;
-> RETURN STUFF(re,1,1,'');
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql>
mysql>
你把我的sql拿去执行下吧,就知道问题再哪里了。
别截图了,把你的sql copy出来,我拿过来执行下,就知道问题在哪里了,也许是中间的格式不对,也许是你有特殊字符造成的。
我这边执行是OK的,没有报错。
+----------------------+
| version() |
+----------------------+
| 5.0.51a-community-nt |
+----------------------+
1 row in set
可能还真是版本问题耶,怎么升级到你的那个版本?目前最稳定广泛使用的哪个版本?
我知道问题所在了,是大小写的问题,不是版本的问题。
mysql> DELIMITER ;
mysql>
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.f_Int2IP$$
Query OK, 0 rows affected, 1 warning (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> select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+
1 row in set (0.00 sec)mysql>
你看你的create的时候是 stuff,但是在引用的时候用的是STUFF,大小写不兼容啊。我再次看了你的图片的报错,你不是copy我的sql,我的sql里面大小写是统一的。