コミュニティ 学ぶ ツールライブラリ レジャー
検索
日本語
ホームページ > データベース > mysql チュートリアル > mysql ft とは何を指しますか?

mysql ft とは何を指しますか?

PHPz
リリース： 2023-04-14 17:42:03
転載
1605 人が閲覧しました

mysql ft は FullText、つまりフルテキスト インデックスを指します。フルテキスト インデックスは、正確な数値比較ではなく、類似性に基づいてクエリを解決するためのものです。フルテキスト インデックスは N 回作成できます。大量のデータに直面したときよりも速く、速度は桁違いではありません。

MySQL フルテキスト インデックス (FullText)

1. はじめに

基本概念

フルテキスト インデックス正確な数値比較ではなく、類似性に基づいてニーズ クエリを解決することです。

あいまい一致は like % を使用して実現することもできますが、大量のテキスト データの検索では考えられません。大量のデータに直面した場合、全文インデックス作成は like よりも N 倍高速になる可能性がありますが、その速度は桁違いではありません。

バージョンのサポート

  1. MySQL 5.6 以前のバージョンでは、MyISAM ストレージ エンジンのみがフルテキスト インデックスをサポートしていました

  2. MySQL 5.6 以降のバージョン、MyISAM および InnoDB ストレージ エンジンはフルテキスト インデックスをサポートします

  3. #MySQL 5.7.6 は、中国語、日本語、および韓国語 (CJK) をサポートする組み込みのフルテキスト ngram パーサー と、インストール可能な MeCab for Japanese フルテキスト パーサー プラグイン

  4. フルテキスト インデックスは

    InnoDB または MyISAM でのみ使用できますテーブルであり、CHAR、VARCHAR、TEXT列の作成

  5. 大規模なデータ セットの場合にのみ使用できます。 フルテキスト インデックスを使用せずにテーブルにデータをロードしてからインデックスを作成するのは非常に手間がかかります。既存の全文インデックスを使用してテーブルにデータをロードするよりも高速です

  6. RDS MySQL 5.6 中国語の全文検索もサポートしていますが、バグ##があります

    7. #制限事項と欠点

  • 大量のディスク リソースの使用が発生します

    。フルテキスト インデックス自体は、パフォーマンスのためにディスク領域を使用する方法です。全文インデックスが大きい理由は、特定の言語に基づいて単語を分割するためです。

    • 全文インデックスの作成が遅く、さまざまなデータを変更するのに時間がかかるためです。フルテキスト インデックスも遅い

    • フルテキスト インデックスの使用はアプリケーションにとって透過的ではありません。フルテキスト インデックスを使用する場合は、クエリ ステートメントを変更する必要があります。元のクエリ ステートメントにフルテキスト インデックスを使用することは不可能なので、フルテキスト インデックスで指定された構文に変更する必要があります。

    • 大文字と小文字は区別されません

  • ##パーティションテーブルは全文検索をサポートしていません

  • 複数の列で構成される全文検索のインデックスは同じ文字セットと並べ替え規則を使用する必要があります

    • フルテキスト インデックスには精度の問題がある可能性があります
  • 。つまり、フルテキスト インデックスで見つかったデータは

    like 列と一致しない可能性があります。 MATCH() 関数内 IN BOOLEAN MODE モードを使用した全文検索が MyISAM テーブルで使用されていない限り、FULLTEXT インデックスで定義された列とまったく同じである必要があります (検索はインデックスが作成されていない列でも実行できますが、速度が非常に遅い)

    単一列の全文インデックスを個別に作成する場合、複数列のあいまいクエリは有効になりません

  • 完全-テキスト インデックスの異なるテーブルを一緒にクエリすることはできません。2 つのステートメントに OR## を追加できます。

  • #2. フルテキスト インデックスの操作

    • 2.1 最低限の設定を行う検索長

  • SQL コマンドを使用して、現在設定されている最小検索長 (単語分割長) を表示できます。

    SHOW VARIABLES LIKE 'ft%';
    ログイン後にコピー

Variable_name

Value

ft_boolean_syntax -> ;<()~*:""&|ft_max_word_len 84120#ft_stopword_file
##ft_min_word_len
ft_query_expansion_limit
##(組み込み)

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容：

[mysqld]
innodb_ft_min_token_size = 1
# 最短的索引字符串，默认值为4
ft_min_word_len = 1
ログイン後にコピー

