Mysql使用MATCH（）AGAINST（）搜索字符串和数字

I have a problem with the MATCH AGAINST function.

我的MATCH AGAINST功能有问题。

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?

如何在FULL TEXT表的列中搜索字符串和数字？

Thanks

谢谢

2 个解决方案

#1

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

如果您需要菲亚特和500任何地方订单无关紧要，那么

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

If you need Fiat 500 together, then

如果你需要菲亚特500，那么

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

If you need Fiat and zero or more 500, then

如果你需要菲亚特和零或更多500，那么

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

If you need 500 and zero or more Fiat, then

如果您需要500和零或更多菲亚特，那么

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

Give it a Try !!!

试一试 ！！！

UPDATE 2013-01-28 18:28 EDT

Here are the default settings for FULLTEXT searching

以下是FULLTEXT搜索的默认设置

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>

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:

请注意，ft_min_word_len默认为4。令牌500的长度为3.因此根本不会对其进行索引。你将不得不做三（3）件事：

STEP 01 : Configure for smaller string tokens

Add this to /etc/my.cnf

将其添加到/etc/my.cnf

[mysqld]
ft_min_word_len = 1

STEP 02 : Restart mysql

service mysql restart

STEP 03 : Reindex all indexes in the models table

You could just drop and add the FULLTEXT index

您可以删除并添加FULLTEXT索引

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;

Give it a Try !!!

试一试 ！！！

#2

Just in case it helps others, if you're using InnoDB you need to use a different set of mysql.cnf properties

为了防止其他人，如果你使用的是InnoDB，你需要使用一组不同的mysql.cnf属性

eg

例如

show variables like 'innodb_ft%';

and in my.cnf set the following value instead of ft_min_word_len

并在my.cnf中设置以下值而不是ft_min_word_len

innodb_ft_min_token_size=1