Detaillierte Erläuterung der Verwendung der integrierten Funktion „locate instr position find_in_set“ in der effizienten Fuzzy-Suche von MySQL

Es gibt die folgenden vier häufig verwendeten Methoden:

1. Verwenden Sie die Methode „locate()“

Allgemeine Verwendung:

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

Ähnlich wie indexOf(); in Java

Allerdings gibt locate() ein Ergebnis größer als 0 zurück, solange es das Ergebnis findet (auch wenn der Abfrageinhalt ganz am Anfang steht), und gibt 0 zurück, wenn es nicht gefunden wird.

Geben Sie die Startposition an:

SELECT LOCATE('bar','foobarbar',5); (Suche beginnt an der fünften Position von foobarbar)

2. Verwenden Sie die Funktion instr() (angeblich eine Aliasfunktion von locate())

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

Der einzige Unterschied ist der Speicherort des Abfrageinhalts

3. Verwenden Sie die Methode position() (die auch als Aliasfunktion der Methode locate() bezeichnet wird und dieselbe Funktion hat).

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

Allerdings wird nicht mehr nach dem Rückgabewert, sondern nach dem Schlüsselwort in

4. Verwenden Sie die Funktion find_in_set()

Beispiel: find_in_set(str,strlist), strlist muss eine durch Kommas getrennte Zeichenfolge sein.

Wenn sich der String str in der Stringliste befindet, die aus N Teilstrings in strlist besteht, liegt der Rückgabewert zwischen 1 und N.

SQL>SELECT FIND_IN_SET('b','a,b,c,d');
---------------------------------------------+
|SELECTFIND_IN_SET('b','a,b,c,d')|
---------------------------------------------+
|2|
---------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

Zusammenfassung: Der einzige Unterschied zwischen Locate, Position und Instr ist die Position der Parameter. Abgesehen davon, dass Locate einen zusätzlichen Parameter für die Startposition hat, sind beide gleich.

find_in_set() ist etwas Besonderes, aber alle geben die Position der Teilzeichenfolge zurück, die in der angegebenen Zeichenfolge gefunden werden soll.

Die ersten drei sind etwas schneller als die Verwendung von „like“. (Allerdings kann keine dieser vier Funktionen Indizes verwenden)

Nachfolgend finden Sie eine Einführung in die Verwendung von Like in MySQL

Platzhalter in MySQLs LIKE-Anweisung: Prozentzeichen, Unterstrich und Escape

%: steht für ein oder mehrere beliebige Zeichen. Stimmt mit Zeichen beliebigen Typs und jeder Länge überein.

SQL-Code

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

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

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

Wenn Sie außerdem Datensätze finden müssen, die sowohl "三" als auch "猫" in u_name enthalten, verwenden Sie die Bedingung und

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

Wenn Sie SELECT * FROM ［user］ WHERE u_name LIKE '%三%貓%' verwenden

Sie können zwar nach „三脚猫“ suchen, jedoch nicht nach „张猫三“, was den Kriterien entspricht.

_: steht für ein beliebiges einzelnes Zeichen. Entspricht einem einzelnen beliebigen Zeichen, das häufig verwendet wird, um die Zeichenlänge der Ausdrucksanweisung zu begrenzen: (kann ein chinesisches Zeichen darstellen)

SQL-Code

select * from user where username like '_';

select * from user where username like 'huxia_';

select * from user where username like 'h_xiao';

Was ist, wenn ich wirklich % oder _ überprüfen möchte? Bei Verwendung von Escape werden das % oder _ nach dem Escape-Zeichen nicht als Platzhalter verwendet. Beachten Sie, dass das % und _ ohne das Escape-Zeichen davor weiterhin als Platzhalter fungieren.

SQL-Code

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

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

MySQL-Platzhalter

Mit der SQL-Musterübereinstimmung können Sie „_“ für die Übereinstimmung mit jedem einzelnen Zeichen und „%“ für die Übereinstimmung mit einer beliebigen Anzahl von Zeichen (einschließlich Nullzeichen) verwenden. In MySQL wird bei den SQL-Modi standardmäßig die Groß-/Kleinschreibung nicht beachtet. Nachfolgend sind einige Beispiele aufgeführt.

Beachten Sie, dass Sie im SQL-Modus nicht = oder != verwenden können. Verwenden Sie stattdessen die Vergleichsoperatoren LIKE oder NOT LIKE.

So finden Sie Namen, die mit „b“ beginnen:

mysql> SELECT * FROM Haustier WHERE Name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
| Bowser | Diane | Hund | m | 31.08.1989 | 29.07.1995 |
+--------+--------+---------+------+------------+------------+

So finden Sie Namen, die mit „fy“ enden:

mysql> SELECT * FROM Haustier WHERE Name LIKE "%fy";
+--------+--------+---------+------+------------+-----------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+--------+--------+---------+------+------------+-----------+
| Fluffy | Harold | Katze | f | 04.02.1993 | NULL |
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
+--------+--------+---------+------+------------+-----------+

So finden Sie Namen, die ein „w“ enthalten:

mysql> SELECT * FROM Haustier WHERE Name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+----------+-------+---------+------+------------+------------+
| Krallen | Gwen | Katze | m | 17.03.1994 | NULL |
| Bowser | Diane | Hund | m | 31.08.1989 | 29.07.1995 |
| Whistler | Gwen | Vogel | NULL | 09.12.1997 | NULL |
+----------+-------+---------+------+------------+------------+

Um Namen zu finden, die genau aus 5 Zeichen bestehen, verwenden Sie das Musterzeichen „_“:

mysql> SELECT * FROM Haustier WHERE Name LIKE "_____";
+-------+--------+---------+------+------------+-----------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+-------+--------+---------+------+------------+-----------+
| Krallen | Gwen | Katze | m | 17.03.1994 | NULL |
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
+-------+--------+---------+------+------------+-----------+

