在SQL中实现子字符串搜索的最佳方法是什么?

查看我的演示文稿 Practical Fulltext Search in MySQL .

我比较了一下:

• LIKE
• Regular expression 谓词(不比 就像
• 弥撒 FULLTEXT 标引
• Sphinx Search
• Apache Lucene
• 谷歌 Custom Search Engine

今天我要用的是 Apache Solr ,这使Lucene成为一个具有许多额外功能和工具的服务。

回复你的评论:啊哈,好吧,不。我提到的所有全文搜索功能都没有帮助,因为它们都假设了某种单词边界

N-gram 接近。基本上,创建所有可能的N个字母序列的索引,并指向每个序列出现的字符串。通常这是在N=3的情况下完成的,或者 trigram

我不知道支持n-gram索引的任何SQL数据库,但是您可以使用 :

create table trigrams (
  trigram char(3) primary key
);

create table trigram_matches (
  trigram char(3),
  document_id int,
  primary key (trigram, document_id),
  foreign key (trigram) references trigrams(trigram),
  foreign key (document_id) references mytable(document_id)
);

insert into trigram_matches
  select t.trigram, d.document_id
  from trigrams t join mytable d
    on d.textcolumn like concat('%', t.trigram, '%');

当然这要花很长时间!但一旦完成,你可以更快地搜索:

select d.*
from mytable d join trigram_matches t
  on t.document_id = d.document_id
where t.trigram = 'abc'

当然,您可以搜索超过三个字符的模式,但是反向索引仍然有助于缩小搜索范围:

select d.*
from mytable d join trigram_matches t
  on t.document_id = d.document_id
where t.trigram = 'abc'
  and d.textcolumn like '%abcdef%';

FULLTEXT 索引& MATCH() AGAINST() . 当然,还要加载数据库服务器:根据您的特定需要,在适当的时间内缓存结果。

如果您有一个具有长描述性文本的字段(saya a notes字段),并且搜索总是按整词进行,则可以执行全文搜索。

请考虑首先执行完全匹配搜索,并且仅在没有返回结果时执行通配符匹配。如果您有可以提供精确匹配的用户,则这将起作用。我们做过一次机场名称搜索,如果他们输入准确的名称,搜索速度会很快,如果没有,搜索速度会慢一些。

如果您只想搜索文本中某个地方的字符串,而不是单词,那么您的性能相当糟糕。

1. 如果您的语言不是英语,mysql全文搜索的质量(为此目的)很差

3. postgreSQL有 trigram index ,很容易使用:)

4. -每一个三联图只存储一次

   -一个简单的php类使用数据

   <?php
   
     /*
   
       # mysql table structure
       CREATE TABLE `trigram2content` (
   `trigram_id` int NOT NULL REFERENCES trigrams(id),
   `content_type_id` int(11) NOT NULL,
   `record_id` int(11) NOT NULL,
   PRIMARY KEY (`content_type_id`,`trigram_id`,`record_id`)
   );
   
   #each trigram is stored only once
   CREATE TABLE `trigrams` (
   `id` int not null auto_increment,
   `token` varchar(3) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE token(token)
   ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   
   
   SELECT count(*), record_id FROM trigrams t
   inner join trigram2content c ON t.id=c.trigram_id
   WHERE (
   t.token IN ('loc','ock','ck ','blo',' bl', ' bu', 'bur', 'urn')
   AND c.content_type_id = 0
   )
   GROUP by record_id
   ORDER BY count(*) DESC
   limit 20;
   
   
   */
   class trigram
   {
   
       private $dbLink;
   
       var $types = array(
           array(0, 'name'),
           array(1, 'city'));
   
   
       function trigram()
       {
         //connect to db
         $this->dbLink = mysql_connect("localhost", "username", "password");
         if ($this->dbLink) mysql_select_db("dbname");
         else mysql_error();
   
         mysql_query("SET NAMES utf8;", $this->dbLink);
       }
   
       function get_type_value($type_name){
         for($i=0; $i<count($this->types); $i++){
             if($this->types[$i][1] == $type_name)
                 return $this->types[$i][0];
         }
         return "";
       }
   
       function getNgrams($word, $n = 3) {
           $ngrams = array();
           $len = mb_strlen($word, 'utf-8');
           for($i = 0; $i < $len-($n-1); $i++) {
               $ngrams[] = mysql_real_escape_string(mb_substr($word, $i, $n, 'utf-8'), $this->dbLink);
           }
           return $ngrams;
       }
   
       /**
       input: array('hel', 'ell', 'llo', 'lo ', 'o B', ' Be', 'Bel', 'ell', 'llo', 'lo ', 'o  ')
       output: array(1,     2,     3,      4,      5,      6,      7,     2,   3,  4,      8)
       */
       private function getTrigramIds(&$t){
           $u = array_unique($t);
           $q = "SELECT * FROM trigrams WHERE token IN ('" . implode("', '", $u) . "')";
   
           $query = mysql_query($q, $this->dbLink);
           $n = mysql_num_rows($query);
   
           $ids = array(); //these trigrams are already in db, they have id
           $ok = array();
   
           for ($i=0; $i<$n; $i++)
           {
             $row = mysql_fetch_array($query, MYSQL_ASSOC);
             $ok []= $row['token'];
             $ids[ $row['token'] ] = $row['id'];
           }
           $diff = array_diff($u, $ok); //these trigrams are not yet in the db
           foreach($diff as $n){
               mysql_query("INSERT INTO trigrams (token) VALUES('$n')", $this->dbLink);
               $ids[$n]= mysql_insert_id();
           }
   
           //so many ids than items (if a trigram occurs more times in input, then it will occur more times in output as well)
           $result = array();
           foreach($t as $n){
               $result[]= $ids[$n];
           }
           return $result;
       }
   
       function insertData($id, $data, $type){
           $t = $this->getNgrams($data);
   
           $id = intval($id);
           $type = $this->get_type_value($type);
           $tIds = $this->getTrigramIds($t);
           $q = "INSERT INTO trigram2content (trigram_id, content_type_id, record_id) VALUES ";
           $rows = array();
           foreach($tIds as $n => $tid){
               $rows[]= "($tid, $type, $id)";
           }
           $q .= implode(", ", $rows);
           mysql_query($q, $this->dbLink);
       }
   
       function updateData($id, $data, $type){
           mysql_query("DELETE FROM trigram2content WHERE record_id=".intval($id)." AND content_type_id=".$this->get_type_value($type), $this->dbLink);
           $this->insertData($id, $data, $type);
       }
   
       function search($str, $type){
   
           $tri = $this->getNgrams($str);
           $max = count($tri);
           $q = "SELECT count(*), count(*)/$max as score, record_id FROM trigrams t inner join trigram2content c ON t.id=c.trigram_id
   WHERE (
   t.token IN ('" . implode("', '", $tri) . "')
   AND c.content_type_id = ".$this->get_type_value($type)."
   )
   GROUP by record_id
   HAVING score >= 0.6
   ORDER BY count(*) DESC
   limit 20;";
           $query = mysql_query($q, $this->dbLink);
           $n = mysql_num_rows($query);
   
           $result = array();
           for ($i=0; $i<$n; $i++)
           {
             $row = mysql_fetch_array($query, MYSQL_ASSOC);
             $result[] = $row;
           }
           return $result;
       }
   
   
   };
   

 $t = new trigram();

 $t->insertData(1, "hello bello", "name");
 $t->insertData(2, "hellllo Mammmma mia", "name");

  print_r($t->search("helo", "name"));