对于以下两种场景:
1、存在null值的字段添加not null约束
2、存在重复值的字段添加唯一约束
Ghost操作结果:
1、任何模式下,如果之前存在空值,则会把空值改为0(隐形默认值),存在业务涵义上的风险
2、任何模式下,会把之前重复的数据删除,只保留第一次扫描到的记录
原生sql操作结果:
1、 Sql_mode严格模式下,失败。非严格模式下,成功,null值更改为隐形默认值。
2、 任何模式下都是失败。
default值生效的场景
default只在insert没有显式赋值的时候生效。
若显式赋值为null或者update为null,默认值无效。需要使用not null进行约束。
当前字段中存在null情况下添加not null约束
在sql_mode为严格模式下,修改操作报ERROR。
在sql_mode为非严格模式下,修改操作成功,mysql自动填充一个隐形的默认值。
隐式的默认值,不是该字段指定的默认值。这个隐式默认值取决于字段类型,对于数字就是0,对于string就是’’,对于时间就是zero。
https://dev.mysql.com/doc/refman/5.6/en/insert.html
mysql 5.6 默认sql_mode为非严格模式。
mysql 5.7 默认sql_mode为严格模式。
mysql ignore操作的影响
标黄部分为严格模式下,ignore的实际效用。
Operational Mode | When Statement Default is Error | When Statement Default is Warning |
Without IGNORE or strict SQL mode | Error | Warning |
With IGNORE | Warning | Warning (same as without IGNORE or strict SQL mode) |
With strict SQL mode | Error (same as without IGNORE or strict SQL mode) | Error |
With IGNORE and strict SQL mode | Warning | Warning |
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#ignore-strict-comparison
ghost 插入ghost表的执行语句为 insert ignore into 临时表 select from 原表。
因为ignore into的存在,往设置not null的字段中插入null值可以只报warning,并被填充为隐式默认值。
此外,ignore对于违反唯一约束的插入处理方式为忽略。