Edited at

MySQLの1つのカラムでIPv4、IPv6両方のIPアドレスを格納する方法を考える

More than 1 year has passed since last update.

ログイン時のIPをログとしてテーブルに保存したかったので考えた

具体的な方法だけ知りたい人は一番最後だけ見てください


MySQLにおけるIPアドレスの格納方法


  • 文字列として格納する

  • 数値、バイナリとして格納する


文字列

本来のIPアドレスの形式ではない文字列に変換して格納してしまうが、画面上などに表示を行う場合にそのまま使用できる


数値、バイナリ

IPアドレスを本来のバイナリの形で格納できるが、文字列として使用したい場合に変換が必要になる

ここでは文字列ではなく、数値、バイナリの形で保存する方法を考える


MySQL的なIPv4とIPv6の違い


IPv4


  • 32bit


  • UNSIGNED INTまたはBINARY(4)を使って格納する


  • INET_NTOA(),INET_ATON()関数を使って文字列形式との変換ができる


  • IS_IPV4()関数を使ってIPv4形式であるか判定できる


IPv6


  • 128bit


  • BINARY(16)を使って格納する


  • INET6_NTOA(),INET6_ATON()関数を使って文字列形式との変換ができる


  • IS_IPV6()関数を使ってIPv6形式であるか判定できる


どちらも格納できる型を考える

IPv6を格納する必要がある段階で型は128bit以上のサイズが必要になる

よって、選択肢としてはBINARY(16)になるがIPv4(32bit)格納時の効率が悪いので、VARBINARY(16)を用いるのが適切


実装方法


INSERT,UPDATE

IS_IPV6()関数でIPv6であるかを判断して呼び出す関数を分ける


SELECT

IPv6は128bitなのでVARBINARY(16)を最大まで使う

しかしIPv4は32bitなので最大まで使わない

この違いを利用してLENGTH()関数を利用することにより、どちらの形式であるか判断でき呼び出す関数を分ける


実装例

hogeテーブルにip_addressというカラムがあるという想定です


hogeテーブル

カラム名

ip_address
VARBINARY(16)


INSERT(,UPDATE)

INSERT INTO hoge(ip_address) VALUES (IF(IS_IPV6(:ip_address), INET6_ATON(:ip_address), INET_ATON(:ip_address)));


SELECT

SELECT IF(LENGTH(ip_address) = 16 ,INET6_NTOA(ip_address), INET_NTOA(ip_address)) AS ip_address FROM hoge;