56,675
社区成员
发帖
与我相关
我的任务
分享
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
[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
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>
[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
[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类型值错,但是还有这个问题,看看怎么回事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>
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.0.51a-community-nt |
+----------------------+
1 row in set
可能还真是版本问题耶,怎么升级到你的那个版本?目前最稳定广泛使用的哪个版本?