Verwendung von MySQL-Fuzzy-Abfragen (regulär, Platzhalter, integrierte Funktion)

SELECT * aus Tabelle, in der der Benutzername wie „%陈哈哈%“ und das Hobby wie „%牛逼“ ist.

Dies ist eine Fuzzy-Abfragemethode, die wir häufig in MySQL verwenden und die den Platzhalter % zum Abgleichen verwendet. Tatsächlich ist dies nur die Spitze des Eisbergs. In MySQL gibt es viele Methoden, die Fuzzy-Matching unterstützen, und jede hat ihre eigenen Vorteile. Heute möchte ich Ihnen einen Blick hinter die Kulissen von MySQL gewähren und Ihnen zeigen, wie viele wenig bekannte gute Dinge sich unter der Fuzzy-Abfrage verbergen.

1. MySQL-Platzhalter-Fuzzy-Abfrage (%,_)

1-1. Klassifizierung von Platzhaltern

• „%“ Prozent-Platzhalter: steht für eine beliebige Anzahl von Vorkommen eines beliebigen Zeichens (kann 0-mal vorkommen).
• Unterstrich-Platzhalter „_“: Gibt an, dass nur ein einziges Zeichen übereinstimmen kann, nicht mehr und nicht weniger, nur ein Zeichen. Natürlich können Sie auch „陈____“ liken, die Anzahl ist nicht begrenzt.
• LIKE-Operator: LIKE wird verwendet, um MySQL anzuweisen, für den nachfolgenden Suchmodus Platzhalter anstelle des direkten Gleichheitsabgleichs zu verwenden. Wenn jedoch nach LIKE kein Platzhalter steht, wird LIKE während der SQL-Ausführungsoptimierung standardmäßig als "=" ausgeführt.

Hinweis: Wenn auf den Like-Operator kein universeller Matcher (% oder _) folgt, ist die Wirkung dieselbe wie bei „=“. Wenn SQL optimiert ist, verwendet der Abfrageoptimierer standardmäßig "=" für "wie". SELECT * FROM movies WHERE movie_name like '唐伯虎'; kann nur mit dem Ergebnis von movie_name="唐伯虎" übereinstimmen, aber nicht mit dem Ergebnis von "唐伯虎点秋香" oder "唐伯虎点烟".

1-2. Verwendung von Platzhaltern

1) % Platzhalter:

- Fuzzy-Matching von Daten, die das Wort „网“ enthalten

SELECT * aus app_info, wobei AppName wie „%网%“ ist;

- Fuzzy-Matching von Daten, die mit „网“ enden

SELECT * aus app_info, wobei AppName wie „%网“ ist; 

- Fuzzy-Matching von Daten, die mit „网“ beginnen

SELECT * aus app_info, wobei appName wie '网%' ist; 

-- Genaue Übereinstimmung, App-Name wie „网“ ist gleichbedeutend mit: App-Name = „网“

SELECT * aus app_info, wobei appName = "App" ist;
– Entspricht „SELECT * from app_info where appName like '网'“;

- Fuzzy-Matching von Daten, die „xxx net xxx car xxx“ enthalten, wie etwa „TuTu Online-Terminal zur Mitfahrvermittlung für Fahrer, Online-Plattform zur Mitfahrvermittlung“

SELECT * aus app_info, wobei App-Name wie „%网%车%“ ist;

2) _ Platzhalter:

- Abfragedaten, die mit „网“ enden und drei Zeichen lang sind, z. B. „联家网“.

SELECT * aus app_info, wobei appName wie '__网' ist; 

Hinweis: „%__网、__%网“ ist gleichbedeutend mit „%网“.

- Die ersten drei Zeichen der Abfrage sind XX.com, und die folgenden Zeichen können beliebig abgeglichen werden, z. B. „Chengtong-Netzwerkdiskette, Mold-Netzwerkplattform“.

