Querying by Subnet in MySQL

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

Finding all rows where an IP address falls within a subnet requires comparing numeric IP values against a range. MySQL’s standard approach uses bitwise arithmetic with INET_ATON(). VillageSQL’s INET type provides network functions that make the same query more readable and work for both IPv4 and IPv6.

The Standard Approach: Bitwise Range Checks

For IPv4, INET_ATON() converts an address to an unsigned 32-bit integer. A subnet membership check is a bitwise AND: if (ip & netmask) == network_address, the IP is in the subnet.

CREATE TABLE access_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INT UNSIGNED,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_log (ip_address) VALUES
    (INET_ATON('192.168.1.10')),
    (INET_ATON('192.168.1.50')),
    (INET_ATON('192.168.2.1')),
    (INET_ATON('10.0.0.5'));

-- Find all IPs in 192.168.1.0/24
SELECT INET_NTOA(ip_address) FROM access_log
WHERE ip_address BETWEEN INET_ATON('192.168.1.0')
                     AND INET_ATON('192.168.1.255');

-- Or using bitwise AND with the netmask
SELECT INET_NTOA(ip_address) FROM access_log
WHERE (ip_address & 0xFFFFFF00) = INET_ATON('192.168.1.0');

Both work for IPv4. The BETWEEN approach is readable. The bitwise form is more composable when subnet parameters are variables. Neither approach works for IPv6 — INET_ATON() only handles IPv4.

With VillageSQL: Network Functions on INET Columns

VillageSQL’s vsql_network_address extension provides containment predicates that answer “is this address inside that subnet” directly — inet_contained_by(), inet_contains(), and inet_overlaps() — plus inet_network(), inet_set_masklen(), and inet_compare() for building the check by hand. All of them work on both IPv4 and IPv6 INET columns.

INSTALL EXTENSION vsql_network_address;

CREATE TABLE access_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INET,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_log (ip_address) VALUES
    (inet_from_string('192.168.1.10')),
    (inet_from_string('192.168.1.50')),
    (inet_from_string('192.168.2.1')),
    (inet_from_string('10.0.0.5')),
    (inet_from_string('192.168.1.200'));

Subnet containment query

To check whether a stored IP belongs to a subnet, mask the address to the subnet’s prefix length and compare the result to the target network:

-- All IPs in 192.168.1.0/24
SELECT inet_to_string(ip_address) FROM access_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip_address, 24)))
    = '192.168.1.0/24';

For a parameterized version where the subnet comes from a variable:

SET @subnet     = '192.168.1.0/24';
SET @prefix_len = inet_masklen(inet_from_string(@subnet));
SET @network    = cidr_to_string(cidr_from_string(@subnet));

SELECT inet_to_string(ip_address) FROM access_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip_address, @prefix_len)))
    = @network;

Containment predicates

The extension ships PostgreSQL’s five containment predicates as functions. They take two INET values and return 1, 0, or NULL:

Function	PostgreSQL operator	True when
`inet_contained_by(a, b)`	`a << b`	`a` is inside `b`, and `b` is the wider network
`inet_contained_by_or_equals(a, b)`	`a <<= b`	`a` is inside `b`, or the two networks are equal
`inet_contains(a, b)`	`a >> b`	`a` is the wider network and `b` is inside it
`inet_contains_or_equals(a, b)`	`a >>= b`	`b` is inside `a`, or the two networks are equal
`inet_overlaps(a, b)`	`a && b`	either network contains the other, or they are equal

The subnet query above becomes a single call:

-- All IPs in 192.168.1.0/24
SELECT inet_to_string(ip_address) FROM access_log
WHERE inet_contained_by(ip_address, inet_from_string('192.168.1.0/24'));

Only the network part of each value is compared, through the shorter of the two prefix lengths — so 192.168.1.5/24 and 192.168.1.9/24 are the same network, and the strict forms return 0 for them while the _or_equals forms return 1. Two addresses of different families never match: comparing an IPv4 value with an IPv6 one returns 0 rather than raising an error, matching PostgreSQL. Both arguments must be INET. A CIDR value is not coerced:

ERROR 3219 (HY000): Cannot initialize function 'inet_contains': argument 1 type mismatch (expected vsql_network_address.INET, got vsql_network_address.CIDR)

Pass inet_from_string() on the subnet instead, as the example above does. These functions were added in vsql_network_address 0.0.5.

Range-based containment with inet_compare

An alternative that avoids recomputing the network on every row — useful when you have the broadcast address available:

-- IPs in 192.168.1.0/24: from .0 to .255
SELECT inet_to_string(ip_address) FROM access_log
WHERE inet_compare(ip_address, inet_from_string('192.168.1.0'))   >= 0
  AND inet_compare(ip_address, inet_from_string('192.168.1.255')) <= 0;

