logo标签列表

在SQL Server中存储IP地址，最合适的数据类型是什么？

sqlsql-servertypesip-addressipv4
66

在SQL Server中，存储IPv4地址的最推荐数据类型是什么？

或者已经有人为此创建了用户SQL数据类型(.Net程序集)吗？

我不需要排序。

19
请注意IPv6已经正式推出。当我分析网络服务器日志时，有时会遇到一个IPv6地址…… - Eric J.
13个回答
63

将IPv4地址存储为binary(4)最符合其表示的真实情况，并允许轻松进行子网掩码样式的查询。但是，如果您实际上需要文本表示，则需要进行转换。在这种情况下，您可能更喜欢字符串格式。

顺便提一下，如果您将其存储为字符串，则可能会有一个很少使用的SQL Server函数PARSENAME可以帮助。虽然它不是专门为IP地址设计的，但非常适用于它们。以下调用将返回“14”：

SELECT PARSENAME('123.234.23.14', 1)

（编号从右到左）。

2
如果需要，您还可以创建UDF来使用点分十进制表示法处理这些数据...可能同时用于输入和检索。 - Arjan Einbu
1
是的。如果使用ORM，这种转换可以很容易地封装起来 - 例如，在（N）Hibernate中使用UserType。 - David M
如何使选择返回单个int？此外，哪种占用更多空间，单个SQL int还是二进制（4）？这样我就可以使用IPAddress的构造函数（以* long *为参数）了？查看@我的答案：https://dev59.com/iHNA5IYBdhLWcg3wPbSe - Shimmy Weitzhandler
@DavidM为什么不用整数？您能解释一下将其存储为二进制而不是整数的性能优势吗？ - Pacerier
我并没有说有任何东西。我说这是数据最真实的表现。 - David M
27
我通常只使用varchar(15)来存储IPv4地址，但是如果不填充零，则对它们进行排序很麻烦。
过去我也将它们存储为INT。System.Net.IPAddress有一个GetAddressBytes方法，它将以表示IP地址的4个字节的数组形式返回IP地址。您可以使用以下C#代码将IPAddress转换为int...
var ipAsInt = BitConverter.ToInt32(ip.GetAddressBytes(), 0);

我使用这种方法是因为我需要搜索大量重复地址，并希望索引尽可能小且快速。然后，要将地址从int转换回.net中的IPAddress对象，请使用BitConverter上的GetBytes方法将int转换为字节数组。将该字节数组传递给IPAddress的constructor，该构造函数接受一个字节数组，您最终会得到您开始时的IPAddress。

var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));
4
如果您要填充IP地址，使用CHAR(15)会更合理。 - Dan McClain
为什么不使用varchar并忘记零呢？ - Shimmy Weitzhandler
12
在IP地址前补零会使其成为不同的IP地址。010.001.001.100和10.1.1.100不是同一个IP地址。在一个八位字节中以零开头的值表示该字节是用八进制写的。正确地存储IP地址的方法是解析它们的各种可能表示，并将它们存储为二进制值（根据它们是IPv4还是IPv6地址，可以是32位或128位）。 - Evan Anderson
请注意，当在字符串上使用时，IPAddress.Parse非常有用。 另外请注意，在.NET 4.0中，微软添加了一个新函数GetAddressBytes，您不需要将其转换为字节数组，并且可以使用其中一个支持它的构造函数来创建简单的IP地址。 - Shimmy Weitzhandler
1
回复：排序 IP 地址。在 SQL Server 2008 中，您可以使用 hierarchyid 来帮助解决此问题。https://dev59.com/iHNA5IYBdhLWcg3wPbSe#3441685 - Martin Smith
此外，IPAddress构造函数接受一个长整型值（奇怪的是，它应该是无符号整型）作为初始化参数，请参见我的答案：https://dev59.com/iHNA5IYBdhLWcg3wPbSe。 - Shimmy Weitzhandler
19

关于被接受的答案中的这个评论

如果不填充零，排序会很麻烦。

