PHP/MySQL : Custom site search

Question

首先:- 我无法使用 sphinx，因为我使用共享托管- 我不喜欢谷歌解决方案，即。自定义搜索有这些愚蠢的广告，而且网站搜索不是免费的

我想自己创建搜索机制。我有页面表，我想通过关键字搜索页面内容，在结果页面上我想显示与所需关键字匹配的部分文本(与谷歌一样)。

提前谢谢

Answer 1

那么你有两个(半)选择:

• 使用MyISAM引擎来搜索您要搜索的数据
• 编写您自己的索引内容
• 更改托管或更改 DBMS(1/2 解决方案)

以下是如何执行第二个选项的简短版本:

假设您要搜索文章的内容。基本上，您必须为您可能想要搜索的所有单词创建一个索引。

以下代码摘自书籍 SQL Antipatterns 并且只修改了一点点。

我假设您想要索引文章:

CREATE TABLE Articles(
   article_id INT AUTO_INCREMENT,
   title VARCHAR(120),
   content TEXT,
   PRIMARY KEY ( article_id )
);

您需要一个关键字表(每个关键字可以出现在多篇文章中):

CREATE TABLE Keywords(
   keyword_id INT AUTO_INCREMENT,
   keyword VARCHAR(40) UNIQUE NOT NULL,
   PRIMARY KEY ( keyword_id )
);

现在要实现多对多关系的表:

CREATE TABLE ArticlesKeywords(
   keyword_id INT,
   article_id INT,
   PRIMARY KEY ( keyword_id , article_id ),
   FOREIGN KEY ( keyword_id ) REFERENCES Keywords( keyword_id ),
   FOREIGN KEY ( article_id ) REFERENCES Articles( article_id )
);

接下来创建一个存储过程，用于填充索引机制:

CREATE PROCEDURE ArticlesSearch(keyword VARCHAR(40))
BEGIN
   SET @keyword = keyword;
   PREPARE s1 FROM 'SELECT MAX(keyword_id) INTO @k FROM Keywords
      WHERE keyword = ?';
   EXECUTE s1 USING @keyword;
   DEALLOCATE PREPARE s1;
   IF (@k IS NULL) THEN

      PREPARE s2 FROM 'INSERT INTO Keywords (keyword) VALUES (?)';
      EXECUTE s2 USING @keyword;
      DEALLOCATE PREPARE s2;

      SELECT LAST_INSERT_ID() INTO @k;

      PREPARE s3 FROM 'INSERT INTO ArticlesKeywords (article_id, keyword_id)
         SELECT article_id, ? FROM Articles
         WHERE title REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>:]]'')
            OR content REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>]]'')';
      EXECUTE s3 USING @k, @keyword, @keyword;
      DEALLOCATE PREPARE s3;

   END IF;

   PREPARE s4 FROM 'SELECT b.*FROM Articles b
      JOIN ArticlesKeywords k USING (article_id)
      WHERE k.keyword_id = ?';
   EXECUTE s4 USING @k;
   DEALLOCATE PREPARE s4;
END

现在您可以使用此过程在索引中搜索关键字。

CALL ArticlesSearch('OMG');

解决方案的最后一部分是确保自动为每篇新文章建立索引:

CREATE TRIGGER Articles_Insert AFTER INSERT ON Articles
FOR EACH ROW
BEGIN
   INSERT INTO ArticlesKeywords (article_id, keyword_id)
      SELECT NEW.article_id, k.keyword_id FROM Keywords k
      WHERE NEW.content REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]')
         OR NEW.title REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]');
END

.

附注我从来没有需要测试这种方法，这就是为什么我不能保证它会起作用。