mysql全文インデックス、非効率なlikeクエリーを回避

796 ワード

1、データベースプロファイルの変更my.cnf innodb_ft_min_token_size=2 ft_min_word_len=2再起動
2、表例CREATE TABLE fullidx_test(id bigint(20)NOT NULL AUTO_INCREMENT, tittle varchar(255) DEFAULT NULL, book_mark varchar(255) DEFAULT NULL, PRIMARY KEY ( id ), FULLTEXT KEY book_mark ( book_mark ) WITH PARSER ngram ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
3、照会方式select*from fullidx_test WHERE MATCH(book_mark)AGAINST(‘trade’IN NATURAL LANGUAGE MODE);select * from fullidx_test WHERE MATCH ( book_mark ) AGAINST (‘trade’ IN BOOLEAN MODE);
SELECT * FROM fullidx_test WHERE MATCH(book_mark)AGAINST(‘夏季’IN BOOLEAN MODE)