SELECT * aus app_info, wobei appName wie '__网%' ist; 

- Fuzzy-Matching von Daten, die „xx网x车xxx“ enthalten, wie etwa: „Ctrip Online Car-hailing Client“

SELECT * aus app_info, wobei appName wie '__网_车%' ist; 

Notiz:

Achten Sie auf Groß- und Kleinschreibung. Bei der Verwendung von Fuzzy-Matching, also dem Abgleichen von Text, wird in der Standardkonfiguration von MySQL nicht zwischen Groß- und Kleinschreibung unterschieden. Wenn Sie die MySQL-Datenbank einer anderen Person verwenden, achten Sie darauf, ob die Groß-/Kleinschreibung beachtet wird. Ob die Groß-/Kleinschreibung beachtet wird, hängt davon ab, wie der Benutzer MySQL konfiguriert. Wenn die Groß-/Kleinschreibung beachtet wird, kann ein Datensatz wie Test12 nicht mit einer Übereinstimmungsbedingung wie „test__“ abgeglichen werden.
Beachten Sie das Leerzeichen am Ende. „%test“ kann nicht mit Datensätzen wie „test“ übereinstimmen.
Beachten Sie, dass die Platzhalterzeichen NULL und % jedem beliebigen Zeichen entsprechen können, jedoch nicht NULL. Das heißt, dass SELECT * FROM blog where title_name like '%'; nicht mit Datensätzen übereinstimmt, bei denen title_name NULL ist.

1-3. Tipps und Vorschläge:

Wie Sie sehen, sind MySQL-Platzhalter sehr nützlich. Diese Funktionalität hat jedoch ihren Preis: Die Verarbeitung von Platzhaltersuchen dauert im Allgemeinen länger als die anderen zuvor besprochenen Suchvorgänge und verbrauchen mehr Speicher und andere Ressourcen. Hier sind einige Tipps, die Sie bei der Verwendung von Platzhaltern beachten sollten.

• Verwenden Sie Platzhalter nicht zu häufig. Wenn andere Operatoren denselben Zweck erreichen können, sollten Sie andere Operatoren verwenden.
• Wenn Sie Platzhalter verwenden müssen, verwenden Sie diese nicht am Anfang eines Suchmusters, es sei denn, dies ist unbedingt erforderlich. Da MySQL nach „where“ von links nach rechts ausgeführt wird, ist die Suche am langsamsten, wenn das Platzhalterzeichen am Anfang des Suchmusters (ganz links) platziert wird (da die gesamte Datenbank gescannt werden muss).
• Beachten Sie sorgfältig die Platzierung der Platzhalterzeichen. Wenn es an der falschen Stelle platziert wird, werden möglicherweise nicht die gewünschten Daten zurückgegeben.

Aufmerksame Freunde werden feststellen, dass, wenn die Daten Symbole wie „%“ und „_“ enthalten, es dann nicht zu Konflikten mit Platzhaltern kommt?

SELECT * aus app_info, wobei Anwendungsname LIKE '%%%' ist;
SELECT * aus app_info, wobei Anwendungsname LIKE '%_%' ist;

Tatsächlich fragen die beiden obigen SQL-Anweisungen die gesamten Tabellendaten ab und nicht die mit „%“ und „_“ angegebenen Daten. Zum Entkommen wird hier das Schlüsselwort ESCAPE benötigt.

Wie unten gezeigt folgt auf ESCAPE ein Zeichen, das etwas enthält. MySQL behandelt dieses Symbol als Escape-Zeichen. Ich schreibe es normalerweise als "/". Schreiben Sie dieses Zeichen dann, genau wie die Escape-Zeichen in der Sprache C, wie '\n' und '\t', vor das %-Zeichen, das Sie escapen müssen.

SELECT * aus app_info, wobei Anwendungsname LIKE '%/_%' ESCAPE '/' ist; 

