duckduckgo
Diese Verbindung ist mit SSL verschlüsselt

tags: news software mysql deutsch opensource webdesign


28.Jan.2017 15:37 - zuletzt aktualisiert: 22.Feb.2017 11:04

MySQL Notizen bzw Beobachtungen eines MySQL-Anfängers (SELECT, WHERE, FULLTEXT)

Ich schreibe das hier, um einerseits meine Erfahrungen zu dokumentieren, andererseits in der Hoffnung eventuell Tipps und Anmerkungen von anderen MySQL-Nutzern zu bekommen. Das Internet ist zwar voll von Tutorials usw, aber ein Gedankenaustausch ist da nochmal was ganz Anderes. Also laßt mir gerne ein paar Kommentare da!

Der Winter ist immer eine gute Zeit für Programmierung, Pflege der Webseiten usw. Auftritte sind rar und das Programmieren hilft die tägliche musikalische Kreativität auszugleichen. Da sind solch formale Dinge super.

Im Moment bastel ich gerade an einer Datenbankanwendung. Das große Ziel diesmal ist, für eine meiner Bands eine Organisationshilfe zu schaffen. Bookingdatenbank, Terminfindung, sowas eben. Das sind ja nahezu klassische Anwednungsgebiete für MySQL.

Damit meine PHP-Kenntnisse nicht einschlafen, und auch mein MySQL-Horizont sich erweitert, mach ich das selber. Klar - es gibt jede Menge vorhandene Software, die genau sowas macht, nur würde ich dabei nichts lernen! :o)

Das nötige HTML ist schnell gemacht, ein paar Tabellen, ein paar Formulare, ein bißchen JavaScript um die Formulare bequemer zu machen, und schon kann der kniffelige Teil losgehen.

MySQL Grundbefehle

Es gibt erstmal nur 4 Grundbefehle: SELECT, INSERT, UPDATE und DELETE. Das stimmt natürlich so nicht! Es gibt noch eine ganze Reihe von anderen Befehlen, die allerdings nicht wirklich relevant sind für einfache Datanbankanwendungen. Mit diesen 4 Befehlen, kann man schon mal alles wichtige erledigen:

  • SELECT: eine Abfrage der DB, mit Ausgabe
  • INSERT: einen Datensatz einfügen
  • UPDATE: einen Datensatz verändern
  • DELETE: einen Datensatz löschen

SELECT

Diese Befehle lassen sich auch unheimlich umfangreich feineinstellen. So würde zum Beispiel die einfachste denkbare Abfrage aussehen:

SELECT * FROM `$table`

Das heißt auf deutsch: suche/zeige (SELECT) mir alle Spalten (*) aus der Tabelle $table (FROM). $table ist in diesem Falle eine PHP-Variable, die den Tabellennamen enthält.

SELECT ... WHERE ... LIKE

Ein sehr zielgerichteter SELECT könnte eher so aussehen:

SELECT name, phone, email FROM `$table` WHERE `name` LIKE 'chris_blues%' ORDER BY name ASC

Heißt:

  • SELECT name, phone, email: zeige Spalten mit Namen name, phone, email an. Alle anderen Tabellenspalten werden nicht angezeigt.
  • FROM `$dbtable`: wie vorher schon - die Tabelle, die durchsucht werden soll
  • WHERE `name` LIKE 'chris_blues%': alle Einträge, in deren Spalte "name" chris_blues steht. Die Prozentzeichen sind dabei eine Wildcard. Vergleichbar mit dem * in den meisten anderen Anwendungen. %test würde alle Einträge anzeigen, die auf "test" enden und test% alle, die mit test beginnen. Bemerkenswert ist hier noch, daß Suchen mit einer Wildcard am Anfang sehr rechenintensiv sind, was bei kleineren Tabellen eher unmerklich ist, bei größeren jedoch deutlich spürbar.
  • ORDER BY name: sortiere die Ergebnisse nach der Spalte name, und zwar
  • ASC: aufsteigend! (a, b, c...) Das Gegenstück wäre DESC (z, y, x...)

Mit WHERE lassen sich auch mathematische Bedingungen abgleichen:

SELECT * FROM `$table` WHERE `index` > 100

würde alle Einträge heraussuchen, deren index größer als 100 ist.

Volltextsuche

Etwas komplizierter und rechenintensiver, aber dafür flexibler ist die Volltextsuche FULLTEXT. Dafür muß man aber die Tabelle vorher etwas bearbeiten. Man muß z.Bsp. vorher festlegen, in welchen Spalten man suchen möchte. Auch geht das antürlich nur in Spalten, die nicht numerische Typen haben. Um nun einen Suchindex einzurichten (den braucht man nämlich für die Volltextsuche), setzt man einmalig das folgende Kommando in die Datenbank:

