• 1. Waiting for global read lock
• 2. Waiting for backup lock
• 3. Waiting for table metadata lock

数据库卡住了，最容易排查的就是慢SQL阻塞，processlist排序即可；次之，则是长事务未提交，导致锁等待，这种查询information_schema.innodb_trx按照trx_started排序即可找出根源；但往往有一些锁等待，前面两种方式很难定位出来。

1. Waiting for global read lock

触发：有线程执行flush table with read lock(FTWRL)，mysqldump、xtrabackup都可能触发

# 获取全局锁来源
select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,
 (SELECT PROCESSLIST_ID FROM performance_schema.threads WHERE THREAD_ID = t.OWNER_THREAD_ID) AS pid,
 (SELECT PROCESSLIST_USER FROM performance_schema.threads WHERE THREAD_ID = t.OWNER_THREAD_ID) AS user 
FROM performance_schema.metadata_locks AS t where OBJECT_TYPE='GLOBAL' AND LOCK_STATUS='GRANTED';

# KILL上面的pid

2. Waiting for backup lock

触发：物理冷备（xtrabackup、mysqlbackup）获取一致性快照，LOCK INSTANCE FOR BACKUP

# 查询BACKUP LOCK
select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,
 (SELECT PROCESSLIST_ID FROM performance_schema.threads WHERE THREAD_ID = t.OWNER_THREAD_ID) AS pid,
 (SELECT PROCESSLIST_USER FROM performance_schema.threads WHERE THREAD_ID = t.OWNER_THREAD_ID) AS user 
FROM performance_schema.metadata_locks AS t where OBJECT_TYPE='BACKUP LOCK' AND LOCK_STATUS='GRANTED';

# KILL上面的pid

3. Waiting for table metadata lock

触发：普通查询、变更都会持有表的MDL，长事务、lock tables t read

# 找出被阻塞的block_processlist_id
SELECT
  -- 锁对象公共信息
  w.lock_object_type   AS object_type,
  w.table_schema       AS table_schema,
  w.table_name         AS table_name,
  w.metadata_lock_type AS lock_type,
  -- 等待锁的线程（被阻塞）
  w.lock_status        AS status,
  w.connection_id      AS processlist_id,
  w.client_ip          AS client_ip,
  concat(left(w.current_sql,20),'...')        AS `sql`,
  -- 持有锁的线程（阻塞源）
  g.lock_status        AS hold_status,
  g.connection_id      AS "block_processlist_id(*)",
  g.client_ip          AS block_client_ip
  ,concat(left(g.current_sql,20),'...')       AS block_sql
FROM
(
  SELECT 
    ml.OBJECT_TYPE       AS lock_object_type,
    ml.OBJECT_SCHEMA     AS table_schema,
    ml.OBJECT_NAME       AS table_name,
    ml.LOCK_TYPE         AS metadata_lock_type,
    ml.LOCK_STATUS       AS lock_status,
    ml.LOCK_DURATION     AS lock_duration,
    t.PROCESSLIST_ID     AS connection_id,
    t.PROCESSLIST_HOST   AS client_ip,
    COALESCE(esc.SQL_TEXT, t.PROCESSLIST_INFO) AS current_sql
  FROM performance_schema.metadata_locks ml
  JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID = t.THREAD_ID
  LEFT JOIN performance_schema.events_statements_current esc ON t.THREAD_ID = esc.THREAD_ID
  WHERE ml.LOCK_STATUS = 'PENDING'
    AND ml.OBJECT_TYPE IN ('TABLE','SCHEMA') 
    AND t.PROCESSLIST_ID IS NOT NULL
) w
JOIN
(
  SELECT 
    ml.OBJECT_TYPE       AS lock_object_type,
    ml.OBJECT_SCHEMA     AS table_schema,
    ml.OBJECT_NAME       AS table_name,
    ml.LOCK_TYPE         AS metadata_lock_type,
    ml.LOCK_STATUS       AS lock_status,
    ml.LOCK_DURATION     AS lock_duration,
    t.PROCESSLIST_ID     AS connection_id,
    t.PROCESSLIST_HOST   AS client_ip,
    COALESCE(esc.SQL_TEXT, t.PROCESSLIST_INFO) AS current_sql
  FROM performance_schema.metadata_locks ml
  JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID = t.THREAD_ID
  LEFT JOIN performance_schema.events_statements_current esc ON t.THREAD_ID = esc.THREAD_ID
  WHERE ml.LOCK_STATUS = 'GRANTED'
    AND ml.OBJECT_TYPE IN ('TABLE','SCHEMA') 
    AND t.PROCESSLIST_ID IS NOT NULL
) g
ON w.lock_object_type = g.lock_object_type 
AND w.table_schema = g.table_schema 
AND w.table_name = g.table_name 
AND w.lock_duration = g.lock_duration
AND w.connection_id != g.connection_id
ORDER BY w.connection_id;

