Jak prosto przechowywać adres IP w bazie danych MySQL?

Ostatnio w poszukiwaniu możliwości zapisywania adresu IP do bazy danych natknąłem się na jakże prosty, ale poniekąd jeszcze wtedy niezbyt znany mi sposób na ten proceder. Wreszcie od jakiegoś czasu udało mi się zapoznać z czymś dla mnie jeszcze nieznanym w zakresie tworzenia rozwiązań internetowych. Ale do rzeczy.

Dla najbardziej aktualnie rozpowszechnionego protokołu komunikacyjnego IPv4 adres IP składa się z czterech oktetów liczbowych postaci dziesiętnej oddzielonych kropkami.

Przykład: 127.0.0.1 (A.B.C.D)

I właśnie przez te rozdzielające kropki adresu nie można takiego adresu zapisać jako bardziej wydajny INT.

Najprostszą linią oporu można by dla powyższej potrzeby utworzyć tabelę z kolumną typu VARCHAR lub CHAR i do niej zapisywać w przedstawionej powyżej postaci adresy IP. Najmniej wydajny jest ostatni typ – który w wypadku zapisania danych o mniejszej długości niż ta ustawiona wraz z typem uzupełnia pozostały zakres wartościami zerowymi.

CREATE TABLE przyklad ( ip VARCHAR(15) NOT NULL ); # w najprostszym przykładzie

Wszelkie próby ominięcia używania typów znakowych w bazie, czy to poprzez szyfrowanie, czy inne wytwory ludzkie wyobraźni zawsze będą wymagać innego typu danych niż liczbowy (chyba, że jakiś sposób uniwersalny znacie?). Nie licząc tych wbudowanych w MySQL…

Mówię tu o funkcjach typu INET_ATON i INET_NTOA. Jak one działają?

• INET_ATON – pozwala na konwersję znakowego 4 lub 8 bajtowego adresu IP do jej reprezentacji liczbowej (32bitowej). Konwersja odbywa się wg. prostej reguły, czyli iloczynu wartości danego oktetu i 256 podniesione do potęgi równej numerowi oktetu liczonego od prawej (pierwszy równa się 0).

  INET_ATON(’127.0.0.1′) = 127 * 256^3 + 0 * 256^2 + 0 * 256^1 + 1*256^0 = 2130706432 + 0 + 0 + 1 = 2130706433

  Należy także wspomnieć o dwóch rzeczach. Nie trzeba podawać w argumencie powyższej funkcji pełnego adresu – można ograniczyć się np. do danego zakresu, czy dwóch najwyższych klas – A i B.

  ... INET_ATON('127.0') ...

  Druga sprawa to fakt, że typ kolumny i pól do których będziemy zapisywać dane powinien być typu INT UNSIGNED (tylko wartości dodatnie). W przypadku kiedy pierwszy oktet (klasa A) będzie przy zapisie większa niż 127 mogą wystąpić błędy w bazie jeśli kolumna tychże adresów będzie ustawiona na czyste INT spowodowane wyjściem poza zakres tego typu (o czym więcej można przeczytać w powyższych linkach).

  CREATE TABLE przyklad ( ip INT(11) UNSIGNED NOT NULL ); # najprostsza wersja przygotowana pod użycie powyższej funkcji

• INET_NTOA – konwersja zwrotna do powyższej. Pozwala na zmianę z wartości liczbowej do wartości łańcucha znaków, czyli mówiąc potoczniej z powrotem do znanego wszem i wobec adresu IP.

Jak można się domyśleć pierwsza wymieniona wyżej funkcja będzie służyć do zapisu adresów IP, druga do odczytu. Kolejno można to zrobić w taki sposób:

INSERT INTO przyklad ( ip ) VALUES ( INET_ATON('127.0.0.1') );

SELECT INET_NTOA( ip ) FROM przyklad;

Należy pamiętać, że w przypadku odczytu argumentu z INET_NTOA nie powinno być żadnego typu apostrofów – niekiedy jako, że odczytujemy liczbę całkowitą może nastąpić błąd przy wykonaniu zapytania.