Regex on IP numeric value in MySQL?

Question

Assume a table titled 'transactions'

Typical query: select * from transactions where ip=INET_ATON('127.0.0.1');

I want the ability to do a regex search for all ip's with a particular first octet (i.e. 127) but I can't quite figure out the syntax.

Thanks!

Answer 1

Like this?

select * from transactions where ip REGEXP "^[1-2][1-9][1-9]"

Answer 2

SELECT * FROM transactions WHERE ip LIKE '127.%';

Answer 3

You are comparing ip to the result of INET_ATON, which implies that ip is a 32-bit integer. Your operation should be simply...

SELECT * FROM transactions WHERE (longip & 0xFF000000) = (0x7F000000)

This will be far faster than applying a regex to a dotted IP in a string, and faster than the LIKE '127.%' solution.

Answer 4

‘transactions.ip’ is already an integer, which is why the questioner is using INT_ATON. It's no good trying to match it directly against a string like/regexp, you would have to convert it back to an IPv4 address first using the reverse function INT_NTOA:

SELECT * FROM transactions WHERE INT_NTOA(ip) LIKE '127.%';

This requires a complete table scan (defeating indexes) to convert and compare.

I want the ability to do a regex search for all ip's with a particular first octet (i.e. 127)

However, in the case where you want to compare against the front part of an IP address, you can take advantage of the fact that the first octet is stored in the most significant bits of the number, and say:

SELECT * FROM transactions WHERE ip BETWEEN INT_NTOA('127.0.0.0') AND INT_NTOA('127.255.255.255');

Which can be done using an index on ‘ip’ so could be considerably more efficient.