MySQLのLIKE、BINARYの挙動を確認してみた

SHIFT Group 技術ブログ

2022年8月26日 16:41

はじめに

こんにちは、SHIFT の開発部門に所属しているmurasawaです。
中途で入社、バックエンド関連の開発を担当しています。

現在、社内基盤の開発を行っています。
開発の中でDB等、新たに学んだり得た知見をアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

今回はlike演算子、binary演算子を実際に使い、挙動を確認してみました。

使用環境 mysql:5.7.35

コンソール上で操作します。

今回使用するテーブル、データは以下になります。

mysql> DESC users;
+---------------+----------------------+------+-----+-------------------+-----------------------------+
| Field         | Type                 | Null | Key | Default           | Extra                       |
+---------------+----------------------+------+-----+-------------------+-----------------------------+
| id            | int(10) unsigned     | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(255)         | NO   |     | NULL              |                             |
| favorite_book | text                 | NO   | MUL | NULL              |                             |
| score         | smallint(5) unsigned | YES  |     | NULL              |                             |
| created_at    | timestamp            | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at    | datetime             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+----------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.03 sec)

mysql> SELECT * FROM users;
+----+-----------+-----------------+-------+---------------------+---------------------+
| id | name      |
favorite_book | score | created_at          | updated_at          |
+----+-----------+-----------------+-------+---------------------+---------------------+
|  1 | sato      | SQL入門          |   450 | 2022-08-23 15:48:43 | 2022-08-23 15:48:52 |
|  2 | saito     | postgreSQL入門   |   420 | 2022-08-23 15:49:33 | 2022-08-23 15:49:33 |
|  3 | kato      | AdobeXD入門      |   480 | 2022-08-23 15:50:30 | 2022-08-23 15:50:30 |
|  4 | yamamoto  | 異邦人            |   490 | 2022-08-23 15:52:10 | 2022-08-23 15:52:10 |
|  5 | yamashita | Readable code   |   NULL | 2022-08-23 15:53:12 | 2022-08-23 15:53:12 |
|  6 | fujimoto  | 千夜一夜物語        |   500 | 2022-08-23 15:54:33 | 2022-08-23 15:54:46 |
+----+-----------+-----------------+-------+---------------------+---------------------+
6 rows in set (0.06 sec)

mysql>

LIKE

likeを使うと、tableに格納されているデータの中で文字を指定してその文字を含むデータを取得することができます。

「ya」から始まるデータを取得したい場合、

mysql> SELECT * FROM users WHERE name LIKE 'ya%';
+----+-----------+-----------------+-------+---------------------+---------------------+
| id | name      |
favorite_book | score | created_at          | updated_at          |
+----+-----------+-----------------+-------+---------------------+---------------------+
|  4 | yamamoto  | 異邦人            |   490 | 2022-08-23 15:52:10 | 2022-08-23 15:52:10 |
|  5 | yamashita | Readable code   |   NULL | 2022-08-23 15:53:12 | 2022-08-23 15:53:12 |
+----+-----------+-----------------+-------+---------------------+---------------------+
2 rows in set (0.04 sec)

mysql>

like '検索したい文字%'と指定することでyaから始まるyamamotoさんとyamashitaさんを取得できました。
%は任意の文字数を表しています。

「入門」で終わるデータを取得したい場合、

mysql> SELECT * FROM users WHERE favorite_book LIKE '%入門';
+----+-------+---------------+-------+---------------------+---------------------+
| id | name  | favorite_book | score | created_at          | updated_at          |
+----+-------+---------------+-------+---------------------+---------------------+
|  1 | sato  | SQL入門        |   450 | 2022-08-23 15:48:43 | 2022-08-23 15:48:52 |
|  2 | saito | postgreSQL入門 |   420 | 2022-08-23 15:49:33 | 2022-08-23 15:49:33 |
|  3 | kato  | AdobeXD入門    |   480 | 2022-08-23 15:50:30 | 2022-08-23 15:50:30 |
+----+-------+---------------+-------+---------------------+---------------------+
3 rows in set (0.03 sec)

