MySQL ファジークエリの使用法 (通常、ワイルドカード、組み込み関数)

ユーザー名が「%陈哈哈%」で趣味が「%牛逼」であるテーブルから SELECT * を実行します。

これは、MySQL でよく使用されるあいまいクエリ方法で、ワイルドカード % を使用してマッチングを行います。実際、これは氷山の一角にすぎません。MySQL にはあいまいマッチングをサポートする方法が多数あり、それぞれに利点があります。さて、今日は MySQL のスカートをめくって、ファジー クエリの下に隠れているあまり知られていない優れた機能がいくつあるかを見てみましょう。

1. MySQL ワイルドカード ファジー クエリ (%,_)

1-1. ワイルドカードの分類

• 「%」パーセントワイルドカード: 任意の文字の任意の回数の出現を表します (0 回も可)。
• 「_」アンダースコア ワイルドカード: 1 文字のみに一致し、それ以上でもそれ以下でもなく、1 文字のみに一致することを示します。もちろん、「陈____」も好きになれます。数に制限はありません。
• LIKE 演算子: LIKE は、後続の検索モードで直接の等価一致ではなくワイルドカードを使用するように MySQL に指示するために使用されます。ただし、LIKE の後にワイルドカードがない場合、SQL 実行の最適化中に、LIKE はデフォルトで "=" として実行されます。

注意: like 演算子の後にユニバーサル マッチャー (% または _) が続かない場合、効果は "=" と同じになります。 SQL が最適化されると、クエリ オプティマイザーは、like に対してデフォルトで "=" を使用します。SELECT * FROM movies WHERE movie_name like '唐伯虎'; は、movie_name="唐伯虎" の結果にのみ一致し、"唐伯虎点秋香" または "唐伯虎点烟" の結果には一致しません。

1-2. ワイルドカードの使用

1) % ワイルドカード:

-- 「網」という単語を含むデータのあいまい一致

app_info から SELECT * を実行します。appName は '%网%' のような値になります。

-- 「网」で終わるデータのあいまい一致

app_info から * を選択し、 appName が '%网' であるかどうかを確認します。 

-- 「网」で始まるデータのあいまい一致

app_info から * を選択し、 appName が '网%' のような場合; 

-- 完全一致、appName が '网' の場合、appName = '网' と同等です。

appName = '网' の場合、app_info から * を選択します。
-- SELECT * from app_info where appName like '网' と同等です。

-- 「TuTu オンライン配車ドライバー端末、オンライン配車プラットフォーム」など、「xxx ネット xxx 車 xxx」を含むデータのあいまい一致

app_info から * を選択し、 appName が '%网%车%' のような場合;

2) _ ワイルドカード:

-- 「联家网」など、「网」で終わり、長さが 3 文字のデータをクエリします。

app_info から * を選択し、 appName が '__网' であるかどうかを調べます。 

注: '%__网、__%网' は '%网' と同等です

-- クエリの最初の 3 文字は XX.com であり、次の文字は「Chengtong ネットワーク ディスク、mold ネットワーク プラットフォーム」のように任意に一致できます。

app_info から * を選択し、 appName が '__网%' のような場合; 

-- 「xx网x车xxx」を含むデータのあいまい一致（例: 「Ctrip Online Car-hailing Client」）

app_info から * を選択し、 appName が '__网_车%' のような場合; 

注記：

大文字と小文字に注意してください。あいまい一致、つまりテキストの一致を使用する場合、MySQL のデフォルト設定では大文字と小文字は区別されません。他の人の MySQL データベースを使用する場合は、大文字と小文字が区別されるかどうかに注意してください。大文字と小文字が区別されるかどうかは、ユーザーが MySQL をどのように構成するかによって異なります。大文字と小文字が区別される場合、Test12 のようなレコードは、"test__" のような一致条件と一致しません。
末尾のスペースに注意してください。「%test」は「test」のようなレコードと一致しません。
NULL および % ワイルドカード文字は任意の文字と一致しますが、NULL とは一致しないことに注意してください。つまり、SELECT * FROM blog where title_name like '%'; は、title_name が NULL であるレコードとは一致しません。

1-3. ヒントと提案:

ご覧のとおり、MySQL ワイルドカードは非常に便利です。ただし、この機能には代償が伴います。ワイルドカード検索は一般に、前述の他の検索よりも処理に時間がかかり、メモリやその他のリソースをより多く消費します。ワイルドカードを使用する際に覚えておくべきヒントをいくつか紹介します。

