Einrichten einer Tabelle
Ermitteln, wie lang die Zeichenkett in Beschreibung ist
Teile einer Zeichenkette rausschneiden
Zeichenketten verknüpfen: concat()
Ermitteln, wann eine Zeichenkette anfängt: locate;
Regular Expressions
Fulltext searches

Einrichten einer Tabelle

nach oben

Wir stehen wieder vor unserem alten Problem. Um zu zeigen, wie man in mysql auf höchstkomplexe Art nach Zeichenketten suchen kann, brauchen wir erstmal eine Tabelle, die grosse Textblöcke enthält. Wir machen also eine zweite Tabelle, die die Tabelle, die wir im Kapitel count, distinct, grouped by, having,min,max,sum,avg verwendet haben, ergänzt. Genau genommen speichern wir in dieser Tabelle Zusatzinformationen zu unseren Kunden. Die Normalisierung dieser Tabellen werden wir im Kapitel joins nachholen.
Unser neue Tabelle hat dann folgenden Struktur:

Jetzt haben wir wieder zwei Möglichkeiten. Wir können entweder das sql Statement, dass diese Tabelle generiert direkt von der Kommando Ebene aus auslösen, oder es in einem Flatfile abspeichern und dann das Programm nach dem Schema auslösen, wie es in Daten aus einem Flatfile in Tabellen importieren beschrieben wurde. Da das sql Statement relativ kurz ist, entscheiden wir uns aber dafür, es direkt an der Kommando Ebene abzusetzen.

mysql> create table Kunden (KundenNr int(5) NOT NULL, email char(50),
-> www char(50),beschreibung text,PRIMARY KEY(KundenNr));
Query OK, 0 rows affected (0.00 sec)
mysql>

Damit ist die Tabelle dann eingerichtet. Jetzt kommt der zweite Teil. Wir brauchen Daten. Diese allerdings spulen wir aus einem Flatfile in die Datenbank ein. Den Flatfile kann man sich hier downloaden.
Er hat folgendes Aussehen: Hier clicken.
Den Tabulator verwenden wir diesmal nicht als Delimiter, den dies dient nicht der Übersichtlichkeit. Wir verwenden 'wesnich'. Wir können dann die Daten aus dem Flatfile mit diesem Statement in die mysql Tabelle Kunden einlesen. Befinden wir uns in der Datenbank testlauf, können wir die Daten mit folgendem sql Statement einspeisen.