Aber gibt es für diese Situation eine höherwertige Lösung? Die Art, die Kollegen oder Vorgesetzte, die Ihren Code überprüfen, dazu bringen kann, Sie mit anderen Augen zu sehen~~

Schauen wir uns natürlich die zweite Art der MySQL-Fuzzy-Matching-Methode an - die integrierte Funktionsabfrage

2. Abrufen integrierter MySQL-Funktionen (Lokalisieren, Positionieren, Instrieren)

In Fortsetzung des vorherigen Artikels wird die Übereinstimmung über die integrierten Funktionen „locate“, „position“ und „instr“ durchgeführt, was der Methode str.contains() in Java entspricht. Sie gibt die Position des übereinstimmenden Inhalts in der Zeichenfolge zurück, was in Bezug auf Effizienz und Benutzerfreundlichkeit besser ist als die Übereinstimmung mit Platzhaltern.

SELECT * aus app_info, wobei INSTR(`appName`, '%') > 0;
SELECT * aus app_info, wobei LOCATE('%', `appName`) > 0;
SELECT * aus app_info, wobei POSITION( '%' IN `appName`) > 0;

Wie oben gezeigt ist der Standardwert für die drei integrierten Funktionen: > 0, daher kann das folgende > 0 hinzugefügt werden oder nicht, und das Hinzufügen macht es lesbarer.

OK, schauen wir uns an, wie diese drei integrierten Funktionen verwendet werden.

Lassen Sie uns zunächst klarstellen, dass die Indizes in MySQL von links nach rechts bei 1 beginnen. In Java ist der Index ganz links also 0. Wenn der Index in MySQL also 0 ist, bedeutet dies, dass er nicht existiert.

2-1. Funktion LOCATE()

Syntax: LOCATE (substr, str)

Gibt die Position des ersten Vorkommens von substr in str zurück. Wenn substr nicht in str vorhanden ist, ist der Rückgabewert 0. Wenn substr in str vorhanden ist, ist der Rückgabewert: die Position, an der substr zum ersten Mal in str auftritt.

Hinweis: LOCATE(substr, str) und POSITION(substr IN str) sind Synonyme und haben die gleiche Funktionalität.

Syntax: LOCATE(substr, str, [pos])

Die Position des ersten Vorkommens der Teilzeichenfolge „substr“ in der Zeichenfolge „str“, beginnend bei Position „pos“. Wenn substr nicht in str ist, wird 0 zurückgegeben. Wenn substr oder str NULL ist, wird NULL zurückgegeben.

SELECT lokalisieren('a', 'Banane'); -- 2
SELECT lokalisieren('a', 'Banane', 3); -- 4
SELECT lokalisieren('z', 'Banane'); -- 0
SELECT lokalisieren(10, 'Banane'); -- 0
SELECT lokalisieren(NULL , 'Banane'); -- null
SELECT lokalisieren('a' , NULL ); -- null

Beispiele:

-- Verwenden Sie das Schlüsselwort LOCATE für Fuzzy-Matching, das entspricht: „like ‚%网%‘“

SELECT * aus app_info, wobei LOCATE('App', `appName`) > 0;

-- Verwenden Sie das Schlüsselwort LOCATE für Fuzzy-Matching und gleichen Sie „网“ ab dem zweiten Zeichen ab, sodass Daten wie „NetEase Cloud Games, Wanglai Merchants“ herausgefiltert werden

SELECT * aus app_info, wobei LOCATE('Benutzer', `appName`, 2) > 0;

2-2. POSITION()-Methode

Syntax: POSITION(substr IN substr)

Diese Methode kann als Alias ​​für die Methode locate(substr, str) verstanden werden, da sie dieselbe Funktion wie die Methode locate(substr, str) hat.

Beispiele:

-- Verwenden Sie das Schlüsselwort POSITION für Fuzzy-Matching, das entspricht: „like ‚%网%‘“

