Syntax error Which MySQL datatype to used to store an IP address?

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)
Updated on: 2019-07-30T22:30:23+05:30

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements