Best Way to Store Ip in Database

Best way to store IP in database?

Store the ip as a INT(11) UNSIGNED, then use the INET_ATON and INET_NTOA functions to store/retrieve the ip address.

Sample code:

INSERT table(ip) VALUES (INET_ATON('192.168.0.1')); /*ip = 3232235521*/
SELECT INET_NTOA(ip) As IPAddress FROM table; /*IPAddress = 192.168.0.1*/

Most efficient way to store IP Address in MySQL

For IPv4 addresses, you may want to store them as an int unsigned and use the INET_ATON() and INET_NTOA() functions to return the IP address from its numeric value, and vice versa.

Example:

SELECT INET_ATON('127.0.0.1');

+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 | 
+------------------------+
1 row in set (0.00 sec)

SELECT INET_NTOA('2130706433');

+-------------------------+
| INET_NTOA('2130706433') |
+-------------------------+
| 127.0.0.1               | 
+-------------------------+
1 row in set (0.02 sec)

What is the most appropriate data type for storing an IP address in SQL server?

Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.

A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return '14':

SELECT PARSENAME('123.234.23.14', 1)

(numbering is right to left).

Store IP Address in Data Base best way

"Octects in four tinyint columns." http://web.archive.org/web/20150511204915/http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

How to store an IP in mySQL

I would suggest looking at what type of queries you will be running to decide which format you adopt.

Only if you need to pull out or compare individual octets would you have to consider splitting them up into separate fields.

Otherwise, store it as a 4 byte integer. That also has the bonus of allowing you to use the MySQL built-in INET_ATON() and INET_NTOA() functions.

Performance vs. Space

Storage:

If you are only going to support IPv4 addresses then your datatype in MySQL can be an UNSIGNED INT which only uses 4 bytes of storage.

To store the individual octets you would only need to use UNSIGNED TINYINT datatypes, not SMALLINTS, which would use up 1 byte each of storage.

Both methods would use similar storage with perhaps slightly more for separate fields for some overhead.

More info:

• Numeric Type Overview
• Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Performance:

Using a single field will yield much better performance, it's a single comparison instead of 4. You mentioned that you will only run queries against the whole IP address, so there should be no need to keep the octets separate. Using the INET_* functions of MySQL will do the conversion between the text and integer representations once for the comparison.

Store IP into mysql database

For handling and storing both IPv4 and IPv6 addresses, you can use datatype VARBINARY(16).

In version 5.6, MySQL (finally!) introduced conversion functions for IPv6 addresses: INET6_ATON, so you don't have to do the conversion in your application.

If you are handling only IPv4 addresses, you can continue to use the INET6_ATON function, and BINARY(4) is a suitable datatype for storing it.

Conveniently, the inverse functions, for converting from the binary representation back to dotted decimal form (for IPv4 addresses) or the hex colon form (for IPv6 addresses) are also available.

Reference: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton

Q: How to should I make this column decimal if the ip (version 6) can have more than 15 chars ?

A: Don't make the column DECIMAL, make it VARBINARY(16), and convert both the IPv4 dotted decimal representation, and the IPv6 hex colon representation into a binary representatin to store it.

What is the ideal datatype to store IP address in a mysql table?

IPv4 addresses use 32 bits (4 bytes), IPv6 addresses use 128 bits (16 bytes). Thus, you can store their binary representation in BINARY(16) or VARBINARY(16) fields.

Also see my answer to the question IP address storing in mysql database. It also provides comments why you would choose one over the other.

---

Related Topics