mysqlft가 뭐야?

mysql ft는 전체 텍스트 인덱스를 의미합니다. 즉, 전체 텍스트 인덱스는 정확한 수치 비교보다는 유사성을 기반으로 해야 하는 쿼리를 해결하는 데 사용되는 것보다 N배 빠릅니다. 많은 양의 데이터에 직면하고 있으며 속도는 한 단계도 아닙니다.

이 튜토리얼의 운영 환경: Windows 10 시스템, mysql8 버전, Dell G3 컴퓨터.

mysql ft가 무엇인가요?

즉, 전체 텍스트 색인(FullText)입니다.

MySQL 전체 텍스트 인덱스(FullText)

1. 소개

기본 개념

전체 텍스트 인덱스는 정확한 수치 비교보다는 유사성을 기반으로 해야 하는 쿼리를 해결하기 위한 것입니다.

like + %를 사용하여 퍼지 매칭을 수행할 수도 있지만, 대량의 텍스트 데이터를 검색하는 것은 상상할 수 없습니다. 많은 양의 데이터가 있는 경우 전체 텍스트 인덱싱은 like보다 N배 더 빠를 수 있지만 속도는 한 단계도 아닙니다. like + % 也可以实现模糊匹配，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like 快 N 倍，速度不是一个数量级。

版本支持

1. MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引
2. MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
3. MySQL 5.7.6 中，提供了支持中文、日文和韩文（CJK）的内置全文 ngram 解析器，以及用于日文的可安装 MeCab 全文解析器插件
4. 全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建
5. 对于大型数据集，将数据加载到没有全文索引的表中然后创建索引要比将数据加载到具有现有全文索引的表中快得多
6. RDS MySQL 5.6 虽然也支持中文全文检索，但存在BUG

限制与缺点

• 导致磁盘资源的大量占用。全文索引本身就是一个利用磁盘空间换取性能的方法。全文索引大的原因是，按照某种语言来进行分词
• 全文索引创建速度慢，而且对有全文索引的各种数据修改操作也慢
• 使用全文索引并不是对应用透明的。如果要想利用全文索引，必须修改查询语句。原有的查询语句是不可能利用全文索引的，需要改成全文索引规定的语法
• 不区分大小写
• 分区表不支持全文搜索
• 由多列组合而成的全文检索的索引必须使用相同的字符集与排序规则
• 全文索引可能存在精度问题，即全文索引找到的数据，可能和like

  버전 지원

1. MySQL 5.6 이전 버전에서는 MyISAM 스토리지 엔진만 전체 텍스트 인덱싱을 지원합니다
2. MySQL 5.6 이상 버전, MyISAM 및 InnoDB 스토리지 엔진은 전체 텍스트 인덱스를 지원합니다.
3. MySQL 5.7.6은 중국어, 일본어, 한국어(CJK)를 지원하는 내장된 전체 텍스트 ngram 파서 및 일본어용 설치 가능한 MeCab 전체 텍스트 파서 플러그인
전체 텍스트 색인은 InnoDB 또는 MyISAM 테이블을 사용하려면 CHAR, VARCHAR 및 TEXT에 대해서만 생성할 수 있습니다. code> 열 <h2 id="二操作全文索引"></h2>대규모 데이터 세트의 경우 <span quot="quot" dashed="""dashed""" yellow="""yellow"""> 전체 텍스트 인덱스를 생성한 다음 인덱스를 생성하는 것이 기존 전체 텍스트 인덱스가 있는 테이블에 데이터를 로드하는 것보다 훨씬 빠릅니다<h3 id="21-配置最小搜索长度"></h3> <code>RDS MySQL 5.6 중국어 전체 텍스트 검색도 지원되지만 버그가 있습니다.

제한 사항 및 단점

