MySQL の組み込み関数 find_in_set を使用した効率的なあいまい検索の詳細な説明

一般的に使用される方法は次の 4 つです。

1.locate()メソッドを使用する

一般的な使用法:

SELECT`column`from`table`wherelocate('keyword',`condition`)>0

Java の indexOf() に似ています。

ただし、locate() は、結果が見つかる限り (クエリの内容が最初の部分であっても) 0 より大きい結果を返し、見つからない場合は 0 を返します。

開始位置を指定します:

SELECT LOCATE('bar','foobarbar',5); (検索はfoobarbarの5番目の位置から始まります)

2. instr()関数を使用する（locate()のエイリアス関数と言われています）

SELECT `column` from `table` where instr(`condition`,'keyword')>0

唯一の違いはクエリコンテンツの場所である

3. position() メソッドを使用する（locate() メソッドの別名関数とも呼ばれ、同じ機能を持つ）

SELECT `column` from `table` where position('keyword' IN `condition`)

しかし、もはや戻り値ではなく、キーワードによって判断されます。

4. find_in_set()関数を使用する

たとえば、find_in_set(str,strlist) の場合、strlist はカンマで区切られた文字列である必要があります。

文字列 str が strlist 内の N 個の部分文字列で構成される文字列リスト内にある場合、戻り値の範囲は 1 から N になります。

SQL>FIND_IN_SET('b','a,b,c,d') を選択します。
---------------------------------------------------------+
|SELECTFIND_IN_SET('b','a,b,c,d')|
---------------------------------------------------------+
|2|
---------------------------------------------------------+
1行目挿入(0.00秒)

要約:locate、position、instr の唯一の違いは、パラメータの位置です。locate には開始位置の追加パラメータがあるという事実を除けば、これら 2 つは同じです。

find_in_set() は特別ですが、これらはすべて指定された文字列内で検索される部分文字列の位置を返します。

最初の 3 つは like を使用するよりもわずかに高速です。（ただし、これら 4 つの関数はいずれもインデックスを使用できません）

以下はMySQLでのLikeの使用法の紹介です。

MySQL の LIKE ステートメントのワイルドカード: パーセント記号、アンダースコア、エスケープ

%: 1 つ以上の文字を表します。あらゆるタイプと長さの文字に一致します。

SQLコード

select * from user where username like '%huxiao';

select * from user where username like 'huxiao%';

select * from user where username like '%huxiao%';

さらに、u_nameに「三」と「猫」の両方を含むレコードを検索する必要がある場合は、and条件を使用します。

SELECT * FROM ［user］ WHERE u_name LIKE '%三%' AND u_name LIKE '%貓%'

SELECT * FROM ［user］ WHERE u_name LIKE '%三%貓%'を使用する場合

「三脚猫」は検索できますが、条件に合う「张猫三」は検索できません。

_: は任意の 1 文字を表します。任意の 1 文字と一致します。これは、式ステートメントの文字の長さを制限するためによく使用されます。(中国語の文字を表すことができます)

SQLコード

select * from user where username like '_';

select * from user where username like 'huxia_';

select * from user where username like 'h_xiao';

本当に % または _ をチェックしたい場合はどうすればいいでしょうか?エスケープを使用する場合、エスケープ文字の後の % または _ はワイルドカードとして使用されません。先頭にエスケープ文字がない場合でも、% と _ はワイルドカードとして機能することに注意してください。

SQLコード

select username from gg_user where username like '%xiao/_%' escape '/';

select username from gg_user where username like '%xiao/%%' escape '/';

MySQL ワイルドカード

SQL パターンマッチングでは、任意の 1 文字に一致させるには「_」を使用し、任意の数の文字 (0 文字を含む) に一致させるには「%」を使用できます。 MySQL では、SQL モードはデフォルトで大文字と小文字を区別しません。以下にいくつかの例を示します。

SQL モードを使用する場合、= または != は使用できないことに注意してください。代わりに、LIKE または NOT LIKE 比較演算子を使用します。

「b」で始まる名前を検索するには:

mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+---------+---------+----------+------------+------------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+--------+---------+---------+----------+------------+------------+
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
| クッパ | ダイアン | 犬 | 男性 | 1989-08-31 | 1995-07-29 |
+--------+---------+---------+----------+------------+------------+

「fy」で終わる名前を検索するには:

mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+---------+---------+---------+---------+-------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+--------+---------+---------+---------+---------+-------+
| ふわふわ | ハロルド | 猫 | f | 1993-02-04 | NULL |
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
+--------+---------+---------+---------+---------+-------+

「w」を含む名前を検索するには:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+--------+---------+----------+------------+------------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+----------+--------+---------+----------+------------+------------+
| 爪 | グウェン | 猫 | m | 1994-03-17 | NULL |
| クッパ | ダイアン | 犬 | 男性 | 1989-08-31 | 1995-07-29 |
| ウィスラー | グウェン | 鳥 | NULL | 1997-12-09 | NULL |
+----------+--------+---------+----------+------------+------------+

