MySQL - 文本搜索和数据库结构

Question

这是我当前的数据库结构:

CREATE TABLE `books` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `year` year(4) NOT NULL DEFAULT '0000',
  `author` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE `chapters` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` int(10) unsigned NOT NULL DEFAULT '0',
  `number` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `book_id` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

ALTER TABLE `chapters`
  ADD CONSTRAINT `chapters_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE `pages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` int(10) unsigned NOT NULL DEFAULT '0',
  `chapter_id` int(10) unsigned NOT NULL DEFAULT '0',
  `number` int(10) unsigned NOT NULL DEFAULT '0',
  `text` text COLLATE utf8_unicode_ci NOT NULL,
  `words` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `book_id` (`book_id`),
  KEY `chapter_id` (`chapter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

ALTER TABLE `pages`
  ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `pages_ibfk_2` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

结构非常简单......基本上我逐页提取书籍文本并将所有内容存储到我的数据库中，该数据库被组织成书籍>章节>页面系统。我试图使其尽可能灵活，这样我就可以轻松地从整本书或按章节的角度聚合数据......但如果您认为我可以做得更好，我愿意接受任何建议!

现在，我希望允许用户在书中执行关键字搜索...以便他们可以在他们从下拉列表中选择的书中搜索单个单词甚至短语的所有出现。

我的网络服务器并不位于存储MySQL数据库的同一台机器上(短期内我无法摆脱的技术问题)...所以为了避免两台机器之间的巨大数据流量，我更喜欢通过 SQL 查询运行文本搜索。检索所有页面并使用 PHP 分析它们每次将转化为 5-10 Mb 的数据。

现在我的问题是:

• 是否可以仅使用查询命令(LIKE、MATCH、REPLACE 等)来执行此类过程？
• 我想通过以下方式获取按页面格式化的结果:[第1页|第1页] 0 次出现]，[第 2 页 | 1 次出现]，[第 3 页 | 1 次出现]，[第 4 页 |出现 2 次]...这可能吗？
• 您认为在将页面文本存储到 pages.text 字段之前从页面文本中删除空格字符(换行符、制表符等)和标点符号是个好主意吗？<

感谢您的帮助!

Answer 1

试试这个:

SELECT p.number, ROUND (   
        (
            LENGTH(p.text)
            - LENGTH( REPLACE ( p.text, "{your string here}", "") ) 
        ) / LENGTH("{your string here}")        
    ) AS count  
FROM pages p
JOIN
chapters c ON c.id = p.chapter_id
JOIN
books b on b.id = c.book_id
WHERE b.title = {your book title}
GROUP BY p.number

-- solution inspired by:
-- http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field

您可以去除空格，但可能会遇到一些问题:

• 您还需要删除搜索查询(这并不是真正的问题，只是额外的工作)

• 更重要的是，如果您想向用户显示页面的全文，甚至只是摘录，您一开始就不知道空白字符在哪里。