• 问题复现

早上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的。

• MySQL开发规范
  • 一、命名规范
  • 二、建表规范
  • 三、索引规范
  • 四、SQL规范

MySQL开发规范

一、命名规范

1. 命名要见名知意，全部使用小写字母，多个单词之间使用下划线连接

   禁止使用中文拼音！

   例：t_article_detail

2. database
   db_[数据库名]
   例如：db_hms
3. table
   t_[表名]
   例如：t_school
4. index
   idx_[普通索引]、uk_[唯一索引]
   例如：idx_uid、uk_record_id
5. view
   v_[视图名]
6. function（默认禁止使用）
   f_[方法名]
7. procedure（默认禁止使用）
   p_[存储过程名]
8. event（默认禁止使用）
   e_[事件名]

二、建表规范

1. 所有列名、表名都要有注释

2. 字符集统一使用utf8mb4，排序规则使用utf8mb4_general_ci（特殊例外）
   排序规则默认使用utf8mb4_general_ci，列值如果需要根据大小写过滤则配置对应列排序规则为utf8mb4_bi，如验证码、密码

### 字符集使用utf8mb4，默认使用utf8mb4_general_ci
CREATE TABLE t_test (
 id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
 val varchar(20) NOT NULL DEFAULT '' COMMENT '值',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

### 特殊列需要根据大小写过滤，配置列排序规则utf8mb4_bin
CREATE TABLE `t_test` (
 id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
 pwd varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '密码',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

3. 字段名禁止使用数据库保留关键字
   例如：date、datetime、desc、range、match、comment、add等
   所有MySQL保留关键字详见 mysql5.7关键字和保留字

4. 表的主键的命名建议依据表的含义+_id
   例如：用户表t_user 主键为user_id

5. 新建表需要创建字段
   【必选】创建时间create_time ,修改时间 update_time；【可选】是否可用 is_flag

 CREATE TABLE `t_test` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `is_flag` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '是否可用，1是，0否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表'
;

6. 每个表都需要有主键，自增主键使用unsigned
   建议使用自增键作为主键，避免使用uuid、字符串等做主键，禁止无主键
7. 小数类型使用 decimal ，禁止使用 float 和 double
   说明：float 和 double 在存储的时候,存在精度损失的问题。decimal(M,D)，M表示总位数，D表示小数点后位数；
   例如：decimal(5,2)，整数部分可以存储3位，小数部分可以存储2位
8. 数值类型的字段只有正数时，指定unsigned

 CREATE TABLE `t_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '只有正数，指定unsigned'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表'
;

9. 存储货币，类型使用整型，单位为分
10. 字段设置默认值，尽量避免空值null
11. 表之间的关联字段，字段类型和字符集要保持一致

#t_class的id和t_student的class_id关联，两者的字段类型和字符集要一致
select t_class.*,t_student.* 
from t_student  
 left join t_class on t_class.id=t_student.class_id;

12. 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是unsigned tinyint( 1 表示是,0 表示否 ) 。
    说明:任何字段如果为非负数,必须是unsigned。
    例如:表达逻辑删除的字段名 is_deleted ,1 表示删除,0 表示未删除。
13. varchar长度限制
    建议不要超过5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来关联，避免影响其它字段索引效率。
14. 删除自增初始值
    说明：有的表结构通过测试环境导出，会含有AUTO_INCREMENT=N，请将其删除再提交。
15. 修改同一张多个字段，合并为单条SQL
    说明：经常有同事对同一张表添加多个字段，分成多条SQL，带来的影响是每执行一条DDL都会对该表重建和锁表。

#反例，添加t_task多个字段
ALTER TABLE t_task ADD identity_type  tinyint(4) DEFAULT '1' COMMENT '用户身份类型: 非会员:0、全员:1、白银会员:2、超级会员:3;';
ALTER TABLE t_task ADD online_time date DEFAULT NULL COMMENT '上线时间 格式： YYYY-MM-DD';
ALTER TABLE t_task ADD offline_time date DEFAULT NULL COMMENT '下线时间 格式： YYYY-MM-DD';

#正例
ALTER TABLE t_task 
  ADD identity_type  tinyint(4) DEFAULT '1' COMMENT '用户身份类型: 非会员:0、全员:1、白银会员:2、超级会员:3;',
  ADD online_time date DEFAULT NULL COMMENT '上线时间 格式： YYYY-MM-DD',
  ADD offline_time date DEFAULT NULL COMMENT '下线时间 格式： YYYY-MM-DD';

16. 有限类型选项，选择【够用】类型
    说明：不同类型取值范围不一样，有的字段取值就0、1、2，10以内，字段类型可选择为tinyint

    类型	取值范围（有符号）	取值范围（无符号）
    tinyint	-128~127	0~255
    smallint	-32768~32767	0~65535
    mediumint	-8388608~8388607	0~16777215
    int	-2147483648~2147483647	0~4294967295

17. 禁止使用外键

18. 基础字段长度标准
    说明：经常有表需要添加字段【操作人名称】(operation_name/user_name)、图片链接地址(url)等，可按此标准创建（需开发确认），防止字段长度过短被截取或报错。

    字段	建议长度	备注
    operation_name/user_name	varchar(50)	操作人名称
    url	varchar(200)	图片链接地址

19. 表限制 列数：单表列数小于30 行数：以三年内的数据量，超过 500 万行或者单表容量超过 2 GB ,建议进行分库分表。

20. 时间字段类型建议使用datetime，而不是timestamp
    说明：虽然timestamp占用空间比datetime小，但仅能存储到2038-01-19

21. 字段类型tinyint(1)建议更改为tinyint(2) 说明：括号中数字跟存储的大小范围无关，但canal会将tinyint(1)类型解析成bit导致同步错误。

22. DRDS分布式事务 drds支持2PC，但在同一个事务里，涉及insert的表都需要创建全局序列Sequence，否则会报错。(目前仅dbfileserver有使用)

三、索引规范

1. 索引命名
   以idx_开头，单个字段为列名全称，组合索引的列名之间通过下划线关联，列名内取消下划线

alter table t_test add index idx_student_id(student_id);  #单个字段，列名全称
alter table t_test add index idx_classid_studentid(class_id,student_id);  #多个字段，列名内取消下划线，列名之间通过下划线关联

2. 索引限制 单表索引个数建议在5个以内，单个索引字段不超过5个

3. 禁止左模糊搜索或全模糊搜索

#正例
select * from t_student where name like '中%';
 
#反例
select * from t_student where name like '%中';
select * from t_student where name like '%中%';

4. varchar字段建立索引，指定索引长度
   正常没必要对整个字段建立索引，一般长度20能区分90%数据。
   varchar长度在50以内，可以直接使用整个字段建立索引，超过50以后使用count(distinct left(列名, 索引长度))/count(*)的区分度来确认索引长度。

#name字段长度为100
alter table t_test add index idx_name(name(20));

5. 避免在区分度不高，频繁更新列上建立索引
6. 表数据量在1000以内，可不用建索引

四、SQL规范

1. 尽量使用简单SQL
   对复杂SQL进行拆分，化简成简单SQL，尽量减少多表关联。
2. 避免在索引列进行运算导致索引失效引发全表扫描

#反例：对列create_time进行date_format运算
select * from t_student where date_format(create_time,'%Y-%m-%d')='2021-02-24';
 
#正例：转换成对值的判断
select * from t_student where create_time >= '2021-02-24' and create_time < '2021-02-25';

3. 避免使用大事务造成执行锁定时间，同时导致备库只读节点数据延迟
4. 查询时显示指定要查询列，避免直接select *
5. 利用延迟关联或者子查询优化超多分页场景

#原始SQL
SELECT * FROM 表1 where 条件 LIMIT 100000,20;
 
#建议，延迟关联，先查询主键id，再通过关联出所有字段
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id;