# kill上一步的block_processlist_id

• 问题复现

早上RD突然发消息说，有个DDLalter table xx modify column col varchar(10)无法执行。
一开始以为是个简单的长事务没有关闭，对表xx加了S锁。熟练的查询select * from information_schema.innodb_trx;竟然没有数据！再查一下processlist，alter table xx的进程真的是出于Waiting for table metadata lock。此时判断可能真没长事务，但有高频查询SQL ，导致DDL无法获取到X锁。看了下监控指标，查询QPS竟然也不高...此时跟RD反馈，让他把本地的SQL查询工具关了。alter执行成功！反馈说有开了另外一个窗口进行事务insert，但执行报错。

问题复现

会话一：
create table tmp(id int,val varchar(5));
begin;
insert into tmp values(1,'aaaaaa');  /*执行报错，事务没关闭*/

会话二：
alter table tmp modify column val varchar(10); /*MDL锁等待*/

会话三：
select * from information_schema.innodb_trx; /*查无会话一对应的长事务*/
select * from performance_schema.metadata_locks;

疑问：

1. 会话三为何查无会话一的长事务，因为innodb_trx仅记录已成功开始执行的活跃事务，会话一开始就执行失败。
2. 如何定位到是会话一导致的？（开启PS，select * from performance_schema.metadata_locks）

• 背景
• 解决
  • 迁移过程的参考步骤​

引用自迁移Grafana 默认的 SQLite DB 数据改为使用 MySQL 方式

背景

grafana默认使用sqlite3存储数据，数据量上升需调整为MySQL，涉及将SQLite数据转MySQL。 一开始搜索文档为sqlite to mysql，各种折腾，比较有参考意义的，如：https://study-database.readthedocs.io/zh/latest/%E6%95%B0%E6%8D%AE%E5%BA%9304sqlite%E8%BD%ACmysql.html#sqlitesql ，总结了各种方案，遇到的坑，虽然最后也没采用。如果直接转换，涉及字段类型text可能需转换成varchar，或者text，因此表结构最好直接通过grafana来生成。

解决

迁移过程的参考步骤​

• 首先需要暂停 Grafana 服务，导出默认的 SQLite 数据库文件，如 grafana.db ；
• 可借助 Grafana 官方提供的工具 database-migrator 将数据库导出转化为支持 MySQL 协议的 SQL 文件；
• 修改 Grafana 配置文件 grafana.ini 数据库相关配置参数 -e "GF_DATABASE_URL=mysql://$db_username:$db_passwd@$db_host:$db_port/$db_name" 使用 MySQL 数据库；
• 重新启动 Grafana 让其在 MySQL 中设置数据库和表结构，初始化完再先关停下 Grafana ；
• 导入前面使用工具从 SQLLite 原生默认数据库中导出的 sql 数据到 MySQL 对应的 DB 中；
• 重新开启 Grafana 按理即可；

附：

1. grafana官方文档：https://grafana.com/docs/grafana/latest/setup-grafana/installation/docker/
2. database-migrator：https://github.com/grafana/database-migrator

• ubuntu自动升级一些事

1. 环境：ubuntu22.04 mysql8.0.39
2. 问题：mysql服务突然停止shutdown，查看mysql异常日志如下：

Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.39-0ubuntu0.22.04.1).
/usr/sbin/mysqld: Forcing close of thread 24  user: 'zabbix'.
/usr/sbin/mysqld: Shutdown complete (mysqld 8.0.39-0ubuntu0.22.04.1)  (Ubuntu).

3. 排查：

• 查资料，有的说是DNS反向解析导致mysql异常停止，要开启skip-name-resolve，查my.cnf已开启。
• 查看/var/log/syslog和journalctl -xe
  发现每次mysql停止前都会出现启动apt-daily-upgrade.service，而该服务是每天定时更新服务upgrade and clean activities，再查/var/log/apt/term.log也看到了确实是更新的时候触发mysql shutdown，也有出现mysql被直接升级到最新

4. 解决：
   卸载mysql服务（当时dpkg安装），重新安装；或者停止apt-daily-upgrade

ubuntu自动升级一些事

1. 触发服务apt-daily和apt-daily-upgrade
   systemctl status apt-daily-upgrade查看，都是调用同一个脚本/usr/lib/apt/apt.systemd.daily
2. 何为apt-daily-upgrade
   顾名思义就是自动升级。
   是否开启自动升级、自动升级规则，查看目录/etc/apt/apt.conf.d/
   重点关注50unattended-upgrades（无人值守自动升级），默认都是开启，每日6点多默默后台升级
3. 查看日志
   已自动升级日志：/var/log/apt/history.log
   升级日志明细（含kill）：/var/log/apt/term.log
4. 自动升级规则
   /etc/apt/apt.conf.d/50unattended-upgrades

 Unattended-Upgrade::Allowed-Origins {
      "${distro_id}:${distro_codename}";
      "${distro_id}:${distro_codename}-security";
      "${distro_id}ESMApps:${distro_codename}-apps-security";
      "${distro_id}ESM:${distro_codename}-infra-security";
 };

${distro_id}代表发行版的ID（如Ubuntu）；
${distro_codename}代表发行版的代号（如jammy，对应于Ubuntu 22.04）

例如：`apt search mysql-server` （如下版本就支持自动升级）  
mysql-server-8.0/jammy-updates,jammy-security 8.0.37-0ubuntu0.22.04.3 amd64

5. 已安装包查看是否会被自动升级
   apt policy mysql-server

mysql-server:
  Installed: (none)						(已安装版本)
  Candidate: 8.0.37-0ubuntu0.22.04.3	(候选版本)
  Version table:						(版本表)
     8.0.37-0ubuntu0.22.04.3 500
        500 http://cn.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages
        500 http://cn.archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages
     8.0.28-0ubuntu4 500
        500 http://cn.archive.ubuntu.com/ubuntu jammy/main amd64 Packages

6. 经验
   因此，如果使用apt安装来自ubuntu官方源的包，可能会被静默升级，导致服务不可用。

• 复现
• 思考

很久没写文章了，今天看了篇文章《MySQL 创建表后神秘消失？揭秘零宽字符陷阱》，一开始也是被标题吸引进去。

复现

CREATE TABLE `lang​` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# show tables;可看到表lang，但直接select查不到
select * from lang;
ERROR 1146 (42S02): Table 'test.lang' doesn't exist

原因为lang后面含有零宽字符

思考

1. 原文中已经说了如何解决，例如复制到编辑器中可能会提示异常符号、show tables查看的时候边框线 “|” 与其他行是不对齐的。
2. 而我的排查方式不一样，第一直觉就是觉得里面含有特殊字符，直接查hex字符。

abul test>select hex(table_name),hex('lang') from information_schema.tables where table_schema='test' and table_namelike 'lang%';
+-----------------+-------------+
| hex(table_name) | hex('lang') |
+-----------------+-------------+
| 6C616E67E2808B  | 6C616E67    |
+-----------------+-------------+
1 row in set (0.00 sec)

3. 另外，如何避免？上线SQL正常会经过goInception审核，那如何避免RD提交类似特殊字符。可能得正则查找。

get_first_zero_width_char_pos(sql:str) -> int:
    """
    查找第一个零宽字符的位置，无零宽字符返回-1
    """
    zwc = re.search(r'[\u200B-\u200D\u2060\uFEFF]', sql)
    return zwc.start() if zwc else -1

4. 零宽字符大多数情况不可见，那会用在什么地方？某些社交平台，敏感词汇会被脱敏，这样是否就可以反脱敏。

• 复现
• 排查
  • 1. 权限
  • 2. 手动执行
  • 3. 比对存储过程
• 结论

场景：今天OP反馈使用DTS将zabbix同步到新实例，原先旧实例执行存储过程call partition_maintenance_all('zabbix')成功，迁移到新实例后，执行报错，提示如下：

