Umgang mit IP-Adressen in MySQL

Ich sehe immer wieder, dass Leute IPs als String in die Datenbank speichern. Aus diesem Grund möchte ich euch heute zeigen, wie man das Ganze in MySQL ein kleines bisschen eleganter hinbekommen kann.

IP-Adressen als UNSIGNED INT speichern

Wer eine IPv4-Addresse als String in die Datenbank speichern möchte, benötigt schon ein VARCHAR(16). Das entspricht 17 Byte. Nehmen wir aber ein INT, werden nur 4 Byte benötigt. Klingt auf dem ersten Blick nicht sehr viel, sieht man es aber in der Masse, ist es schon spürbar, dass weniger als ein Viertel des Speichers genutzt werden muss.

Der Speicher macht sich aber auch in der Geschwindigkeit bemerkbar: Ein Filter auf INT ist schneller als ein Filter auf VARCHAR.

IP-Adresse von String in INT formatieren

Ist nicht sehr schwer, MySQL bietet hier die Funktion INET_ATON, in der einfach die IP-Adresse übergeben werden kann:

SELECT INET_ATON('127.0.0.1')

Ergebnis: 2130706433

Den Befehl gibt es auch in verschiedenen Programmiersprachen. In PHP macht das die Funktion ip2long:

echo ip2long('127.0.0.1');

Integer wieder in String konvertieren

Natürlich geht der Weg auch in die andere Richtung. So kommen wir wieder auf 127.0.0.1:

SELECT INET_NTOA('2130706433')

und in PHP:

echo long2ip(2130706433);

Abfrage auf IP-Netze

Vielleicht sehen einige von euch jetzt einen Nachteil darin, dass man das IP-Netz jetzt nicht mit LIKE abfragen kann. Das würde ich selbst wenn ihr die IP noch als String speichert nach Möglichkeit vermeiden. Denn so geht ihr nicht über einen Index, was die ganze Abfrage verlangsamt.

Meine Empfehlung: Wenn die Netz-Abfrage wirklich benötigt wird, legt dafür eine neue Spalte an, in der ihr das Netz auch als INT speichert. So könnt ihr darüber auch einen Index anlegen.

Wichtig: UNSINGED

Beim Speichern der IP ist es wichtig, das die INT Spalte wirklich auf UNSIGNED steht. Im CREATE-Statement könnt ihr statt INT einfach „UNSINGED INT“ nehmen. Andernfalls werden IP-Adressen aus bestimmten Bereichen nicht richtig gespeichert.

IPv6 in MySQL

Wie Henning in den Kommentaren erwähnt hat, gibt es bereits Funktionen, die auch IPv6 abbilden können. Diese heißen INET6_ATON und INET6_NTOA in MySQL und inet_pton bzw. inet_ntop in PHP. Das Ergebnis ist allerdings zu groß für INTEGER und muss in VARBINARY(16) gespeichert werden.

Weiterführende Seiten

Du arbeitest in einer Agentur oder als Freelancer?
Dann wirf doch mal einen Blick auf unsere Software FeatValue.

Kommentare

Henning schrieb am 12.08.2013:

Ist ja schön das man aus IPv4 einen int machen kann und auch wieder aus einem int ein IPv4 aber wie immer wird die Zukunft vergessen ... IPv6. Du solltest angeben das es die Funktion INET6_ATON und INET6_NTOA unter mysql gibt und in PHP gibt es die Funktionen inet_pton und inet_ntoa welche mit IPv4 und v6 umgehen können.

Stefan Wienströer schrieb am 13.08.2013:

Da hast du natürlich Recht. Hab einen Hinweis auf IPv6 hinzugefügt.

Pascal schrieb am 13.08.2013:

Für alle mit 32 Bit Systemen und Problemen mit negativen Werten: http://stackoverflow.com/questions/3062843/php-ip2long-returning-negative-val

Über uns

Wir entwickeln Webanwendungen mit viel Leidenschaft. Unser Wissen geben wir dabei gerne weiter. Mehr über a coding project