国产数据库兼容过程中涉及的MySQL非严格模式

点击上方蓝字关注我

在国产数据库兼容适配过程中，经常遇到因源数据库是MySQL，迁移至其他国产数据库后，因MySQL端兼容模式有非严格模式，导致适配过程过程中需要做调整。那么，MySQL主要的非严格模式小结如下：

1、非严格模式参数

MySQL的非严格模式指的是在MySQL配置中禁用严格模式（Strict Mode）的情况下执行的SQL。在非严格模式下，MySQL会对某些数据插入、更新和比较操作执行隐式转换，从而在一些情况下允许执行一些宽松的操作，而不抛出错误或警告。
涉及的主要参数说明如下：

a) STRICT_TRANS_TABLES：在插入或更新数据时，禁止自动转换类型，确保所有数据都符合表定义的数据类型范围。如果值无法转换为合法的数据类型，则抛出错误。
b) STRICT_ALL_TABLES：对所有表都启用STRICT_TRANS_TABLES模式，确保数据插入或更新时严格符合表定义的数据类型。
c) NO_ZERO_IN_DATE：禁止在日期中使用零值，例如'0000-00-00'，在严格模式下会被视为非法日期。
d) NO_ZERO_DATE：禁止使用零值表示日期的部分，例如'2000-00-00'，在严格模式下会被视为非法日期。
e) ERROR_FOR_DIVISION_BY_ZERO：当除数为零时，抛出错误而不是返回NULL。
f) ANSI_QUOTES：启用ANSI_QUOTES模式，要求使用双引号来引用字符串，而不是MySQL默认的单引号。
g) NO_AUTO_CREATE_USER：禁止在GRANT语句中自动创建新用户。
h) NO_ENGINE_SUBSTITUTION：如果指定的存储引擎不可用，不允许MySQL使用默认的存储引擎替代。

2.  简单举例

以下是一些非严格模式下可能出现的案例情况：
1) 非严格的日期和时间插入：在非严格模式下，MySQL允许插入不符合日期和时间格式的值，会自动进行转换或舍入
eg:  不合法的日期值'0000-00-00'等

2) 非严格的字符串插入：在非严格模式下，MySQL允许插入过长的字符串，会自动截断超过字段长度的部分 （建议已开启此类严格模式）
eg:   varchar(2) 类型的字段，插入ABC可以成功，插入结果为AB

3) 非严格的数值插入：在非严格模式下，MySQL允许插入超出字段范围的数值，会自动进行范围调整（建议已开启此类严格模式）
eg:  TINYINT（默认最大127） 可以插入150，且插入后的值自动截断为 127

4) 非严格的零值插入：在非严格模式下，MySQL允许插入字符串类型的值到数值类型字段，会将非数值字符串转换为0 （建议已开启此类严格模式）
eg:  例如可以将字符串'123' 插入int类型，结果是123；将'abc'插入int,结果是0

5) 非严格的分组查询：在非严格模式下，MySQL允许在GROUP BY查询中选择非聚合列，这可能导致不确定的结果
eg:  SELECT   a,b,COUNT(*)  FROM  tb GROUP BY a

6) 非严格的NULL值比较：在非严格模式下，MySQL允许使用普通比较运算符（如=、<、>等）与NULL值进行比较，这可能导致不确定的结果 （建议已开启此类严格模式）
eg：  column_name =NULL 或者 column_name <> NULL

7) 非严格的外键约束：在非严格模式下，MySQL对外键约束的检查较为宽松，可能会允许插入或更新关联字段中不存在的值。（建议已开启此类严格模式）
eg:  tb表外键字段指向ta表的主键，如果tb表的外键字段插入的内容不存在于a表中时也可以写入成功

8) 非严格的除数为0校验：在非严格模式下，MySQL允许除数为0
eg:  10/0 ,结果为null

9) 非严格的字符串引号标识：在非严格模式下，MySQL允许单引号及双引号来引用字符串
eg: 'abc' 或 "abc"均可

10)非严格的grant模式自动创建用户：在非严格模式下，MySQL允许grant语句中如果用户不存在时自动创建用户
eg:  grant select  on db1)tb1 to test;

11) 非严格的存储引擎自动替代:  在非严格模式下，MySQL允许指定的存储引擎不可用是使用默认的存储引擎替代
eg: create table tb2(id int primary key ) engin = aaa;

PS：还会有其他的非严格模式的组合以及不同数据库的兼容模式情况，大家可以继续探索，也可以多多沟通交流，感谢。

往期精彩回顾

1.  MySQL高可用之MHA集群部署

2.  mysql8.0新增用户及加密规则修改的那些事

3.  比hive快10倍的大数据查询利器-- presto

4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5.  PostgreSQL主从复制--物理复制

6.  MySQL传统点位复制在线转为GTID模式复制

7.  MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注