问题描述
我的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')
如何在FULL TEXT表的一列中同时搜索字符串和数字?
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');
尝试一下!
这是FULLTEXT搜索的默认设置
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
STEP 02:重新启动mysql
service mysql restart
STEP 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()搜索字符串和数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!