ALTER command denied to user 'db_arch_w'@'x.x.x.x' for table 'history'

复现

# 旧实例，执行成功
mysql -h [旧实例地址] -u db_arch_w -p zabbix -e "call partition_maintenance_all('zabbix');"

# 新实例，执行报错，提示`ALTER command denied`
mysql -h [新实例地址] -u db_arch_w -p zabbix -e "call partition_maintenance_all('zabbix');"

排查

1. 权限

提示没权限，首先比对下新旧实例db_arch_w的权限，竟然都一样，如下，且没有zabbix库的ALTER权限！

show grants for 'db_arch_w'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for db_arch_w@%                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `db_arch_w`@`%` WITH GRANT OPTION |
| GRANT SHOW_ROUTINE ON *.* TO `db_arch_w`@`%` WITH GRANT OPTION                                                                  |
| GRANT EXECUTE ON `zabbix`.* TO `db_arch_w`@`%`                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------+

确实没有ALTER权限，但旧实例为什么能执行？

2. 手动执行

看了下报错的位置，手动执行，执行SQL如下

# 新实例，alter报错，提示ALTER command denied
mysql -h [新实例地址] -u db_arch_w -p zabbix -e "alter table zabbix.history add partition (partition p202403260000 values less than(1711468800))"

# 旧实例，一样报错
mysql -h [旧实例地址] -u db_arch_w -p zabbix -e "alter table zabbix.history add partition (partition p202403260000 values less than(1711468800))"

这个时候新旧报错又是一致的，这是为什么呢？

3. 比对存储过程

其实在第2步的时候，就可以确定是存储过程有问题。

# 旧实例存储过程
CREATE DEFINER=`admin`@`%` PROCEDURE `partition_maintenance_all`(
IN `SCHEMA_NAME` varchar(32) charset utf8mb4 collate utf8mb4_0900_ai_ci )
BEGIN
    # xxx 具体内容，忽略
END


# 新实例存储过程
CREATE DEFINER=`admin`@`%` PROCEDURE `partition_maintenance_all`(
IN `SCHEMA_NAME` varchar(32) charset utf8mb4 collate utf8mb4_0900_ai_ci )
    SQL SECURITY INVOKER
BEGIN
    # xxx 具体内容，忽略
END

有个细微区别，新实例存储过程中含有SQL SECURITY INVOKER，其含义为执行权限使用调用者账号所属权限。例如我们一开始用db_arch_w账号连接，则使用db_arch_w的权限去执行，而存储过程中含有ALTER命令，db_arch_w没有权限就报错；而旧实例没有指定SQL SECURITY，则默认使用DEFINER定义者的权限，此处为admin账号权限（超管），因此执行不会报错。

SQL SECURITY：有两个值DEFINER和INVOKER，没有指定则默认DEFINER。
DEFINER：使用定义者权限；INVOKER：使用调用者权限。

结论

DTS同步时，存储过程会自动添加SQL SECURITY INVOKER，导致调用时使用的是调用者的权限，当存储过程中含有调用者没有的权限时就会报错。因此仅需删除所有存储过程中SQL SECURITY INVOKER即可执行。

思考：为什么DTS要指定INVOKER呢？可能是考虑同步的目标实例没有定义的用户，会导致无法执行。

• 浅谈MySQL json类型
  • 一、官网说明
  • 二、使用说明
  • 三、建议

浅谈MySQL json类型

mysql很早就已经支持原生的json数据类型（从MySQL 5.7.8起），但目前看还是有些开发同学不了解，或者不敢用json类型。下面我们一起来学习下官网mysql的json类型。 {.is-info}

一、官网说明

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: 从MySQL5.7.8开始，mysql支持RFC 7159定义的原生JSON数据类型，能够有效访问JSON文档中的数据。相比于存储于字符串，json类型存储有以下优点：
Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error. 自动校验json文档是否正确，不合法的值将返回error。
Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document. 优化存储格式。存储在json列中的json文档会被转换成内部格式，使得更快访问文档里面的元素。当服务器需要读取以二进制格式存储的JSON值时，不需要从文本中解析。二进制格式结构化能时服务器直接通过键值或者数组索引查找对应值，而不需要访问之前或之后的所有值。
The space required to store a JSON document is roughly the same as for LONGBLOB or LONGTEXT; see Section 11.7, “Data Type Storage Requirements”, for more information. It is important to keep in mind that the size of any JSON document stored in a JSON column is limited to the value of the max_allowed_packet system variable. JSON文档占用的空间与LONGBLOB或者LONGTEXT基本等同。JSON列的大小受限于max_allowed_packet变量的值。
A JSON column cannot have a non-NULL default value. JSON类型不能有非空默认值。
JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. JSON字段跟其它二进制类型一样，不能直接创建索引；但是可以通过在生成的列上创建一个索引，生成的列指向JSON里的标量值。

