我有一张桌子
CREATE TABLE `devicelist` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`serial` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`networkname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`login` varchar(130) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(130) COLLATE utf8_unicode_ci DEFAULT NULL,
`peopleid` int(10) unsigned NOT NULL,
`deviceid` int(10) unsigned NOT NULL,
`placeid` int(10) unsigned NOT NULL,
`stationid` int(10) unsigned NOT NULL,
`place` text COLLATE utf8_unicode_ci,
`date` date NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `placeid` (`placeid`),
KEY `stationid` (`stationid`),
KEY `peopleid` (`peopleid`),
KEY `deviceid` (`deviceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
如您所见,串行设置为NOT NULL,
但是当我运行查询时
UPDATE DeviceList SET serial = NULL WHERE id = 1
它输出
1 row affected. ( Query took 0.0079 sec )
为什么将null插入非null字段?
我想要发生错误:D
/// ******************************编辑
好吧,我找到了答案
问题出在sql_mode中
https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict
我的sql_mode默认是空的
我通过改为STRICT_ALL_TABLES
SET GLOBAL sql_mode='STRICT_ALL_TABLES'
解决方法:
由于这个原因,MySQL今天在我的日子里玩得很糟糕,所以我只是分享我对此的了解.
MySQL 5.6默认为NO_ENGINE_SUBSTITUTION.
MySQL 5.7默认为ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION.
如MySQL参考手册中的page所述,
MySQL installation programs may configure the SQL mode during the installation process. For example, mysql_install_db creates a default option file named my.cnf in the base installation directory.