はじめに

皆さん、こんにちは、エンジニアの Hodoshima です。

最近、私の担当している業務で大規模なシステムのリニューアルがあり、アプリケーションで使うデータベースが MySQL から PostgreSQL になりました。そのデータベースの移行にあたって、両者の間における微妙な違いを学びました。

色々な違いはありますが、今回はデータベースを検索する際に特に大きく関わってくる LINE 句を通して下記2点の違いを見てみます。

1. LIKE で大文字小文字を区別するかの決まり方が違う
2. 前方一致の LIKE でインデックスが効く条件が違う

検証環境

• MariaDB 10.6.23 (MySQL 互換)
• PostgreSQL 16.10

本記事の MySQL の動作は、MySQL 互換の MariaDB で確認しています。照合順序を指定しない場合の既定値は製品やバージョンによって異なる (MariaDB 10.6 は utf8mb4_general_ci、MySQL 8.0 以降は utf8mb4_0900_ai_ci) ため、本記事のサンプルでは照合順序を明示しています。

MySQL と PostgreSQL における LIKE 句の違い

1. LIKE で大文字小文字を区別するかの決まり方が違う

MySQL では、LIKE が大文字小文字を区別するかは列の照合順序 (COLLATE) で決まります。一方、PostgreSQL では照合順序に関係なく LIKE は常に大文字小文字を区別し、区別しない検索には ILIKE を使います。

MySQL の場合

MySQL では、COLLATE によって、文字列を比較するときのルール (照合順序) を指定することができます。照合順序の名前の先頭は、その照合順序が対象とする文字セット (ここでは utf8mb4) を表しています。

例えば、

• utf8mb4_general_ci: utf8mb4 用の照合順序で、大文字と小文字を区別しない (Case-Insensitive) 判定を行います。
  • この照合順序のもとでは、例えば、a と A は文字そのものは違いますが、大文字小文字の違いでしかないので、同じ文字として扱われます。
• utf8mb4_bin: utf8mb4 用の照合順序で、文字コードの値そのもの (Binary) で判定を行います。
  • この照合順序のもとでは、a と A は大文字小文字の違いでしかないですが、文字コードが違うため、異なる文字として扱われます。

どの照合順序を使うかを MySQL ではさまざまな場所で指定することができます。例えば、以下のようにテーブルを作成する SQL において、列ごとに COLLATE として指定することで、列ごとに照合順序を変えることができます。

CREATE TABLE case_test (
  id   INT PRIMARY KEY,
  name_ci  VARCHAR(20) COLLATE utf8mb4_general_ci,  -- 区別しない
  name_bin VARCHAR(20) COLLATE utf8mb4_bin          -- 区別する
) DEFAULT CHARSET = utf8mb4;

INSERT INTO case_test VALUES
  (1, 'Apple', 'Apple'),
  (2, 'apple', 'apple'),
  (3, 'APPLE', 'APPLE'),
  (4, 'banana', 'banana');

utf8mb4_general_ci とした列に対して、apple% の LIKE 検索をかけると、先頭が apple であるレコードを検索します。その際に、大文字小文字は区別されないので、Apple, apple, APPLE の全てが引っ掛かります。

SELECT id, name_ci FROM case_test WHERE name_ci LIKE 'apple%';

id|name_ci|
--+-------+
 1|Apple  |
 2|apple  |
 3|APPLE  |

utf8mb4_bin とした列に対して、apple% の LIKE 検索をかけると、先頭が apple であるレコードを検索します。今度は先ほどと同じ SQL ではありますが、大文字と小文字を区別するため、大文字小文字まで一致して apple で始まるもの (ここでは apple のみ) がヒットします。

SELECT id, name_bin FROM case_test WHERE name_bin LIKE 'apple%';

id|name_bin|
--+--------+
 2|apple   |

ちなみにですが、検索パターンの後ろに COLLATE を付けることで、CREATE 文で列に指定した照合順序とは別の照合順序を使うことができます。

ただし、その列に貼っていたインデックスは使われないので注意してください (詳しくは (2) で説明します)。