• ワイルドカードを過度に使用しないでください。他の演算子で同じ目的を達成できる場合は、他の演算子を使用する必要があります。
• ワイルドカードを使用する必要がある場合は、絶対に必要な場合を除き、検索パターンの先頭で使用しないでください。 MySQL は where の後に左から右に実行されるため、ワイルドカードを検索パターンの先頭 (一番左側) に配置すると、検索は最も遅くなります (データベース全体をスキャンする必要があるため)。
• ワイルドカード文字の配置に注意してください。間違った場所に配置すると、必要なデータが返されない可能性があります。

注意深い友人は、データ内に「%」や「_」などの記号がある場合、ワイルドカードと競合しないかどうかに気付くでしょう。

app_info から * を選択し、 appName が '%%%' であることを確認します。
app_info から * を選択し、 appName が '%_%' であることを確認します。

実際、上記の 2 つの SQL ステートメントは、「%」と「_」で指定されたデータではなく、テーブル データ全体をクエリします。エスケープするには、ここで ESCAPE キーワードが必要です。

下に示すように、ESCAPE の後に何かを含む文字が続きます。MySQL では、その記号はエスケープ文字として扱われます。私は通常、これを「/」と書きます。次に、C 言語のエスケープ文字 ('\n' や '\t' など) と同様に、エスケープする必要がある % 記号の前にこの文字を記述します。

app_info から * を選択し、 appName を LIKE '%/_%' ESCAPE '/' にします。 

しかし、この状況に対するより高度な解決策はあるのでしょうか?あなたのコードをチェックする同僚やリーダーがあなたを新しい目で見るようになるようなもの~~

もちろん、MySQLのあいまい一致方法の2番目のタイプである組み込み関数クエリを見てみましょう。

2. MySQL組み込み関数の取得（locate、position、instr）

前回の記事に引き続き、マッチングは組み込み関数のlocate、position、instrを通じて実行されます。これはJavaのstr.contains()メソッドに相当します。文字列内の一致するコンテンツの位置を返すため、効率性と使いやすさの点でワイルドカードマッチングよりも優れています。

app_info から * を選択し、INSTR(`appName`, '%') > 0 とします。
app_info から * を選択し、LOCATE('%', `appName`) > 0 であるかどうかを確認します。
app_info から POSITION( '%' IN `appName`) > 0 となる SELECT * を実行します。

上記のように、3 つの組み込み関数のデフォルト値は > 0 なので、次の > 0 は追加してもしなくてもかまいません。追加すると読みやすくなります。

さて、これら 3 つの組み込み関数の使い方を見てみましょう。

まず、MySQL の添え字は左から右に 1 から始まることを明確にしておきましょう。Java では左端の添え字が 0 ですが、MySQL では添え字が 0 の場合は存在しないことを意味します。

2-1. LOCATE() 関数

構文: LOCATE（substr,str）

str 内で substr が最初に出現する位置を返します。 substr が str 内に存在しない場合、戻り値は 0 になります。substr が str 内に存在する場合、戻り値は str 内で substr が最初に出現する位置になります。

注: LOCATE(substr, str) と POSITION(substr IN str) は同義語であり、同じ機能を持ちます。

構文: LOCATE(substr, str, [pos])

位置 pos から始まる文字列 str 内の部分文字列 substr が最初に出現する位置。 substr が str にない場合は 0 を返します。 substr または str が NULL の場合、NULL を返します。

SELECT 位置指定('a', 'バナナ'); -- 2
SELECT 位置指定('a', 'バナナ', 3); -- 4
SELECT 位置指定('z', 'バナナ'); -- 0
SELECT 位置指定(10, 'バナナ'); -- 0
SELECTlocate(NULL, 'バナナ'); -- null
SELECT 位置指定('a', NULL); -- null

例:

-- あいまい一致には LOCATE キーワードを使用します。これは「like '%网%'」と同等です。

app_info から * を選択し、LOCATE('网', `appName`) > 0 とします。

-- あいまい一致には LOCATE キーワードを使用し、2 番目の文字から「网」を一致させると、「NetEase Cloud Games、Wanglai Merchants」などのデータが除外されます。

app_info から * を選択し、LOCATE('网', `appName`, 2) > 0 とします。

2-2. POSITION() メソッド

構文: POSITION(substr IN substr)

このメソッドは、locate(substr, str) メソッドと同じ機能を持つため、locate(substr, str) メソッドのエイリアスとして理解できます。

例:

-- あいまい一致には POSITION キーワードを使用します。これは「like '%网%'」と同等です。

app_info から POSITION('网' IN `appName`) で * を選択します。

