Table of Contents
In MySQL there are several types of Data Types. Each data type is responsible to handle each respective value. MySQL stores various data, it includes string values, integer values, date and time, blob for images, enum for options etc.
Inside this article we will see Which DataType stores IP Address in MySQL. DataType in MySQL is used to store different different types of data.
Learn More –
- Create and Call MySQL Stored Procedure
- How to Add Comments on Columns of MySQL Table
- Import and Export Data To MySQL Using Command Line
Let’s get started.
IP Address Structure
The IP address is a 32-bit number that uniquely identifies a network interface on a machine. An IP address is typically written in decimal digits, formatted as four 8-bit fields separated by periods. Each 8-bit field represents a byte of the IP address.
Data Type in MySQL – IP Address
To store IP address in MySQL, use integer data type. Using INT data type we can save more space in database.
CREATE TABLE `ip_addresses` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip_address` int(4) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
As you can see we have taken int(4) unsigned to store IP address.
Let’s see an example how can we do any row insertion into this table.
INSERT INTO `ip_addresses` (`ip_address`) VALUES (INET_ATON("127.0.0.1"));
What is INET_ATON ?
INET_ATON is a MySQL function. This method is used to convert an ip address to number.
In the above image we can see the inserted IP address is now converted into a number format. This is magic done by INET_ATON MySQL function.
This function remove dots(.) from IP address value and store.
How can we access IP address from Database ?
To access the IP address into it’s format means with 3 dots(.), we need to use INET_NTOA. INET_NTOA is a MySQL function which converts number to ip address.
SELECT id, INET_NTOA(
ip_address) as ip FROM
This is the use of INET_ATON and INET_NTOA mysql functions.
PHP Functions For IP Address
If we are looking for PHP functions which works same as MySQL functions for IP address.
The ip2long() and long2ip() functions
Note : You should use those for IPv4 addresses. Please make sure, $_SERVER[‘REMOTE_ADDR’] actually contains a valid IPv4 address (and not some IPv6-stuff).
int(2067765757) string(14) "188.8.131.52"
We hope this article helped you to learn about i.e Which DataType Stores IP Address In MySQL Tutorial in a very detailed way.