MySQL
 Computer >> コンピューター >  >> プログラミング >> MySQL

MySQLのSUBSTRING_INDEX()関数を使ってIPアドレスを4つのオクテットに分割する方法

「ipaddress」というテーブルがあり、列「IP」にIPアドレスが格納されていると仮定します。テーブルの内容は以下の通りです。

mysql> Select * from ipaddress;
+-----------------+
| ip              |
+-----------------+
| 192.128.0.5     |
| 255.255.255.255 |
| 192.0.255.255   |
| 192.0.1.5       |
+-----------------+
4 rows in set (0.10 sec)

SUBSTRING_INDEX()関数によるIPアドレスの分割

SUBSTRING_INDEX()関数は、指定した区切り文字を基準に文字列を分割できるMySQLの便利な文字列関数です。構文は SUBSTRING_INDEX(対象文字列, 区切り文字, 出現回数) となっており、出現回数に正の値を指定すると左から、負の値を指定すると右から数えた位置を基準に部分文字列を返します。

IPアドレスはドット（.）で区切られた4つのオクテットで構成されているため、この関数を組み合わせることで各オクテットを個別に抽出できます。実際のクエリは以下の通りです。

mysql> Select IP, SUBSTRING_INDEX(ip,'.',1)AS '1st Part',
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1)AS '2nd Part',
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1)AS '3rd Part',
    -> SUBSTRING_INDEX(ip,'.',-1)AS '4th Part' from ipaddress;
+-----------------+----------+----------+----------+----------+
| IP              | 1st Part | 2nd Part | 3rd Part | 4th Part |
+-----------------+----------+----------+----------+----------+
| 192.128.0.5     | 192      | 128      | 0        | 5        |
| 255.255.255.255 | 255      | 255      | 255      | 255      |
| 192.0.255.255   | 192      | 0        | 255      | 255      |
| 192.0.1.5       | 192      | 0        | 1        | 5        |
+-----------------+----------+----------+----------+----------+
4 rows in set (0.05 sec)

各オクテットの抽出ロジック

  • 第1オクテット： SUBSTRING_INDEX(ip, '.', 1) — 左から1番目のドットより前の文字列をそのまま取得します。
  • 第2オクテット： SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1) — 左から2番目のドットまでの文字列を取得した後、その末尾のセグメントを取り出します。
  • 第3オクテット： SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1) — 右から2番目のドット以降の文字列を取得した後、その先頭のセグメントを取り出します。
  • 第4オクテット： SUBSTRING_INDEX(ip, '.', -1) — 右から1番目のドットより後の文字列をそのまま取得します。

このように、SUBSTRING_INDEX()関数を入れ子（ネスト）にして使うことで、中間のオクテットも正確に抽出できます。ログ解析やアクセス元ネットワークの集計など、IPアドレスを扱うデータ処理の場面で非常に役立つテクニックです。

  1. COALESCE()関数を使ってMySQLのNULLを0に変換する方法

    COALESCE()関数とは？NULLを0に変換できる理由 MySQLでは、COALESCE()関数を使うことで、NULL値を0に変換できます。COALESCE()は、引数の中から最初のNULL以外の値を返すという性質を持つ関数です。そのため、第1引数に対象のカラム、第2引数に代替値の0を指定すれば、NULLだった場合だけ0が返される仕組みになります。 基本構文は以下の通りです。 SELECT COALESCE(yourColumnName, 0) AS anyAliasName FROM yourTableName; それでは、実際にサンプルテーブルを作成しながら動作を確認していきましょう。

  2. MySQLのSUBSTRING_INDEX()関数で文字列を分割する方法を徹底解説

    MySQLで文字列を特定の区切り文字で分割したい場合、SUBSTRING_INDEX() 関数が非常に便利です。この記事では、サンプルテーブルの作成から、氏名を「名」と「姓」に分割するクエリの実行まで、具体的な手順をわかりやすく解説します。 SUBSTRING_INDEX()関数とは SUBSTRING_INDEX(str, delim, count) は、区切り文字 delim の count 回目の出現位置を基準に部分文字列を返す関数です。count が正の値の場合は左側から、負の値の場合は右側から数えます。この特性を組み合わせることで、柔軟に文字列を分割できます。 サンプルテーブルの作成