Mysql 使用 MATCH() AGAINST() 搜索字符串和数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14572375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql search for string and number using MATCH() AGAINST()
提问by Gambric
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?
如何在全文表的一列中搜索字符串和数字？
Thanks
谢谢
回答by RolandoMySQLDBA
If you need Fiatand 500anywhere where order does not matter, then
如果您需要Fiat并且500在任何订单无关紧要的地方，那么
SELECT * FROM models MATCH(name) AGAINST('+Fiat +500');
If you need Fiat 500together, then
如果你需要Fiat 500一起，那么
SELECT * FROM models MATCH(name) AGAINST('+"Fiat 500"');
If you need Fiatand zero or more 500, then
如果您需要Fiat零个或多个500，则
SELECT * FROM models MATCH(name) AGAINST('+Fiat 500');
If you need 500and zero or more Fiat, then
如果您需要500零个或多个Fiat，则
SELECT * FROM models MATCH(name) AGAINST('Fiat +500');
Give it a Try !!!
试一试 ！！！
UPDATE 2013-01-28 18:28 EDT
更新 2013-01-28 18:28 EDT
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>
Notice that ft_min_word_lenis 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
步骤 01：配置较小的字符串令牌
Add this to /etc/my.cnf
将此添加到 /etc/my.cnf
[mysqld]
ft_min_word_len = 1
STEP 02 : Restart mysql
步骤 02：重启 mysql
service mysql restart
STEP 03 : Reindex all indexes in the modelstable
步骤 03：重新索引models表中的所有索引
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 !!!
试一试 ！！！
回答by ashario
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
