sql2005 ip地址点分十进制与长整形表示法相互转换

最新推荐文章于 2025-03-17 17:58:34 发布

love__coder

最新推荐文章于 2025-03-17 17:58:34 发布

阅读量3k

点赞数

分类专栏： SQL 文章标签： sql function 数据库 date 脚本存储

本文链接：https://blog.youkuaiyun.com/love__coder/article/details/7836084

版权

SQL 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了如何在数据库中利用SQL自定义函数将点分十进制IP地址转换为bigint类型，以及如何将bigint转换回点分十进制形式。此外，还对比了这种方法与MySQL内置函数的效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在数据库设计时，为了查询效率，常常把点分十进制表示的ip地址设计为bigint类型。存储的时候，怎么把点分十进制转换为bigint，请参考下面的sql自定义函数：

USE [temp]
GO
/****** 对象:  UserDefinedFunction [dbo].[UF_CovertIPToInt]    脚本日期: 08/06/2012 16:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		----
-- Create date: 2009-05-11
-- Description:	转换ip
-- =============================================
CREATE FUNCTION [dbo].[UF_CovertIPToInt] 
(
	@ip varchar(20)
)
RETURNS bigint
AS
BEGIN
	declare @pos int
	declare @sub1 varchar(10)
	declare @sub2 varchar(10)
	declare @sub3 varchar(10)
	declare @sub4 varchar(10)

	set  @pos = charindex('.',@ip)

	if(@pos<1) return 0

	set @sub1 = substring(@ip,0,@pos)

	--select @sub1

	set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)

	--select @ip

	set  @pos = charindex('.',@ip)

	if(@pos<1) return 0

	set @sub2 = substring(@ip,0,@pos)

	--select @sub2

	set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)

	--select @ip

	set  @pos = charindex('.',@ip)

	if(@pos<1) return 0

	set @sub3 = substring(@ip,0,@pos)

	--select @sub3

	set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)

	if(len(@ip)<1) return 0

	select @sub4 = @ip

	--select @sub4
	
	return cast(@sub1 as bigint ) * 16777216 + cast(@sub2 as bigint ) * 65536 + cast(@sub3 as bigint )*256 + cast(@sub4 as bigint )

END

有了上面的函数，我可以方便地进行转换，例如：

select dbo.UF_CovertIPToInt('192.168.1.100') as ip

显示的时候，为了方便阅读，常常需要把bigint转换为点分十进制，请参考下面的自定义函数：

USE [temp]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[UF_ConvertIPToStr] 
(
	@ip bigint
)
RETURNS varchar(20)
AS
BEGIN
	RETURN  cast((@ip/16777216)% 256 as varchar(4)) + '.' + cast( (@ip/65536)% 256 as varchar(4)) +'.'+ cast( (@ip/256)%256 as varchar(4))+ '.'+ cast( (@ip%256)as varchar(4))

END

可以通过下面的方式调用该函数：

select dbo.UF_ConvertIPToStr (3232235790) as ip

当然，对于mysql数据，就没必要这么麻烦了。完全可以使用内置的函数：inet_aton 和inet_ntoa

select inet_aton('192.168.1.12');
select inet_ntoa(3232235788);