配置完后重启 MySQL 服务器，并修复或重建全文索引方可生效。
可使用下面的命令修复：

repair table test quick;
ログイン後にコピー

2.2 创建索引

  • 建表时创建全文索引

CREATE TABLE fulltext_test (
  id int(11) NOT NULL AUTO_INCREMENT,
	content TEXT NOT NULL,
	tag VARCHAR(255),
	PRIMARY KEY (id),
	FULLTEXT KEY content_tag_fulltext(content, tag) WITH PARSER ngram
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;
ログイン後にコピー

  • 在已存在的表上创建全文索引

CREATE FULLTEXT INDEX content_fulltext ON fulltext_test(content) with parser ngram;
ログイン後にコピー

  • 通过 SQL 语句 ALTER TABLE 创建全文索引

ALTER TABLE fulltext_test ADD FULLTEXT INDEX content_fulltext(content) with parser ngram;
ログイン後にコピー

2.3 删除索引

  • 使用 DROP INDEX 删除全文索引

DROP INDEX content_fulltext ON fulltext_test;
ログイン後にコピー

  • 通过 SQL 语句 ALTER TABLE 删除全文索引

ALTER TABLE fulltext_test DROP INDEX content_fulltext;
ログイン後にコピー

三、检索数据

3.1 自然语言的全文检索

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索。

SELECT * FROM 表名 WHERE Match(列名1,列名2) Against (检索内容1 检索内容2);
ログイン後にコピー

检索内容不需要用逗号隔开！

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。
ログイン後にコピー

3.2 布尔全文检索

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

  • 空(也就是默认状况)，表示可选的，包含该词的顺序较高

  • + 表示必须包含

  • - 表示必须排除

  • “>” 表示出现该单词时增加相关性，查询的结果靠前

  • “<” 表示出现该单词时降低相关性，查询的结果靠后

  • * 表示通配符，只能接在词后面

  • ~ 允许出现该单词，但是出现时相关性为负，表示拥有该字会下降相关性，但不像「-」将之排除，只是排在较后面

  • "" 双引号表示短语，表示要彻底相符，不可拆字效果，类同于 like '%keyword%'

  • () 经过括号来使用字条件:

+aaa +(>bbb <ccc) aaa="aaa" sql="sql" select="select" from="from" test="test" where="where" match="match" against="against" in="in" boolean="boolean" mode="mode" select="select" from="from" tommy="tommy" where="where" match="match" against="against" in="in" boolean="boolean" mode="mode" select="select" from="from" tommy="tommy" where="where" match="match" against="against">李秀琴 <练习册 <不是人>是个鬼&#39; in boolean mode);
ログイン後にコピー

四、测试结果

测试环境：本机4核16G Windows10，MySQL 8.0
测试数据量：salebilldetail 表 1276万行，salebill 表 269 万行, customer 表 30 万行, goods 表 75 万行。

争对测试用的SQL语句，增加了以下全文索引：

CREATE FULLTEXT INDEX billno_fulltext ON salebill(billno) WITH PARSER ngram;
CREATE FULLTEXT INDEX remarks_fulltext ON salebill(remarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX remarks_fulltext ON salebilldetail(remarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX goodsremarks_fulltext ON salebilldetail(goodsremarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX remarks_goodsremarks_fulltext ON salebilldetail(remarks, goodsremarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX custname_fulltext ON customer(custname) WITH PARSER ngram;
CREATE FULLTEXT INDEX goodsname_fulltext ON goods(goodsname) WITH PARSER ngram;
CREATE FULLTEXT INDEX goodscode_fulltext ON goods(goodscode) WITH PARSER ngram;
ログイン後にコピー

测试结果，总的来说很魔幻。
为什么魔幻，看下面几个语句：

test_1
-- 测试1，原始 like 查询方式，用时 0.765s
select 1 from salebilldetail d where d.tid=260434 and ((d.remarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)) or (d.goodsremarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)));
ログイン後にコピー
test_2
-- 测试2，使用全文索引 remarks_fulltext、goodsremarks_fulltext, 用时 0.834s
select 1 from salebilldetail d where d.tid=260434 and ((match(d.remarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)) or (match(d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;)  in boolean mode)));
ログイン後にコピー
test_3
-- 测试3，使用全文索引 remarks_goodsremarks_fulltext, 用时 0.242s
select 1 from salebilldetail d where d.tid=260434 and ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)));
ログイン後にコピー
test_4
-- 测试4，原始 like 查询方式，不过滤 tid ，用时 22.654s
select t from salebilldetail d where ((d.remarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)) or (d.goodsremarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)));
ログイン後にコピー
test_5
-- 测试5，使用全文索引 remarks_fulltext、goodsremarks_fulltext,  不过滤 tid ，用时 24.855s
select 1 from salebilldetail d where ((match(d.remarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)) or (match(d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;)  in boolean mode)));
ログイン後にコピー
test_6
-- 测试6，使用全文索引 remarks_goodsremarks_fulltext, 不过滤 tid ，用时 0.213s
select 1 from salebilldetail d where ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)));
ログイン後にコピー
test_7
-- 测试7，使用全文索引 remarks_goodsremarks_fulltext, 用时 0.22s
select count(1) from salebilldetail d where d.tid=260434 and  ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)));
ログイン後にコピー
test_8
-- 测试8，使用全文索引 remarks_goodsremarks_fulltext, 不过滤 tid ，用时 0.007s
select count(1) from salebilldetail d where ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)));
ログイン後にコピー

