查问我看笔记功能的实现过程之三-MYSQL8.0全文索引使用​

mysql无组件中文全文索引,兼容各个版本,上亿数据亲测无压力

PHPer 2020-04-10 856 0 0
感觉这个思路好像不错,先切分为单个的汉字,但每个要查询的字段,都要建立一个对应的字段,数据量多了不少。20200410 2341

一直有人找我咨询全文索引问题,特别是sphinx问题。我给大家一个比sphinx更容易上手的方案:

一、原理

mysql较早版本不支持全文索引是因为字节切分问题,数据是二进制存储的,有的字符是2个字节,有的是3个或者4个,mysql无法精确的处理好切割位置。

我这个方案是定义切割字符,预先切割好数据存入数据库。

方案已经稳定使用6年,上过亿级数据,查询无压力和延时

二、直接给大家一个php语言写的案例

/*
表结构:
CREATE TABLE `full_text` (
  `id` int(11) NOT NULL,
  `text` mediumtext NOT NULL,
  `full_index` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
ALTER TABLE `full_text` ADD PRIMARY KEY (`id`);
ALTER TABLE `full_text` ADD FULLTEXT KEY `full_index` (`full_index`);
ALTER TABLE `full_text` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
*/
define('FTS',"|");
//text为原文本,full_index为全文索引
//插入数据
$text = "《唐诗三百首》选诗范围相当广泛,收录了77家诗";
$text = toUtf8($text);//UTF8并非必要
$full_index = fullTextSplit($text,true);
$insert_sql = "INSERT INTO `full_text` (`id`, `text`, `full_index`) VALUES (NULL, '".$text."', '".$full_index."')";
echo $insert_sql,"\r\n";
//查询语句
$query_field = 'full_index';//索引字段
$query_single_word = '唐诗';
$query_sql = "SELECT * FROM `full_text` WHERE ".keyword2FullTextSql($query_single_word,$query_field);
echo $query_sql,"\r\n";
$query_multi_word = '唐诗 收录';
$query_sql = "SELECT * FROM `full_text` WHERE ".keyword2FullTextSql($query_multi_word,$query_field);
echo $query_sql,"\r\n";
//索引子串
function fullTextSplit($text,$wrap = false){
	if (!defined('FTS')) define('FTS', '|');
	$cind = 0;
	$new_text = FTS;
	for($i = 0; $i < strlen($text); $i++){
		if(strlen(substr($text, $cind, 1)) > 0){
			if(ord(substr($text, $cind, 1)) < 192){
				if(preg_match("/[a-zA-Z0-9\.]/",substr($text, $cind, 1))){
					$is_numeric = preg_match("/[0-9\.]/",substr($text, $cind, 1));
					if($cind  $is_numeric_next){
						$new_text .= substr($text, $cind, 1).FTS;
					}else{
						$new_text .= substr($text, $cind, 1);
					}					
				}else{
					$new_text .= FTS;
				}
				$cind++;
			}elseif(ord(substr($text, $cind, 1)) < 224) {
				$new_text .= FTS.substr($text, $cind, 2);		
				$cind+=2;
			}else{
				$new_text .= FTS.substr($text, $cind, 3).FTS;		
				$cind+=3;
			}
		}
	}
	$new_text = explode(FTS,$new_text);
	$new_text = arrayFilter($new_text);
	if($wrap){
		return FTS. implode(FTS,$new_text) .FTS;
	}else{
		return implode(FTS,$new_text);
	}
}
//查询sql
function keyword2FullTextSql($keyword,$field){
	$keyword = explode('|',$keyword);
	$keyword = array_unique(array_diff($keyword,array('',NULL,false)));
	$keyword_sql = array();
	if(!empty($keyword)){
		$return = '';
		foreach($keyword as $kw){
			$kw = explode(' ',$kw);
			$kw = array_unique(array_diff($kw,array('',NULL,false)));
			if(!empty($kw)){
				foreach($kw as $key => $val){
					$kw[$key] = '"'.fullTextSplit($val,true).'"';
				}
				$keyword_sql[] = ' MATCH(`'.$field.'`) AGAINST(\'+'.implode(' +',$kw).'\'  IN BOOLEAN MODE)';
			}
		}
	}
	if(!empty($keyword_sql)){
		return '('. implode(" OR ",$keyword_sql) .')';
	}else{
		return false;
	}
}
//转换编码(非必须)
function toUtf8($text,$ignore = false){
	if(is_array($text)){
		foreach($text as $k=>$v){
			$text[$k] = toUtf8($text[$k],$ignore);
		}
		return $text;
	}else{
		$charset = mb_detect_encoding($text,array('UTF-8','ASCII','EUC-CN','CP936','BIG-5','GB2312','GBK'));
		if ($charset != 'UTF-8' && !empty($charset)){
			@$text = mb_convert_encoding($text, "UTF-8", $charset);
		}else{
			@$text = mb_convert_encoding($text, "UTF-8", 'auto');
		}
		if($ignore) $text = iconv('UTF-8','UTF-8//IGNORE',$text);
		return preg_replace ( '/()/i', "\\1UTF-8\\3", $text, 1 );
	}
}
//数组过滤
function arrayFilter($array){
	$array = array_diff($array,array('',NULL,false));
	return $array;
}
\s+.+?content=".+?charset=)(.+?)("\s?\>($text)){>


...

立即注册

更新于:2020-04-10 23:41:59
相关内容

查问我看笔记功能的实现过程之三-MYSQL8.0全文索引使用​

MYSQL8.0全文索引使用概述:在一堆文字中找到含有关键字的应用。当然也可以用以下语句实现:SELECT * FROM <表名> WHERE <字段名> like ‘%ABC%’但是它的效率太低,是全盘扫描。Mysql 提供了更高效的方法全文索引(FULLTEXT)重要:Mysql 5.6之前版本,只有myisam支持全文索引,5.6之后,Innodb和myisam均支持全文...

mysql数据库存入报错,好像是内容超出了Text格式的长度

mysql数据库存入报错之二,好像是内容超出了Text格式的长度

实测,mysql8 中文全文索引

Mysql8全文索引查询实测-match(title) against

mysql无组件中文全文索引,兼容各个版本,上亿数据亲测无压力

Yii2中使用order by locate的写法

Yii2中自带分页类实现分页

用match查询yi和yii返回结果都是0条,索引用的是ngram

关于mysql命令的使用问题

mysqldump这个命令不是登录数据库操作,直接centos命令行输入运行的,我之前记错了

一键安装LNMP1.8

服务器配置是1核CPU,1G内存,操作系统Centos8 Stream x64

查问我看笔记功能的实现过程2

查问我看笔记功能的实现的重点就是全文搜索,如果不用Yii自带的ActiveRecord的话,就要找扩展,先找了个yii-xunsearch,不行太差了,又找了yiisoft/yii2-elasticsearch,看了下使用方法和网上的文章,感觉太复杂了。试试yiisoft/yii2-sphinx yiisoft/yii2-sphinx也坑了,安装很简单,一行代码,但...

yii-xunsearch全文搜索扩展的各种坑-于是果断到YiiFramework官网上找需要的

Yii的几个著名数据库相关的扩展介绍​MongoDB,ElasticSearch,Sphinx

这里把用的linux命令记录下

Sphinx Extension for Yii 2

Mysql全文索引查不到数据的问题

MySQL如何重建索引

mysql8使用自带全文索引(带中文分词)

PHP获取类名及所有函数名

查问我看笔记功能的实现之3

查问我看笔记功能的实现之4

推荐内容

如何注册Spotify,注册中遇到的问题

起因是找阿特拉斯耸耸肩3里片尾的歌曲,后面用谷歌插件 aha music找到了歌曲名字和作曲人:The Beginning Elia Cmiral,然后资料在spotify有,于是就注册,甚至通过远程服务器,在服务器上打开浏览器也...

Spotify无法注册,想了很多办法后无果于是联系客服

Spotify无法注册,想了很多办法后无果于是联系客服

客服回答中国地区现在无法注册 If you still need help, contact Spotify Support.

mysql8使用自带全文索引(带中文分词)

如果之前建立全文索引,要先删除建立的索引,然后用下面的重新建立索引,亲测有效,nice 20200408 1307 ALTER TABLE `w_note` DROP INDEX content ALTER TABLE `w_note` ADD FULLT...

MySQL如何重建索引

总结一下MySQL索引重建的方法:1: DROP INDEX + RECREATE INDEX.2: ALTER TABLE方法3: REPAIR TABLE方法,这种方法对于InnoDB存储引擎的表无效。4: OPTI...

linux Centos8邮件服务器的搭建和使用

echo "content" | mail -s "title" xxx@gmail.com vi /etc/postfix/master.cf 更改配置文件把#smtps inet n - - - - smtpd 注释去掉 保存退出,重启postfix服务,报错 [root@vultrguest ~]# sudo systemctl restart ...

webmin和postfix