实战之给博客加全文索引
时间:2022-04-02 17:42:03.004 +0800 CST 浏览:164

卸载

systemctl stop mariadb
mv /etc/my.cnf /etc/my.cnf.bak
yum remove mariadb mariadb-server

升级

cat /etc/yum.repos.d/mariadb.repo

# MariaDB 10.5 CentOS repository list - created 2022-03-29 13:11 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.5/centos8-amd64
module_hotfixes=1
gpgkey=https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
sudo yum makecache
sudo yum install -y MariaDB-client MariaDB-server
sudo systemctl enable mariadb
sudo systemctl start mariadb

# 对DB进行升级
mysql_upgrade  -u root -p

# 加载mroonga插件
mysql -u root
> INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';
> CREATE FUNCTION last_insert_grn_id RETURNS INTEGER SONAME 'ha_mroonga.so';
> CREATE FUNCTION mroonga_snippet RETURNS STRING SONAME 'ha_mroonga.so';
> CREATE FUNCTION mroonga_command RETURNS STRING SONAME 'ha_mroonga.so';
> CREATE FUNCTION mroonga_escape RETURNS STRING SONAME 'ha_mroonga.so';
> exit

设置密码

升级后虽然使用原来的密码能登录,但是需要再重新设置一次密码。

mysql -u -p
> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

> SET PASSWORD = PASSWORD('new_password');

切换引擎

show engines; # 查看支持的引擎
# 如果原来的表使用的是InnoDB的话,执行下面第一条会报错,可执行下面的第二条,先切换到MyISAM,再切回到InnoDB。
ALTER TABLE `articles` ENGINE = Mroonga COMMENT = 'engine "InnoDB"';
ALTER TABLE `articles` ENGINE = Mroonga COMMENT = 'engine "MyISAM"';
# ALTER TABLE `articles` ENGINE = Mroonga;

添加全文索引

https://mroonga.org/docs/tutorial/storage.html#how-to-specify-the-parser-for-full-text-search

# 创建全文索引
ALTER TABLE `articles` ADD FULLTEXT INDEX  idx_fulltext(title,content,tags)  COMMENT 'tokenizer "TokenBigramSplitSymbolAlphaDigit"'
# 使用全文索引查询
SELECT * FROM `articles` USE INDEX(idx_fulltext) WHERE (MATCH(title,content,tags) AGAINST('*D+ "更新" "不生效" "清理缓存"' IN BOOLEAN MODE)) LIMIT 1 \G;

查看索引

SHOW INDEX FROM `articles`;

删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

参考

https://deepskyfire.com/sub/23.html
https://qiita.com/nayuneko/items/e1d4cad31b9ec23fd12c
https://mroonga.org/docs/tutorial/storage.html#how-to-specify-the-parser-for-full-text-search



如果这篇文章对你有所帮助,可以通过下边的“打赏”功能进行小额的打赏。

本网站部分内容来源于互联网,如有侵犯版权请来信告知,我们将立即处理。


来说两句吧