ALTER TABLE `$table` ADD FULLTEXT index_all(name, phone, email)

Nun wird ein Suchindex in der Tabelle $table erstellt, der die Spalten name, phone, email umfaßt. Alle anderen Spalten der Tabelle werden ignoriert.

Nun läßt sich eine Suche starten:

SELECT * FROM `$table` WHERE MATCH (name, phone, email) AGAINST ('chris_blues')

findet unter Umständen (und den entsprechenden Modi) nun auch Einträge, die eher entfernt mit dem Suchbegriff zu tun haben. In jedem Falle wird die Groß- und Kleinschreibung berücksichtigt, das heißt eine Suche nach "Test" findet auch "test". Wichtig für die Volltextsuche ist es außerdem, daß die zu durchsuchenden Spalten nicht binärer Collation sind. Also utf8mb4_bin ist nicht geeignet, da bei binären Spalten unter Anderem die Groß- und Kleinschreibung nicht berücksichtigt werden kann. Im Textmodus, also z.Bsp. utf8mb4_general_ci wird mit der Suche nach "test" auch der Begriff "Test" gefunden, binär nicht.

Nun gibt es insgesamt drei verschiedene Suchmodi:

  • IN BOOLEAN MODE
  • IN NATURAL LANGUAGE MODE
  • WITH QUERY EXPANSION Kurzform für: IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

Mal ganz grob umrissen unterscheiden sich diese Suchemethoden wie folgt:

  • boolean sucht nach direkten Treffern, ganz ähnlich wie die Suche mit WHERE ... LIKE ...

    A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Certain common words (stopwords) are omitted from the search index and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 13.9.2, “Boolean Full-Text Searches”.

  • der "natürliche" Modus berücksichtigt schon gewisse Ähnlichkeiten und Unterschiede. Der Unterschied zum boolean Modus ist eher subtil. Es werden bei der Suche "relevante Begriffe" gespeichert. Also vermutlich Begriffe, die öfter auftauchen. Diese sind eigentlich erst für die nächsten Suchmodi von Bedeutung.

    A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. For more information about stopword lists, see Section 13.9.4, “Full-Text Stopwords”.

  • mit der "Erweiterung" kommt eine neue Qualität ins Spiel. Es werden die Relevanzen berücksichtigt, die die "natürliche" Suche mitbringt. Beim zweiten Durchlauf werden dann diese relevanten Begriffe mitgesucht. Das bedeutet auch, daß die Ergebnisse eventuell gar nichts bis nur wenig mit dem eigentlichen Suchbegriff zu tun haben können. Ich stelle mir das so vor: Ich suche z.Bsp. nach "test". Im ersten Durchgang werden alle Tabelleneinträge gefunden, die das Wort "test" enthalten. In diesen Einträgen wird nun nach Gemeinsamkeiten gesucht. Taucht z.Bsp. hin und wieder auch das Wort "foo" auf, dann wird im zweiten Durchgang nach "test" und "foo" gesucht.

    A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 13.9.3, “Full-Text Searches with Query Expansion”.

Ein Aufruf mit einer bestimmten Methode sähe so aus:

SELECT * FROM `$table` WHERE MATCH (name, phone, email) AGAINST ('chris_blues' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)

Na mal sehen, was ich da in den nächsten Tagen noch so in Erfahrung bringen kann, was vielleicht auch über Kommentare ans Licht kommt. Ich jedenfalls bin schwer gespannt... :o)

Zu erwarten sind jedenfalls noch einige Blogposts mehr zum Thema MySQL, denkbar wäre auch noch, das weitere Datenhandling im PHP-Bereich. Ich sehe es zumindest nicht kommen, in nächster Zeit mit dem Projekt fertig zu werden.

Jruß
chris

Creative Commons Lizenzvertrag
Dieses Werk ist lizenziert unter einer
Creative Commons Namensnennung 4.0 International Lizenz .

Kommentare

1) anonymous

28.Jan.2017 21:09

Wenn deine Notizen wirklich am Anfang beginnen (und so vielleicht auch anderen Neulingen den Einstieg erleichtern) sollen, dann wäre es nur konsequent, wenn du mit dem Erzeugen einer Datenbank, dem Auf- und Abbau der Datenbankverbindung und mit dem Anlegen von Tabellen startest. ?

2) chris_blues

28.Jan.2017 21:14

Hm, an sich ne gute Idee. Mal sehen, ob ich da mal einen Artikel hinterher schiebe. Im Moment kämpfe ich eher mit der Volltextsuche. Eigenartig, wie die sich so verhält... ?

