mysql - MySQL - 在结果中按列和关键字位置搜索行和顺序

Question

我又一次碰到了一个问题，我需要你的帮助来解决它。我的上下文如下：

我有一个表，它存储具有如下基本结构的记录（还有一些其他列，但这些列在当前问题中使用）：

|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
|ID |type|title                                            | description                                                                      |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 1 | 0  | Lorem ipsum keyword_1 dolor keyword_2 keyword_3 | Mauris faucibus, keyword_2 odio keyword_1 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 2 | 0  | Lorem ipsum keyword_1 dolor keyword_2           | Mauris faucibus, odio keyword_1 gravida keyword_3, lacus leo malesuada           |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 3 | 1  | keyword_1 Lorem ipsum dolor keyword_2           | Mauris keyword_1 faucibus, odio keyword_2 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 4 | 0  | Lorem ipsum keyword_1 dolor keyword_2 keyword_3 | Mauris faucibus, keyword_2 odio keyword_1 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 5 | 0  | Lorem ipsum keyword_1 dolor keyword_2           | Mauris faucibus, odio keyword_1 gravida keyword_3, lacus leo malesuada           |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 6 | 1  | keyword_1 Lorem ipsum dolor keyword_2           | Mauris keyword_1 faucibus, odio keyword_2 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 7 | 0  | keyword_1 keyword_2 keyword_3 Lorem ipsum dolor | keyword_1 keyword_2 Mauris faucibus, odio keyword_3 gravida, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|

...等等。

• ID列是 auto_increment
• 类型列是 int(1)，只有两个可能的值：0 或 1
• 标题列是 varchar(255)
• 描述栏是mediumtext

用户对“ keyword_1 keyword_2 keyword_3 ”或“ keyword_1 keyword_2 ”或“ keyword_1 ”执行搜索，我希望通过符合以下条件的查询获得结果：

1. 它应该首先返回 type=0 的行
2. 它应该返回包含标题和/或描述中所有关键字的行（标题或描述（或两者）应该包含用户输入的所有关键字）
3. 它应该首先返回两列（标题和/或描述）中关键字位置最靠左的行（存储在表中的字符串的开头）
4. foreach 的关键字，它应该只匹配整个单词（我不想要关键字_1andsomeotherchars的结果）
5. 更新if type= 0 then search only in titleelse search in titleordescription

伪查询类似于

select * from table 
where title and / or description contains all the keywords 
and keywords are most to the beginig of title and or description
order by type asc and by position of keywords in title and / or description

目前，我的查询如下所示：

SELECT *
FROM table
WHERE table.`type` IN ('0','1')
  AND table.`approved` = 1
  AND table.`trash` = 0
  AND ((table.`type` = 0
        AND (match(table.`title`) against ('+keyword_1' IN BOOLEAN MODE)
             AND match(table.`title`) against ('+keyword_2' IN BOOLEAN MODE)
             AND match(table.`title`) against ('+keyword_3' IN BOOLEAN MODE))
        OR table.`title` REGEXP '\bkeyword_1 keyword_2 keyword_3\b')
       OR (table.`type` = 1
           AND ((match(table.`title`) against ('+keyword_1' IN BOOLEAN MODE)
                 AND match(table.`title`) against ('+keyword_2' IN BOOLEAN MODE)
                 AND match(table.`title`) against ('+keyword_3' IN BOOLEAN MODE))
                OR (match(table.`description`) against ('+keyword_1' IN BOOLEAN MODE)
                    AND match(table.`description`) against ('+keyword_2' IN BOOLEAN MODE)
                    AND match(table.`description`) against ('+keyword_3' IN BOOLEAN MODE)))
           OR (table.`title` REGEXP '\bkeyword_1 keyword_2 keyword_3\b'
               OR table.`description` REGEXP '\bkeyword_1 keyword_2 keyword_3\b'))
       OR (table.`type` = 1
           AND ((table.`title` REGEXP '\bkeyword_1\b'
                 OR table.`title` REGEXP '\bkeyword_2\b'
                 OR table.`title` REGEXP '\bkeyword_3\b')
                AND (table.`description` REGEXP '\bkeyword_1\b'
                     AND table.`description` REGEXP '\bkeyword_2\b'
                     AND table.`description` REGEXP '\bkeyword_3\b'))))
ORDER BY table.`type` ASC,
         CASE
             WHEN table.`type` = 0 THEN INSTR(table.`title`, 'keyword_1 keyword_2 keyword_3')
             WHEN table.`type` = 1 THEN INSTR(concat(table.`title`, table.`description`), 'keyword_1 keyword_2 keyword_3')
         END ASC
LIMIT 0,20

但我没有得到我想要的。谁能帮我解决这个问题？

提前感谢您的时间和帮助！

标签： mysql