IP's richtig in MYSQL Datenbank verwenden Tutorial

Info: Dieses Tutorial komplett durchlessen, da auch weiter unten eine wichtige Update-Information ist.

Ip-Adressen werden oftmals bei einer Datenbank als Typ varchar(15) gespeichert, aber dies beansprucht bis zu 16 Bytes, was sich jetzt im Moment nicht viel anhört, aber dies ist schon beträchtig, wenn man dies auf viele Einträge sieht.

Eine IP-Adresse ist so aufgebaut:
0.0.0.0 - 255.255.255.255

Die maximale Länge einer IP beträgt 15 Zeichen.

Es gibt bestimmte Funktionen, um eine IP-Adresse in einen Integer-Wert umzuwandeln, die IP-Adresse ist dann eine ganze Zahl und kann dann so in die Datenbank hinterlegt werden.

Die jeweiligen Werte (255, 255,...) werden dann aneinander angeordnet im Dualsystem, also aus lauter 0 und 1.

Die Länge beträgt dann im Dualsystem immer 32 Zeichen. (101010110......)
Die jeweiligen Funktionen wandeln, dann die Zahl danach ins Dezimalsystem um.

Also so dies würde es im Hintergrund ablaufen:
255.255.255.255 -> 11111... (32 mal die Zahl 1) -> 4 294 967 295
IP-Adresse -> Dualsystem -> Dezimalzahl

Für diesen Wert eignet sich die der Spaltentyp INT mit der Attribute UNSIGNED, das UNSIGNED dient dazu das nur positive Werte eingetragen werden können, so ist Definitionsbereich nur im positiven Bereich und deshalb größer.

Ohne UNSIGNED währe der Definitionsbereich nur die Hälfte sozusagen, weil das negative mit einbezogen wird, so wäre der größte positive Wert dann maximal nur 2^31 - 1=2 147 483 647 groß und mit UNSIGNED: 2^31 - 1=4 294 967 295

Der Spaltentyp INT verbraucht nur 4 Bytes, was wesentlich weniger ist als varchar(15) mit seinen maximalen 16 Bytes.

Das gute an so einer Methode ist, dass wenn man, dann nach einer IP-Adresse sucht dies viel schneller funktioniert, da man nun nach einer Zahl sucht und nicht nach einem Text.


So dann wollen wir mal zum richtigen Teil übergehen, den Programmierteil:

Bevor ihr etwas an eurer Datenbank macht, erstellt unbedingt eine Datenbanksicherung, um Ärger zu vermeiden!

MYSQL und PHP bieten Funktionen an, um die IP-Adresse in eine Zahl umzuwandeln und wieder in eine IP-Adresse.

PHP liefert diese Befehle:
http://de.php.net/ip2long - IP zu Zahl umwandeln
http://de.php.net/long2ip - Zahl zu IP umwandeln

So sieht die Funktionsweise zum Beispiel aus:


In diesem Beispiel wird die aktuelle Ip-Adresse des Auszuführenden in eine Zahl umgewandelt und kann dann als solche im Laufe des Scripts verwendet werden.

So kann man dann anschließend die Variable $ipadresse als die IP verwenden und in die Datenbank eintragen, man muss bemerken, dass es sich um eine Zahl dann handelt und nicht um einem Text. -> ....VALUES('.$ipadresse.',...


Mysql bietet hierfür diese Befehle an:
INET_ATON('127.0.0.1'
-> Wandelt IP-Adresse in eine Zahl um, dortiger Inhalt wird als Text gesehen und muss auch so behandelt werden!

INET_NTOA(2130706433)
-> Wandelt Zahl in eine Ip-Adresse um, der Inhalt dort drin wird als eine Zahl gesehen!


Wenn ihr in einer Datenbank die IP als Zahl vorliegen habt könnt ihr mit dem INET_NTOA Befehl ganz einfach diese Zahl in eine Ip-Adresse umwandeln, so könnte euere MYSQL-Abfrage beispielhaft aussehen:

Bevor ihr etwas an eurer Datenbank macht, erstellt unbedingt eine Datenbanksicherung, um Ärger zu vermeiden!


Wenn ihr eure Datenbank umstellen wollt, hab ich das Richtige für euch.
Bei dieser Methode müsst ihr nur diese Befehle im phpmyadmin ausführen lassen, natürlich müsst ihr es noch anpassen.

Diese Befehle bewirken, dass eine neue Spalte nach spaltemitipadresse mit dem Namen ip_neu erstellt wird mit Typ int UNSIGNET. Anschließend wird die Ip umgewandelt und in die neue Spalte ip_neu hinterlegt als Zahl, danach wird die Spalte spaltemitipadresse zum Typ int UNSIGNED abgeändert. Der Inhalt von ip_neu wird in spaltemitipadresse übernommen. Zum Schluss wird die Spalte ip_neu wieder gelöscht.


Ich wünsche euch noch viel Spaß mit diesem Tutorial.

Update:
Ich habe jetzt etwas wichtiges herausgefunden und zwar, dass der php-Befehl anders funktioniert als gedacht. Der mysql Befehl funktioniert wie oben beschrieben, aber bei php geht es anders zu.

Und zwar wenn ihr mal dieses Script testweise mal ausführt, dann werdet ihr es verstehen.
also die Zahlen Skala geht ungefähr so:
0.0.0.0 - 127.255.255.255 -> quasy die Hälfte -- 0 bis 2147483647
128.0.0.0 - 255.255.255.255 -> die ander Hälfte -- -2147483648 bis -1

Das hängt mit dem Vorzeichen zusammen, also wenn ihr lieber mit php die Konvertierung umsetzen wollt, was auch eigentlich mehr Sinn macht, dann müsst ihr nur das UNSIGNED nicht auswählen bei der Datenbank, also mit negativen Zeichen, so ist der Zahlenbereich dann von -2147483648 bis +2147483647.

Also vergisst nicht, dass man die beiden Varianten nur separat betreiben kann, also nur durch mysql oder php beides geht nicht!

mfg Eugen

Mitglieder-Kommentare

---