Praktische Einführung: Volltextsuche in einem GENERIC-Textindex durchführen

Starten Sie Interactive SQL und stellen Sie eine Verbindung mit der Beispieldatenbank unter Verwendung der SQL Anywhere 12 Demo-Datenquelle her.

Erstellen Sie das Textkonfigurationsobjekt.

Das folgende Beispiel erstellt ein Textkonfigurationsobjekt namens myTxtConfig. Sie müssen die FROM-Klausel aufnehmen, um das Textkonfigurationsobjekt anzugeben, das als Vorlage verwendet werden soll.

CREATE TEXT CONFIGURATION myTxtConfig FROM default_char;

Passen Sie das Textkonfigurationsobjekt an.

Fügen Sie eine Stoppliste hinzu, die die Begriffe "because", "about", "therefore" und "only" enthält. Anschließend setzen Sie die Begriff-Höchstlänge auf 30. Sie müssen dies in separaten ALTER TEXT CONFIGURATION-Anweisungen durchführen, und zwar folgendermaßen:

ALTER TEXT CONFIGURATION myTxtConfig
   STOPLIST 'because about therefore only';
ALTER TEXT CONFIGURATION myTxtConfig
   MAXIMUM TERM LENGTH 30;

Starten Sie Sybase Central und stellen Sie eine Verbindung mit der Beispieldatenbank unter Verwendung der SQL Anywhere 12 Demo-Datenquelle her.

Erstellen Sie eine Kopie der MarketingInformation-Tabelle.

Erweitern Sie den Ordner Tabellen.
Rechtsklicken Sie auf MarketingInformation und wählen Sie Kopieren.
Rechtsklicken Sie auf den Ordner Tabellen und wählen Sie Einfügen.
Im Feld Name geben Sie MarketingInformation1 ein.
Klicken Sie auf OK.

In Interactive SQL führen Sie den folgenden Befehl aus, um die neue Tabelle mit Daten anzufüllen:

INSERT INTO MarketingInformation1
   SELECT * FROM MarketingInformation;

Für die Spalte "Description" der Tabelle "MarketingInformation1" in der Beispieldatenbank erstellen Sie einen Textindex, der das Textkonfigurationsobjekt "myTxtConfig" referenziert. Setzen Sie das Aktualisierungsintervall auf 24 Stunden.

CREATE TEXT INDEX myTxtIndex ON MarketingInformation1 ( Description ) 
   CONFIGURATION myTxtConfig
   AUTO REFRESH EVERY 24 HOURS;

Führen Sie die folgende Anweisung aus, um den Textindex zu aktualisieren:

REFRESH TEXT INDEX myTxtIndex ON MarketingInformation1;

Führen Sie die folgenden Anweisungen aus, um den Textindex zu testen:

Diese Anweisung durchsucht den Textindex nach den Begriffen cotton oder cap. Die Ergebnisse werden anhand der Punktewerte in absteigender Sortierfolge sortiert. Cap hat eine höhere Bewertung als cotton, weil cap weniger häufig im Textindex vorkommt.

SELECT ID, Description, ct.*
   FROM MarketingInformation1
     CONTAINS ( Description, 'cotton | cap' ) ct
   ORDER BY score DESC;

ID	Description	Score
905	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A lightweight wool cap with mesh side vents for breathable comfort during aerobic activities. Moisture-absorbing headband liner.</span></p></body></html>`	2,2742084275032632
904	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This fashionable hat is ideal for glacier travel, sea-kayaking, and hiking. With concealed draw cord for windy days.</span></p></body></html>`	1,6980426550094467
908	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage. </span></p></body></html>`	0,9461597363521859
910	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying cotton shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html>`	0,9244136988525732
906	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton construction. Shields against sun and precipitation. Metallic ions in the fibers inhibit bacterial growth, and help neutralize odor.</span></p></body></html>`	0,9134171046194403
909	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% cotton/20% polyester blend makes it easy to keep them clean.</span></p></body></html>`	0,8856420222728282

Die folgende Anweisung durchsucht den Textindex nach dem Begriff "cotton". Zeilen, die auch das Wort "visor" enthalten, werden verworfen. Die Ergebnisse werden nicht als Treffer betrachtet, da die Klausel CONTAINS ein Prädikat verwendet.

SELECT ID, Description 
   FROM MarketingInformation1
   WHERE CONTAINS( Description, 'cotton -visor' );

ID	Description
908	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage.</span></p></body></html>`
909	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% cotton/20% polyester blend makes it easy to keep them clean.</span></p></body></html>`
910	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying cotton shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html>`

Die folgende Anweisung testet jede Zeile auf den Begriff cotton. Wenn eine Zeile den Begriff enthält, erscheint eine 1 in der Spalte "Ergebnisse", ansonsten wird 0 zurückgegeben.

SELECT ID, Description, IF CONTAINS ( Description, 'cotton' )
      THEN 1
      ELSE 0
      ENDIF AS Results
   FROM MarketingInformation1;

ID	Description	Results
901	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>We've improved the design of this perennial favorite. A sleek and technical shirt built for the trail, track, or sidewalk. UPF rating of 50+.</span></p></body></html>`	0
902	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This simple, sleek, and lightweight technical shirt is designed for high-intensity workouts in hot and humid weather. The recycled polyester fabric is gentle on the earth and soft against your skin.</span></p></body></html>`	0
903	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A sporty, casual shirt made of recycled water bottles. It will serve you equally well on trails or around town. The fabric has a wicking finish to pull perspiration away from your skin.</span></p></body></html>`	0
904	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This fashionable hat is ideal for glacier travel, sea-kayaking, and hiking. With concealed draw cord for windy days.</span></p></body></html>`	0
905	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A lightweight wool cap with mesh side vents for breathable comfort during aerobic activities. Moisture-absorbing headband liner.</span></p></body></html>`	0
906	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton construction. Shields against sun and precipitation. Metallic ions in the fibers inhibit bacterial growth, and help neutralize odor.</span></p></body></html>`	1
907	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A polycarbonate visor with an abrasion-resistant coating on the outside. Great for jogging in the spring, summer, and early fall. The elastic headband has plenty of stretch to give you a snug yet comfortable fit every time you wear it.</span></p></body></html>`	0
908	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage.</span></p></body></html>`	1
909	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% cotton/20% polyester blend makes it easy to keep them clean.</span></p></body></html>`	1
910	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying cotton shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html>`	1

Trennen Sie die Verbindung zu Interactive SQL und Sybase Central.

(Optional) Stellen Sie den Originalzustand der Beispieldatenbank (demo.db) mit den Schritten unter Die Beispieldatenbank neu erstellen (demo.db) wieder her.

Praktische Einführung: Volltextsuche in einem GENERIC-Textindex durchführen

♦ Volltextsuche in einem GENERIC-Textindex durchführen

Siehe auch