Internet Protocol address is a special kind of data. This data does not correspond directly to available MySQL built-in data types. I have seen in many servers, the IP(Internet Protocol) address is stored in CHAR(12), VARCHAR(15), TEXT and so on.
The dotted-quad notation is just a way of writing for better readability, it is not the original form of raw data. The IP address is a UNSIGNED INTEGERS they are not strings.
So the question is how we can store the IP addresses with dot in integer columns? The MySQL provides the built-it function to do this operation. The functions are given below:
For IPv4 addresses:
For IPv6 addresses:
(or)
The dotted-quad notation is just a way of writing for better readability, it is not the original form of raw data. The IP address is a UNSIGNED INTEGERS they are not strings.
So the question is how we can store the IP addresses with dot in integer columns? The MySQL provides the built-it function to do this operation. The functions are given below:
For IPv4 addresses:
INET_ATON()mysql> select inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
| 2130706433 |
+------------------------+
1 row in set (0.11 sec)INET_NTOA()SELECT inet_ntoa('2130706433');
+-------------------------+
| inet_ntoa('2130706433') |
+-------------------------+
| 127.0.0.1 |
+-------------------------+
1 row in set (0.02 sec)For IPv6 addresses:
The MySQL provides the following built-in functions to store and retrieve the IPv6 addresses. Here, IPv6 addresses are not stored as integers, since numeric-format IPv6 addresses require more bytes than the UNSIGNED BIGINT. So the below functions returns the VARBINARY(16) data types. Let us see with an example.
mysql> select hex(inet6_aton('127.0.0.1'));
+---------------------------------+
| hex(inet6_aton('127.0.0.1')) |
+---------------------------------+
| 7F000001 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334'));
+---------------------------------------------------------------------------+
| hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334')) |
+---------------------------------------------------------------------------+
| 20010DB885A3000000008A2E03707334 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select inet6_ntoa(unhex('20010DB885A3000000008A2E03707334'));
+----------------------------------------------------------------------------+
| inet6_ntoa(unhex('20010DB885A3000000008A2E03707334')) |
+----------------------------------------------------------------------------+
| 2001:db8:85a3::8a2e:370:7334 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
Note:
Assume that, you are writing a query to find out the users, who are connected from the internet protocol address 127.0.0.1. So the following query can be derived.
SELECT name FROM user WHERE inet_ntoa(ipaddress) = '127.0.0.1';
Be aware that, this query will not use the index created on the ipaddress column, since, we are modifying the index column during the execution, also, it will convert the integer into IP adress row by row. So the work around will be:
SET @ip = inet_aton('127.0.0.1');
SELECT name FROM user WHERE ipaddress = @ip;
SELECT name FROM user WHERE ipaddress = inet_aton('127.0.0.1');
Now if you check the query execution plan, you will see that, the query using index on ipaddress column. I hope this post may help you. Please share your views on comment section and if you have other better methods, please share it on comment section.
