Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » 全文搜索 » 如何执行全文搜索

 

教程:对 GENERIC 文本索引执行全文搜索

可通过以下过程对使用 GENERIC 术语断开器的文本索引执行全文搜索。

另请参见: 教程:执行模糊全文搜索.

 ♦  对 GENERIC 文本索引执行全文搜索
  1. 启动 Interactive SQL 并使用 SQL Anywhere 12 Demo 数据源连接到示例数据库。

  2. 创建文本配置对象。

    以下示例创建一个名为 myTxtConfig 的文本配置对象。必须包括 FROM 子句,才能指定用作模板的文本配置对象。

    CREATE TEXT CONFIGURATION myTxtConfig FROM default_char;
  3. 自定义文本配置对象。

    添加包括 because、about、therefore 和 only 等单词的非索引字表。然后,将最大术语长度设置为 30。必须通过单独的 ALTER TEXT CONFIGURATION 语句来执行此操作,如下所示:

    ALTER TEXT CONFIGURATION myTxtConfig
       STOPLIST 'because about therefore only';
    ALTER TEXT CONFIGURATION myTxtConfig
       MAXIMUM TERM LENGTH 30;
  4. 启动 Sybase Central 并使用 SQL Anywhere 12 Demo 数据源连接到示例数据库。

  5. 创建 MarketingInformation 表的副本。

    1. 展开 [] 文件夹。

    2. 右击 MarketingInformation 并选择 [复制]。

    3. 右击 [] 文件夹,然后选择 [粘贴]。

    4. 在 [名称] 字段中键入 MarketingInformation1

    5. 单击 [确定]。

  6. 在 Interactive SQL 中,执行以下命令以向新表中填充数据:

    INSERT INTO MarketingInformation1
       SELECT * FROM MarketingInformation;
  7. 在示例数据库中 MarketingInformation1 表的 Description 列上创建引用 myTxtConfig 文本配置对象的文本索引。将刷新间隔设置为 24 小时。

    CREATE TEXT INDEX myTxtIndex ON MarketingInformation1 ( Description ) 
       CONFIGURATION myTxtConfig
       AUTO REFRESH EVERY 24 HOURS;
  8. 执行以下语句来刷新文本索引:

    REFRESH TEXT INDEX myTxtIndex ON MarketingInformation1;
  9. 执行以下语句来测试文本索引。

    1. 此语句在文本索引中搜索术语 cottoncap。结果按分数降序排序。Capcotton 的分数高,因为 cap 在文本索引中的出现频率较低。

      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
    2. 以下语句在文本索引中搜索术语 cotton。同时包含单词 visor 的行将被放弃。由于子句 CONTAINS 使用了谓语,所以结果未计分。

      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>
    3. 以下语句测试各行中是否存在术语 cotton。如果行包含该术语,则结果列中显示 1;否则返回 0。

      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
  10. 从 Interactive SQL 和 Sybase Central 断开连接。

  11. (可选)按照以下所述的步骤将示例数据库 (demo.db) 恢复至其初始状态: 重新创建示例数据库 (demo.db).

 另请参见