Which MySQL datatype to used to store an IP address?

We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format.

Let us see an example.

Creating a table.

mysql> create table IPV4AddressDemo
   -> (
   -> `IPV4Address` INT UNSIGNED
   -> );
Query OK, 0 rows affected (0.52 sec)

Inserting IP address into the table, with INET_ATON.

mysql> insert into IPV4AddressDemo values(INET_ATON("120.0.0.1"));
Query OK, 1 row affected (0.17 sec)

To display all records.

mysql> select *from IPV4AddressDemo;

The following is the output, but definitely we want it to be in IP address format.

+-------------+
| IPV4Address |
+-------------+
|  2013265921 |
+-------------+
1 row in set (0.00 sec)

As the above output is giving a sequence of integers, but we can convert them into the original IP address format. For that, use INET_NTOA

mysql> SELECT INET_NTOA(`IPV4Address`) FROM IPV4AddressDemo;

The following is the output that shows IP address in the actual format.

+--------------------------+
| INET_NTOA(`IPV4Address`) |
+--------------------------+
| 120.0.0.1                |
+--------------------------+
1 row in set (0.00 sec)

Chandu yadav

e

Updated on: 30-Jul-2019

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started