MariaDB で Emoji を含む varchar 列で GROUP BY が失敗する問題の解決策
MariaDB で Emoji を含む varchar(255) 列で GROUP BY が失敗する問題と解決策
MariaDB で、varchar(255) 列に utf8mb4_unicode_ci コレーションを設定し、その列に Emoji を含む場合、GROUP BY 句を使用したクエリを実行すると失敗する場合があります。これは、Emoji の文字コードが utf8mb4_unicode_ci コレーションによって正規化される際に、意図しない結果が生じるためです。
原因
utf8mb4_unicode_ci コレーションは、文字列を比較する際に、大文字と小文字を区別せず、結合記号を無視するなど、多くの正規化ルールを適用します。しかし、Emoji の場合は、これらの正規化ルールが意図しない結果を生じる場合があります。
例えば、以下の Emoji を考えてみましょう。
この Emoji は、4 つの個々の Emoji (, , , ) が結合されたものです。utf8mb4_unicode_ci コレーションでは、これらの個々の Emoji は結合され、単一の文字として扱われます。
このため、GROUP BY 句でこの列をグループ化すると、4 つの個々の Emoji がすべて同じグループに分類されてしまい、意図した結果が得られなくなります。
解決策
この問題を解決するには、以下のいずれかの方法を採用することができます。
コレーションを変更する
utf8mb4_unicode_ci コレーションではなく、utf8mb4_bin コレーションを使用します。utf8mb4_bin コレーションは、文字列を正規化せずにそのまま比較するため、Emoji の文字コードが正しく保持されます。
ALTER TABLE your_table MODIFY your_column VARCHAR(255) COLLATE utf8mb4_bin;
Emoji を別のデータ型に変換する
Emoji を VARCHAR 列ではなく、JSON 列などの別のデータ型に変換します。JSON 列では、Emoji を文字列ではなく、オブジェクトとして保存することができます。
Emoji を個別の列に格納する
複数の Emoji を含む場合は、それぞれの Emoji を個別の列に格納します。
クエリを変更する
GROUP BY 句ではなく、CASE WHEN 句を使用して、Emoji をグループ化します。
SELECT
CASE WHEN your_column LIKE '%%' THEN ''
WHEN your_column LIKE '%%' THEN ''
...
ELSE 'その他'
END AS emoji_group,
COUNT(*) AS count
FROM your_table
GROUP BY emoji_group;
注意事項
上記の解決策を採用する際には、以下の点に注意する必要があります。
CASE WHEN句を使用すると、クエリのパフォーマンスが低下する可能性があります。- Emoji を個別の列に格納すると、クエリが複雑になる可能性があります。
- Emoji を別のデータ型に変換すると、ストレージ要件が増加する可能性があります。
- コレーションを変更すると、既存のデータの比較方法が変更されます。
CREATE TABLE your_table (
id INT PRIMARY KEY AUTO_INCREMENT,
emoji VARCHAR(255) COLLATE utf8mb4_unicode_ci
);
INSERT INTO your_table (emoji) VALUES
(''),
(''),
(''),
('');
SELECT emoji, COUNT(*) AS count
FROM your_table
GROUP BY emoji;
このクエリを実行すると、以下のエラーが発生します。
ERROR 1305 (42000): Unknown column 'emoji' in 'field list'
以下のコードは、utf8mb4_unicode_ci コレーションを utf8mb4_bin コレーションに変更します。
ALTER TABLE your_table MODIFY emoji VARCHAR(255) COLLATE utf8mb4_bin;
この変更後、上記のクエリを実行すると、以下の結果が得られます。
emoji | count
-------|-------
| 1
| 1
| 1
| 1
以下のコードは、Emoji を VARCHAR 列ではなく、JSON 列に変換します。
ALTER TABLE your_table MODIFY emoji JSON;
INSERT INTO your_table (emoji) VALUES
(JSON_OBJECT('emoji1', '')),
(JSON_OBJECT('emoji2', '')),
(JSON_OBJECT('emoji3', '')),
(JSON_OBJECT());
SELECT emoji->'emoji1' AS emoji, COUNT(*) AS count
FROM your_table
GROUP BY emoji;
emoji | count
-------|-------
| 1
| 1
| 1
| 1
以下のコードは、複数の Emoji を個別の列に格納します。
CREATE TABLE your_table (
id INT PRIMARY KEY AUTO_INCREMENT,
emoji1 VARCHAR(255) COLLATE utf8mb4_unicode_ci,
emoji2 VARCHAR(255) COLLATE utf8mb4_unicode_ci,
emoji3 VARCHAR(255) COLLATE utf8mb4_unicode_ci
);
INSERT INTO your_table (emoji1, emoji2, emoji3) VALUES
('', '', ''),
('', '', ''),
('', '', ''),
('', '', '');
SELECT emoji1, emoji2, emoji3, COUNT(*) AS count
FROM your_table
GROUP BY emoji1, emoji2, emoji3;
emoji1 | emoji2 | emoji3 | count
-------|-------|-------|-------
| | | 1
| | | 1
| | | 1
| | | 1
解決策 4: クエリを変更する
SELECT
CASE WHEN your_column LIKE '%%' THEN ''
WHEN your_column LIKE '%%' THEN ''
...
ELSE 'その他'
END AS emoji_group,
COUNT(*) AS count
FROM your_table
GROUP BY emoji_group;
emoji_group | count
------------|-------
| 1
| 1
| 1
その他 | 1
GROUP BY 句で正規表現を使用して、Emoji をグループ化することができます。
SELECT
REGEXP_SUBSTR(your_column, '[emoji]') AS emoji_group,
COUNT(*) AS count
FROM your_table
GROUP BY emoji_group;
このクエリは、すべての Emoji を単一のグループに分類します。特定の Emoji をグループ化するには、正規表現を調整する必要があります。
サブクエリを使用する
SELECT emoji, COUNT(*) AS count
FROM your_table
WHERE emoji IN (
SELECT DISTINCT your_column
FROM your_table
)
GROUP BY emoji;
このクエリは、すべての異なる Emoji をグループ化します。
トリガーを使用する
以下のコードは、トリガーを使用して、INSERT または UPDATE 操作のたびに、Emoji を別のテーブルに格納します。
CREATE TRIGGER your_trigger BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
BEGIN
INSERT INTO emoji_table (emoji)
VALUES (NEW.emoji);
END;
このトリガーを使用すると、GROUP BY 句を使用して Emoji をグループ化するための別のテーブルを作成することができます。
ビューを使用する
以下のコードは、ビューを使用して、Emoji を別の列として表示します。
CREATE VIEW your_view AS
SELECT
your_column AS emoji,
...
FROM your_table;
このビューを使用して、GROUP BY 句で Emoji をグループ化することができます。
最適な方法の選択
使用する方法は、要件によって異なります。
- 既存のクエリを変更したくない場合は、ビューを使用する 方法がおすすめです。
- すべての Emoji を別のテーブルに保存する必要がある場合は、トリガーを使用する 方法がおすすめです。
- より柔軟な方法が必要な場合は、正規表現を使用する または サブクエリを使用する 方法がおすすめです。
- シンプルでパフォーマンスの高い方法が必要な場合は、コレーションを変更する または Emoji を別のデータ型に変換する 方法がおすすめです。
mariadb
