SQL:二进制到IP地址 [英] SQL: Binary to IP Address
本文介绍了SQL:二进制到IP地址的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将二进制IP转换为人类可读的IP
I'm trying to convert A binary IP to a human-readable IP
SELECT HEX( `ip_bin` ) FROM `log_metadata`
给我
4333D26E000000000000000000000000
还有
SELECT INET_NTOA(0x4333D26E)
给我67.51.210.110
所以我尝试了:
SELECT
SUBSTRING( CONVERT(HEX(`ip_bin`), CHAR(32)), 1, 8 ) AS `A`
, INET_NTOA(
SUBSTRING( CONVERT(HEX(`ip_bin`), CHAR(32)), 1, 8 )
) AS `B`
, INET_NTOA(hex(`ip_bin`)) AS `C`
, INET_NTOA(`ip_bin`) AS `D`
FROM `log_metadata`
但我只能得到
+----------+------------+------------+---------+
| A | B | C | D |
+----------+------------+------------+---------+
| 4333D26E | 0.0.16.237 | 0.0.16.237 | 0.0.0.0 |
+----------+------------+------------+---------+
有什么建议吗?
推荐答案
mysql> select inet_ntoa(conv('4333d26e', 16, 10));
+-------------------------------------+
| inet_ntoa(conv('4333d26e', 16, 10)) |
+-------------------------------------+
| 67.51.210.110 |
+-------------------------------------+
1 row in set (0.00 sec)
检查它是否也可以工作=)
Check if it works there too =)
问题在于,inet_ntoa
似乎是从十进制 strings
数字表示形式而不是十六进制数字或十六进制integers
解析的.比较:
The problem is that inet_ntoa
seems to parse from decimal strings
number representation, not hexadecimal ones, or from hexadecimal integers
. Compare:
mysql> select inet_ntoa(0x4333d26e);
+-----------------------+
| inet_ntoa(0x4333d26e) |
+-----------------------+
| 67.51.210.110 |
+-----------------------+
1 row in set (0.02 sec)
mysql> select inet_ntoa('0x4333d26e');
+-------------------------+
| inet_ntoa('0x4333d26e') |
+-------------------------+
| 0.0.0.0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
编辑
这更简单,而且似乎也可以工作:
Edit
This is simpler and seems to work too:
SELECT INET_NTOA(CONV(ip_bin, 2, 10)) FROM log_metadata
这篇关于SQL:二进制到IP地址的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文