Most efficient way to store IP Address in MySQL

MysqlSqlIp Address

Mysql Problem Overview

What is the most efficient way to store and retrieve IP addresses in MySQL? Right now I'm doing:

SELECT * FROM logins WHERE ip = '1.2.3.4'

Where ip is a VARCHAR(15) field.

Is there a better way to do this?

Mysql Solutions

Solution 1 - 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)

Solution 2 - Mysql

If you only want to store IPv4 addresses, then you can store them in a 32-bit integer field.

If you want to support IPv6 as well, then a string is probably the most easy-to-read/use way (though you could technically store them in a 16-byte VARBINARY() field, it would be annoying trying to generate SQL statements to select by IP address "by hand")

Solution 3 - Mysql

The most important thing is to make sure that column is indexed. This could make a huge difference to queries based on IP address.

Solution 4 - Mysql

Whatever is easiest for you to work with. The size or speed issue is not an issue until you know it is an issue by profiling. In some cases, a string might be easier to work with if you need to do partial matching. But as a space or performance issue, don't worry about it unless you have real cause to worry about it.

Solution 5 - Mysql

maybe store the integer value directly in an integer field? An IP address is basically 4 "shorts".

Check it out: http://en.kioskea.net/faq/945-converting-a-32-bit-integer-into-ip

Categories
Recommended Mysql Solutions
Recommended Sql Solutions
Recommended Ip Address Solutions

Previous Solution:

Check whether the string is a unix timestamp

PhpDatetimeTimeUnix Timestamp

Next Solution:

Javascript Regular Expressions - Replace non-numeric characters

JavascriptRegex

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionensnareView Question on Stackoverflow
Solution 1 - MysqlDaniel VassalloView Answer on Stackoverflow
Solution 2 - MysqlDean HardingView Answer on Stackoverflow
Solution 3 - MysqlMark ByersView Answer on Stackoverflow
Solution 4 - MysqlDGMView Answer on Stackoverflow
Solution 5 - MysqlBrian DilleyView Answer on Stackoverflow