3) Christian

28.Jan.2017 23:26

Eine LIKE-Suche, die mit einer Wildcard beginnt, ist eine schlechte Idee, da dabei kein Index nutzbar ist. Bei größeren Tabellen wird das höllisch langsam.

Soweit ich weiß ist "WITH QUERY EXPANSION" nur die Kurzfassung für "IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION". Dieser Modus wäre auch bei einer Boolschen Volltextsuche eher suboptimal, da es dabei keine Relevanzberechnung gibt.

Erstere sind sehr empfindlich und geben kaum Ergebnisse heraus, wenn die Groß- und Kleinschreibung nicht stimmt etc. Zweiteres liefert mehr Ergebnisse, als ich mir erklären kann und ignoriert Groß- und Kleinschreibung.

Volltextsuchen (bool oder natürlich) sind generell case-insensitive. Entweder stimmt die Collation nicht (ggf. binary?) oder die Abfrage ist fehlerhaft. Wenn Zweiteres "with query expansion" ist: die MySQL garniert die Suchanfrage mit weiteren "relevanten" Suchbegriffen. Das kann natürlich ein äußerst unscharfes Resultset ergeben.

4) chris_blues

29.Jan.2017 00:30

Vielen Dank Christian! Sehr interessante Tips!

Eine LIKE-Suche, die mit einer Wildcard beginnt, ist eine schlechte Idee, da dabei kein Index nutzbar ist. Bei größeren Tabellen wird das höllisch langsam.

Und wie stellt man jetzt eine Suche an, wenn ich in einer Spalte mehrere Werte habe, die im Grunde genommen für sich einzeln stehen? Beispiel hier in meinem Blog wäre die Spalte `tags`. Da stehen eigentlich immer mehrere Werte drin. Wie würde ich jetzt am besten danach suchen? Oder lieber für jeden möglichen tag eine bool-Spalte anlegen (true/false)?

Soweit ich weiß ist "WITH QUERY EXPANSION" nur die Kurzfassung für "IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION".

Ok, ich dachte das wären zwei unterschiedliche Tierchen. Da hab ich wohl die Doku mistverstanden.

Dieser Modus wäre auch bei einer Boolschen Volltextsuche eher suboptimal, da es dabei keine Relevanzberechnung gibt.

Das versteh ich jetzt nicht so ganz. Ich dachte die boolsche Suche wäre genau das, bool. Treffer oder kein Treffer... ?

Volltextsuchen (bool oder natürlich) sind generell case-insensitive. Entweder stimmt die Collation nicht (ggf. binary?) oder die Abfrage ist fehlerhaft.

Aha! Die Collation darf nicht binary sein? Also utf8mb4_bin? Das hab ich offenbar auch übersehen.

Wenn Zweiteres "with query expansion" ist: die MySQL garniert die Suchanfrage mit weiteren "relevanten" Suchbegriffen. Das kann natürlich ein äußerst unscharfes Resultset ergeben.

Ja, mit "expansion" kommen irgendwie ziemlich lustige Ergebnisse zustande. Aber vielleicht ist die Datenbank einfach noch nicht groß genug, um da wirklich brauchbare Ergebnisse zu liefern...

Nochmals Danke für die Hinweise!!! ?

5) Christian

29.Jan.2017 11:47

Und wie stellt man jetzt eine Suche an, wenn ich in einer Spalte mehrere Werte habe, die im Grunde genommen für sich einzeln stehen?

Angesichts der relationalen Datenbank wäre wohl eine normalisierte Speicherung die sauberste Möglichkeit. Alle Tags werden nur in einer separaten Tabelle 'tags' mit einer eindeutigen ID gespeichert. Die Verknüpfung zwischen Blog-Artikeln und Tags findet über eine Relationstabelle (z.B. 'blog_tags') statt.

| blog | title   |
|------|---------|
| 1 | Titel 1 |
| 2 | Titel 2 |
| 3 | Titel 3 |

| tag | text |
|-----|---------|
| 1 | foo |
| 2 | bar |
| 3 | awesome |

| blog_tag | blog | tag |
|----------|------|-----|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 3 | 3 |


Alle Artikel mit dem Tag "foo" können dann über einen einfachen Join abgefragt werden:
SELECT blog.blog, blog.title FROM blog, tag, blog_tag WHERE tag.text = "foo" AND blog_tag.tag = tag.tag AND blog.blog = blog_tag.blog


