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

[axisray]

搞了一下午了，没搞出来，来求助万能的 V 友了
列 src_ip 类型是 binary(16)

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

[msg7086]

试试 cast src_ip 到 interger 再运算。

[axisray]

@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]

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

[axisray]

@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 |

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