在 SQL Server 中存储 IP 地址最合适的数据类型是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1038950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the most appropriate data type for storing an IP address in SQL server?
提问by Shimmy Weitzhandler
What should be the most recommended datatype for storing an IPv4 address in SQL server?
在 SQL Server 中存储 IPv4 地址的最推荐数据类型应该是什么？
Or maybe someone has already created a user SQL data-type (.Net assembly) for it?
或者也许有人已经为它创建了一个用户 SQL 数据类型（.Net 程序集）？
I don't need sorting.
我不需要排序。
采纳答案by David M
Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.
将 IPv4 地址存储为binary(4) 最符合它所代表的含义，并允许轻松进行子网掩码式查询。但是，如果您实际上是在文本表示之后，则需要进行输入和输出转换。在这种情况下，您可能更喜欢字符串格式。
A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return '14':
PARSENAME顺便说一下，一个很少使用的 SQL Server 函数在您存储为字符串时可能会有所帮助。不是为 IP 地址设计的，但非常适合它们。下面的调用将返回“14”：
SELECT PARSENAME('123.234.23.14', 1)
(numbering is right to left).
（编号从右到左）。
回答by Scott Ivey
I normally just use varchar(15) for IPv4 addresses - but sorting them is a pain unless you pad zeros.
我通常只将 varchar(15) 用于 IPv4 地址 - 但除非您填充零，否则对它们进行排序很痛苦。
I've also stored them as an INT in the past. System.Net.IPAddresshas a GetAddressBytesmethod that will return the IP address as an array of the 4 bytes that represent the IP address. You can use the following C# code to convert an IPAddressto an int...
我过去也将它们存储为 INT。 System.Net.IPAddress有一个GetAddressBytes方法可以将 IP 地址作为表示 IP 地址的 4 个字节的数组返回。您可以使用以下 C# 代码将 an 转换IPAddress为int...
var ipAsInt = BitConverter.ToInt32(ip.GetAddressBytes(), 0);
I had used that because I had to do a lot of searching for dupe addresses, and wanted the indexes to be as small & quick as possible. Then to pull the address back out of the int and into an IPAddressobject in .net, use the GetBytesmethod on BitConverterto get the int as a byte array. Pass that byte array to the constructorfor IPAddressthat takes a byte array, and you end back up with the IPAddressthat you started with.
我使用它是因为我必须对重复地址进行大量搜索，并且希望索引尽可能小和快速。然后要将地址从 int 中拉回到IPAddress.net 中的对象中，请使用GetByteson 方法BitConverter将 int 作为字节数组获取。将该字节数组传递给构造函数，该构造函数IPAddress需要一个字节数组，最后返回IPAddress到您开始使用的那个。
var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));
回答by Martin Smith
Regarding this comment in the accepted answer
关于已接受答案中的此评论
sorting them is a pain unless you pad zeros.
除非您填充零，否则对它们进行排序很痛苦。
Here's a trick for SQL Server 2008 (From Itzik Ben-Gan in this book)
这是 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)
Returns
退货
ip_address
-------------
2.12.4.4
2.12.4.169
131.33.2.201
131.33.2.202
131.107.2.201
回答by Bobby
I'm reading a lot of similar questions on here, and none of the replies in this one mention the number one answer in others: "For IPv4 addresses, you may want to store them as an int unsigned and use the INET_ATON() and INET_NTOA() functions to return the IP address from its numeric value, and vice versa." I think this is what I'm going to go with in my db, unless I decide to use the php functions mentioned above.
我在这里阅读了很多类似的问题，并且没有一个回复提到其他人的第一答案：“对于 IPv4 地址，您可能希望将它们存储为 int unsigned 并使用 INET_ATON() 和INET_NTOA() 函数从其数值返回 IP 地址，反之亦然。” 我认为这就是我要在我的数据库中使用的内容，除非我决定使用上面提到的 php 函数。
回答by rmeador
One of my favorite articlestalks about why you shouldn't use regular expressions to parse IP addresses. Most of what they're talking about is really explaining why you should be very careful with textual representations of IP addresses. I suggest you read it before deciding what datatype to use in your database, and probably also for whatever handling your app will be doing (even though the article is written about Perl, it's useful for any language).
我最喜欢的一篇文章谈到了为什么不应该使用正则表达式来解析 IP 地址。他们谈论的大部分内容实际上是在解释为什么您应该对 IP 地址的文本表示非常小心。我建议您在决定在您的数据库中使用什么数据类型之前阅读它，并且可能也用于您的应用程序将要执行的任何处理（即使这篇文章是关于 Perl 的，它对任何语言都有用）。
I think in the end a 32 bit datatype (or four 8-bit datatypes) would be the best choice.
我认为最终 32 位数据类型（或四个 8 位数据类型）将是最佳选择。
回答by Shimmy Weitzhandler
Best way (when no need sorting and other control on the IPs) is store it as int, storing it as varchar etc. would cost way more performance than just a simple innocent int.
最好的方法（当不需要对 IP 进行排序和其他控制时）将其存储为 int，将其存储为 varchar 等将比简单的无辜 int 花费更多的性能。
There is a property IPAddress.Addressbut it's obsolete, I don't know why, since if you don't need sorting or control over the IP classes, the best way is to store it as unsigned integer (that has a max value of 0xffffffffwhich equals to 255.255.255.255in decimal representation.
有一个属性IPAddress.Address但它已经过时了，我不知道为什么，因为如果您不需要对 IP 类进行排序或控制，最好的方法是将其存储为无符号整数（其最大值0xffffffff等于255.255.255.255以十进制表示。
Also the IPAddress class has a constructor that accepts a long argument.
IPAddress 类还有一个接受长参数的构造函数。
And according to VS debugger visualizer, that IPAddress class itself stores its internal variable as one number (not byte array).
根据 VS 调试器可视化工具，该 IPAddress 类本身将其内部变量存储为一个数字（而不是字节数组）。
Read more on workarounds storing a unit in MS SQL Server:
阅读有关在 MS SQL Server 中存储单元的解决方法的更多信息：
回答by Lee Atkinson
Don't forget about IPv6 - you need a lot more room if you need to store them - 128bits compares to IPv4's 32.
不要忘记 IPv6 - 如果需要存储它们，则需要更多空间 - 128 位与 IPv4 的 32 位相比。
I'd go for bigint, though you will need some helper code to translate to human friendly versions.
我会选择 bigint，但您需要一些帮助代码来转换为人类友好的版本。
回答by Guffa
For space efficient storage and when the values are to be processed (matched or compared to a range), I use an int. The IP address really is just a 32 bit value.
为了节省空间的存储以及在处理值（匹配或与范围比较）时，我使用int. IP 地址实际上只是一个 32 位值。
For a simple solution where you just want to store the value to view it, I use a varchar(15)to store the string representation of the IP adress.
对于您只想存储值以查看它的简单解决方案，我使用 avarchar(15)来存储 IP 地址的字符串表示形式。
回答by Cade Roux
IPV4? int? or tinyint x 4?
IPV4？内部？还是tinyint x 4？
It really depends on whether it's just storage and retrieval or if it's going to be a ranged search criteria.
这实际上取决于它是否只是存储和检索，或者它是否将成为范围搜索条件。
回答by Matt Rogish
I've had some success with making four smallint (or whatever smallish integer datatype you prefer) columns -- one for each octet. Then, you can make a view which smashes them together as a char string (for display) or then you can write simple operators to determine who all is in what subnet etc.
我在制作四个 smallint（或您喜欢的任何小整数数据类型）列方面取得了一些成功——每个八位字节一个。然后，您可以创建一个视图，将它们作为字符字符串粉碎在一起（用于显示），或者您可以编写简单的运算符来确定谁都在哪个子网中等等。
It is quite fast (provided you do proper indexing) and also allows for really easy querying (no string manipulation!).
它非常快（如果你做适当的索引）并且还允许非常简单的查询（没有字符串操作！）。