Alternativ: Tags weiterhin redundant in einer Spalte mit Volltextindex speichern und mit IN BOOLEAN MODE abfragen. Dabei ist jedoch die Config-Variable "ft_min_word_len" zu beachten. Standardmäßig ist diese auf 4 eingestellt, sodass Tags mit einer Länge von 1-3 Zeichen nicht gefunden werden könnten.

Ich dachte die boolsche Suche wäre genau das, bool. Treffer oder kein Treffer

Stimmt auch ;) IN BOOLEAN MODE liefert als Rückgabe die Anzahl der im Ergebnis gefundenen Suchterme. IN NATURAL LANGUAGE MODE gewichtet die Suchergebnisse und ordnet einen Relevanzwert zu (bei Interesse: https://dev.mysql.com/doc/internals/en/full-text-search.html). Nach meinem Verständnis wir diese Relevanzberechnung für die Query Expansion benötigt.

Die Collation darf nicht binary sein?

Aus der Doku:
By default, the search is performed in case-insensitive fashion. To perform a case-sensitive full-text search, use a binary collation for the indexed columns.

Binary wird also benötigt, wenn eine case-sensitive (Volltext-)Suche, Sortierung, etc. gewünscht wird. Mit der Collation "utf8_general_ci" sollte es wie gewünscht funktionieren.

6) chris_blues

29.Jan.2017 14:21

Angesichts der relationalen Datenbank wäre wohl eine normalisierte Speicherung die sauberste Möglichkeit. Alle Tags werden nur in einer separaten Tabelle 'tags' mit einer eindeutigen ID gespeichert. Die Verknüpfung zwischen Blog-Artikeln und Tags findet über eine Relationstabelle (z.B. 'blog_tags') statt.

Jo! Das hat jetzt ne ganze Weile gedauert, bis ich das kapiert hab. ? Also in blog_tag würde man dann der Reihe nach die `blog`-Einträge abklappern. Eigentlich gar nich so doof! Immer schön ein Feld pro Wert. Wär ich so schnell von selbst nicht drauf gekommen!
Das mit dem boolschen Modus und mind. 4 Zeichen schreckt mich da schon eher wieder ab. Wäre für das Tag 'php' blöd irgendwie. ?

Zu den Suchmodi: Dann habs ichs ja doch richtig verstanden. Nur daß halt WITH QUERY EXPANSION und IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION das selbe sind.

Binary wird also benötigt, wenn eine case-sensitive (Volltext-)Suche, Sortierung, etc. gewünscht wird. Mit der Collation "utf8_general_ci" sollte es wie gewünscht funktionieren.

Aha! Klingt auch irgendwie plausibel. Wenn man Dateien liest verhält sich das ja genauso. Text hat halt noch einige relevanten Funktionen mehr, während binär alles nur noch einzigartige Zeichen sind. A != a

Super Infos! Danke!

7) Tux.

30.Jan.2017 17:44

Ich würde auf PostgreSQL oder MariaDB setzen, um Oracle nicht weiter zu fördern. Sind beide auch performanter und zuverlässiger.

8) Tux.

30.Jan.2017 17:45

Übrigens: hält man sich an die im Platzhalter empfohlene URL-Formatierung, lässt also das http weg, gibt es eine Fehlermeldung hier.

9) chris_blues

30.Jan.2017 19:47

Hier bei mir ist ne MariaDB am Werkeln.

Mit der URL, meinste da die Kommentarfunktion?
Test: http://musicchris.de

10) Tux.

30.Jan.2017 19:52

Ich meine das Websitefeld im Kommentarformular.

11) chris_blues

31.Jan.2017 00:48

Test
musicchris
http://musicchris.de

Na das is ja mal ziemlich bescheuert! file://musicchris.de ist eine gültige URL, www.musicchris.de nicht! Das macht aber der Browser. Das hat man nun davon, daß man das tag <input type="url" /> benutzt... Tztztz! ? ? ?
Das kommt dann wohl bei nächster Gelegenheit mal wech! ♽
Ist das Kunst oder kann das wech?
Name: (optional)

leer lassen

Benachrichtigen: (optional, um zu neuen Kommentaren benachrichtigt zu werden)

Website: (optional)

Ihr Kommentar
😀😁😂😃😄😅😆😇😈😉😊😋😌😍😎😏😐😑😒😓😔😕😖😗😘😙😚😛😜😝😞😟😠😡😢😣😤😥😦😧😨😩😪😫😬😭😮😯😰😱😲😳😴😵😶😷🙁🙂🙃🙄🙅🙆🙇🙈🙉🙊🙋🙌🙍🙎🤐🤑🤒🤓🤔🤕🤖🤗🤘🤠🤡🤢🤣🤤🤥🤦🤧𝄞🌍🌹🍻🍾