ちょうど 5 文字を含む名前を検索するには、「_」パターン文字を使用します。

mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+---------+---------+----------+--------+-------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+-------+---------+---------+----------+--------+-------+
| 爪 | グウェン | 猫 | m | 1994-03-17 | NULL |
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
+-------+---------+---------+----------+--------+-------+

MySQL が提供するもう 1 つのタイプのパターンマッチングは、拡張正規表現を使用することです。このようなパターンの一致をテストする場合は、REGEXP 演算子と NOT REGEXP 演算子 (または同義語の RLIKE と NOT RLIKE) を使用します。

拡張正規表現の文字には次のようなものがあります。

「.」は任意の 1 文字に一致します。

文字クラス "[...]" は、角括弧内の任意の文字に一致します。

たとえば、「[abc]」は「a」、「b」、または「c」に一致します。文字の範囲に名前を付けるには、「-」を使用します。 "[az]" は任意の小文字に一致し、"[0-9]" は任意の数字に一致します。

「 * 」は、その前に続く 0 個以上の文字と一致します。

たとえば、「x*」は任意の数の「x」文字に一致し、「[0-9]*」は任意の数の数字に一致し、「.*」は任意の数の任意の文字に一致します。

正規表現では大文字と小文字が区別されますが、必要に応じて文字クラスを使用して両方を一致させることもできます。

たとえば、「[aA]」は小文字または大文字の「a」に一致し、「[a-zA-Z]」は大文字と小文字のどちらの文字にも一致します。

パターンは、テスト対象の値のどこかに出現する場合に一致します (SQL パターンは、値全体が一致する限り一致します)。

テストする値の先頭または末尾に一致するようにパターンを配置するには、パターンの先頭に「^」を使用するか、パターンの末尾に「$」を使用します。

拡張正規表現がどのように機能するかを説明するために、上記の LIKE クエリを REGEXP を使用して以下のように書き換えます。

「b」で始まる名前を検索するには、名前の先頭に一致するように「^」を使用し、小文字または大文字の「b」に一致するように「[bB]」を使用します。

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
+--------+---------+---------+----------+------------+------------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+--------+---------+---------+----------+------------+------------+
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
| クッパ | ダイアン | 犬 | 男性 | 1989-08-31 | 1995-07-29 |
+--------+---------+---------+----------+------------+------------+

「fy」で終わる名前を検索するには、名前の末尾に一致する「$」を使用します。

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";
+--------+---------+---------+---------+---------+-------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+--------+---------+---------+---------+---------+-------+
| ふわふわ | ハロルド | 猫 | f | 1993-02-04 | NULL |
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
+--------+---------+---------+---------+---------+-------+

「w」を含む名前を検索するには、小文字または大文字の「w」に一致する「[wW]」を使用します。

mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";
+----------+--------+---------+----------+------------+------------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+----------+--------+---------+----------+------------+------------+
| 爪 | グウェン | 猫 | m | 1994-03-17 | NULL |
| クッパ | ダイアン | 犬 | 男性 | 1989-08-31 | 1995-07-29 |
| ウィスラー | グウェン | 鳥 | NULL | 1997-12-09 | NULL |
+----------+--------+---------+----------+------------+------------+

正規表現が値のどこかに出現すればパターンが一致するため、SQL パターンを使用する場合のように、前のクエリでパターンの両側にワイルドカードを配置して値全体に一致させる必要はありません。

ちょうど 5 文字を含む名前を検索するには、名前の先頭と末尾に「^」と「$」を使用し、その間に「.」を 5 回入力します。

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
+-------+---------+---------+----------+--------+-------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+-------+---------+---------+----------+--------+-------+
| 爪 | グウェン | 猫 | m | 1994-03-17 | NULL |
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
+-------+---------+---------+----------+--------+-------+

「{n}」演算子「n 回繰り返す」を使用して、前のクエリを書き換えることもできます。

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";
+-------+---------+---------+----------+--------+-------+
| 名前 | 所有者 | 種 | 性別 | 生年月日 | 死亡日 |
+-------+---------+---------+----------+--------+-------+
| 爪 | グウェン | 猫 | m | 1994-03-17 | NULL |
| バフィー | ハロルド | 犬 | 女性 | 1989-05-13 | NULL |
+-------+---------+---------+----------+--------+-------+

上記はMySQLのあいまい検索方法Likeの使用例です。locate、instr、positionの3つの組み込み関数と比較すると、パフォーマンスは遅くなります。どのあいまい検索を使用しても、クエリ時に大量のサーバーリソースを消費します。したがって、実際の作業ではあいまい検索をできるだけ使用しないようにする必要があります。

以下もご興味があるかもしれません: