mysql全文索引实现模糊查询

配置mysql的ngram,打开mysql.ini的配置文件,编辑在[mysqld]下面加入这样的配置

# vim /etc/my.cnf
[mysqld]
ngram_token_size=2
ft_min_word_len=1 //最小分词长度(默认是4,小于4则忽略搜索)

保存退出,并重启mysql

# service mysql restart

再登入mysql,并通过命令查看:

mysql> show variables like 'ngram_token_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 1     |
+------------------+-------+

创建测试表

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `data` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name` (`name`) WITH PARSER ngram 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test VALUES(null,'welcome to you!',unix_timestamp());
INSERT INTO test VALUES(null,'this is the fan site of you xp 你好',unix_timestamp());
INSERT INTO test VALUES(null,'hello phpjs,you are welcome',unix_timestamp());
INSERT INTO test VALUES(null,'fuck you tomorrow!!',unix_timestamp());
//查询语句
mysql> SELECT *  FROM test WHERE MATCH ( name ) against ( 'you' IN BOOLEAN MODE );

💫 评论卡

备案号:粤ICP备18155514号-2