MySQLでIPv4/IPv6アドレスをVARBINARY(16)型として効率的に保存・検索する具体的なSQLとPHP実装
MySQLにおいてIPv4およびIPv6アドレスを効率的に格納・検索するには、VARBINARY(16) 型を採用し、MySQLの標準関数である INET6_ATON()（文字列からバイナリへ変換）と INET6_NTOA()（バイナリから文字列へ変換）を組み合わせるのが最適です。
文字列型（VARCHAR(45)）と比較してデータサイズを大幅に削減（IPv4は4バイト、IPv6は16バイト）でき、インデックス効率と検索速度が向上します。
1. テーブル設計（DDL）
SQL
CREATE TABLE access_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NULL,
ip_address VARBINARY(16) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_ip_address (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. 基本的なSQLクエリの考え方
登録 (INSERT):
SQL
-- 文字列IPをバイナリに変換して保存（IPv4/IPv6自動判別）
INSERT INTO access_logs (ip_address) VALUES (INET6_ATON('192.0.2.1'));
INSERT INTO access_logs (ip_address) VALUES (INET6_ATON('2001:db8::1'));
取得 (SELECT):
SQL
-- バイナリから人間が読める文字列形式に復元して取得
SELECT id, INET6_NTOA(ip_address) AS ip_string, created_at
FROM access_logs;
検索 (WHERE):
SQL
-- インデックスを効かせるため、カラム側ではなく検索値側に INET6_ATON を適用する
SELECT * FROM access_logs
WHERE ip_address = INET6_ATON('192.0.2.1');
3. PHPによる実装例（PDOを使用）
バイナリ変換を「MySQL側（SQL関数）」で行う方法と、「PHP側（inet_pton）」で行う方法の2通りがありますが、SQL側で INET6_ATON() / INET6_NTOA() を使う方がPHPのバイナリバインドの扱いが容易で安全です。
登録処理（INSERT）
PHP
function logAccess(PDO $pdo, string $clientIp, ?int $userId = null): bool
{
// 1. IPアドレス形式の妥当性検証 (IPv4 / IPv6 両対応)
if (!filter_var($clientIp, FILTER_VALIDATE_IP)) {
return false;
}
// 2. INET6_ATON() を通して保存
$sql = "INSERT INTO access_logs (user_id, ip_address, created_at)
VALUES (:user_id, INET6_ATON(:ip), NOW())";
$stmt = $pdo->prepare($sql);
return $stmt->execute([
':user_id' => $userId,
':ip' => $clientIp,
]);
}
単一検索処理（SELECT by IP）
PHP
function getLogsByIp(PDO $pdo, string $searchIp): array
{
if (!filter_var($searchIp, FILTER_VALIDATE_IP)) {
return [];
}
// カラム側に式を書かず、条件値に INET6_ATON() を当てることでインデックスを使用
$sql = "SELECT id, user_id, INET6_NTOA(ip_address) AS ip_address, created_at
FROM access_logs
WHERE ip_address = INET6_ATON(:ip)";
$stmt = $pdo->prepare($sql);
$stmt->execute([':ip' => $searchIp]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
4. CIDR範囲検索（サブネット検索）の応用
INET6_ATON() で生成されるバイナリデータは、ビット単位の比較演算が可能です。特定のサブネット（例: 192.0.2.0/24）に含まれるレコードを抽出する場合は、以下のようにビットマスク（&）を用いて判定できます。
SQL
-- IPv4: /24 (255.255.255.0) の範囲検索例
SELECT id, INET6_NTOA(ip_address) AS ip
FROM access_logs
WHERE ip_address >= INET6_ATON('192.0.2.0')
AND ip_address <= INET6_ATON('192.0.2.255');
※ 範囲検索（BETWEEN や >= / <=）で記述すれば、B-treeインデックス（INDEX idx_ip_address）をフル活用して高速にスキャンされます。
