This question already has an answer here:
这个问题已经有了答案:
- Datatype for storing ip address in SQL Server 10 answers
- 用于在SQL Server 10中存储ip地址的数据类型
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数据类型(。净组装)吗?
I don't need sorting.
我不需要排序。
15 个解决方案
#1
59
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地址存储为二进制(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':
顺便说一下，一个很少使用的SQL Server函数是PARSENAME，如果您将它存储为字符串，它可能会有所帮助。不是为IP地址而设计的，但非常适合它们。下面的电话将返回“14”:
SELECT PARSENAME('123.234.23.14', 1)
(numbering is right to left).
(编号从右到左)。
#2
25
I normally just use varchar(15) for IPv4 addresses - but sorting them is a pain unless you pad zeros.
我通常只对IPv4地址使用varchar(15)——但是对它们进行排序是一件很痛苦的事情，除非您将其填满0。
I've also stored them as an INT in the past. System.Net.IPAddress has a GetAddressBytes method 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 IPAddress to an int...
我还将它们存储为INT类型。System.Net。IPAddress有一个GetAddressBytes方法，该方法将以代表IP地址的4字节数组的形式返回IP地址。您可以使用以下c#代码将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 IPAddress object in .net, use the GetBytes method on BitConverter to get the int as a byte array. Pass that byte array to the constructor for IPAddress that takes a byte array, and you end back up with the IPAddress that you started with.
我之所以使用它，是因为我必须做大量的搜索来寻找欺骗地址，并且希望索引尽可能的小和快速。然后，要将地址从int中取出并放到.net中的IPAddress对象中，使用位转换器上的GetBytes方法将int作为字节数组。将该字节数组传递给获取字节数组的IPAddress的构造函数，然后返回初始的IPAddress。
var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));
#3
17
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
#4
4
One of my favorite articles talks 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位数据类型(或4个8位数据类型)将是最好的选择。
#5
3
IPV4? int? or tinyint x 4?
IPV4吗?int ?或非常小的整数x 4 ?
It really depends on whether it's just storage and retrieval or if it's going to be a ranged search criteria.
这取决于是存储和检索还是远程搜索条件。
#6
3
Don't forget about IPv6 - you need a lot more room if you need to store them - 128bits compares to IPv4's 32.
别忘了IPv6——如果你需要存储的话，你需要更多的空间——128bit和IPv4的32相比。
I'd go for bigint, though you will need some helper code to translate to human friendly versions.
我选择bigint，尽管您需要一些帮助代码来转换为人类友好的版本。
#7
3
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无符号并使用INET_ATON()和INET_NTOA()函数返回的IP地址数值,反之亦然。”我想这就是我要在db中使用的，除非我决定使用上面提到的php函数。
#8
3
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.
最好的方法(当不需要排序和IPs上的其他控制时)是将它存储为int，将它存储为varchar等等，这将比一个简单的无害的int花费更多的性能。
There is a property IPAddress.Address but 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 0xffffffff which equals to 255.255.255.255 in decimal representation.
有一个属性IPAddress。地址但是它已经过时了，我不知道为什么，因为如果不需要对IP类进行排序或控制，最好的方法是将它存储为无符号整数(最大值为0xffffffff，在十进制表示中等于255.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中存储单元的工作区:
- 4 byte unsigned int in ms sql?
- 在sql中有4个字节无符号整数?
- Sql server 4 byte unsigned int
- Sql server 4字节无符号整数
#9
2
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.
对于一个简单的解决方案，您只需存储该值以查看它，我使用varchar(15)来存储IP地址的字符串表示。
#10
1
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.
我已经成功地制作了4个smallint(或您喜欢的任何小整数数据类型)列——每个八位组一个。然后，您可以创建一个视图，将它们作为一个char字符串(用于显示)合并在一起，或者您可以编写简单的操作符来确定哪些人都在哪个子网中等等。
It is quite fast (provided you do proper indexing) and also allows for really easy querying (no string manipulation!).
它非常快(只要您做了适当的索引)，并且允许非常容易的查询(没有字符串操作!)
#11
0
Since an IP address has 32 bits in it, can you just use a LONG to store the numerical value?
It wouldn't be as space-wasteful as using VARCHAR, but then you'd have to decode it back to an IP before you use it, every time, and the delay and overhead that costs might not be worth it.
由于一个IP地址有32位，你能不能用一个长来存储数值?它不会像使用VARCHAR那样浪费空间，但是在每次使用它之前，您必须先把它解码回一个IP，这样的延迟和开销可能是不值得的。
#12
0
I'd probably go with a varchar or char.
我可能会选择varchar或char。
And set the size to 15.
将大小设置为15。
#13
0
The most appropriate data type for storing an IPv4 address in an MSSQL database, is an int. The only fiddly bit is converting it back to the dotted notation for display/sorting, hence I recommend you create a view that automates this for you.
在MSSQL数据库中存储IPv4地址的最合适的数据类型是int类型，惟一需要修改的是将其转换回点状表示法，以便进行显示/排序，因此我建议您创建一个视图，使其自动化。
#14
-3
I'm newbie @ php,sql , but i think fastest way to store something in sql db is to convert it to int value and save as int.
我是php sql的新手，但是我认为在sql db中存储东西最快的方法是将它转换为int值并保存为int。
I used function in php -
我用的函数是php -
function ip_convert() {
$ip = $_SERVER['REMOTE_ADDR'];
$intip = str_replace(".","0",$ip);
return $intip;
}
And then i just replace all dots with zeros. Then if i need use this ip from sql.. if($ip == ip_convert())
然后我用0替换所有的点。如果我需要使用sql的这个ip。如果($ ip = = ip_convert())
But this only if you use PHP.
但这只适用于使用PHP。
#15
-4
Quoting this:
引用:
Store IP addresses in a CHAR(15) column. Depending on how much data you're storing, this can be quite wasteful (why do we need to store the dots?). I
在CHAR(15)列中存储IP地址。这取决于您存储的数据量，这可能非常浪费(为什么我们需要存储这些点?)我
#1
59
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地址存储为二进制(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':
顺便说一下，一个很少使用的SQL Server函数是PARSENAME，如果您将它存储为字符串，它可能会有所帮助。不是为IP地址而设计的，但非常适合它们。下面的电话将返回“14”:
SELECT PARSENAME('123.234.23.14', 1)
(numbering is right to left).
(编号从右到左)。
#2
25
I normally just use varchar(15) for IPv4 addresses - but sorting them is a pain unless you pad zeros.
我通常只对IPv4地址使用varchar(15)——但是对它们进行排序是一件很痛苦的事情，除非您将其填满0。
I've also stored them as an INT in the past. System.Net.IPAddress has a GetAddressBytes method 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 IPAddress to an int...
我还将它们存储为INT类型。System.Net。IPAddress有一个GetAddressBytes方法，该方法将以代表IP地址的4字节数组的形式返回IP地址。您可以使用以下c#代码将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 IPAddress object in .net, use the GetBytes method on BitConverter to get the int as a byte array. Pass that byte array to the constructor for IPAddress that takes a byte array, and you end back up with the IPAddress that you started with.
我之所以使用它，是因为我必须做大量的搜索来寻找欺骗地址，并且希望索引尽可能的小和快速。然后，要将地址从int中取出并放到.net中的IPAddress对象中，使用位转换器上的GetBytes方法将int作为字节数组。将该字节数组传递给获取字节数组的IPAddress的构造函数，然后返回初始的IPAddress。
var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));
#3
17
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
#4
4
One of my favorite articles talks 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位数据类型(或4个8位数据类型)将是最好的选择。
#5
3
IPV4? int? or tinyint x 4?
IPV4吗?int ?或非常小的整数x 4 ?
It really depends on whether it's just storage and retrieval or if it's going to be a ranged search criteria.
这取决于是存储和检索还是远程搜索条件。
#6
3
Don't forget about IPv6 - you need a lot more room if you need to store them - 128bits compares to IPv4's 32.
别忘了IPv6——如果你需要存储的话，你需要更多的空间——128bit和IPv4的32相比。
I'd go for bigint, though you will need some helper code to translate to human friendly versions.
我选择bigint，尽管您需要一些帮助代码来转换为人类友好的版本。
#7
3
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无符号并使用INET_ATON()和INET_NTOA()函数返回的IP地址数值,反之亦然。”我想这就是我要在db中使用的，除非我决定使用上面提到的php函数。
#8
3
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.
最好的方法(当不需要排序和IPs上的其他控制时)是将它存储为int，将它存储为varchar等等，这将比一个简单的无害的int花费更多的性能。
There is a property IPAddress.Address but 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 0xffffffff which equals to 255.255.255.255 in decimal representation.
有一个属性IPAddress。地址但是它已经过时了，我不知道为什么，因为如果不需要对IP类进行排序或控制，最好的方法是将它存储为无符号整数(最大值为0xffffffff，在十进制表示中等于255.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中存储单元的工作区:
- 4 byte unsigned int in ms sql?
- 在sql中有4个字节无符号整数?
- Sql server 4 byte unsigned int
- Sql server 4字节无符号整数
#9
2
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.
对于一个简单的解决方案，您只需存储该值以查看它，我使用varchar(15)来存储IP地址的字符串表示。
#10
1
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.
我已经成功地制作了4个smallint(或您喜欢的任何小整数数据类型)列——每个八位组一个。然后，您可以创建一个视图，将它们作为一个char字符串(用于显示)合并在一起，或者您可以编写简单的操作符来确定哪些人都在哪个子网中等等。
It is quite fast (provided you do proper indexing) and also allows for really easy querying (no string manipulation!).
它非常快(只要您做了适当的索引)，并且允许非常容易的查询(没有字符串操作!)
#11
0
Since an IP address has 32 bits in it, can you just use a LONG to store the numerical value?
It wouldn't be as space-wasteful as using VARCHAR, but then you'd have to decode it back to an IP before you use it, every time, and the delay and overhead that costs might not be worth it.
由于一个IP地址有32位，你能不能用一个长来存储数值?它不会像使用VARCHAR那样浪费空间，但是在每次使用它之前，您必须先把它解码回一个IP，这样的延迟和开销可能是不值得的。
#12
0
I'd probably go with a varchar or char.
我可能会选择varchar或char。
And set the size to 15.
将大小设置为15。
#13
0
The most appropriate data type for storing an IPv4 address in an MSSQL database, is an int. The only fiddly bit is converting it back to the dotted notation for display/sorting, hence I recommend you create a view that automates this for you.
在MSSQL数据库中存储IPv4地址的最合适的数据类型是int类型，惟一需要修改的是将其转换回点状表示法，以便进行显示/排序，因此我建议您创建一个视图，使其自动化。
#14
-3
I'm newbie @ php,sql , but i think fastest way to store something in sql db is to convert it to int value and save as int.
我是php sql的新手，但是我认为在sql db中存储东西最快的方法是将它转换为int值并保存为int。
I used function in php -
我用的函数是php -
function ip_convert() {
$ip = $_SERVER['REMOTE_ADDR'];
$intip = str_replace(".","0",$ip);
return $intip;
}
And then i just replace all dots with zeros. Then if i need use this ip from sql.. if($ip == ip_convert())
然后我用0替换所有的点。如果我需要使用sql的这个ip。如果($ ip = = ip_convert())
But this only if you use PHP.
但这只适用于使用PHP。
#15
-4
Quoting this:
引用:
Store IP addresses in a CHAR(15) column. Depending on how much data you're storing, this can be quite wasteful (why do we need to store the dots?). I
在CHAR(15)列中存储IP地址。这取决于您存储的数据量，这可能非常浪费(为什么我们需要存储这些点?)我