Mysql 使用 MATCH() AGAINST() 搜索字符串和数字 [英] Mysql search for string and number using MATCH() AGAINST()

问题描述

我的 MATCH AGAINST 函数有问题.

I have a problem with the MATCH AGAINST function.

以下查询给了我相同的结果:

The following query give me the same result:

SELECT * FROM models MATCH(name) AGAINST('Fiat 500')
SELECT * FROM models MATCH(name) AGAINST('Fiat')

如何在全文表的一列中同时搜索字符串和数字?

How can I search for both strings and numbers in a column of a FULL TEXT table?

谢谢

推荐答案

如果您需要 Fiat 和 500 任何顺序无关紧要的地方，那么

If you need Fiat and 500 anywhere where order does not matter, then

SELECT * FROM models MATCH(name) AGAINST('+Fiat +500');

如果你需要Fiat 500一起，那么

SELECT * FROM models MATCH(name) AGAINST('+"Fiat 500"');

如果您需要 Fiat 和零个或多个 500，那么

If you need Fiat and zero or more 500, then

SELECT * FROM models MATCH(name) AGAINST('+Fiat 500');

如果您需要 500 和零个或多个 Fiat，那么

If you need 500 and zero or more Fiat, then

SELECT * FROM models MATCH(name) AGAINST('Fiat +500');

试一试！！！

这里是全文搜索的默认设置

Here are the default settings for FULLTEXT searching

mysql> show variables like 'ft%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (0.00 sec)

mysql>

请注意ft_min_word_len 默认为 4.令牌 500 的长度为 3.因此它根本不会被索引.您必须做三 (3) 件事:

Notice that ft_min_word_len is 4 by default. The token 500 is length 3. thus it will not be indexed at all. You will have to do three(3) things:

将此添加到/etc/my.cnf

[mysqld]
ft_min_word_len = 1

步骤 02:重启 mysql

service mysql restart

第 03 步:重新索引 models 表中的所有索引

您可以删除并添加 FULLTEXT 索引

STEP 03 : Reindex all indexes in the models table

You could just drop and add the FULLTEXT index

或者分阶段做，提前看看会变多大

or do it in stages and see how big it will get in advance

CREATE TABLE models_new LIKE models;
ALTER TABLE models_new DROP INDEX name;
ALTER TABLE models_new ADD FULLTEXT name (name);
ALTER TABLE models_new DISABLE KEYS;
INSERT INTO models_new SELECT * FROM models;
ALTER TABLE models_new ENABLE KEYS;
ALTER TABLE models RENAME models_old;
ALTER TABLE models_new RENAME models;

当你对这个工作感到满意时，然后运行

When you are satisfied this worked, then run

DROP TABLE models_old;

试一试！！！

这篇关于Mysql 使用 MATCH() AGAINST() 搜索字符串和数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！