ipcalc() in MySQL: Network, Broadcast and Prefix From a Netmask
Fedora and RHEL ship a small ipcalc command that takes an address and a netmask and tells you the network, the broadcast address and the prefix length. In 2010 I had a table full of addresses and masks. I wanted the same answers inside a query, without pulling every row out into a script. MySQL already has INET_ATON() and INET_NTOA(). With those two plus a few bitwise operators, the whole of ipcalc fits in three short stored functions.
This post is the cleaned-up version of that 2010 snippet: ipcalc(), mask2prefix() and prefix2mask(), tested on MariaDB 11.8, plus a shorter mask2prefix() built on BIT_COUNT().
The idea: an IPv4 address is a 32-bit number
INET_ATON('192.168.0.1') returns 3232235521, which is just the four octets packed into one unsigned 32-bit integer. INET_NTOA() goes the other way. Once both the address and the mask are numbers, the rest is the same arithmetic every router does:
- network = address AND mask
- broadcast = network OR (NOT mask)
- prefix = the number of 1 bits in the mask
The only problem is that MySQL does bitwise arithmetic on 64-bit unsigned integers. ~mask flips all 64 bits, so the high 32 bits become ones and the result is no longer a valid IPv4 address. Every ~ and every left shift therefore gets an & 0xFFFFFFFF to remove the top 32 bits.
The functions
-- ipcalc for MySQL/MariaDB: network, broadcast and prefix of an IPv4 address
DELIMITER //
-- mask2prefix() -- convert a dotted netmask to a prefix length
CREATE FUNCTION mask2prefix(mask VARCHAR(15))
RETURNS TINYINT UNSIGNED
DETERMINISTIC NO SQL
BEGIN
DECLARE m BIGINT UNSIGNED;
DECLARE c TINYINT UNSIGNED DEFAULT 0;
SET m = INET_ATON(mask);
IF m IS NULL THEN
RETURN NULL;
END IF;
WHILE m > 0 DO
SET m = (m << 1) & 0xFFFFFFFF;
SET c = c + 1;
END WHILE;
RETURN c;
END//
-- prefix2mask() -- convert a prefix length (0-32) to a dotted netmask
CREATE FUNCTION prefix2mask(prefix TINYINT UNSIGNED)
RETURNS VARCHAR(15)
DETERMINISTIC NO SQL
BEGIN
IF prefix IS NULL OR prefix > 32 THEN
RETURN NULL;
END IF;
RETURN INET_NTOA((0xFFFFFFFF << (32 - prefix)) & 0xFFFFFFFF);
END//
-- ipcalc() -- opt: n = network, b = broadcast, p = prefix
CREATE FUNCTION ipcalc(opt CHAR(1), ip VARCHAR(15), mask VARCHAR(15))
RETURNS VARCHAR(15)
DETERMINISTIC NO SQL
BEGIN
DECLARE i BIGINT UNSIGNED;
DECLARE m BIGINT UNSIGNED;
SET i = INET_ATON(ip);
SET m = INET_ATON(mask);
CASE opt
WHEN 'p' THEN RETURN mask2prefix(mask);
WHEN 'n' THEN RETURN INET_NTOA(i & m);
WHEN 'b' THEN RETURN INET_NTOA((i & m) | (~m & 0xFFFFFFFF));
ELSE RETURN NULL;
END CASE;
END//
DELIMITER ;Paste that into the mysql or mariadb command-line client while connected to the database that should own the functions. Stored functions belong to a database, so call them as mydb.ipcalc(...) from anywhere else.
How it works
DELIMITER // is not SQL. It is a command for the command-line client. It tells the client to stop treating ; as the end of a statement, so the semicolons inside BEGIN ... END reach the server intact. GUI tools and most client libraries send one statement at a time and do not need it (phpMyAdmin has its own delimiter box).
mask2prefix() keeps the 2010 approach: shift the mask left one bit at a time, cut it back to 32 bits, and count how many shifts it takes to reach zero. For 255.255.255.0 that is 24 shifts. prefix2mask() does the reverse: start with 32 one-bits, shift left by the number of host bits, and mask off the overflow. A prefix of 20 gives 0xFFFFF000, which INET_NTOA() prints as 255.255.240.0.
ipcalc() converts both arguments once and then picks a formula. The original broadcast line had no brackets and relied on operator precedence (& binds tighter than |). It gave the right answer, but only because of that rule. The explicit brackets cost nothing, and the next reader does not need to look up the rule in the manual.
Both INET_ATON() and INET_NTOA() return NULL for input they cannot parse, and NULL propagates through the bitwise operators. That gives you error handling without extra code: a bad address or an unknown option comes back as NULL. You do not get a wrong address that looks correct.
Usage
SELECT ipcalc('n','192.168.0.1','255.255.255.0') AS network,
ipcalc('b','192.168.0.1','255.255.255.0') AS broadcast,
ipcalc('p','192.168.0.1','255.255.255.0') AS prefix;+-------------+---------------+--------+
| network | broadcast | prefix |
+-------------+---------------+--------+
| 192.168.0.0 | 192.168.0.255 | 24 |
+-------------+---------------+--------+The prefix is left-aligned because ipcalc() returns a string. Call mask2prefix() directly when you want a number. If you think in CIDR, let prefix2mask() build the mask:
SELECT ipcalc('n','10.20.30.40',prefix2mask(20)) AS network,
ipcalc('b','10.20.30.40',prefix2mask(20)) AS broadcast;+------------+--------------+
| network | broadcast |
+------------+--------------+
| 10.20.16.0 | 10.20.31.255 |
+------------+--------------+The main use is running it over a table. Here is a small one with three hosts:
CREATE TABLE hosts (
ip VARCHAR(15) NOT NULL,
mask VARCHAR(15) NOT NULL
);
INSERT INTO hosts VALUES
('192.168.0.1', '255.255.255.0'),
('192.168.2.1', '255.255.255.0'),
('172.16.5.9', '255.255.0.0');
SELECT ip, mask, ipcalc('n', ip, mask) AS network,
mask2prefix(mask) AS prefix
FROM hosts;+-------------+---------------+-------------+--------+
| ip | mask | network | prefix |
+-------------+---------------+-------------+--------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0 | 24 |
| 192.168.2.1 | 255.255.255.0 | 192.168.2.0 | 24 |
| 172.16.5.9 | 255.255.0.0 | 172.16.0.0 | 16 |
+-------------+---------------+-------------+--------+And in CIDR notation, with the broadcast address alongside:
SELECT ip, CONCAT(ipcalc('n', ip, mask), '/', mask2prefix(mask)) AS cidr,
ipcalc('b', ip, mask) AS broadcast
FROM hosts;+-------------+----------------+----------------+
| ip | cidr | broadcast |
+-------------+----------------+----------------+
| 192.168.0.1 | 192.168.0.0/24 | 192.168.0.255 |
| 192.168.2.1 | 192.168.2.0/24 | 192.168.2.255 |
| 172.16.5.9 | 172.16.0.0/16 | 172.16.255.255 |
+-------------+----------------+----------------+Note: the 2010 post inserted 192.168.2.1 with 255.255.255.0 but showed it in the output as 255.255.0.0 with network 192.168.0.0. That output could not have come from that insert. With the mask that was actually inserted the network is 192.168.2.0, as above.
A cleaner mask2prefix() with BIT_COUNT()
The loop works, but MySQL has had BIT_COUNT() for a long time. It counts the 1 bits in a number. For a valid netmask that count is the prefix length, so the loop becomes one call. I also added the check the old version never had: a netmask must be ones followed by zeros. Invert it and you get the host bits, which must look like 0...011...1. Adding one to such a number gives a power of two, so h & (h + 1) is zero only for a contiguous mask.
DROP FUNCTION IF EXISTS mask2prefix;
DELIMITER //
-- mask2prefix() -- netmask to prefix length; NULL if the mask is not valid
CREATE FUNCTION mask2prefix(mask VARCHAR(15))
RETURNS TINYINT UNSIGNED
DETERMINISTIC NO SQL
BEGIN
DECLARE m BIGINT UNSIGNED DEFAULT INET_ATON(mask);
DECLARE h BIGINT UNSIGNED;
IF m IS NULL OR m > 0xFFFFFFFF THEN
RETURN NULL;
END IF;
SET h = ~m & 0xFFFFFFFF; -- host bits, e.g. 0.0.0.255
IF (h & (h + 1)) <> 0 THEN -- host bits must be 0...01...1
RETURN NULL;
END IF;
RETURN BIT_COUNT(m);
END//
DELIMITER ;
SELECT mask2prefix('255.255.255.0') AS a, mask2prefix('255.255.240.0') AS b,
mask2prefix('0.0.0.0') AS c, mask2prefix('255.255.255.255') AS d,
mask2prefix('255.0.255.0') AS e, mask2prefix('bogus') AS f;+------+------+------+------+------+------+
| a | b | c | d | e | f |
+------+------+------+------+------+------+
| 24 | 20 | 0 | 32 | NULL | NULL |
+------+------+------+------+------+------+The loop version returns 24 for the invalid mask 255.0.255.0 (the position of the last 1 bit). The original 2010 code returned 0 for 'bogus', because WHILE NULL > 0 is simply false. Neither is an answer you want in a report. To make sure the two directions agree, I converted every prefix from 0 to 32 to a mask and back, using MariaDB's built-in seq_0_to_32 sequence table:
SELECT SUM(mask2prefix(prefix2mask(seq)) = seq) AS ok FROM seq_0_to_32;+------+
| ok |
+------+
| 33 |
+------+All 33 match. The sequence engine is MariaDB-only. On MySQL use a recursive CTE or a small numbers table for the same test.
Pitfalls
BIGINT(10) and other display widths
The original declared RETURNS bigint(10) and INT(3). The number in brackets never limited the range of an integer. It was only a display width hint. MySQL 8.0.17 deprecated display widths for integer types, and MySQL 8 raises a deprecation warning when you use them. MariaDB 11.8 still accepts the 2010 code without a warning, but there is no reason to keep it. TINYINT UNSIGNED holds 0 to 32 easily, and a plain BIGINT UNSIGNED is the right type for the intermediate values.
DETERMINISTIC and binary logging
DETERMINISTIC promises the server that the same arguments always give the same result. That is true here, and NO SQL adds that the function reads no tables. These characteristics matter when binary logging is on (replication, point-in-time recovery). I tested it on a temporary MariaDB 11.8 instance started with --log-bin. As root, a function with neither keyword is refused:
ERROR 1418 (HY000) at line 1: This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled (you *might*
want to use the less safe log_bin_trust_function_creators variable)As an ordinary user with ALL on the database but no SUPER, even the correctly declared function is refused:
ERROR 1419 (HY000) at line 2: You do not have the SUPER privilege and binary
logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)An administrator can run SET GLOBAL log_bin_trust_function_creators = 1; (and put it in the server config so it stays after a restart). After that, the same user could create the function. On shared hosting you will usually have to ask for that, or create the functions as an administrative user. Do not mark a function DETERMINISTIC just to avoid the error if it is not. Replicas can then end up with different data.
INET_ATON() accepts some unusual input
INET_ATON('10.1') returns 167772161, the same as 10.0.0.1, because the short forms of the old C inet_aton() are accepted. Leading spaces and octets above 255 give NULL. If the column is filled by humans, validate the data when it is inserted. Do not trust the functions to reject everything.
IPv4 only
All of this is IPv4. For IPv6 you need INET6_ATON() and 128-bit values, which bitwise operators on BIGINT cannot handle.
History of this snippet
The 2010 post was written in Malay. It created the functions with display widths and a utf8 return charset, and it returned 0 instead of NULL for an invalid mask. Its sample output also did not match its own INSERT. The arithmetic was right. This version keeps the same three functions and the same call style. It adds NO SQL, input checks and the BIT_COUNT() variant, and I ran every result above on MariaDB 11.8.
Related functions
- Match an IPv4 address against a CIDR subnet in MySQL
- Storing IPv4 and IPv6 addresses with INET6_ATON()
- is_num(): check for a number in Bash
Reference: MySQL miscellaneous functions (INET_ATON(), INET_NTOA()).