[MySQL] Resultaat als Regular Expression met distinct

[pierre-oord]

Na een lange zoektocht (ook hier) lijkt een simpele vraag uit een database, niet zo makkelijk te gaan. Stel je voor, een database IP met als kolom ook IP. Inhoud:

1.2.3.4
1.2.3.5
1.2.3.60
4.5.6.7
4.5.6.84
5.6.7.9

Leuk, allemaal IP adressen. Ik wil nu weten weten welke /24 er allemaal in de database voorkomen. Dat zijn dus:
1.2.3
4.5.6
5.6.7

En dat wil ik dus ook terugkrijgen.

SELECT DISTINCT `ip` FROM `ip` WHERE `ip` REGEXP '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.'

Geeft natuurlijk niet het juiste terug: Hij selecteer het juiste, en geeft alles dat in de database voorkomt terug. De selectie is in principe alles, maar het resultaat moet anders zijn. Om alles te selecteren en vervolgens in PHP een regexp erop los laten, lijkt me erg inefficient. Weet iemand hoe ik het gewenste resultaat van MySQL terug kan krijgen? Thx!

[Janoz]

Je bent compleet de verkeerde richting ingeslagen mbt de oplossing. Je gaat werken met de string representatie van een ip-nummer en hier vervolgens string tools op los laten.

Een ip-nummer is gewoon een 32bits unsigned getal dat voor de leesbaarheid als 4 losse bytes gescheiden door puntjes wordt weergegeven. Zou je je ip-nummers opslaan als long dan kun je gewoon simpel AND-en met 255.255.255.0 en dan ben je klaar. Naast dat het veel simpeler is dan een regexp is het ook nog eens ontelbaar veel efficienter.

[MueR]

Hoewel Janoz's oplossing beter is, zou je eventueel de individuele blokken om kunnen zetten naar hex waarden. Dan kan je per /24 blok gaan kijken naar de eerste 6 karakters van die string.

Bijv: 192.168.0.1 = C0A80001

[ Voor 9% gewijzigd door MueR op 06-06-2008 17:10 . Reden: duh, typgeit ]

[Janoz]

Als je je string al in een hexstring om weet te zetten dan is dat exact evenveel werk als het omzetten naar een int. Ik zie niet echt wat de meerwaarde is.

Los daarvan heeft MySQL gewoon standaard functies om een ip string naar een int en weer terug om te zetten: http://dev.mysql.com/doc/...s.html#function_inet-aton

[MueR]

Ik zei toch dat je oplossing beter was

[RobIII]

Dus:
• Nieuw field (int) aanmaken
• update mytable set newfield = INET_ATON(oldfield )
• Oldfield wegtyfen
• evt newfield renamen naar oldfield
• Janoz's oplossing gebruiken

Voila Nog steeds duizenden malen efficiënter dan met (al dan niet "hex")strings liggen klooien.

_{Wel even rekening houden met het feit dat je dus (feitelijk) het type van het field hebt gewijzigd en als je er ergens anders aan refereert de mist in zult gaan}

[ Voor 58% gewijzigd door RobIII op 06-06-2008 17:35 ]

[pierre-oord]

Toch: is er een oplossing voor de manier die ik zoek? Ik ga zeker bovenstaande proberen en uitzoeken. Echter zijn er al situaties waarin ik tabellen wil joinen met andere tabellen die al een dergelijke opmaak hebben, waardoor ik vast zit aan deze setup. Tenzij het geheel echt langzaam word, wil ik dat niet direct overboord gooien. Het gaat nu om maar zo'n 1500 rijen.

Is er een mogelijkheid om iets te selecteren op de manier die ik zoek? Ik waardeer overigens de andere oplossingen ook; ik heb me niet enorm verdiept in alle IP adres kennis, maar zal dit nog wel doen.

[Janoz]

Ook dan kun je gewoon veel beter INET_ATON gebruiken. Ik snap niet waarom je constant met string-oplossingen aan wilt komen terwijl je een numeriek probleem hebt.

[pierre-oord]

Janoz, ik begrijp je punt wel.

Ik zie echter nog geen oplossing voor het gestelde probleem. Stel, ik heb allemaal numerieke waarden in de database staan. Hoe kan ik dan bepalen welke verschillende /24's er allemaal in voorkomen?

[Verwijderd]

Een /24 heeft een netmask van 255.255.255.0 ofwel FFFFFF00 ofwel 4294967040 ofwel INET_ATON('255.255.255.0').
Een ip adres hoort in de /24 van ip & netmask, dus tweakers.net zit in INET_NTOA(INET_ATON('213.239.154.35') & INET_ATON('255.255.255.0')) = 213.239.154.0/24

Staat allemaal in de 1e post van Janoz.

[ Voor 7% gewijzigd door Verwijderd op 07-06-2008 14:32 ]

[pierre-oord]

SELECT `ip` , INET_NTOA( `ip` )
FROM `iptest`

ip INET_NTOA( `ip` )
16909061 1.2.3.5
16909060 1.2.3.4
16909116 1.2.3.60
67438087 4.5.6.7
67438164 4.5.6.84
84281097 5.6.7.9

Hoewel het plaatje me nog niet heel helder is wat IP adressen-berekenen, ben ik erg blij dat het nu wel lukt met:

SELECT DISTINCT INET_NTOA(`ip` & INET_ATON('255.255.255.0')) as `ranges` FROM `iptest`

Hardstikke bedankt allemaal, ik kan weer verder!

[ACM]

Omdat je /24's wilt kan het wel met string-operaties in MySQL, door de substring_index-functie te gebruiken:

SQL:

mysql> select substring_index('123.45.67.89', '.', 3);
+-----------------------------------------+
| substring_index('123.45.67.89', '.', 3) |
+-----------------------------------------+
| 123.45.67                               |
+-----------------------------------------+

Zodra je /23's of /25's wilt weten gaat je dat uiteraard niet meer lukken.