这是一个针对 SQL Server 2008 的技巧（来自 Itzik Ben-Gan 在这本书中）

with ip_addresses as
(
SELECT '131.33.2.201' AS ip_address UNION ALL
SELECT '2.12.4.4' AS ip_address UNION ALL
SELECT '131.33.2.202' AS ip_address UNION ALL
SELECT '2.12.4.169' AS ip_address UNION ALL
SELECT '131.107.2.201' AS ip_address 
)
select ip_address
from ip_addresses
ORDER  BY CAST('/' + ip_address + '/' AS hierarchyid)

返回

ip_address
-------------
2.12.4.4
2.12.4.169
131.33.2.201
131.33.2.202
131.107.2.201
这里有另一种更容易从数据库中获取的解决方案。 您可以在此处找到： https://dev59.com/3n7aa4cB1Zd3GeqPuMDi - fahdshaykh
8

为了节省空间并且需要处理值（匹配或与范围比较），我使用int。IP地址实际上只是一个32位的值。

如果您只想简单存储该值以查看它，我使用varchar(15)来存储IP地址的字符串表示形式。

3

我在这里看到很多类似的问题，但在这个问题的回复中没有提到其他问题中最常见的答案：“对于IPv4地址，你可能希望将它们存储为无符号整数，并使用INET_ATON()和INET_NTOA()函数从其数值返回IP地址，反之亦然。”我认为这是我要在我的数据库中采用的方法，除非我决定使用上面提到的php函数。

SQL Server 没有这样的函数。 - Martin Smith
3

不要忘记IPv6 - 如果需要存储它们，你需要更多的空间 - 128位与IPv4的32位相比。

我会选择bigint，尽管你需要一些辅助代码来将其转换为人类友好的版本。

3

我最喜欢的文章之一谈到了为什么你不应该使用正则表达式来解析IP地址。他们大多数讲的是为什么你应该非常小心地处理IP地址的文本表示。在决定数据库中使用哪种数据类型以及处理应用程序时，建议您先阅读此文章（即使这篇文章是关于Perl编写的，但对任何语言都有用）。

我认为最终选择32位数据类型（或四个8位数据类型）将是最佳选择。

1
有点脱离上下文了，你觉得呢？他正在将它们序列化到数据库中——无论他选择什么数据库数据类型或格式，他都将控制该转换。这篇文章只是在卖弄学问——我没有看到它所谈论的任何真实世界的应用。 - Frank Krueger
2
这取决于您的目的。如果您想要最好的存储和可能的性能，并且在很大程度上，将其存储为int，将其存储为varchar等会比简单无辜的int更耗费性能。 您还可以通过将搜索参数设置为所需的int来按IP搜索。
有一个属性IPAddress.Address，但它已过时，我不知道为什么，因为如果您不需要对IP类进行排序或控制，则最佳方法是将其存储为无符号整数（其最大值为0xffffffff，相当于十进制表示中的255.255.255.255）。 使用EF Core，您可以使用转换器自动转换为和从IPAddress转换。
此外，IPAddress类具有接受长整型参数的构造函数。
根据VS调试器可视化器，IPAddress类本身将其内部变量存储为一个数字（而不是字节数组）。
阅读有关在MS SQL Server中存储单位的解决方法的更多信息：
我其实不太理解你的第一段话.. 你是在说要存储为整数还是不要存储为整数？ - Pacerier
@Pacerier，将其存储为int。 - Shimmy Weitzhandler
2

IPV4？int？还是tinyint x 4？

这取决于它是仅用于存储和检索还是将成为范围搜索条件。

0

由于IP地址有32位，你能否使用LONG来存储数值？
这样做不会像使用VARCHAR那样浪费空间，但是你每次使用它之前都必须将其解码回IP地址，这会导致延迟和额外的开销，可能得不偿失。

无符号整数为32位：https://learn.microsoft.com/zh-cn/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15 - theking2
@theking2 - SQL Server 的 int 数据类型是有符号的。 - Alex
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接