09 MySQL三种模式的指定方式

195次阅读
没有评论

共计 1973 个字符,预计需要花费 5 分钟才能阅读完成。

引入

我们之前所学的表数据类型中定义 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;  # (这是演示)

09 MySQL 三种模式的指定方式

2.like 使用模糊匹配查找

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

09 MySQL 三种模式的指定方式

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

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

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

09 MySQL 三种模式的指定方式

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';
正文完
 
shawn
版权声明:本站原创文章,由 shawn 2023-06-16发表,共计1973字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)