从上面的测试语句可以看出，数据量越多，查询越简单，全文索引的效果越好。

再来看看我们的业务测试SQL:

test_9
-- 测试9
select 
    i.billid
    ,if(0,0,i.qty) as qty  
    ,if(0,0,i.goodstotal) as total          
    ,if(0,0,i.chktotal) as selfchktotal   
    ,if(0,0,i.distotal) as distotal 
    ,if(0,0,i.otherpay) as feetotal  
    ,if(0,0,ifnull(d.costtotal,0)) as costtotal  
    ,if(0,0,ifnull(d.maoli,0)) as maoli         
    ,i.billno
    ,from_unixtime(i.billdate,&#39;%Y-%m-%d&#39;) as billdate /*单据日期*/
    ,from_unixtime(i.createdate,&#39;%Y-%m-%d %H:%i:%s&#39;) as createdate /*制单日期*/
    ,if(i.sdate=0,&#39;&#39;,from_unixtime(i.sdate,&#39;%Y-%m-%d  %H:%i:%s&#39;)) as sdate /*过账日期*/
    ,from_unixtime(i.udate,&#39;%Y-%m-%d %H:%i:%s&#39;) as udate /*最后修改时间*/
    ,i.custid ,c.custname
    ,i.storeid ,k.storename
    ,i.empid ,e.empname
    ,i.userid ,u.username
    ,i.remarks                               /*单据备注*/
    ,i.effect,i.settle,i.redold,i.rednew     /*单据状态*/
    ,i.printtimes /* 打印次数 */
    ,(case  when i.rednew=1 then 1  when i.redold=1 then 2  when i.settle=1 then 3  when i.effect=1 then 4  else 9 end) as state /*单据状态*/
    ,(case  when i.rednew=1 then &#39;红冲单&#39;  when i.redold=1 then &#39;已红冲&#39;  when i.settle=1 then &#39;已结算&#39;  when i.effect=1 then &#39;已过账&#39;  else &#39;草稿&#39; end) as statetext
    ,&#39;&#39; as susername /* 操作人 */
    ,&#39;&#39; as accname /* 科目 */
from salebill i
left join coursecentersale d on d.tid=i.tid and d.billid=i.billid
left join customer c on c.tid=i.tid and c.custid=i.custid
left join store k on k.tid=i.tid and k.storeid=i.storeid
left join employee e on e.tid=i.tid and e.empid=i.empid
left join user u on u.tid=i.tid and u.userid=i.userid
where i.tid=260434 and (i.billtype = 5 or i.effect = 1)
    and (&#39;_billdate_f_&#39;!=&#39;&#39;)
    and (&#39;_billdate_t_&#39;!=&#39;&#39;)
    and (&#39;_sdate_f_&#39;!=&#39;&#39;)
    and (&#39;_sdate_t_&#39;!=&#39;&#39;)
    and (&#39;_udate_f_&#39;!=&#39;&#39;)
    and (&#39;_udate_t_&#39;!=&#39;&#39;)
    and (&#39;_cdate_f_&#39;!=&#39;&#39;)
    and (&#39;_cdate_t_&#39;!=&#39;&#39;)
    and (&#39;_billid_&#39;!=&#39;&#39;)      /*单据id*/
    and (&#39;_custid_&#39;!=&#39;&#39;)      /*客户ID*/
    and (&#39;_storeid_&#39;!=&#39;&#39;)     /*店仓ID*/
    and (&#39;_empid_&#39;!=&#39;&#39;)       /*业务员ID*/
    and (&#39;_custstop_&#39;!=&#39;&#39;)       /*客户是否停用*/
    and (
        (i.billno like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;))
        or (i.remarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;))
        or exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((d.remarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)) or (d.goodsremarks like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;))))
        or exists(select 1 from customer c where c.tid=260434 and c.custid=i.custid and (c.custname like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)))
        or exists(select 1 from goods g join salebilldetail d on d.tid=g.tid and d.goodsid=g.goodsid where d.tid=260434 and d.billid=i.billid and ((g.goodsname like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;)) or (g.goodscode like concat(&#39;%&#39;,&#39;葡萄&#39;,&#39;%&#39;))))
    )
    and i.rednew=0 /*单据列表不含红冲单*/ 
    and i.billid not in (select billid from coursecenter_del t where t.tid=260434)
    and ((i.settle=1 and i.effect=1 and i.redold=0 and i.rednew=0)) /*已结算*/
order by udate desc,billno desc
limit 0,100;
ログイン後にコピー

执行时间约 1.6 秒，使用的是 like 方式。

改成使用全文索引方式：

test_10
-- 测试10
select 
    i.billid
    ,if(0,0,i.qty) as qty         
    ,if(0,0,i.goodstotal) as total   
    ,if(0,0,i.chktotal) as selfchktotal  
    ,if(0,0,i.distotal) as distotal 
    ,if(0,0,i.otherpay) as feetotal  
    ,if(0,0,ifnull(d.costtotal,0)) as costtotal 
    ,if(0,0,ifnull(d.maoli,0)) as maoli  
    ,i.billno
    ,from_unixtime(i.billdate,&#39;%Y-%m-%d&#39;) as billdate /*单据日期*/
    ,from_unixtime(i.createdate,&#39;%Y-%m-%d %H:%i:%s&#39;) as createdate /*制单日期*/
    ,if(i.sdate=0,&#39;&#39;,from_unixtime(i.sdate,&#39;%Y-%m-%d  %H:%i:%s&#39;)) as sdate /*过账日期*/
    ,from_unixtime(i.udate,&#39;%Y-%m-%d %H:%i:%s&#39;) as udate /*最后修改时间*/
    ,i.custid ,c.custname
    ,i.storeid ,k.storename
    ,i.empid ,e.empname
    ,i.userid ,u.username
    ,i.remarks                               /*单据备注*/
    ,i.effect,i.settle,i.redold,i.rednew     /*单据状态*/
    ,i.printtimes /* 打印次数 */
    ,(case  when i.rednew=1 then 1  when i.redold=1 then 2  when i.settle=1 then 3  when i.effect=1 then 4  else 9 end) as state /*单据状态*/
    ,(case  when i.rednew=1 then &#39;红冲单&#39;  when i.redold=1 then &#39;已红冲&#39;  when i.settle=1 then &#39;已结算&#39;  when i.effect=1 then &#39;已过账&#39;  else &#39;草稿&#39; end) as statetext
    ,&#39;&#39; as susername /* 操作人 */
    ,&#39;&#39; as accname /* 科目 */
from salebill i
left join coursecentersale d on d.tid=i.tid and d.billid=i.billid
left join customer c on c.tid=i.tid and c.custid=i.custid
left join store k on k.tid=i.tid and k.storeid=i.storeid
left join employee e on e.tid=i.tid and e.empid=i.empid
left join user u on u.tid=i.tid and u.userid=i.userid
where i.tid=260434 and (i.billtype = 5 or i.effect = 1)
    and (&#39;_billdate_f_&#39;!=&#39;&#39;)
    and (&#39;_billdate_t_&#39;!=&#39;&#39;)
    and (&#39;_sdate_f_&#39;!=&#39;&#39;)
    and (&#39;_sdate_t_&#39;!=&#39;&#39;)
    and (&#39;_udate_f_&#39;!=&#39;&#39;)
    and (&#39;_udate_t_&#39;!=&#39;&#39;)
    and (&#39;_cdate_f_&#39;!=&#39;&#39;)
    and (&#39;_cdate_t_&#39;!=&#39;&#39;)
    and (&#39;_billid_&#39;!=&#39;&#39;)      /*单据id*/
    and (&#39;_custid_&#39;!=&#39;&#39;)      /*客户ID*/
    and (&#39;_storeid_&#39;!=&#39;&#39;)     /*店仓ID*/
    and (&#39;_empid_&#39;!=&#39;&#39;)       /*业务员ID*/
    and (&#39;_custstop_&#39;!=&#39;&#39;)       /*客户是否停用*/
    and (
        (match(i.billno) against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))
        or (match(i.remarks) against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))
        or exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)) or (match(d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;)  in boolean mode))))
        or exists(select 1 from customer c where c.tid=260434 and c.custid=i.custid and (match(c.custname) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)))
        or exists(select 1 from goods g join salebilldetail d on d.tid=g.tid and d.goodsid=g.goodsid where d.tid=260434 and d.billid=i.billid 
     and ((match(g.goodsname) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))
     or (match(g.goodscode) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))))
    )
    and i.rednew=0 /*单据列表不含红冲单*/ 
    and i.billid not in (select billid from coursecenter_del t where t.tid=260434)
    and ((i.settle=1 and i.effect=1 and i.redold=0 and i.rednew=0)) /*已结算*/
order by udate desc,billno desc
limit 0,100;
ログイン後にコピー

执行时间约 1.6 秒，与使用的是 like 方式差不多。

最魔幻的地方来了，如果将上面的SQL语句中（salebilldetail表使用全文索引 remarks_fulltext、goodsremarks_fulltext的地方）

exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)) or (match(d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;)  in boolean mode))))
ログイン後にコピー
test_11

改成使用全文索引 remarks_goodsremarks_fulltext

-- 测试11
exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))))
ログイン後にコピー

执行时间无限长（跑了半天没成功）？
经分析，在 where 子句中，一个条件子句中包含一个以上 match 时会出现这样的情况。即：

-- and 中只有一个全文检索时正常， 用时0.2秒
select xxx from xxx
...
and (
	exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))))
)
...

-- 下面这样就异常了，会慢成百上千倍，用时 160 秒， 如果有更多的 match ，会更夸张的慢下去
select xxx from xxx
...
and (
	exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks,d.goodsremarks) Against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode))))
	or match(i.billno) against(concat(&#39;"&#39;,&#39;葡萄&#39;,&#39;"&#39;) in boolean mode)
)
...
ログイン後にコピー

测试结果汇总：

查询用时(秒)备注
test 10.765原始like查询
test 20.834全文索引 remarks_fulltext、goodsremarks_fulltext
test 30.242全文索引 remarks_goodsremarks_fulltext
---

test 422.654原始like查询，不过滤 tid
test 524.855全文索引 remarks_fulltext、goodsremarks_fulltext, 不过滤 tid
test 60.213全文索引 remarks_goodsremarks_fulltext, 不过滤 tid
---

test 70.22全文索引 remarks_goodsremarks_fulltext, count
test 80.007全文索引 remarks_goodsremarks_fulltext, 不过滤 tid, count
---

test 91.6业务测试SQL，原始like查询
test 101.6业务测试SQL，全文索引 remarks_fulltext、goodsremarks_fulltext
test 11失败业务测试SQL，全文索引 remarks_goodsremarks_fulltext

五、MySQL 版本升级

因线上系统目前是 RDS MySQL 5.6，故简单描述升级相关问题。

  • Group By: 在 MySQL 5.7 之后，默认使用增加了限制，一些在 MySQL 5.6 可执行的Group By语句，在 5.7 之后会报错，可以更改新版本 MySQL 的 sqlModel

    -- 查询 sql_mode
select @@SESSION.sql_mode;
-- 设置
SET GLOBAL sql_mode = &#39;STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;;
-- 或 设置 （修改于当前会 话，关闭当前会话后失效）
SET SESSION sql_mode = &#39;STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;;
-- 刷新
flush PRIVILEGES;
    ログイン後にコピー

    • ONLY_FULL_GROUP_BY: 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

    • NO_AUTO_VALUE_ON_ZERO: 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

    • STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制

    • NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零

    • NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告

    • ERROR_FOR_DIVISION_BY_ZERO：在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySql返回NULL

    • NO_AUTO_CREATE_USER: 禁止GRANT创建密码为空的用户

    • NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

    • PIPES_AS_CONCAT：将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似

    • ANSI_QUOTES：启用后，不能用双引号来引用字符串，因为它被解释为识别符

    • 方式2：在配置文件中添加 sql_mode = &#39;对应需要的模式&#39;

    • sql_mode 模式说明：

    • 方式1：重启 MySQL 后失效

  • MySQL8.0 修改了账号密码加密策略 (默认的认证插件由mysql_native_password更改为caching_sha2_password），导致一些可视化软件无法连接 mysql8.0 版本的数据库。如果需要，可以修改默认的策略或者账号密码的认证策略

    [mysqld]
default_authentication_plugin = mysql_native_password
    ログイン後にコピー

    -- 修改加密规则 
ALTER USER &#39;root&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;password&#39; PASSWORD EXPIRE NEVER; 
-- 更新用户密码
ALTER USER &#39;账号&#39;@&#39;%&#39; IDENTIFIED WITH mysql_native_password BY &#39;密码&#39;;
-- 刷新权限
FLUSH PRIVILEGES;
    ログイン後にコピー

    • 方式2：执行语句修改某账号密码验证策略

    • 方式1：配置文件中添加, 让mysql使用原密码策略 (需重启mysql服务)

  • MySQL8.0 授权用户账号语法变更，创建用户的操作已经不支持grant的同时创建用户方式，需要先创建用户再进行授权。

    -- 原来的流程：
mysql> grant all on *.* to &#39;admin&#39;@&#39;%&#39; identified by &#39;admin&#39;;
-- 新的正确流程：
mysql> create user &#39;admin&#39;@&#39;%&#39; identified by &#39;admin&#39;;
mysql> grant all on *.* to &#39;admin&#39;@&#39;%&#39; ;
mysql> flush privileges;
    ログイン後にコピー

  • 数据库连接区别

    jdbc:mysql://{ip}:{port}/{db}&#63;characterEncoding=utf8&useSSL=false&serverTimezone=UTC
// useSSL  如果不配置false 项目可以正常启动但是会提示ssl问题
// serverTimezone=UTC 必须配置【时区设置成自己对应的时区】否则项目会报错
    ログイン後にコピー

    show variables like &#39;%time_zone%&#39;；
set global time_zone=&#39;+8:00&#39;;
    ログイン後にコピー

    • 如果时区问题还不能解决：

    • JDBC 连接串修改如下（首先需要驱动使用8.0对应连接的驱动）：

  • MySQL 5.7 原生支持JSON类型，并引入了众多JSON函数

  • MySQL 8.0 JSON字段的部分更新（JSON Partial Updates）

  • MySQL 8.0 默认字符集由latin1修改为utf8mb4

  • MySQL 8.0 正则表达式的增强，新增了4个相关函数，REGEXP_INSTR()，REGEXP_LIKE()，REGEXP_REPLACE()，REGEXP_SUBSTR()

  • MySQL 8.0 GROUP BY语句不再隐式排序 （忽略在Group By中的排序命令，如 desc, asc）

以上がmysql ft とは何を指しますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

関連ラベル：
mysql
ソース：yisu.com
前の記事：mysql ダーティ ページとは何ですか? 次の記事：MySQL文字列の日付を変換する方法
このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
著者別の最新記事
最新の問題
結果セットから最小値のみを表示する方法 (MYSQL) 次のステートメントがあります: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommessagewhere...
から 2024-04-06 21:44:19
0
1
603
MySQL SQLクエリを使用して別のテーブルのフィールドの合計を計算する 次のようなスキーマがあります。 属性「user_id」と「username」を持つユーザーテーブルと、属性「customer_id」（user_idのFK）と「finalPrice...
から 2024-04-06 19:39:29
0
1
441
MySQL: 「<ここの変数またはサブクエリ> として...からテキストを選択します」 次のデータを含む次のテーブルがあります: id text language 1 german text german 2 english text english 私が望むのは、次...
から 2024-04-06 19:25:54
0
2
529
MySQL でグループ化してカウントするにはどうすればよいですか? 友人に送信され、削除されていないメッセージの合計数を抽出するクエリを作成しようとしています。これはテーブル構造のスクリーンショットです。 達成したい出力は次のとおりです。 idme...
から 2024-04-06 18:30:17
0
1
353
MySQL は複数のテーブルからデータを取得します 次の列を含む eg_design テーブル、および次の列を含む eg_domains テーブル、および次の列を含む eg_fonts テーブルがあります。 $domain_id に...
から 2024-04-06 18:42:44
0
2
479
関連トピック
詳細>
人気のおすすめ
人気のチュートリアル
詳細>
関連するチュートリアル
人気のおすすめ
最新のコース
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート