09 MySQL三种模式的指定方式

500次阅读

没有评论

共计 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)

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

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

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

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

09 MySQL 三种模式的指定方式

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

09 MySQL 三种模式的指定方式

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

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 三种模式的指定方式

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

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

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

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

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

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

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

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

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

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

我们在定义表某一个字段为 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';

正文完