Verschachtelte Select Abfrage

[erselbst]

Hallo, ich möchte von einer Tabelle IPADDR die Anzahl der IP-Adressen (Spalte IPADDR) mit COUNT() ermitteln. In der Tablelle können die IP-Adressen aber mehrmals (max. zweimal) vorkommen. Einmal gibt es die Spalte PORT mit Port 80, das zweite mal (kann, muss aber nicht) mit Port 443. Ich dachte mir, ich werde mit GROUP BY die Spalte IPADDR Gruppieren und davon mit COUNT() die Anzahl ermittlen. Dann bekomme ich aber mehrere Zeilen mit der jeweiligen Anzahl. Wie bekomme ich die Gesammtanzahl der IP-Adressen? Kann das mir jemand sagen?

SELECT COUNT((SELECT *
FROM `ipaddr`
GROUP BY `ipaddr`))
FROM `ipaddr`

Bei meinem Versuch, bekomme ich die Meldung, dass nur eine Spalte erlaubt ist.

[meikel (†)]

Ändere einfach das Konzept:
IP unique/primary key und verwende den SET Typ für die Portspalte.
Dann gibts jede IP nur einmal. Hoffentlich als Integer und nicht als String. ip2long() - vorausgesetzt, Du hast nen 64bit Server - erspart viel Ärger.

[erselbst]

ja ne panimaju :-(
Wie sieht dann die Tabelle in ihrer Struktur dann aus? Geht denn das auch bei MyISAM? Meine Tabelle hat z.Z. diese Struktur:

CREATE TABLE `ipaddr` (
`id` TINYINT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`ipaddr` VARCHAR( 15 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`port` INT( 4 ) NOT NULL DEFAULT '80'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

[meikel (†)]

Ich meinte es so:

CREATE TABLE `test` (
  `ipaddr` bigint(20) unsigned NOT NULL,
  `port` set('80','443') NOT NULL DEFAULT '80',
  UNIQUE KEY `ipaddr` (`ipaddr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Beim Eintragen konvertierst Du mit PHP den IP String ins Integerformat:
int ip2long ( string $ip_address )

ipaddr ist unique. Deshalb wird der Port über den Datentyp SET realisiert.

Hier etwas Futter:

INSERT INTO `test` (`ipaddr`, `port`) VALUES (3232235777, '80,443');
INSERT INTO `test` (`ipaddr`, `port`) VALUES (3232261121, '80');

Hier das Beispiel für die Umwandlung:
http://mod.php.developer.vc/ip2long/

SELECT `ipaddr`, `port` FROM `test` WHERE `ipaddr` = 3232235777;

ergibt:

ipaddr 3232235777 	Port '80,443'

PHP-Code:

echo long2ip(3232235777); 


Problematisch ist allerdings das Integerformat. Bei 64 Bit Systemen klappts ja:
http://mod.php.developer.vc/PHP_INT_MAX/
Bei 32 Bit betrachtet PHP integer nicht als unsigned.

btw: wofür braucht man das?

[erselbst]

Hmm, das funktioniert erstmal so. Muss denn Port vom Type SET sein? Dann kann ich nur vorgegebenen Ports eingeben. Es wäre schön, wenn man die Ports selbst eingeben kann (z.B. 8080). Dann wäre doch besser die Spalte Port vom Type INT(4) zu nehmen. Ist das richtig?

Grr, erst denken, dann posten. Das geht natürlich nicht, wäre ja dann nicht unique bzw könnte nur ein Port zu einer IP-Adresse eingegeben werden.

[meikel (†)]

Zitat von erselbst

Hmm, das funktioniert erstmal so. Muss denn Port vom Type SET sein? Dann kann ich nur vorgegebenen Ports eingeben. Es wäre schön, wenn man die Ports selbst eingeben kann (z.B. 8080). Dann wäre doch besser die Spalte Port vom Type INT(4) zu nehmen. Ist das richtig?

SET ist ein 64 -Bitmaskenfeld.

Grr, erst denken, dann posten. Das geht natürlich nicht, wäre ja dann nicht unique bzw könnte nur ein Port zu einer IP-Adresse eingegeben werden.

Natürlich wäre es möglich, zur Laufzeit die Spalte um weitere Ports zu erweitern.

Teste das einfach mal mit dem PMA (PHPmyAdmin). Der liefert dann auch gleich den SQL String.

Allerdings gibt es ein kleines Problem: die Reihenfolge der Bezeichner darfst Du nicht verändern:
1. Bit hat den Namen '80'
2. Bit hat den Namen '433'
3. Bit hat den Namen '8080'
MySQL trägt in die 8Byte große Spalte dann nur ne 1 oder ne 0 ein.

Und damit Du an die aktuelle Reihenfolge kommst, könntest Du einen Datensatz für die 0.0.0.0 => 0 anlegen, in dem Du alle verfügbaren Bits setzt.

INSERT INTO `test` (`ipaddr`, `port`) VALUES (0, '80,443');

Bei der Abfrage liefert MySQL nen kommaseparierten String aller gesetzten Bits.

SELECT port FROM test WHERE ipaddr = 0;

=> '80,443'
Nu soll Port 8080 dazu. In SQL sähe das so aus:

ALTER TABLE `test` CHANGE `port` `port` SET( '80', '443', '8080' )  NOT NULL DEFAULT '80';

Wie Du den ALTER TABLE String basteln könntest, kannste Dir da mal angucken:
http://mod.php.developer.vc/set-1/ (Quelltext wird angezeigt)

[erselbst]

DISTINCT ist genau was ich gesucht habe ;) Die Folgende Abfrage liefert mir das Ergebnis welches ich benötige.

SELECT COUNT(DISTINCT `ipaddr`) AS `count` FROM `ipaddr`

Trotzdem vielen Dank für die ausführlichen Antworten und Wege, wie man auf die Lösung kommt.

[meikel (†)]

Zitat von erselbst

DISTINCT ist genau was ich gesucht habe ;)

Oder so.

Wofür brauchst Du das denn eigentlich? IP sind ne endliche Menge an Integerzahlen (paar Netze 0.x, 10.x 127.x 192.168.x usw.fallen noch weg). Zudem leben wir in einem Ländle, in dem die wenigsten Surfer über eine feste IP verfügen. Und ein Teil davon surft böswilligerweise über Proxies.

Warste unartig und mußt jetzt Logfiles parsen? <ggg>