MySQL模式设置方式介绍(严格模式:STRICT_TRANS_TABLES,无引擎提交:NO_ENGINE_SUBSTITUTION,填补字符长度:PAD_CHAR_TO_FULL_LENGTH)

引入

我们之前所学的表数据类型中定义 char 或者 varchar 字段, 当你写入长度超过了你指定的长度, 他也不会报错, 这对于mysql来说是没有任何意义的

本篇文章主要介绍 :

• STRICT_TRANS_TABLES : 严格模式
• NO_ENGINE_SUBSTITUTION : 无引擎提交
• PAD_CHAR_TO_FULL_LENGTH : 填补字符到全长度

ps : char不指定宽度. 默认宽度是1, null 关键字没有宽度限制

一.严格模式与非严格模式

简单来说就是MySQL自身对数据进行严格的校验(格式、长度、类型等)，比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错, 定义的类型长度超出了也不会报错, 严格模式则会

二.不同版本的不同效果

• 5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符, 那么会自动帮你截取
• 5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超, 一旦超出范围立刻报错(Data too long for column ‘name’ at row 1)

三.严格模式的好处

使用数据库的准则 :

能尽量少的让数据库干活就尽量少, 不要给数据库增加额外的压力

而在非严格模式下, 当遇到宽度超出时, 会进行额外的截取操作这种操作就会给数据库增加额外的压力

换个角度来说, 本来数据的存储就不因该超出限制范围

四.查看当前模式的两种方式

1.@@ 定位查找

select @@sql_mode;   # 定位查找 sql 模式
select @@gtid_mode;  # (这是演示)
select @@sql_notes;  # (这是演示)

2.like 使用模糊匹配查找

show variables like "%mode";  # 匹配含有 mode 字符的变量
show variables like "sql%";   # 匹配含有 sql 字符的变量

五.严格模式开启、关闭方式 (STRICT_TRANS_TABLES)

须知使用 set 对 SQL 模式的设置是替换操作, 想要保留原来就有的模式并添加新模式就需要将不同模式用逗号隔开

1.临时设置

• set session sql_mode='[模式]'; : 只对当前连接有效(可以省略 session)
• set global sql_mode='[模式]'; : 对全局有效(所有连接)

set sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';  # 开启严格模式+无引擎提交(下面介绍别急,它默认就有我们就带上)
select @@sql_mode;  # 查看 sql_mode

set sql_mode='NO_ENGINE_SUBSTITUTION';  # 关闭严格模式(既然是替换操作,我们把严格模式换掉就行了)
select @@sql_mode;  # 再次查看 sql_mode

2.永久设置

上面的设置方法重启 MySQL 服务之后就失效了, 想要一劳永逸就需要写入文件

• Linux 配置文件 my.cnf
• Windows 配置文件 my.ini

sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"  # 将其配置在 [mysqld] 下面

六.无引擎提交解释 (NO_ENGINE_SUBSTITUTION)

前面已经介绍过了存储引擎的概念以及一些设置的方法

我们可以使用 engine=[存储引擎] 关键字指定的方法进行表类型的设置

我们可以使用 show engines; 查看当前 mysql 所支持的存储引擎

我们可以使用 show variables like 'storage_engine%'; 模糊匹配当前正在使用的存储引擎

那么我们在指定的时候设置的是 MySQL 不支持的存储引擎, MySQL会怎么做呢?

1.添加了 : NO_ENGINE_SUBSTITUTION 的情况下

• 当我们 create table 时指定的 engine 项不被支持, 这个时候 MySQL 会报错

2.没添加 : NO_ENGINE_SUBSTITUTION 的情况下

• create table 时指定的 engine 项不被支持, MySQL 会把表的存储引擎改为 Innodb, 也就是该成默认存储引擎

3.设置方法与严格模式设置一样

七.填补字符到全长度 (PAD_CHAR_TO_FULL_LENGTH)

我们在定义表某一个字段为 char 类型的时候,会往右填充空格来满足你指定的字符长度, 例如指定长度为5, 存大于5字符报错, 小于5就以空格填充到5个字符存储

但在查询时, 查出的结果会自动删除尾部的空格

而设置SQL模式可以让其不删除空格显示

set sql_mode='pad_char_to_full_length';  # sql模式的方法都一样,要保留之前的模式就使用逗号隔开
set sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH';