MySQLでカンマ区切りフィールドの値の個数を数える方法

MySQLでは、CHAR_LENGTH() 関数を使うことで、カンマ区切り（CSV形式）で保存されたフィールドに含まれる値の個数を簡単に数えることができます。

基本的な構文

SELECT *, (CHAR_LENGTH(yourColumnName) - CHAR_LENGTH(REPLACE(yourColumnName, ',', '')) + 1) AS anyVariableName FROM yourTableName;

仕組みはとてもシンプルです。元の文字列の長さからカンマをすべて取り除いた文字列の長さを引くと「カンマの個数」が求められます。さらに1を足せば、それがそのまま値の個数になります。

サンプルテーブルの作成

まずは動作を確認するためのテーブルを作成しましょう。

mysql> create table CountValuesCommaSeparated
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> CommaSeparatedValue text,
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (1.76 sec)

テストデータの挿入

INSERTコマンドを使って、いくつかのレコードを追加します。

mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue)
values('101,104,1900,46675,7895');
Query OK, 1 row affected (0.16 sec)

mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue)
values('1010,18949,37465');
Query OK, 1 row affected (0.21 sec)

mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue)
values('2010,1201,2743874,7485');
Query OK, 1 row affected (0.42 sec)

mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue)
values('4757,457587,48586,378575,3874765,487565');
Query OK, 1 row affected (0.26 sec)

登録データの確認

SELECT文ですべてのレコードを表示してみます。

mysql> select *from CountValuesCommaSeparated;

実行結果は以下の通りです。

+----+-----------------------------------------+
| Id | CommaSeparatedValue                     |
+----+-----------------------------------------+
| 1  | 101,104,1900,46675,7895                 |
| 2  | 1010,18949,37465                        |
| 3  | 2010,1201,2743874,7485                  |
| 4  | 4757,457587,48586,378575,3874765,487565 |
+----+-----------------------------------------+
4 rows in set (0.00 sec)

カンマ区切りフィールドの値をカウントするクエリ

いよいよ、各レコードに含まれる値の個数を数えてみましょう。

mysql> select *,
    -> (CHAR_LENGTH(CommaSeparatedValue) -
CHAR_LENGTH(REPLACE(CommaSeparatedValue, ',', '')) + 1) as TotalValue
    -> from CountValuesCommaSeparated;

実行結果：

+----+-----------------------------------------+------------+
| Id | CommaSeparatedValue                     | TotalValue |
+----+-----------------------------------------+------------+
|  1 | 101,104,1900,46675,7895                 |          5 |
|  2 | 1010,18949,37465                        |          3 |
|  3 | 2010,1201,2743874,7485                  |          4 |
|  4 | 4757,457587,48586,378575,3874765,487565 |          6 |
+----+-----------------------------------------+------------+
4 rows in set (0.00 sec)

TotalValue 列を見ると、カンマ区切りの各フィールドに含まれる値の個数（5・3・4・6）が正しく取得できていることがわかります。

補足：NULLや空文字列への対応

対象の列にNULLや空文字列が含まれる可能性がある場合は、CASE式を組み合わせて除外しておくとより安全です。

SELECT *,
  (CASE WHEN CommaSeparatedValue IS NULL OR CommaSeparatedValue = ''
        THEN 0
        ELSE CHAR_LENGTH(CommaSeparatedValue)
             - CHAR_LENGTH(REPLACE(CommaSeparatedValue, ',', '')) + 1
   END) AS TotalValue
FROM CountValuesCommaSeparated;

また、値の間に半角スペースが混在しているケースでは、あらかじめ REPLACE() や TRIM() でスペースを除去してからカウントすると、より正確な結果が得られます。