IP Address To Numeric (INET_ATON) and Numeric to IP Address (INET_NTOA) in MySql

If you are about to save an IP address into a MySql table of type char or varchar, then it is preferable to make use of the SQL function INET_ATON and save it in integer format instead of char or varchar.

Why to convert from IP Address to Numeric (INET_ATON) ?

Well, this will let you save the value into integer instead of character, and when later, you want to search your saved IP addresses, then you can assume that searching on an integer field is pretty much faster than the string i.e. char, varchar etc.
Even it may help you in creating integer indexes instead of string, for the searching purpose.

How to convert from IP Address To Numeric (INET_ATON) ?

While saving the IP address into numeric or integer field, you can simply use the function as follows:

INSERT INTO tbl_test (id, ip_to_numeric)

Where tbl_test is the table name, and ip_to_numeric is the numeric field.

How to convert back from Numeric to IP Address (INET_NTOA) ?

After successfully converting and saving the values from IP Address into Numeric, you can reconvert those converted values into IP Address through the following query:

SELECT INET_NTOA(ip_to_numeric)
FROM tbl_test;

This will dispolay all the converted numeric IP addresses into their original format.


Leave a Reply

Your email address will not be published. Required fields are marked *