[php] SQL-Ausgabe nach IP-Nummern sortieren.

[Kristian =?iso-8859-1?Q?K=F6hntopp?=]

Kristian Köhntopp wrote:
> Eine Kollegin kam eben mit dem Wunsch zu mir, ein SQL-Statement zu finden, mit
> dem man eine Art "select * from iptest order by ip" machen kann, so daß die
> IP-Nummern in ihrer natürlichen Reihenfolge sortiert sind.

IP-Nummern sind 32-Bit Zahlen. Die natürliche Sortierreihenfolge ergibt sich,
wenn man die IP-Nummer in eine Zahl umrechnet und dann nach dieser Zahl sortiert.
Also

0.0.0.1   -> 1
0.0.1.0   -> 256
a.b.c.d

ipnr = 
  a * 256*256*256
+ b * 256*256
+ c * 256
+ d

Die Aufgabe besteht also darin, einen SELECT-Ausdruck zu finden, sodaß man

select ip,
       ... as a,
       ... as b,
       ... as c,
       ... as d
  from iptest;

+----------------+------+------+------+------+
| ip             | a    | b    | c    | d    |
+----------------+------+------+------+------+
| 1.2.3.4        | 1    | 2    | 3    | 4    |
| 10.11.12.13    | 10   | 11   | 12   | 13   |
| 100.101.102.10 | 100  | 101  | 102  | 10   |
| 111.11.1.0     | 111  | 11   | 1    | 0    |
| 111.10.3.10    | 111  | 10   | 3    | 10   |
| 193.98.110.1   | 193  | 98   | 110  | 1    |
| 193.174.3.10   | 193  | 174  | 3    | 10   |
+----------------+------+------+------+------+
7 rows in set (0.00 sec)

ausrechnen kann. Hat man das, kann man sehr leicht ein

update iptest set ipnr=
       a * 256*256*256
+      b * 256*256
+      c * 256
+      d;

generieren und dann mit

select * from iptest order by ipnr;

die gewünschte Ausgabe generieren. Bleibt das Problem, geeignete Ausdrücke für die
... dort oben zu finden.

Man kann nicht mit substring() alleine arbeiten, da durch Vorbedingungen bei der Eingabe sowohl 001.002.003.004 als auch 1.2.3.4 legal ist. Es ist also notwendig, mittels locate() die Positionen der Punkte zu bestimmen und dann mit variablen Positionen zu arbeiten.

In MySQL-Strings wie "hallo" hat "h" den Index 1, "o" den Index 5.

Sei pos_a der Index, an dem a startet, pos_b die Position, an der b startet und so weiter. 

Dann ist pos_a fest definiert als 1.

Man bestimmt also pos_b als locate(".", ip, pos_a)+1 as pos_b und a ist substring(ip, pos_a, pos_b-2).

Genau entsprechend ist pos_c definiert als locate(".", ip, pos_b)+1 und b ist substring(ip, pos_b, pos_c-2+pos_b).

Genau entsprechend ist pos_d definiert als locate(".", ip, pos_c)+1 und c ist substring(ip, pos_c, pos_d-2+pos_c).

Die letzte Stelle ergibt sich dann als substring(ip, pos_d), also zum String-Ende.


Wir testen:
mysql> select ip, 1 as pos_a, locate(".", ip, pos_a)+1 as pos_b from iptest;
ERROR 1054: Unknown column 'pos_a' in 'field list'

Leider sind Rückbezüge auf vorhergehende Spalten in SELECT-Statements nicht möglich. Ich kann also nicht in einer Spalte pos_a definieren und in der nächsten Spalte dann auf pos_a Bezug nehmen. Das ist unschön, aber nicht tödlich: Ich weiß ja, was ich für pos_a eingesetzt habe (1) und kann stattdessen 1 schreiben. Also ergibt sich:

mysql> select ip, 1 as pos_a, locate(".", ip, 1)+1 as pos_b from iptest;
+----------------+-------+-------+
| ip             | pos_a | pos_b |
+----------------+-------+-------+
| 1.2.3.4        |     1 |     3 |
| 10.11.12.13    |     1 |     4 |
| 100.101.102.10 |     1 |     5 |
| 111.11.1.0     |     1 |     5 |
| 111.10.3.10    |     1 |     5 |
| 193.98.110.1   |     1 |     5 |
| 193.174.3.10   |     1 |     5 |
+----------------+-------+-------+
7 rows in set (0.00 sec)

Ich muß also in locate(".", ip, pos_b)+1 as pos_c einfach die Definition für pos_b einsetzen: 
locate(".", ip, locate(".", ip, 1)+1)+1 as pos_c. 