mysql> load data local infile 'c:/mysql_handbuch/kunden.txt' into table kunden
-> fields terminated by ' wesnich ';
Query OK, 10 rows affected (0.06 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 40
mysql>

Wir erhalten dann in der mysql Datenbank testlauf eine Tabelle kunden mit folgender Struktur.

Ermitteln, wie lang die Zeichenkett in Beschreibung ist

nach oben

mysql> select length(Beschreibung) from Kunden;

	length(Beschreibung)
	213
	233
	239
	265
	287
	196
	144
	306
	117
	77

10 rows in set (0.00 sec)
mysql>

Wir erhalten die Anzahl an Zeichen in der Spalte Beschreibung für jeden Datensatz.

Teile einer Zeichenkette rausschneiden

nach oben

Wenn die Daten so abgespeichert sind, dass einer bestimmten Anzahl von Zeichen eine bestimmte Informationseinheit zugeordnet ist, kann es auch sinnvoll sein, mit left,middle oder right Teile einer Zeichenkette herauszuschneiden. Wir zeigen hier lediglich das Schema, da die Daten für diese Funktionen nicht sinnvoll abgespeichert sind. Die Ergebnisse sind in diesem Beispiel sinnlos.

mysql> select left(beschreibung,10) from Kunden;

	left(beschreibung,10)
	Großversan
	Implementi
	Schnittste
	Sehr kompl
	Ähnlicher
	Alles rund
	Kleiner Ve
	Mailing Ak
	Prospekt z
	Übersetzu

10 rows in set (0.00 sec)
mysql>

Rausgeschnitten wird also, beginnend von ganz links, zu Deutsch dem Anfang der Zeichenkette, die nächsten 10 Buchstaben. So etwas ähnlichen macht auch middle.

mysql> select mid(beschreibung,10,10) from Kunden;

	mid(beschreibung,10,11)
	nd für Dru
	ierung von
	ellen Prog
	lexe Soft-
	Dienst wi
	d um elekt
	erlag in d
	ktion für
	zu allen k
	ungsbüro,

10 rows in set (0.00 sec)
mysql>

Hier ist 10 die Position ab der herausgeschnitten wird und 11 zeigt an, wieviele Zeichen herausgeschnitten werden sollen. die Funktion right mach das gleiche wie die Funktion left, allerdings vom Ende der Zeichenkette her.
substring_index
Mit substring_index ist ein komplexerer Zugriff auf eine Zeichenkette möglich. substring_index springt zu der Stelle, wo eine bestimmte Zeichenkette zum x-ten Mal
aufgetreten ist und zeigt dann den Teil der Zeichenkette bis zu diesem Punkt.

mysql> select substring_index(Beschreibung,'n',4) from Kunden
-> where KundenNr=1;

	substring_index(Beschreibung,'n',4)
	Großversand für Druckerzubehör, Recycling von Druckerpatro

1 row in set (0.11 sec)
mysql>

Zusammen mit dem n von Druckerpatrone, welches nicht mehr angezeigt wird, hätte wir 4 n. Bis zu diesem n wird die Zeichenkette angezeigt. Wird das gesuchte Zeichen allerdings
nicht oft genug gefunden, in unserem Beispiel also nur 3 Mal, wird die gesamte Zeichenkette angezeigt. Würde man statt 4 -4 schreiben, würde die Suche vom Zeichenende herstarten.

Zeichenketten verknüpfen: concat()

nach oben

Mit der Funktion concat lassen sich Werte der Datenbank mit verknüpfen.

mysql> select concat('Die email Adresse de ',KundenNr,' Kunden ist ',email)
-> from Kunden;

	concat('Die email Adresse de ',KundenNr,' Kunden ist ',email)
	Die email Adresse de 1 Kunden ist peter_mueller@yahoo.de
	Die email Adresse de 2 Kunden ist Erikakuenstig@kuenstig.de
	Die email Adresse de 3 Kunden ist info@sauerbier.de
	Die email Adresse de 4 Kunden ist Maria@druck-around-the-clock.de
	Die email Adresse de 5 Kunden ist info@heiner.de
	Die email Adresse de 6 Kunden ist kohl@no-paper.net
	Die email Adresse de 7 Kunden ist info@glottertal.de
	Die email Adresse de 8 Kunden ist l³tzelschwab@it-works.com
	Die email Adresse de 9 Kunden ist peter_brueg@theater.de
	Die email Adresse de 10 Kunden ist erika@lachfeld.de

10 rows in set (0.00 sec)
mysql>

Ermitteln, wann eine Zeichenkette anfängt: locate;

nach oben

Wenn wir ermitteln wollen, an welcher Stelle einer Zeichenkette ein andere Zeichenkette anfängt (so sie denn überhaupt enthalten ist), können wir locate
verwenden.

mysql> select locate('komplexe',beschreibung) from kunden;

	locate('komplexe',beschreibung)
	0
	34
	0
	6
	0
	50
	0
	0
	0
	0

10 rows in set (0.06 sec)
mysql>

Ist die Zeichenkette gar nicht enthalten, erhalten wir 0.

Regular Expressions

nach oben

Die bisherigen Funktionen, left,right,middle,substring_index,locate können nur verwendet werden, wenn der Text ein bestimmtes Muster hat, bzw. verwenden kann man sie immer, allerdings sind die Ergebnisse kaum brauchbar. Das Problem besteht darin, dass man über die Struktur eines Textes meistens nur sehr vage
informiert ist, man folglich eine Möglichkeit benötigt, auch in schlecht strukturierten Texten zu suchen. Genau dies tun Regular Expressions. Ausführliche Beschreibung zu Regular Expressions finden sich auch in dem Handbuch zu Perl und dem Handbuch zu PHP. Regular Expressions sind die abstrakte Formulierung eines bestimmten
Typs von Text. Eine email Adresse z.B. hat eine Struktur, es kommen ein paar Buchstaben, Zahlen oder ein Unterstrich, dann ein @ Zeichen, dann wieder ein paar Buchstaben,Zahlen oder ein Unterstrich, dann ein Punkt und schliesslich wieder zwei oder vier Buchstaben. Dies kann man mit Regular Expression abstrakt formulieren
und dann nach allen Texten suchen lassen, die dieser Struktur entsprechen. Regular Expressions sind das bread and butter der Internetprogrammierung. Im Umfeld der relationalen Datenbanken sollten Regular Expressions eigentlich nicht die gleiche Bedeutung haben, da ja die Datenbank von vorneherein so optimiert sein sollte,
dass mit einfacheren Mechanismen, etwa mit like siehe mysql basics, gesucht werden kann. Weiter ist eine Suche über Regular Expressions, das gleiche gilt für like und die Funktionen oben, bei großen Datenbeständen auch nicht möglich, da mysql dann keine Indexe, siehe Indexe setzen, mehr verwenden kann. Einen anderen Ansatz, wie unten beschrieben, bildet der Index vom Typ Fulltext. Aber zurück zu den Regular Expression. Eingeschränkt, das heisst bei kleineren Datenbanken, sind sie nutzbar. Regular Expressions haben folgende Muster, die dann zu einem Ausdruck zusammengesetzt werden können.

Wollen wir jetzt z.B. alle Datensätze rausfischen, wo Freiburg und Flora im Text der Beschreibung steht, können wir sowas machen.

mysql> select email,www from kunden where beschreibung regexp
-> 'Freiburg .* Fauna';

	email		www
	info@glottertal.de		www.glottertal.de

1 row in set (0.22 sec)
mysql>

Wir suchen also eine Zeichenkette, bei der irgendwann mal das Wort Freiburg auftaucht, dann ein beliebiges Zeichen und davon beliebig viele kommen und irgendwann einmal das Wort Fauna. Diese Regular Expression hat noch ein kleines Problem. Freiburg muss vor Fauna kommen. Dies wäre also nicht praxistauglich, da die umgekehrte Reihenfolge genau so sinnvoll ist. Wir müssen unsere Regular Expression also noch ein bisschen modifizieren.

mysql> select email from kunden where beschreibung regexp
-> '(Freiburg .* Fauna) | (Fauna .* Freiburg)';

	email
	info@glottertal.de

1 row in set (0.06 sec)
mysql>

Verbinden wir zwei Ausdrücke mit oder ( | ) kann Fauna vor Freiburg stehen oder umgekehrt.

Will man alle email Adressen haben, die auf .de enden , kann man diese mit folgendem sql Statement finden.

mysql> select email from Kunden where email regexp '.de';

	email
	peter_mueller@yahoo.de
	Erikakuenstig@kuenstig
	info@sauerbier.de
	Maria@druck-around-the-clock.de
	info@heiner.de
	info@glottertal.de
	peter_brueg@theater.de
	erika@lachfeld.de

6 rows in set (0.00 sec)
mysql>

Fulltext searches

nach oben

Um die Leistungsfähigkeit einer Volltext Suche zu illustrieren, bedarf es einer Datenbank, die sehr viel Text hat. Eine solche lässt sich nur mit einem enormen Aufwand erstellen. Wir orientieren uns von daher an Paul Dubois, Mysql Cookbook, O'Reilly,2003,Seite 473 ff. Er bietet auf der Seite
http://www.kitebird.com/mysql-cookbook/downloads.php die komplette Bibel mit Zusatzinformationen an. Hiervon brauchen wir die Datei kvj.txt. Als Datei bibel.zip kann man sie sich hier herunterladen. Die Datei hat folgenden Aufbau.

Diese Flatfile Datenbank müssen wir jetzt in eine mysql Tabelle einlesen. Dazu müssen wir die Tabelle erstmal generieren. Das geht dann so.

mysql> create table bibel(
-> welches_Testament char(1),
-> Name_des_Buches char(20),
-> Ident_Buch char(20),
-> Kapitel int(3),
-> Versnummer int(4),
-> der_Text text);
Query OK, 0 rows affected (0.00 sec)
mysql>

Danach können wir den Text in die Datenbank reinspulen.

mysql> load data local infile 'c:/bible/bibel.txt' into table bibel;
Query OK, 31102 rows affected (2.47 sec)
Records: 31102 Deleted: 0 Skipped: 0 Warnings: 0
mysql>

Wie deutlich zu sehen braucht mysq um 31102 Datensätze einzulesen 2,47 Sekunden. Die Performance ist also sozusagen der reine Wahnsinn. Die Text Datei hat 4,6 MB, was wohl ausreicht, um sich ein Bild der Performance zu machen. Davon abgesehen ist die Bibel ein dickes Brett, was mit der Bibel möglich ist, ist mit anderen Dokumenten auch möglich.
Nachdem die Tabelle eingerichtet ist und wir die Daten eingelesen haben müssen wir einen Index setzen, in diesem Falle einen sehr speziellen, nämlich einen Fulltext Index.

mysql> alter table bibel add FULLTEXT(der_Text);
Query OK, 31102 rows affected (45.20 sec)
Records: 31102 Duplicates: 0 Warnings: 0
mysql>

Hierfür braucht mysql dann 45 Sekunden. Eine Suche über den Fulltext Index ist jetzt komplizierter, als die Suche über einen normalen Index, siehe Setzen von Indexen, da mysql hier verschiedene Optimierungen hinsichtlich der Relevanz der Dokumente durchführt. Eine Fulltext Index hätte mit

alter table bibel add FULLTEXT(diese_Spalte,jene_Spalte);

auch über mehrere Tabellen angelegt werden können, wenn man weiss, dass eine typische Anfrage über mehrere Spalten geht. Werden mehrere Wörte eingegeben, blendet mysql die Datensätze nach der Relevanz auf (so ein ähnliches Schema haben auch Internetsuchmaschinen wie Google, Lycos etc.). Hierbei gilt folgende
Regel. Ein Wort, das in vielen Dokumenten vorhanden ist, wiegt nicht so stark, wie ein Wort, das nur in wenigen Dokumenten enthalten ist. Das deutsch Wort der z.B. hat überhaupt kein Gewicht, weil es praktisch in jedem deutschen Text vorkommt. Kommt ein Wort überall vor, liefert fulltext index gar keine Treffer mehr.
Will man z.B. wissen, wie und wo das Wort Israel steht, kann man sowas machen.

mysql> select count(*) from bibel where match(der_Text) against('Israel');

	count(*)
	2294

1 row in set (0.11 sec)
mysql>

Wir sehen also, die Performance ist gewaltig. Mit like allerdings wäre dies nicht mehr praktikabel.

mysql> select count(*) from bibel where der_Text like '%Israel%';

	count(*)
	2319

1 row in set (0.33 sec)
mysql>

Die Unterschiede in der Anzahl der gefundenen Datensätze ergeben sich aufgrund der Tatsache, dass tatsächlich nicht das gleiche gesucht wird. %Israel% erfasst
auch Israel:, Elehohe-Israel,Israel. und so weiter.
Läßt man nach mehreren Wörter suchen, ergibt sich folgendes Bild.

mysql> select count(*) from bibel where match(der_Text)
-> against('Israel Moses Joshua');

	count(*)
	6028

1 row in set (1.48 sec)
mysql>

Das gleiche nochnmal mit dem like Operator.

mysql> select count(*) from bibel where
-> der_text like '%Israel%' or der_text like '%Moses%'
-> or der_text like '%Joshua%';

	count(*)
	6088

1 row in set (1.43 sec)
mysql>

Hier ist die Suche mit dem like Operator genauso schnell, wie mit einer Fulltext Suche. Wahrscheinlich wird der Unterschied erst richtig deutlich spürbar, wenn die Datenbank wesentlich größer ist und wenn über mehrer Spalten gesucht wird. Will man komplexere Suchen durchführen, muss die Fulltext Seach mit Regular Expression verglichen werden. Wir gehen mal von dem Fall aus, dass nach zwei Wörtern gesucht werden soll, von denen das eine enthalten sein muss und das andere enthalten sein kann.

mysql> select count(*) from bibel where match(der_text)
-> against ('+Israel God');

	count(*)
	4588

1 row in set (0.22 sec)
mysql>

Dasselbe mit einer Regular Expression ist aufwendig.

mysql> select count(*) from bibel where der_text regexp
-> '(Israel)|(Israel .* Moses)|(Moses .* Israel)';

	count(*)
	4638

1 row in set (52.72 sec)
mysql>

Die Anzahl der gefundenen Datensätze sind aus den oben schon erwähnten Gründen nicht gleich. Auf jeden Fall ist deutlich zu erkennen, dass bei diesem Typ von Abfrage die Performance deutlich in die Knie geht. Eine Sortierung der gefundenen Treffer nach der Relevanz ist weder mit dem like Operator noch mit Regular Expression möglich. Nur die Fulltext Search ist hierzu in der Lage. Wir zeigen dies an unserer Tabelle Kunden. Hierfür müssen wir erstmal für die Tabelle Kunden einen Fulltext Index anlegen.

mysql> alter table kunden add Fulltext(beschreibung);
Query OK, 10 rows affected (0.27 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql>

Wir wollen suchen nach Systeme und Content-Management und wir wollen, dass der Datensatz der beide Begriffe enthält, als erster gelistet wird. Betrachten wir vorher nochmal die hier relevanten Einträge, also die Einträge, wo Systeme oder Content-Management enthalten ist.

Im 1. und 4. Datensatz taucht Systeme alleine auf, im 6. im Zusammenhang mit Content-Management. Eine Fulltext Search anhand der Begriffe Systeme und Content-Management müsste uns also den 6. Datensatz zuerst liefern. Die Datensätze sind oben so abgebildet, wie sie in der Tabelle stehen, die Datensätze, die weder Systeme noch Content-Management erhalten, wurden übersprungen.

mysql> select KundenNr,email from Kunden where match(beschreibung)
-> against('Systeme Content-Management');

	KundenNr		email
	6		kohl@no-paper.net
	4		Maria@druck-around-the-clock.de
	1		peter_mueller@yahoo.de

3 rows in set (0.00 sec)
mysql>

Wir sehen, dass uns die Suche anhand einer Fulltext Search genau diese Ergebnisse liefert. Je mehr Begriffe gefunden werden, desto relevanter ist der Datensatz, desto weiter oben wird er angezeigt. Eine Suche mit dem like Operator würde uns die Datensätze in der Reihenfolge zeigen, wie sie in der Datenbank stehen.

mysql> select KundenNr,email from Kunden where beschreibung
-> like '% Systeme %' or beschreibung like '% Content-Management %';

	KundenNr		email
	1		peter_mueller@yahoo.de
	4		Maria@druck-around-the-clock.de
	6		kohl@no-paper.net

3 rows in set (0.05 sec)
mysql>

vorhergehendes Kapitel	nach oben	nächstes Kapitel