-- Case Insensitive とした列に対して、英語の大文字と小文字まで厳密にみた判定を行う。
SELECT id, name_ci FROM case_test WHERE name_ci LIKE 'apple%' COLLATE utf8mb4_bin;

id|name_ci|
--+-------+
 2|apple  |

なお、LIKE BINARY 'apple%' のように LIKE の直後に BINARY を付けても同じ結果になりますが、MySQL 8.0 では BINARY 演算子が非推奨となっているため、COLLATE を使う方がおすすめです。

逆に、utf8mb4_bin とした列を、大文字小文字を区別せずに検索することもできます。

-- Binary とした列に対して、英語の大文字と小文字を考慮しない判定を行う。
SELECT id, name_bin FROM case_test WHERE name_bin LIKE 'apple%' COLLATE utf8mb4_general_ci;

id|name_bin|
--+--------+
 1|Apple   |
 2|apple   |
 3|APPLE   |

PostgreSQL の場合

PostgreSQL でも列ごとに COLLATE を設定することはできますが、MySQL とは異なり、COLLATE によって LIKE の大文字小文字の区別は変わりません。PostgreSQL の LIKE は常に大文字小文字を区別し、区別せずに検索したい場合は ILIKE という別の演算子を使います。

MySQL と同じようなデータを持つテーブルを用意して、確認してみます。

CREATE TABLE case_test (
  id   INT PRIMARY KEY,
  name TEXT
);

INSERT INTO case_test VALUES
  (1, 'Apple'),
  (2, 'apple'),
  (3, 'APPLE'),
  (4, 'banana');

MySQL で utf8mb4_general_ci とした列と同じように apple% の LIKE 検索をかけても、PostgreSQL では大文字と小文字が区別されるため、apple のみがヒットします。

SELECT id, name FROM case_test WHERE name LIKE 'apple%';

id|name |
--+-----+
 2|apple|

PostgreSQL でも、やや高度にはなりますが、大文字と小文字を区別しない照合順序を作ることはできます。この照合順序を = による比較に指定すると、Apple, apple, APPLE の全てが一致します。

-- 大文字と小文字を区別しない照合順序を作成する
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

SELECT id, name FROM case_test WHERE name = 'apple' COLLATE case_insensitive;

id|name |
--+-----+
 1|Apple|
 2|apple|
 3|APPLE|

それなら LIKE にもこの照合順序を指定すればよいのでは、と思うかもしれません。しかし、同じ照合順序を LIKE に指定すると、エラーになってしまいます (PostgreSQL 16 で確認)。

SELECT id, name FROM case_test WHERE name LIKE 'apple%' COLLATE case_insensitive;

ERROR:  nondeterministic collations are not supported for LIKE

そのため、LIKE で大文字と小文字を区別せずに検索したい場合は、ILIKE を使います。

SELECT id, name FROM case_test WHERE name ILIKE 'apple%';

id|name |
--+-----+
 1|Apple|
 2|apple|
 3|APPLE|

まとめ

	MySQL	PostgreSQL
LIKE で大文字小文字を区別するかを決めるもの	列の照合順序 (COLLATE)	区別する (照合順序に関係しない)
大文字小文字を区別しない検索	_ci の照合順序の列に LIKE	ILIKE
大文字小文字を区別する検索	_bin の照合順序の列に LIKE	LIKE

2. 前方一致の LIKE でインデックスが効く条件が違う

LIKE 'apple%' のような前方一致の検索では、MySQL でも PostgreSQL でもインデックスを使うことができます。ただし、MySQL では大文字小文字を区別しない照合順序 (utf8mb4_general_ci) でも区別する照合順序 (utf8mb4_bin) でもインデックスが効くのに対し、PostgreSQL では列の照合順序によっては、通常のインデックスが効きません。

ちなみに、LIKE '%ple' や LIKE '%ppl%' のような後方一致や中間一致では、どちらのデータベースでも通常のインデックスは効きません。インデックスは文字列を先頭から順に並べたものなので、前方一致であれば一致するものが連続した範囲にまとまることが保証されます。一方で、後方一致や中間一致では、一致するもの (apple、maple、purple など) の先頭がばらばらで、インデックス上のあちこちに散らばってしまうためです。