Aus locate(".", ip, pos_c)+1 folgt entsprechend locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1.

Der Test:

mysql> select ip, 
              1 as pos_a, 
              locate(".", ip, 1)+1 as pos_b, 
              locate(".", ip, locate(".", ip, 1)+1)+1 as pos_c, 
              locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1 as pos_d 
         from iptest;
+----------------+-------+-------+-------+-------+
| ip             | pos_a | pos_b | pos_c | pos_d |
+----------------+-------+-------+-------+-------+
| 1.2.3.4        |     1 |     3 |     5 |     7 |
| 10.11.12.13    |     1 |     4 |     7 |    10 |
| 100.101.102.10 |     1 |     5 |     9 |    13 |
| 111.11.1.0     |     1 |     5 |     8 |    10 |
| 111.10.3.10    |     1 |     5 |     8 |    10 |
| 193.98.110.1   |     1 |     5 |     8 |    12 |
| 193.174.3.10   |     1 |     5 |     9 |    11 |
+----------------+-------+-------+-------+-------+
7 rows in set (0.00 sec)

Mit diesen Definitionen und von weiter oben

substring(ip, pos_a, pos_b-2) as a,
substring(ip, pos_b, pos_c-2+pos_b) as b,
substring(ip, pos_c, pos_d-2+pos_c) as c,
substring(ip, pos_d) as d

bekommt man dann

substring(ip, 1, locate(".", ip, 1)+1-2) as a,
substring(ip, locate(".", ip, 1)+1, locate(".", ip, locate(".", ip, 1)+1)+1 -2+locate(".", ip, 1)+1 as b,
substring(ip, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1 -2+locate(".", ip, locate(".", ip, 1)+1)+1 ) as c,
substring(ip, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1 ) as d

und mit dem Ausrechnen der Konstanten (+1-2 == -1) wird das dann

select ip,
       substring(ip, 1, locate(".", ip, 1)-1) as a,
       substring(ip, locate(".", ip, 1)+1, locate(".", ip, locate(".", ip, 1)+1)-1-locate(".", ip,1)) as b,
       substring(ip, locate(".", ip, locate(".", ip, 1)+1)+1, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)-1-locate(".", ip, locate(".", ip,1)+1)) as c,
       substring(ip, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1) as d
  from iptest;
+----------------+------+------+------+------+
| ip             | a    | b    | c    | d    |
+----------------+------+------+------+------+
| 1.2.3.4        | 1    | 2    | 3    | 4    |
| 10.11.12.13    | 10   | 11   | 12   | 13   |
| 100.101.102.10 | 100  | 101  | 102  | 10   |
| 111.11.1.0     | 111  | 11   | 1    | 0    |
| 111.10.3.10    | 111  | 10   | 3    | 10   |
| 193.98.110.1   | 193  | 98   | 110  | 1    |
| 193.174.3.10   | 193  | 174  | 3    | 10   |
+----------------+------+------+------+------+
7 rows in set (0.00 sec)

(Manche ältere MySQL verstehen substring(ip, 10) nicht und wollen stattdessen substring(ip, 10, 1000) sehen)


Daraus und mit

update iptest set ipnr=
       a * 256*256*256
+      b * 256*256
+      c * 256
+      d;

baut man sich also

update iptest set ipnr=
       substring(ip, 1, locate(".", ip, 1)-1)*256*256*256
+      substring(ip, locate(".", ip, 1)+1, locate(".", ip, locate(".", ip, 1)+1)-1-locate(".", ip,1))*256*256
+      substring(ip, locate(".", ip, locate(".", ip, 1)+1)+1, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)-1-locate(".", ip, locate(".", ip,1)+1))*256
+      substring(ip, locate(".", ip, locate(".", ip, locate(".", ip, 1)+1)+1)+1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 7  Changed: 0  Warnings: 2

mysql> select * from iptest order by ipnr;
+----------------+------------+
| ip             | ipnr       |
+----------------+------------+
| 1.2.3.4        |   16909060 |
| 10.11.12.13    |  168496141 |
| 100.101.102.10 | 1684366858 |
| 111.10.3.10    | 1862927114 |
| 111.11.1.0     | 1862992128 |
| 193.98.110.1   | 2147483647 |
| 193.174.3.10   | 2147483647 |
+----------------+------------+
7 rows in set (0.00 sec)

und alles wird gut.

Kristian

-- 
Kristian Köhntopp, NetUSE Kommunikationstechnologie GmbH
Siemenswall, D-24107 Kiel, Germany, +49 431 386 436 00
Using PHP3? See our web development library at
http://phplib.shonline.de/ (GPL)