IPv6 subnet queries (same pattern)

The same inet_network() approach works for IPv6 without any changes:

CREATE TABLE ipv6_log (id INT, ip INET);
INSERT INTO ipv6_log VALUES
    (1, inet_from_string('2001:db8::1')),
    (2, inet_from_string('2001:db8::100')),
    (3, inet_from_string('2001:db9::1'));

-- Find all IPs in 2001:db8::/32
SET @v6_prefix = 32;
SET @v6_network = '2001:0db8:0000:0000:0000:0000:0000:0000/32';

SELECT inet_to_string(ip) FROM ipv6_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip, @v6_prefix)))
    = @v6_network;

Networks table pattern

A common use case: a networks table with authorized CIDRs, and checking whether an incoming IP is in any of them.

CREATE TABLE networks (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    network CIDR,
    label   VARCHAR(100)
);

INSERT INTO networks (network, label) VALUES
    (cidr_from_string('192.168.1.0/24'), 'office-lan'),
    (cidr_from_string('10.0.0.0/8'),     'vpn-range'),
    (cidr_from_string('172.16.0.0/12'),  'docker-range');

-- Is an incoming IP in any authorized network?
-- inet_masklen() takes INET, not CIDR — convert via string to get the prefix length
SELECT label FROM networks
WHERE cidr_to_string(inet_network(inet_set_masklen(
    inet_from_string('192.168.1.42'),
    inet_masklen(inet_from_string(cidr_to_string(network)))
))) = cidr_to_string(network);

Standard vs VillageSQL

Approach	IPv6	Readable	No manual calculation
`BETWEEN INET_ATON()`	No	Moderate	Requires computing broadcast manually
Bitwise AND with `INET_ATON()`	No	Low	Requires computing netmask manually
`inet_network()` + `inet_set_masklen()`	Yes	High	Functions handle the math
`inet_contained_by()`	Yes	High	One call, no manual calculation

Frequently Asked Questions

Does VillageSQL have PostgreSQL’s `<<` containment operator?

The behaviour is there, as functions rather than operators: inet_contained_by() for <<, inet_contains() for >>, the _or_equals variants for <<= and >>=, and inet_overlaps() for &&. See Containment predicates.

Does the subnet query use the index?

The cidr_to_string(inet_network(inet_set_masklen(...))) pattern does not use an index scan — it recomputes the network address for every row. For large tables, the inet_compare range approach is more index-friendly since it expresses the query as a range on the ip_address column.

Can I store subnets and query containment at the same time?

Yes. Store both the host address and the subnet in INET columns and compare them with inet_contained_by(host_ip, subnet). If the subnet is in a CIDR column, convert it first — the containment predicates take INET on both sides and do not coerce CIDR.

Troubleshooting

Problem	Solution
`FUNCTION <database>.inet_network does not exist`	Run `INSTALL EXTENSION vsql_network_address`
Subnet query returns no rows	Verify `@network` matches the expanded CIDR string — IPv6 CIDRs use expanded notation
Range query includes addresses outside the subnet	Check the broadcast address; for /24 the range is `.0` to `.255`
Mixed IPv4/IPv6 column returns wrong results	IPv4 and IPv6 addresses don’t overlap — the query returns correct results per-family

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

The Standard Approach: Bitwise Range Checks

With VillageSQL: Network Functions on INET Columns

Subnet containment query

Containment predicates

Range-based containment with inet_compare

IPv6 subnet queries (same pattern)

Networks table pattern

Standard vs VillageSQL

Frequently Asked Questions

Does VillageSQL have PostgreSQL’s `<<` containment operator?

Does the subnet query use the index?

Can I store subnets and query containment at the same time?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​The Standard Approach: Bitwise Range Checks

​With VillageSQL: Network Functions on INET Columns

​Subnet containment query

​Containment predicates

​Range-based containment with inet_compare

​IPv6 subnet queries (same pattern)

​Networks table pattern

​Standard vs VillageSQL

​Frequently Asked Questions

​Does VillageSQL have PostgreSQL’s << containment operator?

​Does the subnet query use the index?

​Can I store subnets and query containment at the same time?

​Troubleshooting

​See also

The Standard Approach: Bitwise Range Checks

With VillageSQL: Network Functions on INET Columns

Subnet containment query

Containment predicates

Range-based containment with inet_compare

IPv6 subnet queries (same pattern)

Networks table pattern

Standard vs VillageSQL

Frequently Asked Questions

Does VillageSQL have PostgreSQL’s `<<` containment operator?

Does the subnet query use the index?

Can I store subnets and query containment at the same time?

Troubleshooting

See also