MySQL の場合

(1) と同じく、照合順序の異なる 2 つの列を持つテーブルを用意し、それぞれにインデックスを貼ります。インデックスが使われる様子を確認できるように、ダミーデータを 10,000 件入れておきます。

CREATE TABLE index_test (
  id       INT PRIMARY KEY,
  name_ci  VARCHAR(20) COLLATE utf8mb4_general_ci,
  name_bin VARCHAR(20) COLLATE utf8mb4_bin,
  INDEX idx_ci  (name_ci),
  INDEX idx_bin (name_bin)
) DEFAULT CHARSET = utf8mb4;

-- ダミーデータを 10,000 件入れる (item1 〜 item10000)
INSERT INTO index_test
WITH d (n) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
               UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
     seq (n) AS (SELECT a.n * 1000 + b.n * 100 + c.n * 10 + e.n + 1 FROM d a, d b, d c, d e)
SELECT n, CONCAT('item', n), CONCAT('item', n) FROM seq;

INSERT INTO index_test VALUES
  (10001, 'Apple', 'Apple'),
  (10002, 'apple', 'apple'),
  (10003, 'APPLE', 'APPLE');

ANALYZE TABLE index_test;

EXPLAIN で実行計画を確認すると、utf8mb4_general_ci の列でも utf8mb4_bin の列でも、type が range (インデックスを使った範囲検索) になっており、インデックスが効いていることがわかります。

EXPLAIN SELECT * FROM index_test WHERE name_ci LIKE 'apple%';

type |key   |rows|
-----+------+----+
range|idx_ci|   3|

EXPLAIN SELECT * FROM index_test WHERE name_bin LIKE 'apple%';

type |key    |rows|
-----+-------+----+
range|idx_bin|   1|

※ EXPLAIN の結果は一部の列を抜粋しています。

特に utf8mb4_general_ci の列では、大文字小文字を区別しない検索のまま、インデックスを使って Apple, apple, APPLE の 3 件を絞り込めています (rows が 3)。MySQL では、インデックスの並び順と同じ照合順序のルールで「apple で始まる文字列」の範囲を計算できるため、大文字小文字を区別しない照合順序でも前方一致にインデックスが効きます。

一方で、(1) で紹介した方法で、列とは別の照合順序を検索時に指定すると、type が ALL (全件走査) になり、インデックスが使われなくなります。インデックスは列の照合順序に従って作られているため、別の照合順序での検索には使えないためです。

EXPLAIN SELECT * FROM index_test WHERE name_ci LIKE 'apple%' COLLATE utf8mb4_bin;

type|key |rows|
----+----+----+
ALL |NULL|9921|

PostgreSQL の場合

PostgreSQL では、列の照合順序が C かどうかで、前方一致の LIKE に通常のインデックスが効くかが変わります。

C は文字コードの値 (バイト列) の順で文字列を並べる照合順序で、MySQL の _bin に近いものです。一方、en_US.UTF-8 や ja_JP.UTF-8、ICU の照合順序などの C 以外の照合順序では、言語のルールに従って文字列が並びます。データベースを作成するときの照合順序は環境によって異なるため、自分の環境の照合順序がどちらなのかを確認しておくとよいでしょう。

-- データベースの照合順序を確認する
SELECT datcollate FROM pg_database WHERE datname = current_database();

なお、列に COLLATE を指定しない場合は、データベースの照合順序が使われます。そのため、データベースの照合順序が C 以外であれば、特に意識せずに作ったインデックスは前方一致の LIKE に効かないことになります。

ここでは、照合順序が C の列と、ICU の照合順序 (C 以外) の列を持つテーブルを用意します。

CREATE TABLE index_test (
  id       INT PRIMARY KEY,
  name_c   TEXT COLLATE "C",
  name_icu TEXT COLLATE "und-x-icu"
);

-- ダミーデータを 10,000 件入れる (item1 〜 item10000)
INSERT INTO index_test
SELECT n, 'item' || n, 'item' || n FROM generate_series(1, 10000) AS n;