Eine andere von MySQL bereitgestellte Art der Musterübereinstimmung ist die Verwendung erweiterter regulärer Ausdrücke. Wenn Sie nach einer Übereinstimmung mit solchen Mustern suchen, verwenden Sie die Operatoren REGEXP und NOT REGEXP (oder RLIKE und NOT RLIKE, die Synonyme sind).

Einige Zeichen für erweiterte reguläre Ausdrücke sind:

"." entspricht jedem einzelnen Zeichen.

Eine Zeichenklasse "[...]" entspricht jedem Zeichen innerhalb der eckigen Klammern.

Beispielsweise entspricht „[abc]“ „a“, „b“ oder „c“. Um einen Zeichenbereich zu benennen, verwenden Sie ein "-". „[az]“ entspricht jedem Kleinbuchstaben und „[0-9]“ entspricht jeder Ziffer.

„ * “ stimmt mit null oder mehr der davor stehenden Zeichen überein.

Beispielsweise entspricht „x*“ einer beliebigen Anzahl von „x“-Zeichen, „[0-9]*“ entspricht einer beliebigen Anzahl von Ziffern und „.*“ entspricht einer beliebigen Anzahl von beliebigen Zahlen.

Bei regulären Ausdrücken muss die Groß-/Kleinschreibung beachtet werden. Wenn Sie möchten, können Sie jedoch eine Zeichenklasse verwenden, um beides abzugleichen.

Beispielsweise entspricht „[aA]“ dem großen oder kleinen „a“ und „[a-zA-Z]“ entspricht jedem Buchstaben in beiden Fällen.

Das Muster stimmt überein, wenn es irgendwo im getesteten Wert vorkommt (SQL-Muster stimmen überein, solange sie mit dem gesamten Wert übereinstimmen).

Um ein Muster so zu positionieren, dass es mit dem Anfang oder Ende des getesteten Werts übereinstimmen muss, verwenden Sie „^“ am Anfang des Musters oder „$“ am Ende des Musters.

Um zu veranschaulichen, wie erweiterte reguläre Ausdrücke funktionieren, wird die oben gezeigte LIKE-Abfrage unten mit REGEXP umgeschrieben:

Um Namen zu finden, die mit „b“ beginnen, verwenden Sie „^“ für den Anfang des Namens und „[bB]“ für ein großes oder kleines „b“:

mysql> SELECT * FROM Haustier WHERE Name REGEXP "^[bB]";
+--------+--------+---------+------+------------+------------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
| Bowser | Diane | Hund | m | 31.08.1989 | 29.07.1995 |
+--------+--------+---------+------+------------+------------+

Um Namen zu finden, die mit „fy“ enden, verwenden Sie „$“, um das Ende des Namens abzugleichen:

mysql> SELECT * FROM Haustier WHERE Name REGEXP "fy$";
+--------+--------+---------+------+------------+-----------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+--------+--------+---------+------+------------+-----------+
| Fluffy | Harold | Katze | f | 04.02.1993 | NULL |
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
+--------+--------+---------+------+------------+-----------+

Um Namen zu finden, die ein „w“ enthalten, verwenden Sie „[wW]“, um entweder ein kleines oder ein großes „w“ zu finden:

mysql> SELECT * FROM Haustier WHERE Name REGEXP "[wW]";
+----------+-------+---------+------+------------+------------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+----------+-------+---------+------+------------+------------+
| Krallen | Gwen | Katze | m | 17.03.1994 | NULL |
| Bowser | Diane | Hund | m | 31.08.1989 | 29.07.1995 |
| Whistler | Gwen | Vogel | NULL | 09.12.1997 | NULL |
+----------+-------+---------+------+------------+------------+

Da das Muster übereinstimmt, wenn an beliebiger Stelle im Wert ein regulärer Ausdruck vorkommt, ist es nicht erforderlich, in der vorherigen Abfrage auf beiden Seiten des Musters ein Platzhalterzeichen einzufügen, damit es mit dem gesamten Wert übereinstimmt, wie dies bei Verwendung eines SQL-Musters der Fall wäre.

Um Namen zu finden, die genau aus 5 Zeichen bestehen, verwenden Sie "^" und "$", um den Anfang und das Ende des Namens abzugleichen, und 5 Instanzen von "." dazwischen:

mysql> SELECT * FROM Haustier WHERE Name REGEXP "^.....$";
+-------+--------+---------+------+------------+-----------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+-------+--------+---------+------+------------+-----------+
| Krallen | Gwen | Katze | m | 17.03.1994 | NULL |
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
+-------+--------+---------+------+------------+-----------+

Sie können die vorherige Abfrage auch mit dem Operator „{n}“ (n-mal wiederholen) neu schreiben:

mysql> SELECT * FROM Haustier WHERE Name REGEXP "^.{5}$";
+-------+--------+---------+------+------------+-----------+
| Name | Besitzer | Art | Geschlecht | Geburt | Tod |
+-------+--------+---------+------+------------+-----------+
| Krallen | Gwen | Katze | m | 17.03.1994 | NULL |
| Buffy | Harold | Hund | f | 13.05.1989 | NULL |
+-------+--------+---------+------+------------+-----------+

Oben wird die MySQL-Fuzzy-Suchmethode Like verwendet. Im Vergleich zu den drei integrierten Funktionen Locate, Instr und Position ist die Leistung langsamer. Unabhängig davon, welche Fuzzy-Suche verwendet wird, werden bei der Abfrage viele Serverressourcen verbraucht. Daher sollten wir versuchen, die Fuzzy-Suche bei der tatsächlichen Arbeit so wenig wie möglich zu verwenden.