디스크 리소스 사용량이 많아집니다. 전체 텍스트 인덱싱 자체는 성능을 위해 디스크 공간을 사용하는 방법입니다. 전체 텍스트 색인이 큰 이유는 특정 언어를 기반으로 한 단어 분할이 전체 텍스트 색인을 생성하는 데 느리고, 전체 텍스트 색인을 사용하여 다양한 데이터를 수정하는 속도도 느리기 때문입니다. 인덱스는 애플리케이션에 투명하지 않습니다. 전체 텍스트 인덱스를 사용하려면 쿼리 문을 수정해야 합니다. 원본 쿼리문에는 전체 텍스트 인덱스를 사용할 수 없습니다. 전체 텍스트 인덱스에 지정된 구문으로 변경해야 합니다. 대소문자를 구분하지 않습니다. 파티션된 테이블은 전체 텍스트를 지원하지 않습니다. 전체 텍스트 검색에는 여러 열로 구성된 인덱스를 사용해야 합니다. 동일한 문자 집합 및 정렬 규칙. 텍스트 인덱스는 like에서 찾은 것과 일치하지 않을 수 있습니다. MATCH() 함수의 열은 FULLTEXT와 일치해야 합니다. 전체 텍스트 검색이 아닌 이상 인덱스에 정의된 열은 정확히 동일합니다. IN BOOLEAN MODE 모드는 MyISAM 테이블에서 사용된다. (인덱싱되지 않은 컬럼에 대해서도 검색이 가능하지만 속도가 매우 느리다.) 단일 컬럼에 대한 전체 텍스트 인덱스가 생성되면 여러 컬럼이 흐리게 쿼리 중에는 적용되지 않습니다다른 테이블의 전체 텍스트 인덱스는 함께 쿼리할 수 없습니다. 두 문에 OR를 추가할 수 있습니다 2. 전체 텍스트 인덱스 작업

SHOW VARIABLES LIKE 'ft%';

Variable_nameValueft_boolean_syntax+ -><()~*:" "&|

전체 텍스트 인덱스는 정확성 문제가 있을 수 있습니다
2.1 최소 검색 길이를 구성합니다	SQL 명령을 통해 현재 구성을 볼 수 있습니다. 최소 검색 길이(단어 세그먼트 길이):

🎜🎜🎜 ft_max_word_len🎜🎜 84🎜🎜🎜🎜ft_min_word_len🎜🎜1🎜🎜🎜🎜ft_query_expansion_limit🎜🎜20🎜🎜🎜🎜ft _stopword_file🎜🎜(내장)🎜🎜🎜🎜

全文索引的相关参数都无法进行动态修改，必须通过修改 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">李秀琴 <练习册 <不是人>是个鬼' in boolean mode);<h2 id="四测试结果">四、测试结果</h2>
<p><strong>测试环境</strong>：本机4核16G Windows10，MySQL 8.0<br><strong>测试数据量</strong>：<code>salebilldetail</code> 表 <code>1276</code>万行，<code>salebill</code> 表 <code>269</code> 万行, <code>customer</code> 表 <code>30</code> 万行, <code>goods</code> 表 <code>75</code> 万行。</p>
<p>争对测试用的SQL语句，增加了以下全文索引：</p>
<pre class="brush:php;toolbar:false">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('%','葡萄','%')) or (d.goodsremarks like concat('%','葡萄','%')));

test_2

