mysql - 全文索引与mysql上的列列表不匹配

Question

我使用 contao 4.9，但只有在使用 mysql 5.7.35 而不是 mariadb 时才会出现 View 问题。创建 View 的查询如下:

CREATE
OR REPLACE VIEW `tl_news4ward_articleWithTags` AS
SELECT
tl_news4ward_article.*,
GROUP_CONCAT(tag) AS tags
FROM
tl_news4ward_article
LEFT OUTER JOIN tl_news4ward_tag ON (
    tl_news4ward_tag.pid = tl_news4ward_article.id
)
GROUP BY
tl_news4ward_article.id

tl_news4ward_article 表的有趣部分创建如下:

CREATE TABLE `tl_news4ward_article` (
`title` varchar(255) NOT NULL DEFAULT '',
`keywords` text,
`description` text,
 PRIMARY KEY (`id`),
 KEY `pid` (`pid`),
 KEY `alias` (`alias`)
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=utf8

还有 tl_news4ward_tag:

CREATE TABLE `tl_news4ward_tag` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(10) unsigned NOT NULL DEFAULT '0',
`tstamp` int(10) unsigned NOT NULL DEFAULT '0',
`tag` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
FULLTEXT KEY `tag` (`tag`)

) ENGINE=MyISAM AUTO_INCREMENT=103 默认字符集=utf8

当我运行查询 SELECT MATCH (keywords,title,description) AGAINST ('something1 something2' IN BOOLEAN MODE) AS score FROM tl_news4ward_article 时，它可以正常工作，但如果我运行 SELECT MATCH (keywords,title,description) AGAINST ('something1 something2' IN BOOLEAN MODE) AS score FROM tl_news4ward_articleWithTags 我得到一个错误:

#1191 - Can't find FULLTEXT index matching the column list

如果需要，我可以提供更多信息。 它只适用于 Mariadb。

DB fiddle :

https://www.db-fiddle.com/f/3LN1cAM6aoohaB4a1q6oWb/1

EDIT2:

代码来自这个Contao模块:https://github.com/psi-4ward/news4ward/issues/106我们认为应该与 MySQL 一起使用

EDIT3:

更清晰的比较 fiddle :https://dbfiddle.uk/?rdbms=mysql_5.7&rdbms2=mariadb_10.3&fiddle=2f1ab88e65a8acb3bb992a1cf6fb4101

EDIT4:

上面的查询被简化了 - 正如你在 Contao 模块中看到的那样，标签列也应该包含在分数和匹配中。

Answer 1

• 错误来自没有对 3 列组合的索引:

    FULLTEXT(keywords,title,description)
  

• 你应该转移到 InnoDB，它现在有 FULLTEXT。这两个表可能需要使用相同的引擎。注意:关于 InnoDB 的 FULLTEXT 的一些细节与 MyISAM 的不同。这是一个列表:http://mysql.rjweb.org/doc.php/myisam2innodb#fulltext

• MySQL 的VIEWs 主要是语法糖。虽然我没有遇到您的具体问题，但似乎很明显 VIEW 阻碍了。

• 您是否尝试过明确指定两种“算法”来扩展 View ？ https://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html

• 如果您在查询时遇到性能问题(一旦它起作用)，可能是因为在执行过滤之前执行了 JOIN 。如果您遇到这种情况，我们可以在单独的问题中讨论。

(来自 fiddle )

我更喜欢这种获取记录的方式，加上标签:

SELECT  a.*,
        ( SELECT GROUP_CONCAT(tag)
              FROM tl_news4ward_tag AS t
              WHERE t.pid = a.id
        ) AS tags
    FROM  tl_news4ward_article AS a

但是没有办法(使用当前架构)在 MATCH 中包含 tags。

4-col FT

如果您决定在 FULLTEXT 索引中必须有 tags，那么主表中的标记的 commalist 并去掉 tags表。然后，您需要一些存储过程/函数来处理 add-tag、del-tag 等。提示:FIND_IN_SET('foo', tags) 将在测试特定标签时派上用场。