2-3. INSTR() メソッド

構文: INSTR(str,substr)

文字列 str 内の部分文字列 substr が最初に出現する位置を返します。 INSTR() の 2 つの引数形式は、引数の順序が逆であることを除いて、LOCATE() の形式と同じです。

例:

-- あいまい一致には INSTR キーワードを使用します。これは LIKE と同じ機能を持ち、"like '%网%'" と同等です。

app_info から * を選択し、 INSTR(`appName`, '网');

-- instr 関数は通常、文字列内の文字の位置を取得するために使用され、これは「like '%网%'」と同等です。

app_info から * を選択し、INSTR(`appName`, '网') > 0 とします。

3. regexpとrlikeに基づくMySQL正規マッチングクエリ

MySQL の regexp と rlike キーワードは同義語であり、同じ機能を持ちます。この記事は正規表現に基づいています。

REGEXP はワイルドカード「%、_」をサポートしていませんが、通常の一致ルールをサポートしています。これは、より詳細でエレガントな一致方法です。見てみましょう。

-- 正規表現に含まれるパラメータ型は次のとおりです

-- REGEXP '网' は '%网%' と同等です

app_info から * を選択し、 appName REGEXP '网' を選択します。
-- SELECT * from app_info where appName like '%网%' と同等です。

3-1. 正規表現内の OR: |

機能: 複数の文字列のうち、1つまたは同等のものを検索できます。

-- 「|」または「or」記号をサポートし、「中国」または「インターネット」または「大学」を含むデータを照合し、複数の重複をサポートします。

app_info から * を選択し、 appName REGEXP '中国|インターネット|大学' を指定します。 

-- 「中国」と「网」に同時にヒットするデータを一致させるには、「.+」を使用してそれらを接続します。これは、中国 xxxx ネットワークを意味します。途中の文字数は任意で、順序を逆にすることはできません。

app_info から * を選択し、 appName REGEXP '中国.+网' を指定します。 

3-2. REGEXPにおける正規表現マッチング: []

機能: [] 記号内の文字の 1 つに一致し、正規表現の解析をサポートします。

-- 英語の文字を含むデータに一致します。デフォルトでは大文字と小文字は区別されません。

app_info から * を選択し、 appName REGEXP '[az]' を指定します。 

-- 同じように、セットを否定して「正規表現ではない」を追加するだけです。以下では詳細には触れません。

appName が REGEXP '[az]' ではない app_info から * を選択します。 

-- 大文字の英語の文字を含むデータと一致します。デフォルトでは大文字と小文字は区別されず、「BINARY」キーワードを追加する必要があります。たとえば、appName REGEXP BINARY 'Hello'

-- 大文字と小文字の区別について: MySQL (バージョン 3.23.4 以降) の正規表現マッチングでは、大文字と小文字は区別されません。

app_info から * を選択し、 appName REGEXP BINARY '[AZ]' を指定します。 

-- 数字を含むデータに一致します

app_info から * を選択し、 appName REGEXP '[0-9]' を指定します。

-- 数字または英語を含むデータに一致します。

app_info から * を選択し、 appName REGEXP '[a-z0-9]' を選択します。

az、0-9はすべて1つの単位とみなされ、余分な記号を追加しないでください。数日前、私は特別なケース、非常に興味深いバグを見つけたので、彼と共有します

-- クエリ ステートメントを記述するときに、余分な「|」記号を追加しましたが、これは「または」だと思い込んでいて注意を払っていませんでしたが、結果の数が異なるとは予想していませんでした。

SELECT * from app_info where appName REGEXP '[567]'; -- 87 件の結果 SELECT * from app_info where appName REGEXP '[5|6|7]'; -- 88 件の結果

混乱しています。どれが間違っているか見てみましょう。

-- 「|」記号もマッチングに参加し、単位として認識されることがわかります。偶然にも、完全に一致するデータ「ワイヤレスミキサー」があります。このDJは一体何者ですか？

app_info から * を選択、 appName REGEXP '[5|6|7]' かつ pid が存在しない (app_info から pid を選択、 appName REGEXP '[567]'); 

-- 5、6、7のいずれかで始まるデータをクエリします

app_info から * を選択し、 appName REGEXP '^[5|6|7]' を指定します。 

-- 5、6、または7のいずれかで終わるクエリデータ

app_info から * を選択し、 appName REGEXP '[5|6|7]$' を指定します。 

ヒント: MySQL では、 UTF-8 中国語文字 = 3 バイト、GBK 中国語文字 = 2 バイト

