Un bout de DBA MySQL, En long et en large

La gestion des IP dans MySQL

La gestion des IP dans MySQL est très simple. Premièrement, il faut savoir que la manière la plus efficace de stocker un IP et de le représenté sous une forme numérique, soit un INT UNSIGNED (donc 4 bytes) plutot qu’un CHAR(15) de 15 bytes.

Il demeure malgré tout possible de manipuler les IP avec leur forme alphanumérique en utilisant 2 function de MySQL: INET_ATON() et INET_NTOA().

mysql> SELECT INET_ATON('192.168.20.76');
+----------------------------+
| INET_ATON('192.168.20.76') |
+----------------------------+
|                 3232240716 |
+----------------------------+

mysql> SELECT INET_NTOA(3232240716);
+-----------------------+
| INET_NTOA(3232240716) |
+-----------------------+
| 192.168.20.76         |
+-----------------------+

Si vous avez voulez savoir si un IP fait parti d’un sous reseaux, vous pouvez faire des manipulations bitwise:

SET @myIP := INET_ATON('192.168.20.76');
SET @theNetMask = INET_ATON('255.255.255.255');

-- La premiere addresse du subnet s'écrit (@myIP & @theNetMask)
-- et la dernière (@myIP | ~ @theNetMask & 0xffffff);
-- Donc, pour savoir si un ip fait parti d'un sous-réseaux:

SELECT INET_ATON('192.168.20.0')
BETWEEN (@myIP & @theNetMask)
AND (@myIP | ~ @theNetMask & 0xffffff);

Si on désire connaitre chaque parti du IP à partir de sa notation numérique, on peut faire:

SET @myIP := INET_ATON('192.168.20.76');
SELECT @myIP, (@myIP >> 24) as firstOctet,
(@myIP>>16) & 255 as secondOctet,
(@myIP>>8) & 255 as thirdOctet,
@myIP & 255 as fourthOctet;

+------------+------------+-------------+------------+-------------+
| @myIP      | firstOctet | secondOctet | thirdOctet | fourthOctet |
+------------+------------+-------------+------------+-------------+
| 3232240716 |        192 |         168 |         20 |          76 |
+------------+------------+-------------+------------+-------------+

Notez que les 2 fonctions sont limité à 32bits, donc il ne fonctionne que pour les IPv4. De plus, l’utilisation des methodes rend impossible l’utilisation des indexes lors d’une recherche. Il est préférable de transformer à l’avance le IP avec la fonction PHP ip2long() par exemple.