MySQL と MariaDB における GROUP BY の動作の違い

NULL 値の扱い

MySQL では、GROUP BY 句で指定された列に NULL 値が含まれている場合、その行は結果セットから除外されます。一方、MariaDB では、NULL 値は独自のグループとして扱われます。

例:

SELECT COUNT(*)
FROM table
GROUP BY column;

• MySQL: column に NULL 値を含む行はカウントされません。
• MariaDB: NULL 値を含む行は、NULL 値のみを含むグループとしてカウントされます。

集計関数の動作

MySQL と MariaDB では、集計関数の動作も一部異なります。

SELECT AVG(column)
FROM table
GROUP BY column;

• MySQL: column に NULL 値が含まれているグループは、結果セットから除外されます。
• MariaDB: NULL 値は無視され、NULL 値を含まない行のみを使用して平均値が計算されます。

ORDER BY 句との組み合わせ

MySQL では、GROUP BY 句と ORDER BY 句を組み合わせた場合、ORDER BY 句は GROUP BY 句で指定された列に基づいて結果を順序付けします。一方、MariaDB では、ORDER BY 句は集計関数によって生成された値に基づいて結果を順序付けします。

SELECT AVG(column)
FROM table
GROUP BY column
ORDER BY column;

• MySQL: 結果は column の値に基づいて順序付けされます。

互換性モード

MariaDB は、MySQL との互換性を維持するために、ONLY_FULL_GROUP_BY モードを提供しています。このモードを有効にすると、MariaDB の GROUP BY 句の動作は MySQL と同じになります。

SET @@ONLY_FULL_GROUP_BY = 1;

-- テーブル作成
CREATE TABLE table (
  id INT,
  column VARCHAR(255)
);

-- データ挿入
INSERT INTO table (id, column) VALUES
  (1, 'A'),
  (2, 'B'),
  (3, NULL),
  (4, 'B'),
  (5, NULL);

-- MySQL
SELECT COUNT(*)
FROM table
GROUP BY column;

-- 結果: 2

-- MariaDB
SELECT COUNT(*)
FROM table
GROUP BY column;

-- 結果: 3

-- MariaDB (ONLY_FULL_GROUP_BY モード)
SET @@ONLY_FULL_GROUP_BY = 1;

SELECT COUNT(*)
FROM table
GROUP BY column;

-- 結果: 2

-- MySQL
SELECT AVG(column)
FROM table
GROUP BY column;

-- 結果: NULL

-- MariaDB
SELECT AVG(column)
FROM table
GROUP BY column;

-- 結果: 1.5

-- MariaDB (ORDER BY 句との組み合わせ)
SELECT AVG(column)
FROM table
GROUP BY column
ORDER BY column;

-- 結果: 1.5, NULL

-- MySQL (ORDER BY 句との組み合わせ)
SELECT AVG(column)
FROM table
GROUP BY column
ORDER BY column;

-- 結果: NULL, 1.5

GROUP BY 句の動作の違いを解決する他の方法

SELECT COUNT(*)
FROM (
  SELECT column
  FROM table
  GROUP BY column
) AS t;

この方法では、GROUP BY 句をサブクエリで使用することで、MySQL と MariaDB で同じ結果を取得することができます。

CASE 式を使用する:

SELECT COUNT(*)
FROM table
GROUP BY CASE WHEN column IS NULL THEN 'NULL' ELSE column END;

この方法では、CASE 式を使用して NULL 値を別のグループに分類することで、MariaDB で MySQL と同じ結果を取得することができます。

ウィンドウ関数を使用する:

SELECT COUNT(*) OVER (PARTITION BY column)
FROM table;

この方法では、ウィンドウ関数 PARTITION BY を使用して、column ごとに集計を行うことで、MySQL と MariaDB で同じ結果を取得することができます。

アプリケーション側で処理する:

SELECT column, COUNT(*) AS count
FROM table
GROUP BY column;

# アプリケーション側で NULL 値を処理

この方法では、データベースから取得した結果をアプリケーション側で処理することで、NULL 値を必要なように扱えます。