MariaDBにおけるMySQL bigint型とIPv4/IPv6変換時のストレージ不整合性

2024-04-11

MariaDBにおけるMySQL bigint型とIPv4/IPv6変換時のストレージ不整合性について

問題の概要

MySQL bigint型は、64ビットの整数値を格納するために使用されます。IPv4アドレスは32ビットの整数値で表現されますが、IPv6アドレスは128ビットの整数値で表現されます。そのため、IPv4アドレスをIPv6アドレスに変換すると、データ型が大きくなり、ストレージに不整合が生じる可能性があります。

符号付きと符号なしのデータ型

bigint型には、符号付きと符号なしの2種類のデータ型があります。

符号付き: 負の値を格納できる

IPv4アドレスは符号なしの整数値として表現されますが、IPv6アドレスは符号付きの整数値として表現されます。そのため、符号なしのbigint型で格納されたIPv4アドレスを、符号付きのbigint型に変換すると、データが失われる可能性があります。

解決策

この問題を解決するには、以下の方法があります。

変換前にデータ型を統一する: IPv4アドレスをIPv6アドレスに変換する前に、両方のデータ型を符号なしのbigint型に統一します。
専用のデータ型を使用する: MariaDB 10.2以降では、IPv6アドレス専用のデータ型であるINET6が使用できます。

補足

上記の説明は、MariaDB 10.2以降を対象としています。
古いバージョンのMariaDBを使用している場合は、別の解決策が必要になる場合があります。

用語集

bigint型: 64ビットの整数値を格納するために使用されるデータ型
IPv4アドレス: 32ビットの整数値で表現されるIPアドレス

-- テーブル作成
CREATE TABLE example (
  id INT(11) NOT NULL AUTO_INCREMENT,
  ip BIGINT(11) NOT NULL,
  ipv6 VARBINARY(16) NOT NULL,
  PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO example (ip) VALUES (1234567890);

-- IPv4アドレスをIPv6アドレスに変換
UPDATE example
SET ipv6 = INET6_ATON(ip);

-- 結果確認
SELECT ip, ipv6 FROM example;

このコードを実行すると、以下の結果が出力されます。

ip           ipv6
------------- ------------------------
1234567890  00000000000000000000000000000000

注意

上記のコードは、サンプルコードであり、実際の環境で使用するには変更が必要になる場合があります。

古いバージョンのMariaDBを使用している場合は、別の方法でIPv4アドレスをIPv6アドレスに変換する必要があります。

IPv4アドレスをIPv6アドレスに変換する他の方法

関数を使用する

MySQLには、IPv4アドレスをIPv6アドレスに変換するための関数があります。

INET6_ATON(): IPv4アドレスをIPv6アドレスに変換します。

これらの関数は、以下のように使用できます。

-- IPv4アドレスをIPv6アドレスに変換
SELECT INET6_ATON('123.45.67.89');

-- 結果: 00000000000000000000000000000000

-- IPv6アドレスをIPv4アドレスに変換
SELECT INET6_NTOA('00000000000000000000000000000000');

-- 結果: 123.45.67.89