SELECT * aus app_info, wobei POSITION('Name' IN `appName`);

2-3. INSTR()-Methode

Syntax: INSTR(str,substr)

Gibt die Position des ersten Vorkommens der Teilzeichenfolge „substr“ innerhalb der Zeichenfolge „str“ zurück. Die Zwei-Argument-Form von INSTR() ist identisch mit der von LOCATE(), außer dass die Reihenfolge der Argumente umgekehrt ist.

Beispiele:

-- Verwenden Sie das Schlüsselwort INSTR für Fuzzy-Matching. Es hat die gleiche Funktion wie LIKE, also etwa: „like '%网%'“

SELECT * aus app_info, wobei INSTR(`appName`, 'App');

-- Die Funktion instr wird im Allgemeinen verwendet, um die Position eines Zeichens in einer Zeichenfolge abzurufen. Dies entspricht: „like ‚%网%‘“

SELECT * aus app_info, wobei INSTR(`appName`, 'App') > 0;

3. MySQL-reguläre Matching-Abfrage basierend auf Regexp und Rlike

Die Schlüsselwörter regexp und rlike in MySQL sind Synonyme und haben dieselben Funktionen. Dieser Artikel basiert auf regulären Ausdrücken.

REGEXP unterstützt keine Platzhalter „%, _“, aber reguläre Übereinstimmungsregeln . Es ist eine detailliertere und elegantere Übereinstimmungsmethode. Werfen wir einen Blick darauf.

-- Hier sind die in regexp enthaltenen Parametertypen

-- REGEXP '网' ist gleichbedeutend mit '%网%'

SELECT * aus app_info, wobei AppName REGEXP 'Name' ist;
- Entspricht „SELECT * from app_info where appName like '%网%';“

3-1. ODER im regulären Ausdruck: |

Funktion: Sie können nach einer von mehreren Zeichenfolgen suchen, die gleichbedeutend sind mit oder

- Unterstützt die Symbole "|" oder "oder", die mit Daten übereinstimmen, die "China", "Internet" oder "Universität" enthalten, und unterstützt die Überlappung mehrerer

SELECT * from app_info where appName REGEXP 'China|Internet|Universität'; 

-- Um Daten abzugleichen, die gleichzeitig auf „中国“ und „网“ treffen, können Sie sie mit „.+“ verbinden, was das chinesische xxxx-Netzwerk bedeutet. In der Mitte ist eine beliebige Anzahl von Zeichen zulässig und die Reihenfolge kann nicht umgekehrt werden.

SELECT * from app_info where appName REGEXP 'Deutsch.+Englisch'; 

3-2. Regulärer Ausdrucksabgleich in REGEXP: []

Funktion: Übereinstimmung mit einem der Zeichen im Symbol [], unterstützt das Parsen regulärer Ausdrücke

- Stimmt mit Daten überein, die englische Zeichen enthalten, standardmäßig ohne Berücksichtigung der Groß-/Kleinschreibung

SELECT * aus app_info, wobei AppName REGEXP '[az]' ist; 

-- Genau wie, negiere einfach den Satz und füge "nicht REGEXP" hinzu, ich werde unten nicht ins Detail gehen

SELECT * aus app_info, wobei appName nicht REGEXP '[az]' ist; 

-- Stimmt mit Daten überein, die englische Großbuchstaben enthalten. Standardmäßig wird die Groß-/Kleinschreibung ignoriert und das Schlüsselwort „BINARY“ muss hinzugefügt werden . Zum Beispiel wo appName REGEXP BINARY 'Hallo'

-- Bezüglich der Groß-/Kleinschreibung: Bei der regulären Ausdrucksübereinstimmung in MySQL (seit Version 3.23.4) wird nicht zwischen Groß- und Kleinschreibung unterschieden.

SELECT * aus app_info, wobei Anwendungsname REGEXP BINARY '[AZ]' ist; 

