あいまい検索

where 句は条件を厳密に指定して検索しますが、 LIKE 演算子を使った場合、ある文字列を含むというようなあいまいな条件で検索することができます。

次の場合は、book テーブルから title カラムに SQL が含まれている情報だけを取り出します。

SELECT * FROM book WHERE title LIKE '%SQL%';

ワイルドカード

	SQL	SQL入門	入門SQL	簡単SQL入門	脱SQL
SQL%	○	○	×	×	×
%SQL	○	×	○	×	○
%SQL%	○	○	○	○	○
_SQL	×	×	×	×	○

検索条件を指定する

検索条件の指定を大きく分けると、「前方一致検索」「後方一致検索」「部分一致検索」「完全一致検索」の4つがあります。

前方一致検索

SELECT column_name FROM table_name WHERE column_name LIKE 'keyword%';

後方一致検索

SELECT column_name FROM table_name WHERE column_name LIKE '%keyword';

部分一致検索

SELECT column_name FROM table_name WHERE column_name LIKE '%keyword%';

完全一致検索

SELECT column_name FROM table_name WHERE column_name LIKE 'keyword';

サンプルデータの投入

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'sample',
  password: 'password',
});

let data = [
  { "en" : "apple", "ja" : "りんご"},
  { "en" : "orange", "ja" : "みかん"},
  { "en" : "banana", "ja" : "バナナ"},
  { "en" : "grape", "ja" :"ぶどう" },
  { "en" : "pear", "ja" : "梨"},
  { "en" : "cherry", "ja" : "さくらんぼ"},
  { "en" : "melon", "ja" : "メロン"},
  { "en" : "strawberry", "ja" : "いちご"},
  { "en" : "peach", "ja" : "桃"},
  { "en" : "persimmon, kaki", "ja" : "柿"},
  { "en" : "loquat", "ja" : "びわ"},
  { "en" : "lychee", "ja" : "ライチ"},
  { "en" : "blueberry", "ja" : "ブルーベリー"},
  { "en" : "fig", "ja" : "いちじく"},
  { "en" : "prune", "ja" : "プルーン"}
];

(async () => {
  try {
    let min = 10;
    let max = 100;

    for (let i = 0; i < data.length; i++){
      price = Math.floor( Math.random() * (max + 1 - min) ) + min;
      const [results, fields] = await pool.query('INSERT INTO fruits (name, price) VALUES (?, ?);', [data[i]["ja"], price]);
    }
  } catch (err) {
    console.log(err);
  }
  pool.end();
})();

複合検索

複数のカラムから該当する項目を検索します。

MariaDB [sample]< select * from fruits;
+----+--------------------+-------+
| id | name               | price |
+----+--------------------+-------+
| 16 | りんご             |    89 |
| 17 | みかん             |    42 |
| 18 | バナナ             |    88 |
| 19 | ぶどう             |    79 |
| 20 | 梨                   |    30 |
| 21 | さくらんぼ       |    54 |
| 22 | メロン             |    90 |
| 23 | いちご             |    23 |
| 24 | 桃                   |    20 |
| 25 | 柿                   |    76 |
| 26 | びわ                |    13 |
| 27 | ライチ             |    19 |
| 28 | ブルーベリー    |    71 |
| 29 | いちじく          |    30 |
| 30 | プルーン          |    44 |
+----+--------------------+-------+

次の SQL文を実行すると nameカラムが「りんご」または、priceカラムが「 79 」のデータを取得します。

SELECT * FROM fruits WHERE name LIKE "りんご" or price like "79";

// 結果
MariaDB [sample]< SELECT * FROM fruits WHERE name LIKE "りんご" or price like "79";
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
| 16 | りんご    |    89 |
| 19 | ぶどう    |    79 |
+----+-----------+-------+
2 rows in set (0.001 sec)

複合検索 - concat関数を使う

nameカラムとpriceカラムから「 71 」という値を検索します。

select * from fruits where concat(name, price) like "%71%";

複合検索の注意点

LIKE句では、前方一致検索はインデックスを利用できますが、それ以外ではフルスキャンとなってしまうため、サーバーに負荷がかかりレスポンスが遅れることがあります。