INSERT INTO index_test VALUES
  (10001, 'Apple', 'Apple'),
  (10002, 'apple', 'apple'),
  (10003, 'APPLE', 'APPLE');

CREATE INDEX idx_c   ON index_test (name_c);
CREATE INDEX idx_icu ON index_test (name_icu);
ANALYZE index_test;

照合順序が C の列では、Index Scan となり、インデックスが効いています。LIKE 'apple%' が、'apple' 以上 'applf' 未満という範囲の条件に置き換えられていることがわかります。

EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE name_c LIKE 'apple%';

Index Scan using idx_c on index_test
  Index Cond: ((name_c >= 'apple'::text) AND (name_c < 'applf'::text))
  Filter: (name_c ~~ 'apple%'::text)

一方、C 以外の照合順序の列では、同じようにインデックスを貼っているにもかかわらず、Seq Scan (全件走査) になってしまいます。言語のルールに従った並び順では、「apple で始まる文字列」が必ずしも連続した範囲に並ぶとは限らず、範囲の条件に置き換えられないためです。

EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE name_icu LIKE 'apple%';

Seq Scan on index_test
  Filter: (name_icu ~~ 'apple%'::text)

C 以外の照合順序の列で前方一致の LIKE にインデックスを効かせたい場合は、text_pattern_ops (VARCHAR の列では varchar_pattern_ops) を指定してインデックスを作ります。この指定をすると、照合順序に関係なく文字コードの値の順でインデックスが作られるため、前方一致の検索に使えるようになります。

CREATE INDEX idx_icu_pattern ON index_test (name_icu text_pattern_ops);

EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE name_icu LIKE 'apple%';

Bitmap Heap Scan on index_test
  Filter: (name_icu ~~ 'apple%'::text)
  ->  Bitmap Index Scan on idx_icu_pattern
        Index Cond: ((name_icu ~>=~ 'apple'::text) AND (name_icu ~<~ 'applf'::text))

ただし、text_pattern_ops を指定したインデックスは、ORDER BY などによる比較には使われません。これらにもインデックスを使いたい場合は、通常のインデックスと両方を作る必要があります。

また、(1) で紹介した ILIKE は、照合順序が C の列であったとしても、インデックスが効きません。

EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE name_c ILIKE 'apple%';

Seq Scan on index_test
  Filter: (name_c ~~* 'apple%'::text)

大文字小文字を区別しない前方一致の検索でインデックスを効かせたい場合は、lower() を使った式インデックスを作り、検索も lower(列) LIKE の形で行います。

CREATE INDEX idx_c_lower ON index_test (lower(name_c));

EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(name_c) LIKE 'apple%';

Bitmap Heap Scan on index_test
  Filter: (lower(name_c) ~~ 'apple%'::text)
  ->  Bitmap Index Scan on idx_c_lower
        Index Cond: ((lower(name_c) >= 'apple'::text) AND (lower(name_c) < 'applf'::text))

なお、この例は照合順序が C の列なので通常の式インデックスで効いていますが、C 以外の照合順序の列では、lower(name_icu) text_pattern_ops のように text_pattern_ops も指定する必要があります。

まとめ

	MySQL	PostgreSQL
前方一致の LIKE に通常のインデックスが効くか	utf8mb4_general_ci でも utf8mb4_bin でも効く	列の照合順序が C なら効く。C 以外では効かない
C 以外の照合順序でインデックスを効かせる方法	―	text_pattern_ops を指定してインデックスを作る
大文字小文字を区別しない前方一致	_ci の列なら LIKE のまま効く (検索時に列と別の照合順序を指定すると効かない)	ILIKE は効かない。lower() の式インデックスを使う

最後に

このように、使っていたデータベースとは異なるシステムのデータベースを使おうとする場合、大きな違いにはよく気がつきますが、小さい違いには気がつきにくいものです。 いざ運用してみるとその小さな違いに気づくことが多いと思います。

データの移行が完了した後でも、色々と触って、微妙な動作にも違いがないかをみてみることをおすすめします。