-- Stimmt mit Daten überein, die Zahlen enthalten

SELECT * aus app_info, wobei AppName REGEXP '[0-9]' ist;

- Stimmt mit Daten überein, die Zahlen oder Englisch enthalten.

SELECT * aus app_info, wobei AppName REGEXP '[a-z0-9]' ist;

az, 0-9 werden alle als eine Einheit betrachtet, fügen Sie keine zusätzlichen Symbole hinzu . Vor ein paar Tagen habe ich einen Sonderfall gefunden, einen sehr interessanten Fehler, teilen Sie ihn mit ihm

-- Ich habe beim Schreiben der Abfrageanweisung ein zusätzliches „|“-Symbol hinzugefügt, weil ich dachte, es sei „oder“, und habe nicht darauf geachtet, aber ich hätte nie erwartet, dass die Anzahl der Ergebnisse anders sein würde.

SELECT * from app_info where appName REGEXP '[567]'; -- 87 Ergebnisse SELECT * from app_info where appName REGEXP '[5|6|7]'; -- 88 Ergebnisse 

Ich bin verwirrt. Mal sehen, welches falsch ist.

-- Es stellt sich heraus, dass das Symbol „|“ auch am Abgleich teilnimmt und als Einheit erkannt wird. Zufälligerweise gibt es einen Eintrag: „Wireless Mixer“, der genau passt. Was zur Hölle ist dieser DJ?

SELECT * from app_info where appName REGEXP '[5|6|7]' und pid nicht in (SELECT pid from app_info where appName REGEXP '[567]'); 

-- Abfragedaten beginnend mit einer der Zahlen 5, 6 oder 7

SELECT * aus app_info, wobei Anwendungsname REGEXP '^[5|6|7]' ist; 

-- Abfragedaten, die mit einer der Zahlen 5, 6 oder 7 enden

SELECT * aus app_info, wobei AppName REGEXP '[5|6|7]$' ist; 

Tipps: In MySQL sind chinesische UTF-8-Zeichen 3 Bytes und chinesische GBK-Zeichen 2 Bytes.

-- Abfragedaten mit appName Bytelänge von 10 und beliebigem Inhalt

SELECT * aus app_info, wobei Anwendungsname REGEXP '^.{10}$' ist; 

-- Abfragedaten, bei denen die Länge des AppName-Bytes 10 beträgt und alle auf Englisch sind

SELECT * aus app_info, wobei Anwendungsname REGEXP '^[az]{10}$' ist; 

-- Fragen Sie die Daten ab, deren AppName eine Bytelänge von 10 hat und alle in englischer Großschreibung vorliegen, und fügen Sie BINARY hinzu

SELECT * from app_info where Anwendungsname REGEXP BINARY '^[AZ]{10}$'; 

-- Abfragedaten, deren Versionsname eine Bytelänge von 6 hat und die alle aus Zahlen oder "." bestehen.

SELECT * from app_info where version_name REGEXP '^[0-9.]{6}$'; 

-- Abfragedaten, deren Versionsname eine Bytelänge von 6 hat und die alle aus Zahlen oder „.“ bestehen; die erste Ziffer muss 1 sein

SELECT * from app_info where version_name REGEXP '^1[0-9.]{5}$' ; 

-- Abfragedaten, deren Versionsname eine Bytelänge von 6 hat und die alle aus Zahlen oder „.“ bestehen; die erste Ziffer ist 1 und die letzte Ziffer ist 7

SELECT * from app_info where version_name REGEXP '^1[0-9.]{4}7$' ; 

-- Abfragedaten, deren Versionsname mehr als 6 Zeichen in Bytes enthält und alle Zeichen Zahlen oder „.“ sind; das erste Zeichen muss 1 und das letzte Zeichen 7 sein.

SELECT * from app_info where version_name REGEXP '^1[0-9.]{4,}7$' ; 

