* wildcard only matching whitespace in mysql match against

Question

I am trying to set up a search function on my web site. I have a table called projects with the following content:

+----+--------+------------------------------------------------------------+--------------+--------+----------+---------+------------+
| id | name   | description                                                | creator      | visits | file     | version | date       |
+----+--------+------------------------------------------------------------+--------------+--------+----------+---------+------------+
| 16 | test 1 | <h1>This is a test thing.</h1>
<h3>The file is blank.</h3> | warriorjacq9 |      1 | imported |       0 | 2022-09-23 |
+----+--------+------------------------------------------------------------+--------------+--------+----------+---------+------------+

When I run this sql query:

SELECT * FROM projects WHERE MATCH(name, creator) AGAINST ('*warr*');

it returns an empty set. However, when I change 'warr' to 'warriorjacq9', it returns the row. I am wondering why this happens and if there is a different way I should do this to make it work the way I want it to.

Answer 1

FULLEXT does not handle leading wildcards.

LIKE and REGEXP, though slower, does.

So, either limit users to providing the start of a word and use FULLTEXT, or use some other query.