MySQL/MariaDBでIPアドレスをいじくりまわす話

MySQL/MariaDBでIPアドレスをよしなに扱いたい。

ネットワーク機器ではIPルートはサブネット表記されるのが普通。
その情報から、特定IPアドレスがどのルートテーブルに乗るのかを調べるのは結構めんどい。
Pythonとか使っていればさっくり変換できるが、MySQL上だけでできるとそれはそれで便利。

ということでいろいろなIPアドレス処理をまとめてみた。

変数セット

set @addr = '192.168.3.66';
set @cidr = '26';
set @mask = '255.255.255.192';

IPアドレス計算(サブネットマスク)

select inet_ntoa(inet_aton(@addr) & inet_aton(@mask)) nw_address;
select inet_ntoa(inet_aton(@addr) | ~inet_aton(@mask)<<32>>32 ) bc_address;
select inet_ntoa(inet_aton(@addr) | pow(2,32) - inet_aton(@mask) -1) bc_address;

Mariadbは数値計算が64bitのため、<<32>>32で上位32bitをzeroにしている。
もっと文字数少なく計算するやり方があればそちらにしたい。
また、ビット演算になれていな人向けに数字で計算をする方式も記載。

IPアドレス計算(CIDR)

select inet_ntoa(inet_aton(@addr)>>(32-@cidr)<<(32-@cidr)) nw_address;
select inet_ntoa(inet_aton(@addr) & (pow(2,32) - pow(2,32-@cidr)) ) nw_address;
select inet_ntoa(inet_aton(@addr) | (pow(2,32-@cidr)-1) ) bc_address;

サブネットマスク ⇔ CIDR

select 32 - log2(power(2,32) - inet_aton(@mask)) subnet_mask_to_cidr;
select inet_ntoa(pow(2,32) - pow(2,32-@cidr)) cidr_to_subnet_mask;

inet_aton を使用すればIPアドレスを10進数で処理ができる。
仮に、nw_address,subnet_maskが記載されたテーブルがあった場合、以下のようにして特定のIPアドレス「@addr」をnw_addressとbc_addressのbetweenで抽出すればどのIPアドレスレンジに含まれているかがわかる。

ipaddress range search

select address_range_name,nw_address,subnet_mask 
from ip_address_table t1
where inet_aton(@addr) between inet_aton(nw_address) and ( inet_aton(nw_address) | ~inet_aton(subnet_mask)<<32>>32 )