-- Abfrage der Daten, deren Versionsname eine Bytelänge von 6 bis 8 Zeichen hat und die alle aus Zahlen oder „.“ bestehen; die erste Ziffer muss 1 und die letzte Ziffer 7 sein.

SELECT * from app_info where version_name REGEXP '^1[0-9.]{4,6}7$' ; 

-- Das erste Zeichen ist nicht chinesisch

SELECT * aus app_info, wobei Anwendungsname REGEXP '^[ -~]' ist; 

-- Das erste Zeichen ist Chinesisch

SELECT * aus app_info, wobei Anwendungsname REGEXP '^[^ -~]' ist; 

-- Abfragedaten, die keine chinesischen Schriftzeichen enthalten

SELECT * aus app_info, wobei Anwendungsname REGEXP '^([az]|[0-9]|[AZ])+$' ist; 

-- Daten, die mit 5 oder F beginnen und englische

SELECT * aus app_info, wobei Anwendungsname REGEXP BINARY '^[5F][a-zA-Z].'; 

Um Sonderzeichen wie . zuzuordnen, müssen Sie \\ hinzufügen (beachten Sie, dass es sich um zwei Schrägstriche handelt). Wenn es sich jedoch in [] befindet, müssen Sie es nicht hinzufügen:

-- Übereinstimmung mit Namen, der enthält. select * from app_info where appName regexp '\\.';
-- Übereinstimmung mit Namen, die enthalten .select * from app_info where appName regexp '[.]'; 

3-3. Zeichenklassen-Matching (POSIX)

Es gibt einige spezielle Symbole in MySQL, die verschiedene Arten von Übereinstimmungen darstellen können:

-- Stimmt mit Namen überein, die Zahlen enthalten

Wählen Sie * aus App_info, wobei AppName regulärer Ausdruck '[[:Ziffer:]]' ist;

Andere Zeichenklassen dieses Typs sind:

Dieser Typ von Zeichenklasse erfordert eine zusätzliche Ebene von [] um die Hauptzeichenklasse.

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

In der obigen Zeichenklasse gibt es zwei spezielle. Bei diesen beiden geht es um die Position. [:<:] entspricht dem Wortanfang und [:>:] entspricht dem Wortende. Sie unterscheiden sich von ^ und $.

Letzteres passt zum Anfang und Ende des Ganzen, während Ersteres zum Anfang und Ende eines Wortes passt.

-- Stimmt nur mit der Zeichenfolge überein, die mit „a“ beginnt, z. B. abcd

Wählen Sie * aus App_info, wobei AppName regulärer Ausdruck „^a“ ist.

-- Es kann mit dem ganzen Wort übereinstimmen, das mit „a“ beginnt, und auch mit dem Wort in der Mitte, das mit „a“ beginnt, z. B.: „dance after“.

Wählen Sie * aus App_info, wobei AppName regulärer Ausdruck '[[:<:]]a' ist; 

[[:<:]] und [[:>:]] entsprechen jeweils einer leeren Zeichenfolge am Anfang und Ende eines Worts. Der Anfang und das Ende dieses Worts sind keine in alnum enthaltenen Zeichen und dürfen kein Unterstrich sein.

select "ein Wort ein" REGEXP "[[:<:]]Wort[[:>:]]"; -- 1 (Übereinstimmung) 
 
select "a xword a" REGEXP "[[:<:]]word[[:>:]]"; -- 0 (bedeutet keine Übereinstimmung) 
 
wähle "Wochenabende" REGEXP "^(wee|week)(knights|nights)$"; -- 1 (Übereinstimmung)

IV. Fazit

Nun, dieser Artikel endet hier. Diejenigen, die dies sehen können, sind dazu bestimmt, hier zu sein. Ich hoffe, dieser Artikel kann Ihnen helfen, MySQL besser zu verstehen. Weitere relevante Inhalte zu MySQL-Fuzzy-Abfragen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!