第187回　MySQLにIPアドレスを保存したい

サービスを運用する上で、ユーザー調査やログイン履歴のためにIPアドレスをログに出力したり、データベースに保管したいと思うことが多々あります。

MySQLにIPアドレスの履歴を保存するときは、特に何も考えなければ、出力されるIPアドレスをそのまま文字列としてvarchar型でデータベースに保存してしまえば問題ありません。しかし文字列で保存すると、IPアドレスでない形のデータも保存できる状態になってしまいます。

今回は、IPアドレスをMySQLに保存したいときに知っておくと良いかもしれないことを紹介します。なお、利用しているMySQLのバージョンは8.0.31になります。

文字列としてIPアドレスを保存する

文字列として保存する場合はvarchar型として保存することが多いでしょう。

mysql> CREATE TABLE ip_varchar (ip varchar(255));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO ip_varchar VALUES('127.0.0.1'),('0.0.0.0'),('255.255.255.255');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ip_varchar;
+-----------------+
| ip              |
+-----------------+
| 127.0.0.1       |
| 0.0.0.0         |
| 255.255.255.255 |
+-----------------+
3 rows in set (0.00 sec)

このような形式で作成したデータは実際は文字列であり、たとえ文字数を15文字以内に制限をかけたとしても、IPアドレス形式以外のデータを挿入することができます。

mysql> INSERT INTO ip_varchar VALUES ('256.256.256.256'),('fukamachi');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ip_varchar;
+-----------------+
| ip              |
+-----------------+
| 127.0.0.1       |
| 0.0.0.0         |
| 255.255.255.255 |
| 256.256.256.256 |
| fukamachi       |
+-----------------+
5 rows in set (0.00 sec)

IPアドレスのみを登録できるように制限をかけるにはどうすればよいでしょうか？

実はMySQLにはバージョン5.6.3以降、IS_IPV4()という関数が用意されています。これは引数で与えられた文字列が有効なIPv4アドレスかどうかを判定するものです。文字列がIPアドレスとして有効な場合は1を返し、無効な値の場合は0を返します。

mysql> SELECT ip, is_ipv4(ip) FROM ip_varchar;
+-----------------+-------------+
| ip              | is_ipv4(ip) |
+-----------------+-------------+
| 127.0.0.1       |           1 |
| 0.0.0.0         |           1 |
| 255.255.255.255 |           1 |
| 256.256.256.256 |           0 |
| fukamachi       |           0 |
+-----------------+-------------+
5 rows in set (0.01 sec)

これを利用することで、対象のデータがIPアドレスかどうかを確認することができます。さらに、MySQL 8.0ではCHECK制約が追加されました。そこで、関数IS_IPV4()とチェック制約を組み合わせることで、IPアドレスのみを保存できるようになります。

mysql> CREATE TABLE ip_varchar (ip varchar(255) CHECK(is_ipv4(ip)));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO ip_varchar VALUES('127.0.0.1'),('0.0.0.0'),('255.255.255.255');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO ip_varchar VALUES ('256.256.256.256'),('fukamachi');
ERROR 3819 (HY000): Check constraint 'ip_varchar_chk_1' is violated.

不正な文字列は制限したい場合には有用な手段となります。

INET_ATONを利用して数字としてIPアドレスを保存する

MySQLにはINET_ATON()という関数があり、これはIPアドレスをビッグエンディアンで32ビット符号なし整数にして返します。

mysql> SELECT inet_aton('127.0.0.1'), inet_aton('0.0.0.0'), inet_aton('255.255.255.255'), inet_aton('255.255.255.256');
+------------------------+----------------------+------------------------------+------------------------------+
| inet_aton('127.0.0.1') | inet_aton('0.0.0.0') | inet_aton('255.255.255.255') | inet_aton('255.255.255.256') |
+------------------------+----------------------+------------------------------+------------------------------+
|             2130706433 |                    0 |                   4294967295 |                         NULL |
+------------------------+----------------------+------------------------------+------------------------------+
1 row in set, 1 warning (0.01 sec)

