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

ログイン時の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;