MYSQL全文搜索数字和字符被忽略?

本文介绍了MYSQL全文搜索数字和字符被忽略?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试进行全文搜索，我正在查询的数据库有很多 LCD 屏幕尺寸.例如 32".我需要进行全文搜索，因为搜索短语可能很复杂，我们从 LIKE 比较开始，但它并没有削减它.

I"m trying to do a full text search, the database I'm querying has a lot of LCD screen sizes. Eg. 32". I need to do a full text search as a search phrase can be complex, we started with a LIKE comparison but it didn't cut it.

这就是我们所拥有的.

SELECT stock_items.name AS si_name,
       stock_makes.name AS sm_name,
       stock_items.id AS si_id
FROM stock_items
LEFT JOIN stock_makes ON stock_items.make = stock_makes.id
WHERE MATCH (stock_items.name,
             stock_makes.name) AGAINST ('+32"' IN BOOLEAN MODE)
  AND stock_items.deleted != 1

通过这次搜索，我们得到 0 个结果.尽管 32" 在该领域多次出现.我们修改了 mysql 配置，允许我们搜索 3 个或更多字符(而不是默认的四个)，并且像 +NEC 这样的搜索工作正常.

With this search we get 0 results. Although 32" Appears multiple times in the fields.We have modified the mysql config to allow us to search 3 characters or more (instead of the default four) and searches like +NEC work fine.

我的猜测是 a) mysql 中的全文搜索忽略 " 字符或数字.

My guess is here that either a) full text search in mysql ignores " character or maybe the numbers.

不幸的是，我无法控制数据库数据，否则我会用其他东西替换双引号.还有其他解决方案吗?

I don't have any control over the database data unfortunately or I'd replace the double quotes with something else. Any other solutions ?

推荐答案

MySQL 在索引时忽略某些字符，我认为是其中之一.如此处所述，有几种方法可以更改默认字符设置

MySQL ignore cirtain characters when indexing, " is one of them I presume.There are few ways to change the default character settings as described here

修改 MySQL 源代码:在 myisam/ftdefs.h 中，查看 true_word_char() 和 misc_word_char() 宏.将-"添加到这些宏之一并重新编译 MySQL.

Modify the MySQL source: In myisam/ftdefs.h, see the true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.

修改字符集文件:这不需要重新编译.true_word_char() 宏使用字符类型"表来区分字母和数字与其他字符..您可以在其中一个字符集 XML 文件中编辑数组的内容，以指定-"是一个字母".然后为 FULLTEXT 索引使用给定的字符集.

Modify a character set file: This requires no recompilation. The true_word_char() macro uses a "character type" table to distinguish letters and numbers from other characters. . You can edit the contents of the array in one of the character set XML files to specify that '-' is a "letter." Then use the given character set for your FULLTEXT indexes.

为索引列使用的字符集添加新的排序规则，并更改列以使用该排序规则.

Add a new collation for the character set used by the indexed columns, and alter the columns to use that collation.

我使用了第二种方法，这对我们有用.提供的页面在页面末尾的评论(John Navratil 的评论).

I used the second approach and that worked for us. The provided page has a detailed example in the comments at the end of the page (comment by John Navratil).

在任何情况下，您都需要在更改设置后重建索引:REPAIR TABLE tbl_name QUICK;

In any case you need to rebuild the index after you changed settings: REPAIR TABLE tbl_name QUICK;

这篇关于MYSQL全文搜索数字和字符被忽略?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！