-- 测试2，使用全文索引 remarks_fulltext、goodsremarks_fulltext, 用时 0.834s
select 1 from salebilldetail d where d.tid=260434 and ((match(d.remarks) Against(concat('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  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('"','葡萄','"') in boolean mode)));

test_4

-- 测试4，原始 like 查询方式，不过滤 tid ，用时 22.654s
select t from salebilldetail d where ((d.remarks like concat('%','葡萄','%')) or (d.goodsremarks like concat('%','葡萄','%')));

test_5

-- 测试5，使用全文索引 remarks_fulltext、goodsremarks_fulltext,  不过滤 tid ，用时 24.855s
select 1 from salebilldetail d where ((match(d.remarks) Against(concat('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  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('"','葡萄','"') 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('"','葡萄','"') 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('"','葡萄','"') 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,'%Y-%m-%d') as billdate /*单据日期*/
    ,from_unixtime(i.createdate,'%Y-%m-%d %H:%i:%s') as createdate /*制单日期*/
    ,if(i.sdate=0,'',from_unixtime(i.sdate,'%Y-%m-%d  %H:%i:%s')) as sdate /*过账日期*/
    ,from_unixtime(i.udate,'%Y-%m-%d %H:%i:%s') 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 '红冲单'  when i.redold=1 then '已红冲'  when i.settle=1 then '已结算'  when i.effect=1 then '已过账'  else '草稿' end) as statetext
    ,'' as susername /* 操作人 */
    ,'' 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 ('_billdate_f_'!='')
    and ('_billdate_t_'!='')
    and ('_sdate_f_'!='')
    and ('_sdate_t_'!='')
    and ('_udate_f_'!='')
    and ('_udate_t_'!='')
    and ('_cdate_f_'!='')
    and ('_cdate_t_'!='')
    and ('_billid_'!='')      /*单据id*/
    and ('_custid_'!='')      /*客户ID*/
    and ('_storeid_'!='')     /*店仓ID*/
    and ('_empid_'!='')       /*业务员ID*/
    and ('_custstop_'!='')       /*客户是否停用*/
    and (
        (i.billno like concat('%','葡萄','%'))
        or (i.remarks like concat('%','葡萄','%'))
        or exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((d.remarks like concat('%','葡萄','%')) or (d.goodsremarks like concat('%','葡萄','%'))))
        or exists(select 1 from customer c where c.tid=260434 and c.custid=i.custid and (c.custname like concat('%','葡萄','%')))
        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('%','葡萄','%')) or (g.goodscode like concat('%','葡萄','%'))))
    )
    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,'%Y-%m-%d') as billdate /*单据日期*/
    ,from_unixtime(i.createdate,'%Y-%m-%d %H:%i:%s') as createdate /*制单日期*/
    ,if(i.sdate=0,'',from_unixtime(i.sdate,'%Y-%m-%d  %H:%i:%s')) as sdate /*过账日期*/
    ,from_unixtime(i.udate,'%Y-%m-%d %H:%i:%s') 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 '红冲单'  when i.redold=1 then '已红冲'  when i.settle=1 then '已结算'  when i.effect=1 then '已过账'  else '草稿' end) as statetext
    ,'' as susername /* 操作人 */
    ,'' 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 ('_billdate_f_'!='')
    and ('_billdate_t_'!='')
    and ('_sdate_f_'!='')
    and ('_sdate_t_'!='')
    and ('_udate_f_'!='')
    and ('_udate_t_'!='')
    and ('_cdate_f_'!='')
    and ('_cdate_t_'!='')
    and ('_billid_'!='')      /*单据id*/
    and ('_custid_'!='')      /*客户ID*/
    and ('_storeid_'!='')     /*店仓ID*/
    and ('_empid_'!='')       /*业务员ID*/
    and ('_custstop_'!='')       /*客户是否停用*/
    and (
        (match(i.billno) against(concat('"','葡萄','"') in boolean mode))
        or (match(i.remarks) against(concat('"','葡萄','"') 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('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  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('"','葡萄','"') 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('"','葡萄','"') in boolean mode))
     or (match(g.goodscode) Against(concat('"','葡萄','"') 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('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  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('"','葡萄','"') 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('"','葡萄','"') 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('"','葡萄','"') in boolean mode))))
	or match(i.billno) against(concat('"','葡萄','"') in boolean mode)
)
...

测试结果汇总：

查询	用时(秒)	备注
test 1	0.765	原始like查询
test 2	0.834	全文索引 remarks_fulltext、goodsremarks_fulltext
test 3	0.242	全文索引 remarks_goodsremarks_fulltext
---
test 4	22.654	原始like查询，不过滤 tid
test 5	24.855	全文索引 remarks_fulltext、goodsremarks_fulltext,  不过滤 tid
test 6	0.213	全文索引 remarks_goodsremarks_fulltext, 不过滤 tid
---
test 7	0.22	全文索引 remarks_goodsremarks_fulltext, count
test 8	0.007	全文索引 remarks_goodsremarks_fulltext, 不过滤 tid, count
---
test 9	1.6	业务测试SQL，原始like查询
test 10	1.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

  • 方式1：重启 MySQL 后失效

  -- 查询 sql_mode
  select @@SESSION.sql_mode;
  -- 设置
  SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  -- 或 设置 （修改于当前会 话，关闭当前会话后失效）
  SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  -- 刷新
  flush PRIVILEGES;

  • 方式2：在配置文件中添加 sql_mode = '对应需要的模式'
  • sql_mode 模式说明：
    • 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：启用后，不能用双引号来引用字符串，因为它被解释为识别符
      
      

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

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

  [mysqld]
  default_authentication_plugin = mysql_native_password

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

  -- 修改加密规则 
  ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
  -- 更新用户密码
  ALTER USER '账号'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
  -- 刷新权限
  FLUSH PRIVILEGES;

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

  -- 原来的流程：
  mysql> grant all on *.* to 'admin'@'%' identified by 'admin';
  -- 新的正确流程：
  mysql> create user 'admin'@'%' identified by 'admin';
  mysql> grant all on *.* to 'admin'@'%' ;
  mysql> flush privileges;

• 数据库连接区别

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

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

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

  show variables like '%time_zone%'；
  set global time_zone='+8:00';

• 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视频教程】

위 내용은 mysqlft가 뭐야?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!