mysql>

で取得することができます。

「sql」を含むデータを取得したい場合、

mysql> SELECT * FROM users WHERE favorite_book LIKE '%sql%';
+----+-------+---------------+-------+---------------------+---------------------+
| id | name  | favorite_book | score | created_at          | updated_at          |
+----+-------+---------------+-------+---------------------+---------------------+
|  1 | sato  | SQL入門        |   450 | 2022-08-23 15:48:43 | 2022-08-23 15:48:52 |
|  2 | saito | postgreSQL入門 |   420 | 2022-08-23 15:49:33 | 2022-08-23 15:49:33 |
+----+-------+---------------+-------+---------------------+---------------------+
2 rows in set (0.04 sec)

mysql>

で部分一致するデータを取得できます。

fで始まりでoで終わるデータを取得したい場合

mysql> SELECT * FROM users WHERE name LIKE 'f%o';
+----+----------+---------------+-------+---------------------+---------------------+
| id | name     | favorite_book | score | created_at          | updated_at          |
+----+----------+---------------+-------+---------------------+---------------------+
|  6 | fujimoto | 千夜一夜物語      |   500 | 2022-08-23 15:54:33 | 2022-08-23 16:21:45 |
+----+----------+---------------+-------+---------------------+---------------------+
1 row in set (0.03 sec)

mysql>

で始まりの文字と終わりの文字を指定できます。

また複数文字でも取得可能です。

mysql> SELECT * FROM users WHERE favorite_book LIKE '千夜%物語';
+----+----------+---------------+-------+---------------------+---------------------+
| id | name     | favorite_book | score | created_at          | updated_at          |
+----+----------+---------------+-------+---------------------+---------------------+
|  6 | fujimoto | 千夜一夜物語      |   500 | 2022-08-23 15:54:33 | 2022-08-23 16:21:45 |
+----+----------+---------------+-------+---------------------+---------------------+
1 row in set (0.03 sec)

mysql>

＿(アンダースコア)の利用

like演算子は%のほかに_を使うこともできます。

%は任意の文字列でした(sql%であればsql以降は何文字でもよい)
_は任意の一文字を現します。

例えば、4文字で後尾に「to」がつく名前の人を取得したい場合、

mysql> SELECT * FROM users WHERE name LIKE '__to';
+----+------+---------------+-------+---------------------+---------------------+
| id | name | favorite_book | score | created_at          | updated_at          |
+----+------+---------------+-------+---------------------+---------------------+
|  1 | sato | SQL入門        |   450 | 2022-08-23 15:48:43 | 2022-08-23 15:48:52 |
|  3 | kato | AdobeXD入門    |   480 | 2022-08-23 15:50:30 | 2022-08-23 15:50:30 |
+----+------+---------------+-------+---------------------+---------------------+
2 rows in set (0.04 sec)

mysql>

__toのようにアンダースコアを2つ付けて検索することで
saitoは取得せず、satoとkatoのみ取ることができました。

not like

not likeはlikeに当てはまらないデータを取得します。

部分一致で例を挙げると

mysql> SELECT * FROM users WHERE favorite_book NOT LIKE '%sql%';
+----+-----------+---------------+-------+---------------------+---------------------+
| id | name      | favorite_book | score | created_at          | updated_at          |
+----+-----------+---------------+-------+---------------------+---------------------+
|  3 | kato      | AdobeXD入門    |   480 | 2022-08-23 15:50:30 | 2022-08-23 15:50:30 |
|  4 | yamamoto  | 異邦人          |   490 | 2022-08-23 15:52:10 | 2022-08-23 15:52:10 |
|  5 | yamashita | Readable code |   390 | 2022-08-23 15:53:12 | 2022-08-23 15:53:12 |
|  6 | fujimoto  | 千夜一夜物語      |   500 | 2022-08-23 15:54:33 | 2022-08-23 16:21:45 |
+----+-----------+---------------+-------+---------------------+---------------------+
4 rows in set (0.03 sec)

mysql>

