It is too late to introduce ES, so we decide to use mysql full-text index to implement it in situ. In the process, we step on some mistakes:

In the beginning, the FULLTEXT index was simply used, but when searching, the result was not satisfactory. It turned out that the default word segmentation strategy of MYSQL FULLTEXT index was wrong. Chinese all follow the same word segmentation method. That is, if you don’t search for the full Name in Chinese, INNATURE LANGUAGE MODE won’t yield anything at all. Full-text index document: dev.mysql.com/doc/refman/… But when refer to documents discovered a ngram the interpreter, mysql’s own ngram official document: dev.mysql.com/doc/refman/…

  1. Because these tables are already existing, Navicat or DDL statements can be used to create new tables. I rely on graphical tools to create new tables, but the first pit comes:

After changes made directly on the original index are savedIt didn’t work!!But I didn’t know what was displayed and still couldn’t get results. I checked later and found I couldn’t trust Navicat completely

  1. DDL error: error: DDL error: error: DDL error: error: DDL error:

    ALTER TABLE biz_shop_spu DROP INDEX IDX_NAME_FULLTEXT, ADD FULLTEXT INDEX IDX_NAME_FULLTEXT(NAME) WITH PARSER NGRAM;

    Correct:

    ALTER TABLE biz_shop_spu DROP INDEX IDX_NAME_FULLTEXT;

    ALTER TABLE biz_shop_spu ADD FULLTEXT INDEX IDX_NAME_FULLTEXT(NAME) WITH PARSER NGRAM;

    You must ALTER the table or DROP the old index and insert the index, which FlyWay does not error but still does not use Ngram.

    Flyway execution result diagram:

  2. At this point, an index of full-text types using Ngram as the interpreter is established, but the segmentation will only be for new data, The OPTIMIZE TABLE biz_shop_spu command is used to OPTIMIZE TABLE biz_shop_spu

This is the end of the pit

Here are some ngram tips

  • Observe index participles:

SHOW VARIABLES LIKE ‘%ft%’; Show variables like ‘ngram_token_size%’; The default is 2

  • Explicitly specify full-text retrieval table sources:

SET GLOBAL innodb_fT_aux_TABLE =”db_name/tableName” SELECT * FROM information_schema. Innodb_ft_index_cache ORDER BY doc_id , position; You can see which words are in the full-text index