MySQL(Windows10)使用 MyISAM 表进行 FULLTEXT 搜索不起作用

2022-01-15 00:00:00 mariadb mysql

我有一个问题，我可以用两个非常简单的表重新创建它.表定义如下:

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 表进行 FULLTEXT 搜索时，我没有得到任何结果

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

MariaDB [stackoverflow]> 选择名称，代码 FROM Temp_Table_MyISAMWHERE MATCH(name) 反对('38');
空集(0.00 秒)

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

MariaDB [stackoverflow]> 选择名称，代码 FROM Temp_Table_MyISAMWHERE MATCH(name) AGAINST('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]> 选择名称，代码 FROM Temp_Table_InnoDBWHERE MATCH(name) 反对('38');
空集(0.00 秒)

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

MariaDB [stackoverflow]> 选择名称，代码 FROM Temp_Table_InnoDBWHERE MATCH(name) AGAINST('POLARIS');

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

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

任何见解将不胜感激.

推荐答案

在 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.)

相关文章