「SQL」という文字を含まないデータを取得することができました。

binary演算子

上述した例では小文字大文字の区別はなく

mysql> SELECT * FROM users WHERE favorite_book LIKE '%sql%';
+----+-------+---------------+-------+---------------------+---------------------+
| id | name  | favorite_book | score | created_at          | updated_at          |
+----+-------+---------------+-------+---------------------+---------------------+
|  1 | sato  | SQL入門        |   450 | 2022-08-23 15:48:43 | 2022-08-23 15:48:52 |
|  2 | saito | postgreSQL入門 |   420 | 2022-08-23 15:49:33 | 2022-08-23 15:49:33 |
+----+-------+---------------+-------+---------------------+---------------------+
2 rows in set (0.04 sec)

mysql>

sqlで検索しSQLを含むデータが取得できていましたが、大文字小文字を区別したい場合、binaryを使います。

mysql> SELECT * FROM users WHERE favorite_book LIKE BINARY '%sql%';
Empty set

mysql>

小文字で検索すると引っかかりませんが

mysql> SELECT * FROM users WHERE favorite_book LIKE BINARY '%SQL%';
+----+-------+---------------+-------+---------------------+---------------------+
| id | name  | favorite_book | score | created_at          | updated_at          |
+----+-------+---------------+-------+---------------------+---------------------+
|  1 | sato  | SQL入門        |   450 | 2022-08-23 15:48:43 | 2022-08-23 15:48:52 |
|  2 | saito | postgreSQL入門 |   420 | 2022-08-23 15:49:33 | 2022-08-23 15:49:33 |
+----+-------+---------------+-------+---------------------+---------------------+
2 rows in set (0.04 sec)

mysql>

大文字で検索すると取得することができます。

tips

全文検索についても触れておきます。

以下のような FULLTEXT インデックスを貼ったテーブルを用意します。

mysql> CREATE TABLE sample_table (title TEXT NOT NULL, FULLTEXT KEY(title) WITH PARSER NGRAM);

mysql> DESC sample_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| title | text | NO   | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> SELECT * FROM sample_table;
+---------------+
| title         |
+---------------+
| SQLテスト        |
| テストSQL        |
| postgreSQL入門 |
| AdobeXD入門    |
+---------------+
4 rows in set (0.02 sec)

mysql>

以下のように検索することができます。

mysql> SELECT * FROM sample_table WHERE MATCH(title) AGAINST('SQL');
+---------------+
| title         |
+---------------+
| SQLテスト        |
| テストSQL        |
| postgreSQL入門 |
+---------------+
3 rows in set (0.02 sec)

mysql> SELECT * FROM sample_table WHERE MATCH(title) AGAINST('入門');
+---------------+
| title         |
+---------------+
| postgreSQL入門 |
| AdobeXD入門    |
+---------------+
2 rows in set (0.03 sec)

mysql>

ちなみにlikeで検索すると

mysql> SELECT * FROM sample_table WHERE MATCH(title) AGAINST('SQL');
+---------------+
| title         |
+---------------+
| SQLテスト        |
| テストSQL        |
| postgreSQL入門 |
+---------------+
3 rows in set (0.03 sec)

mysql>

行数が少ないので差がないように見えますが、増えてきたとき

LIKE
FULLTEXT インデックス
Mroonga

など何を使うかの選定が必要になります。

また、FULLTEXT インデックスについて簡易的に確認しましたが。
日本語検索する際など、転置インデックス等考慮する必要があります。

FULLTEXT インデックスについての考慮や行数の多い場合などの性能比較は以下の記事が参考になります。

LIKE検索より50倍速い！？MySQLでラクラク高速な日本語全文検索

Mroonga(MySQL)やLIKE・FULLTEXTインデックスの全文検索の性能を比較してみた

終わりに

like演算子を使う機会がなかったので調べて使ってみましたが、検索方法が豊富で特定の文字列をピックアップしたい際には強力な演算子であることがよくわかりました。

文字列の検索を行う場合、性能差も含め検討し、適切な手法を選べるよう心がけていきます。

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。