使用MyISAM表的MySQL(Windows10)FULLTEXT搜索不起作用 [英] MySQL (Windows10) FULLTEXT searching with MyISAM tables not working

问题描述

我有一个问题，已经能够使用两个非常简单的表来重新创建.这些表的定义如下:

I have a problem which I have been able to recreate with two very simple tables. The tables were defined as follows:

create table Temp_Table_MyISAM(  
  id  INT UNSIGNED AUTO_INCREMENT,  
  code VARCHAR(10) NOT NULL,  
  name VARCHAR(256) NOT NULL,    
  PRIMARY KEY (id),  
  KEY (code),  
  FULLTEXT (name)  
) ENGINE = MYISAM;  


create table Temp_Table_InnoDB(  
  id  INT UNSIGNED AUTO_INCREMENT,  
  code VARCHAR(10) NOT NULL,  
  name VARCHAR(256) NOT NULL,  
  PRIMARY KEY (id),  
  KEY (code),  
  FULLTEXT (name)  
);  

每个表都有两行，从以下两个查询的结果可以看出:

Each table has two rows, as can be seen from the result of the following two queries:

从Temp_Table_MyISAM中选择*；

 +----+---------+----------------+  
 | id | code    | name           |  
 +----+---------+----------------+  
 |  1 | AC-7865 | 38 NORTHRIDGE  |  
 |  2 | DE-3514 | POLARIS VENTRI |  
 +----+---------+----------------+  

从Temp_Table_InnoDB中选择*；

 +----+---------+----------------+  
 | id | code    | name           |  
 +----+---------+----------------+  
 |  1 | AC-7865 | 38 NORTHRIDGE  |  
 |  2 | DE-3514 | POLARIS VENTRI |  
 +----+---------+----------------+  

当我在MyISAM表上进行全文搜索时，我没有得到任何点击次数

When I do a FULLTEXT search on the MyISAM table, I don't get any hits

MariaDB [stackoverflow]> 选择名称，代码来自Temp_Table_MyISAM 在哪里匹配(名称)反对("38")；
空置(0.00秒)

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM WHERE MATCH(name) AGAINST('38');
Empty set (0.00 sec)

MariaDB [stackoverflow]> 选择名称，代码来自Temp_Table_MyISAM 在哪里匹配(名称)反对("POLARIS")；
空置(0.00秒)

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM WHERE MATCH(name) AGAINST('POLARIS');
Empty set (0.00 sec)

当我在InnoDB表上执行FULLTEXT搜索时，仅当要匹配的模式不是以数字值开头时，我才会被击中

When I do a FULLTEXT search on the InnoDB table, I get a hit only when the pattern to be matched does not start with a numeric value

MariaDB [stackoverflow]> 选择名称，代码来自Temp_Table_InnoDB 在哪里匹配(名称)反对("38")；
空置(0.00秒)

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_InnoDB WHERE MATCH(name) AGAINST('38');
Empty set (0.00 sec)

MariaDB [stackoverflow]> 选择名称，代码来自Temp_Table_InnoDB 在哪里匹配(名称)反对("POLARIS")；

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_InnoDB WHERE MATCH(name) AGAINST('POLARIS');

+----------------+---------+  
| name           | code    |  
+----------------+---------+  
| POLARIS VENTRI | DE-3514 |  
+----------------+---------+  

任何见识将不胜感激.

Any insight would be appreciated.

推荐答案

在MyISAM的FULLTEXT中需要注意3条规则:

There are 3 rules to watch out for in MyISAM's FULLTEXT:

• 短于ft_min_word_len(默认4个字符)的文本单词将不被索引. ("38")

• Text words shorter than ft_min_word_len (default 4 characters) will not be indexed. ("38")

显示在超过50％或更多行中的搜索词将被忽略. (北极星")

Search words that show up in more 50% or more of the rows, will be ignored. ("Polaris")

文本中的热门单词"未编入索引. ("the"，"and"，...)

"Stop words" in the text are not indexed. ("the", "and", ...)

由于InnoDB现在支持FULLTEXT，因此您应该转到该引擎. (那里的规则是不同的.)

Since InnoDB now supports FULLTEXT, you should move to that engine. (And the rules are different there.)

这篇关于使用MyISAM表的MySQL(Windows10)FULLTEXT搜索不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！