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

搞了一下午了，没搞出来，来求助万能的 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