How to search fuzzy matches in MySQL?

Question

MySQL

Greetings!
How to implement this kind of search in MySQL and is it really possible?
Search string: St. Petersburg, ter Aviagorodok
Line in the table: Aviagorodok, St. Petersburg
Search string: St. Petersburg, ter. snt Toriki Line in
the table: Toriki, Krasnoselsky district, St. Petersburg
Search line: d . St. Petersburg
Line in the table: St. Petersburg . Plus, you need to rank - when querying Moscow, the result is Moscow


should take precedence over the results of Desenovskoye, Moscow and Butovo Yuzhnoye, Moscow .
And so on, there are about 16 thousand records, the search is performed once for each unique (the project is not a highload, a niche online store).
I tried to conjure with FULLTEXT-search, but so far nothing sensible has come of it.
The use of third-party modules is being considered - Sphinx, Elastic, maybe even nosql databases for some intermediate operations, but the working database of the project is MySQL.
Now the main thing is to understand in which direction to dig.
Thank you all, in the end we managed to write our own algorithm based on simple conditions and the assertion that the address segments in our data are in the reverse order of the original address.
The request is divided by commas, all types are cut out (district, district, region, AO, city, village, farm, snt, etc.), "deployed", then the search is performed sequentially: city + district + region, city + region , city. The search stops at the level at which the result appeared.

Answer 1

mowed oblique oblique oblique

there are no such miracles in Muskul, it cannot be, and it will appear no earlier than in 15 years,
dig in the store like Uber, 2GIs and the same Yandex solve this problem, if for an adult (all sorts of Entity namig recogntion, Tamita parser, vectors on stemmed cowords, etc. .p.)
in a childish way, you can make an algorithmic (with elements of an adult) normalization table:

n Resurrection => Resurrection settlement
Resurrection n => Resurrection settlement

etc.

Answer 2

Perhaps "normalization" from various third-party APIs will help you, ala dadata. It is on your side to store the normalized version, and through the dadata API to normalize the request from the client.

Answer 3

Well, then consider mature search modules, and not the invention of bicycles, which will still be unsuccessful, and even require more time to maintain and refine.
Theoretically, it seems to be a bit of work: to parse the texts in which you will search into the database of keys and search by them ... but practically the same Sphinx already knows how to do this without dancing with a tambourine, and what else do you need?

Answer 4

Once upon a time in antiquity, I wrote an answer-sheet to a similar question. The most effective implementation in the "shit and sticks" nomination.