MySQL (Windows10) FULLTEXT searching with MyISAM tables not working(MySQL(Windows10)使用 MyISAM 表进行 FULLTEXT 搜索不起作用)
问题描述
我有一个问题,我可以用两个非常简单的表重新创建它.表定义如下:
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.)
这篇关于MySQL(Windows10)使用 MyISAM 表进行 FULLTEXT 搜索不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:MySQL(Windows10)使用 MyISAM 表进行 FULLTEXT 搜索不起作用
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 更改自动增量起始编号? 2021-01-01
- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01
- 在SQL中,如何为每个组选择前2行 2021-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- SQL 临时表问题 2022-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01