参考文档：https://dev.mysql.com/doc/refman/5.7/en/json.html

总结：

1. mysql5.7.8开始支持json类型（开发、测试、生产MySQL版本5.7.18，支持√）；

2. 相比于使用varchar存储，json类型能自动校验json格式是否正确；

3. 能直接访问json对象里的键值对；

4. 占用空间大，能存储大字段 理论能存储字符数num = max_allowed_packet / 单字符大小(字母1，中文3，emoji4)

5. JSON类型不能有非空默认值；

create table t_json(
  json_wrong json not null default '{}',   --> 这是错的，不能有非空默认值
  json_right json null default null        --> 这是允许的，default null
)

6. json字段不能直接建索引，但可以通过创建虚拟列，指向JSON里的对应key来创建索引。

二、使用说明

-- 创建表
create table t_json(
  id int unsigned not null auto_increment,
  json_data json null default null comment 'json类型字段',
  primary key(id)
)comment '创建json类型表';

-- 插入数据，跟字符串一样
insert into t_json(json_data) values('{"id":1, "name":"abul"}');

-- 查询，使用->或者->>，注意两个区别（附1）
select json_data->"$.name" as name from t_json;
+--------+
| name   |
+--------+
| "abul" |
+--------+

select json_data->>"$.name" as name from t_json;
+------+
| name |
+------+
| abul |
+------+

-- 根据json里的字段模糊搜索，其余操作相同
select * from t_json where json_data->>"$.name" like 'ab%';

-- 变更某个key值
update t_json set json_data=json_replace(json_data,'$.name','xmabul') where id=1;

-- 创建虚拟列和索引
-- 场景：json某个key为高频查询，需要创建key对应索引
create table t_json_index(
  id int unsigned not null auto_increment,
  json_data json null default null comment 'json类型字段',
  name varchar(20) generated always as (json_data->>"$.name") comment 'json.name',  -- 虚拟列
  primary key(id),
  index idx_name(name)   -- 虚拟列索引
)comment '创建json类型表索引';

-- 判断指定值是否在json数组中
where json_contains(clear_rule_json,json_object("typeEnum",'MERCHANT_GET'))


-- json函数
-- 详细文档：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
->										等价于JSON_EXTRACT().		
->>										等价于JSON_UNQUOTE(JSON_EXTRACT())	
JSON_ARRAY()					创建一个数组
JSON_ARRAY_APPEND()		扩展json数组对象		
JSON_ARRAY_INSERT()		在指定数组位置插入值 
JSON_CONTAINS()				判断指定值是否在json文档中		
JSON_CONTAINS_PATH()	判断是否包含路径	
JSON_DEPTH()					返回最深层级		
JSON_EXTRACT()				解析json，返回value值		
JSON_INSERT()					插入值		
JSON_KEYS()						返回所有key		
JSON_LENGTH()					返回json元素个数		
JSON_MERGE()					json合并
JSON_MERGE_PATCH()		合并json，替换重复key值	
JSON_MERGE_PRESERVE()	json合并，相同key合并值	
JSON_OBJECT()					创建一个object对象		
JSON_PRETTY()					美化json对象
JSON_QUOTE()					添加引号		
JSON_REMOVE()					移除json中的某个key		
JSON_REPLACE()				替换json某个key值		
JSON_SEARCH()					查找值对应路径		
JSON_SET()						插入key值，相当于replace into		
JSON_STORAGE_SIZE()		返回存储json文档的二进制的字节数
JSON_TYPE()						返回类型		
JSON_UNQUOTE()				取消引号		
JSON_VALID()					判断json是否合法		

附：

1. ->和->>区别 mysql 5.7.9开始支持-> ;mysql5.7.13开始支持->>
   ->等价于JSON_EXTRACT()，将json拆解，如果是字符串会包含引号 ->>等价于JSON_UNQUOTE()，将json拆解，并且去掉引号

