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