我正在尝试为我的网站写一个多语言的搜索引擎 .
我编写了一个函数来获取所请求语言的文本，并且如果该语言中不存在该文本，则返回到英语 .
但是因为我得到一个错误，当在MATCH子句中调用函数并且我不想调用它3次时，我只是将函数代码编写为INNER JOIN，但现在我丢失了我的FULLTEXT索引 .
Questions:
-
如何维护我的FULLTEXT索引？
-
有没有比加入第二张表更好的方法？
-
还有其他错误吗？
Error:
找不到与列列表匹配的全文索引
TABLE "translations":
-
TYPE ------ NAME ---- INDEX
-
INT --------- "ID" ------- unique (primary)
-
INT --------- "tr_num" -
-
TEXT "-----" text“----- FULLTEXT
-
CHAR（2） - "lang" -----
Searchengine: （缩短）
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
Function: （不重要 - 只是因为我可以在我的搜索中使用它）
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
Thanks in advance! -Minding
1 回答
以下是我解决问题的方法，以防有人遇到类似的问题：
采取另一种方法，而不是先加入，然后搜索我选择先搜索，然后加入：