求助： MySQL 下 ip 地址 binary 和点分十进制形式转换的问题， inet_ntoa(）

mysql> desc X20180327; +--------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+----------------+ | auto_id | int(10) unsigned | NO | MUL | NULL | auto_increment | | record_id | int(10) unsigned | NO | PRI | NULL | | | src_zone | int(10) unsigned | NO | | NULL | | | src_ip | binary(16) | NO | MUL | NULL | | | src_port | smallint(5) unsigned | NO | | NULL | | | dst_zone | int(10) unsigned | NO | | NULL | | | dst_ip | binary(16) | NO | MUL | NULL | | | dst_port | smallint(5) unsigned | NO | | NULL | |

存储方式如下：
首字节存储非法 IPv6 头，后 4 字节存储 ipv4 数值，其他字节填充 0，网络序存储

mysql> select HEX(src_ip) from FW_LOG_fwlog.X20180327 where auto_id = 1; +----------------------------------+ | HEX(src_ip) | +----------------------------------+ | FFBF000000000000000000007BF94C7D | +----------------------------------+ 1 row in set (0.00 sec)

我单独把值复制出来转换时没问题的

mysql> select inet_ntoa(0xFFBF000000000000000000007BF94C7D & 0xFFFFFFFF) from FW_LOG_fwlog.X20180327 where auto_id = 1; +------------------------------------------------------------+ | inet_ntoa(0xFFBF000000000000000000007BF94C7D & 0xFFFFFFFF) | +------------------------------------------------------------+ | 123.249.76.125 | +------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select inet_ntoa(0xFFBF000000000000000000007BF94C7D) from FW_LOG_fwlog.X20180327 where auto_id = 1; +-----------------------------------------------+ | inet_ntoa(0xFFBF000000000000000000007BF94C7D) | +-----------------------------------------------+ | 123.249.76.125 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> select inet_ntoa(0x7BF94C7D); +-----------------------+ | inet_ntoa(0x7BF94C7D) | +-----------------------+ | 123.249.76.125 | +-----------------------+ 1 row in set (0.00 sec)

但是………………

mysql> select INET_NTOA(src_ip) from FW_LOG_fwlog.X20180327 where auto_id = 1; +-------------------+ | INET_NTOA(src_ip) | +-------------------+ | 0.0.0.0 | +-------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select inet_ntoa(src_ip&0xFFFFFFFF) from FW_LOG_fwlog.X20180327 where auto_id = 1; +------------------------------+ | inet_ntoa(src_ip&0xFFFFFFFF) | +------------------------------+ | 0.0.0.0 | +------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: '??' | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec)

该怎么处理呢？版本:Ver 14.14 Distrib 5.1.58

4 条回复 2018-03-28 09:44:38 +08:00

msg7086

2018-03-27 21:46:55 +08:00

试试 cast src_ip 到 interger 再运算。

axisray

2018-03-28 08:45:13 +08:00

@msg7086
```
mysql> select INET_NTOA(CAST(src_ip AS UNSIGNED)) from FW_LOG_fwlog.X20180327 wh
ere auto_id = 1;
+-------------------------------------+
| INET_NTOA(CAST(src_ip AS UNSIGNED)) |
+-------------------------------------+
| 0.0.0.0 |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)
```

msg7086

2018-03-28 09:24:32 +08:00

+-----------------------------------------------+
| inet_ntoa(conv(substr(hex(src_ip),-8),16,10)) |
+-----------------------------------------------+
| 123.249.76.125 |
+-----------------------------------------------+
1 row in set (0.00 sec)

axisray

2018-03-28 09:44:38 +08:00

@msg7086
哈哈哈，我刚搞定，想法差不多

mysql> select inet_ntoa(conv(right(HEX(src_ip),8),16,10))
from FW_LOG_fwlog.X20180327 limit 100;
+---------------------------------------------+
| inet_ntoa(conv(right(HEX(src_ip),8),16,10)) |
+---------------------------------------------+
| 1.31.58.142 |
| 14.204.67.143 |
| 14.204.126.70 |
| 27.156.89.140 |
| 37.187.148.221 |

虽然这样有点恶心………………行了就这样吧，谢谢啦兄弟！