この関数を利用することで、IPアドレスを数値型として保存することができます。

ただし、これを利用する場合はinet_atonが返す数値型の範囲が0～4,294,967,295までとなるので、INT型で定義する場合はunsigned intで定義しないとIPアドレスが正常に保存できません。また、この関数を利用する場合は省略表記を正しく解釈できないことがあるため、省略表記は利用しないようにする必要があります。

mysql> SELECT inet_aton('127.0.0.1'), inet_aton('127.1'), inet_aton('127.'), inet_aton('127');
+------------------------+--------------------+-------------------+------------------+
| inet_aton('127.0.0.1') | inet_aton('127.1') | inet_aton('127.') | inet_aton('127') |
+------------------------+--------------------+-------------------+------------------+
|             2130706433 |         2130706433 |              NULL |              127 |
+------------------------+--------------------+-------------------+------------------+
1 row in set, 1 warning (0.00 sec)

数値で保存したIPアドレスをINET_NTOAを使って参照する

INET_ATON()を使って数値として保存されたIPアドレスのデータを元のIPアドレスの形に復元するためには、INET_NTOA()という関数を使って参照する必要があります。

mysql> SELECT inet_ntoa(2130706433), inet_ntoa(0), inet_ntoa(4294967295), inet_ntoa(NULL),inet_ntoa('2130706433'), inet_ntoa('a');
+-----------------------+--------------+-----------------------+-----------------+-------------------------+----------------+
| inet_ntoa(2130706433) | inet_ntoa(0) | inet_ntoa(4294967295) | inet_ntoa(NULL) | inet_ntoa('2130706433') | inet_ntoa('a') |
+-----------------------+--------------+-----------------------+-----------------+-------------------------+----------------+
| 127.0.0.1             | 0.0.0.0      | 255.255.255.255       | NULL            | 127.0.0.1               | 0.0.0.0        |
+-----------------------+--------------+-----------------------+-----------------+-------------------------+----------------+
1 row in set, 1 warning (0.00 sec)

INET_NTOA()はNULLの場合はNULLを返しますが、数値以外の文字だった場合は0.0.0.0を返します。数値型で保存する場合はINET_NTOA関数を介したviewを作成しておくと、調査時に利用しやすいでしょう。

文字列で保存するか、数値型で保存するか

IPアドレスを数値型で保存するメリットとしては、データ量が少ないことにあります。通常文字列で保存した場合は最大16バイトですが、数値型の場合4バイトで保存することが可能です。データ量が多くならないような場合では文字列でも良いですが、履歴を溜め込むようなテーブルであれば、数値型で保存することも考慮に入れて良いと思います。

IPv6に対応する関数

IPv6版のIS_IPV4()、INET_ATON()やに対応する関数として、IS_IPV6(), INET6_ATON()があります。

ただし、INET6_ATONは数値型ではなくvarbinary形式のデータが返されます。

また、INET6_ATONで返される対象のIPv6のデータがIPv4 互換アドレスかどうか（IS_IPV4_COMPAT⁠）⁠、IPv4射影アドレスかどうかを返す関数（IS_IPV4_MAPPED）もあります。詳細な使い方については公式ドキュメントを確認してください。

userテーブルのHostはどう保存されているか

ここまで読む中で、アカウント管理をしているmysqlスキーマのuserテーブルのHostカラムはどの形式なのか、気になった方もいるかもしれません。実はHostはchar(255)の形式で保存するように定義されています。実際に保存するデータとしては、IPv4形式のIPアドレス以外にホスト名やワイルドカード文字も入るので、たしかに数値型では保存できないのです。

まとめ

今回はIPアドレスをMySQLに保存したいときに知っておくと良いかもしれないこととして、MySQLで利用できるIPアドレス周りの関数を紹介しました。アプリケーションのサービスの定義によっては不要なものかもしれませんが、データ量が多くなるときや、データの形式を保証したい場合などに利用できるので、このような関数があることを認識しておくとよいでしょう。