如何在不丢失FULLTEXT-Index的情况下连接表格?
问题描述:
我目前正在尝试为我的网站写一点多语言搜索引擎。如何在不丢失FULLTEXT-Index的情况下连接表格?
我写了一个函数来获取所需语言的文本,并回退到英语,如果该文本不存在该语言。
但是,由于我得到一个错误,当调用MATCH子句中的函数时,我不想再调用它3次,我只写了函数代码作为INNER JOIN,,但现在我失去了我的FULLTEXT索引。
问题:
- 如何维护我的FULLTEXT指数?
- 有没有比加入第二个表更好的方法?
- 还有其他错误吗?
错误:
Can't find fulltext index matching the column list
表 “翻译”:
- TYPE ------ NAME ---- INDEX
- INT- --------“ID”------- 唯一(主)
- INT --------- “tr_num” -
- TEXT “-----” 文本 “----- FULLTEXT
- CHAR(2) - ” 郎“ - ----
的搜索引擎:(缩短)
SELECT
c.ID,
c.name,
tr.description,
MATCH(c.name) AGAINST(?) AS relevance_title,
MATCH(tr.description) AGAINST(?) AS relevance_description,
FROM
companies c
#Joining transations (function code)
INNER JOIN (
SELECT
tr1.tr_num,
#Select translation if available
COALESCE(tr2.text, tr1.text) AS description
FROM
translations tr1
LEFT JOIN
translations tr2
ON
tr1.tr_num = tr2.tr_num
AND
tr2.lang = '$lang' #PHP - preferred langauge
WHERE
tr1.lang = 'en' #default language (english)
) tr
ON
tr.tr_num = c.description_tr_num
WHERE
MATCH(c.name) AGAINST(?)
OR MATCH(tr.description) AGAINST(?)
ORDER BY
((relevance_title * "/*PHP*/.$config['relevance_title'].") + (relevance_description * "/*PHP*/.$config['relevance_description'].")) DESC
功能:(不重要 - 只是柜面我可以在我的搜索使用它)
BEGIN
DECLARE output TEXT;
SELECT
COALESCE(tr2.text, tr1.text)
INTO
output
FROM
translations tr1
LEFT JOIN
translations tr2
ON
tr1.tr_num = tr2.tr_num
AND
tr2.lang = input_lang
WHERE
tr1.tr_num = input_tr_num
AND
tr1.lang = 'en';
RETURN output;
END
在此先感谢! - 自扫门前雪
答
这里是我的问题解决了,以防万一有人有类似的问题:
采用另一种方法,而不是加入,然后再寻找我选择了先搜索,然后加入:
#INSTEAD OF
COALESCE(tr2.text, tr1.text) AS description
#AND
MATCH(description) AGAINST(?) AS relevance_description
#USE
GREATEST(
MATCH(tr2.text) AGAINST(?),
MATCH(tr1.text) AGAINST(?)
) AS relevance_description