STRICT_ALL_TABLES 和 STRICT_TRANS_TABLES 是 mysql 中 sql_mode 的两个选项 , 之前一直弄混的 , 这里来记录一下
如何查看 sql_mode
一般通过如下命令来查看 sql_mode
show variables like "sql_mode%";
可以看到 , sql_mode 中默认存在 STRICT_TRANS_TABLES 这个选项
在SQLI中 , 有一种著名的攻击叫做 SQL-Column-Truncation , 该攻击的实现依赖于 sql_mode 的 STRICT_ALL_TABLES 属性 , 该选项和 STRICT_TRANS_TABLES 类似 , 但实际上差别还是蛮大的 , 下面来研究一下它们的区别
在MySQL的官方说明中 , STRICT_MODE 被称为 " 严格模式 "
什么是严格模式 ?
严格模式用于控制 MySQL 如何处理非法或者丢失的输入值
什么样的值被称为 " 非法或者丢失的输入值 "
1. 数据类型错误
2. 插入的数据超出范围
3. 插入的数值不合适列
4. 插入的数值不包含某列没有显示定义Default子句的值
对于上述这些值 , SQL解释器会对其进行丢失处理
区别
STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 对 事务表 和 非事务表 产生的影响是不同的
- 非事务表
非事务表是指 不支持事务的表 , 也就是指 使用MyISAM存储引擎 的表非事务表的特点 : 不支持回滚操作
-
事务表
事务表是指 支持事务的表 , 也就是指 使用InnoDB存储引擎 的表事务表的特点 : 支持回滚操作
-
两个模式对两类表产生的影响的区别
- 如果SQL语句操作的是事务表( 也就是使用InnoDB存储引擎的表 ):STRICT_ALL_TABLES 或者 STRICT_TRANS_TABLES 两个模式对数据的影响是完全一样的 . 如果语句中存在非法值或者丢失值 , 则会出现错误 , 语句被放弃并且执行回滚操作
-
如果SQL语句执行的是非事务表( 也就是使用 MyISAM存储引擎的表 ) , 当需要插入或者更新的第一行出现坏值时 , 两种模式的行为相同 . 语句会被丢弃 , 表表示不变 . 当语句插入或者更新多行 , 并且坏值出现在第二行或者更后面 , 那么两种模式处理数据的行为不同
-
STRICT_ALL_TABLES : 当遇到坏值时 , MySQL返回错误并且忽视剩余的行 , 此时位于坏值前面的数据已经被插入或者更新了 , 但是因为MyISAM存储引擎不支持回滚操作 , 所以这种部分插入或者部分更新或许会不符合需求 . 为了避免这点 , 在非事务表中推荐使用单行语句操作
-
STRICT_TRANS_TABLES : 当遇到坏值时 , MySQL会尝试将非法值转换为最接近该列需求的合法值 , 并且插入调整后的值 . 如果值丢失丢失 , MySQL会插入隐式默认值 . 总之 , 在任何情况下 , MySQL都只会生成警告并继续执行语句 , 而不是提示错误
具体的Demo
-
首先创建两个使用不同存储引擎的数据表
-
查看默认的
sql_mode, 发现其中开启了STRICT_TRANS_TABLES -
正常插入数据
STRICT_TRANS_TABLES模式
- 对于使用 InnoDB存储引擎 的数据表 , 插入坏值会直接报错
-
对于使用 MyISAM存储引擎 的数据表 , 插入坏值时会把坏值进行数据规格化后再进行插入 , 并且返回一个Warning
-
将
STRCIT_TRANS_TABLES模式 切换为STRICT_ALL_TABLES模式 -
初始化两张测试表( 或者删除并重建两张表 )
-
重新插入数据
STRICT_ALL_TABLES模式
-
对于使用 InnoDB存储引擎 的数据表 , 插入坏值时会直接报错
-
对于使用 MyISAM存储引擎 的数据表 , 插入坏值时会报错 , 但是在报错插入或者更新的数据是可以被成功插入的 !
-