三、建议

1. 如果存储的json数据长度小于1000，并且不需要通过json文档里的key进行查询过滤，建议直接定义varchar类型，减少占用空间。
2. 如果需要根据键值查询，可以定义成json；但是当需要存储大字段时，还是建议存储到mongodb中，专业的存储引擎做专业的事。

原则上json数据存储建议 mongodb > mysql.varchar > mysql.json

• 1. 最大可使用内存（ M ）：
• 2.单个连接最大可使用内存（ M ）：
• 3.最大可使用内存（不包含连接占用内存）（ M ）：

1. 最大可使用内存（ M ）：

SELECT 
  (
    @@key_buffer_size + 
    @@query_cache_size + 
    @@tmp_table_size + 
    @@innodb_buffer_pool_size  + 
    @@innodb_log_buffer_size + 
    @@max_connections * (
        @@sort_buffer_size + 
        @@read_buffer_size + 
        @@read_rnd_buffer_size + 
        @@join_buffer_size + 
        @@binlog_cache_size + 
        @@thread_stack
    )
  ) / 1024 / 1024  as `MaxMem(M)` ;

2.单个连接最大可使用内存（ M ）：

  SELECT 
 (
     @@sort_buffer_size + 
     @@read_buffer_size + 
     @@read_rnd_buffer_size + 
     @@join_buffer_size + 
     @@binlog_cache_size + 
     @@thread_stack
 ) / 1024 / 1024 AS `ConnectMem(M)` ;

3.最大可使用内存（不包含连接占用内存）（ M ）：

   SELECT 
     (
   @@key_buffer_size + 
   @@query_cache_size + 
   @@tmp_table_size + 
   @@innodb_buffer_pool_size  + 
   @@innodb_log_buffer_size
     ) / 1024 / 1024 AS `MaxMemExcCon(M)`;

Postman下载地址：https://www.postman.com/

1. 配置Environments
   只需设置变量名，如Authorization，其它列不用配置。

2. 登录，设置Test
   
   登录成功后，变量马上有值，可查看如下：

   使用老版本postman可能会报错，pm is not defined。

3. 其它接口调用token

• 创建全文索引
• 使用
• 注意

说明：本文仅临时备注下，详情自行阅读官网：https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html

版本要求：MySQL 5.7.6后内置ngram全文解析器，用来支持中日韩分词。

# 查看当前mysql是否支持（有返回结果说明支持，值为2表示检索的字符至少要2个）
root test>show global variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.00 sec)

创建全文索引

语法：FULLTEXT (列名,列名,...) WITH PARSER ngram

# 建表时创建全文索引
CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;

# 建表后新增
ALTER TABLE articles ADD FULLTEXT INDEX ft_title(title) with parser ngram;

使用

全文检索模式
常用的全文检索模式有两种：

1. 自然语言模式(NATURAL LANGUAGE MODE) - 默认
   自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
2. BOOLEAN模式(BOOLEAN MODE) - 完全匹配
   BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

之前使用的时候没注意到还有boolean模式，导致之前使用全文索引发现搜索出不相关的结果，就放弃了，今天重新测试，使用boolean可以符合要求，如下。

# 搜索的字符前添加+，表示必须包含，后面添加IN BOOLEAN MODE
SELECT id,title,body
FROM articles
WHERE MATCH (title) AGAINST ('+手机' IN BOOLEAN MODE) ;

BOOLEAN模式下运算符的使用方式：

'apple banana' 
无操作符，表示或，要么包含apple，要么包含banana

'+apple +juice'
必须同时包含两个词

'+apple macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性会更高。

'+apple -macintosh'
必须包含apple，同时不能包含macintosh。

'+apple ~macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。

'+apple +(>juice <pie)'
查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。

'apple*'
查询包含以apple开头的单词的记录，如apple、apples、applet。

'"some words"'
使用双引号把要搜素的词括起来，效果类似于like '%some words%'，
例如“some words of wisdom”会被匹配到，而“some noise words”就不会被匹配。

注意

• 只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
• 全文索引只支持InnoDB和MyISAM引擎。
• MATCH (columnName) AGAINST ('keywords')。
  MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
• MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
• 如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。