De la même manière J’avais dis qu’ j’aimerai afficher  le pays de localisation de l’IP qui se connecte à mon serveur.

Pour les mêmes raison, il me faut une nouvelle table qui ne contiendra que des IP Uniques.

Pour une représentation en page web sympa, j’ai trouvé l’ensemble des drapeaux des différents pays sous la forme de fichiers png du type : fr.png ou gb.png (code du pays sur 2 caractères en minuscules + .png). Il me faut donc une table (TBIPCOUNTRY) qui contiendra mes IP uniques, le code du pays (sur 2 caractères) et un libellé du Pays (un libellé est plus clair qu’un drapeaux) et j’ai besoin (ce besoin n’arrive qu eplus tard, au moment du développement du site) du type d’IP (LAN ou WAN)

L’ensemble de ces drapeaux est téléchargeable ici : flags-mini

Comme il n’est pas certain que je puisse géolocaliser l’IP, j’utiliserai encore une jointure gauche (LEFT JOIN)

Voici le code de création de la table TBIPCOUNTRY :

CREATE TABLE TBIPCOUNTRY (
  IP varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  TYPEIP varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'WAN',
  COUNTRYCODE char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  COUNTRYLIB varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (IP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Et pour obtenir le code du pays et le libellé si je l’ai il suffira de faire :

SELECT A.IP, B.COUNTRYCODE, B.COUNTRYLIB
FROM MYTCPSTAT.TBIPPORT AS A
LEFT JOIN MYTCPSTAT.TBIPCOUNTRY AS B
ON A.IP=B.IP;

Dans ce genre de circonstance, en théorie, toute IP présent dans TBIPPORT devrait exister dans TBIPCOUNTRY et je devrai créer une clé étrangère. Dans notre cas, ce ne serait pas une bonne idée car tcpdump me donne d’abord les IP, que je possède le le pays ou pas.

Par contre, ne rien faire ferait rapidement chuter les performances de MySQL sur cette jointure. Alors il nous faut un créer un index (surtout pas unique) sur la colonne IP de TBIPPORT, ceci rendra l’exécution de cette requête bien plus rapide.

ALTER TABLE MYTCPSTAT.TBIPPORT
ADD INDEX IXIPIPPORT (IP ASC) VISIBLE;