-- appName バイト長が 10 でコンテンツが任意のクエリデータ

app_info から * を選択し、 appName REGEXP '^.{10}$' を指定します。 

-- appName バイトの長さが 10 で、すべてが英語であるクエリ データ

app_info から * を選択し、 appName を REGEXP '^[az]{10}$' にします。 

-- appNameのバイト長が10で、すべて大文字の英語であるデータを照会し、BINARYを追加します。

app_info から * を選択し、 appName REGEXP BINARY '^[AZ]{10}$' を指定します。 

-- version_name のバイト長が 6 で、すべてが数字または「.」であるデータをクエリします。

app_info から * を選択し、version_name を REGEXP '^[0-9.]{6}$' とします。 

-- version_name のバイト長が 6 で、すべてが数字または "." であるクエリ データ。最初の桁は 1 である必要があります。

app_info から * を選択し、version_name を REGEXP '^1[0-9.]{5}$' とします。 

-- version_name のバイト長が 6 で、すべてが数字または「.」であるクエリ データ。最初の桁が 1 で、最後の桁が 7 です。

app_info から * を選択し、version_name を REGEXP '^1[0-9.]{4}7$' とします。 

-- version_name のバイト長が6 文字を超え、すべてが数字または "." であるクエリ データ。最初の文字は 1 で、最後の文字は 7 である必要があります。

app_info から * を選択し、version_name を REGEXP '^1[0-9.]{4,}7$' とします。 

-- version_name バイト長が6 ～ 8 文字で、すべてが数字または「.」であるデータを照会します。最初の桁は 1 で、最後の桁は 7 である必要があります。

app_info から * を選択し、version_name を REGEXP '^1[0-9.]{4,6}7$' とします。 

-- 最初の文字は中国語ではありません

app_info から * を選択し、 appName REGEXP '^[ -~]' を指定します。 

-- 最初の文字は中国語です

app_info から * を選択し、 appName REGEXP '^[^ -~]' を指定します。 

-- 中国語の文字を含まないデータをクエリする

app_info から * を選択し、 appName を REGEXP '^([az]|[0-9]|[AZ])+$' にします。 

-- 5またはFで始まり、英語を含むデータ

app_info から * を選択し、 appName REGEXP BINARY '^[5F][a-zA-Z].' を指定します。 

. などの特殊記号を一致させるには、\\ を追加する必要があります (スラッシュが 2 つあることに注意してください)。ただし、[] で囲まれている場合は、追加する必要はありません。

-- . を含む名前に一致します。select * from app_info where appName regexp '\\.';
-- . を含む名前に一致します。select * from app_info where appName regexp '[.]'; 

3-3. 文字クラスのマッチング（posix）

MySQL には、さまざまなタイプの一致を表すことができる特別な記号がいくつかあります。

-- 数字を含む名前に一致します

app_info から * を選択します。ここで、 appName は正規表現 '[[:digit:]]' です。

このタイプの他の文字クラスには次のものがあります:

このタイプの文字クラスでは、メインの文字クラスの周囲に [] の追加レイヤーが必要です。

3-4. [:<:] と [:>:]

上記の文字クラスには、特別な文字が 2 つあります。これら 2 つは位置に関するものです。[:<:] は単語の先頭に一致し、[:>:] は単語の末尾に一致します。これらは ^ や $ とは異なります。

後者は全体の始まりと終わりに一致しますが、前者は単語の始まりと終わりに一致します。

-- aで始まる文字列（abcdなど）のみに一致します

app_info から * を選択します。appName が正規表現 '^a' の場合。

-- a で始まる単語全体に一致できます。また、dance after のように、a で始まる途中の単語にも一致できます。

app_info から * を選択します。ここで、 appName は正規表現 '[[:<:]]a' です。 

[[:<:]] と [[:>:]] は、それぞれ単語の先頭と末尾の空の文字列に一致します。この単語の先頭と末尾は alnum に含まれる文字ではなく、アンダースコアにすることもできません。

「a 単語 a」を選択 REGEXP "[[:<:]]単語[[:>:]]"; -- 1 (一致) 
 
select "a xword a" REGEXP "[[:<:]]word[[:>:]]"; -- 0 (一致しないことを意味する) 
 
select "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -- 1 (一致)

IV. 結論

さて、この記事はここで終わりです。これを見ることができる人はここにいる運命です。この記事が MySQL のさらなる理解に役立つことを願っています。より関連性の高い MySQL ファジー クエリ コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。 今後とも 123WORDPRESS.COM をよろしくお願いいたします。