mysql fulltext 不起作用,使用MyISAM表的MySQL（Windows10）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:

select * from Temp_Table_MyISAM;

+----+---------+----------------+

| id | code | name |

+----+---------+----------------+

| 1 | AC-7865 | 38 NORTHRIDGE |

| 2 | DE-3514 | POLARIS VENTRI |

+----+---------+----------------+

select * from Temp_Table_InnoDB;

+----+---------+----------------+

| id | code | name |

+----+---------+----------------+

| 1 | AC-7865 | 38 NORTHRIDGE |

| 2 | DE-3514 | POLARIS VENTRI |

+----+---------+----------------+

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

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM

WHERE MATCH(name) AGAINST('38');

Empty set (0.00 sec)

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM

WHERE MATCH(name) AGAINST('POLARIS');

Empty set (0.00 sec)

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]> SELECT name, code FROM Temp_Table_InnoDB

WHERE MATCH(name) AGAINST('38');

Empty set (0.00 sec)

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.

解决方案

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

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

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

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

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