在做一个简单的插入测试时出现如下结果:
- mysql> INSERT INTO `Pro****` VALUES(1,0,0,0,0,'en_us','***.satis***','S*** Index',0,'2008-01-28 23:53:08',0,'2011-04-21 22:59:04',0);
- ERROR 1048 (23000): Column 'CreatedDate' cannot be null
表结构如下:
- mysql> show create table Pro****;
- +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Pro**** | CREATE TABLE `Pro****` (
- `ID` int(11) NOT NULL AUTO_INCREMENT,
- `Industry` tinyint(1) NOT NULL,
- `BusinessId` int(11) NOT NULL,
- `UserId` int(11) NOT NULL,
- `CustomerId` int(11) NOT NULL,
- `Locale` varchar(10) NOT NULL,
- `Property` varchar(254) NOT NULL,
- `Value` text NOT NULL,
- `CreatedUserID` int(11) NOT NULL DEFAULT '0',
- `CreatedDate` datetime NOT NULL,
- `LastModifiedById` int(11) NOT NULL,
- `LastModifiedDate` datetime NOT NULL,
- `LastModifiedUserID` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`ID`),
- KEY `IDX_BusinessId` (`BusinessId`,`Industry`,`Locale`,`Property`),
- KEY `idx_ip` (`Industry`,`Property`)
- ) ENGINE=InnoDB AUTO_INCREMENT=14000 DEFAULT CHARSET=latin1 |
- +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
从结构来看,不应该出现上面的插入错误,检查是否有触发器。发现如下:
- mysql> show create trigger Trig_***_insert;
- +------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
- | Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation |
- +------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
- | Trig_***_insert | | CREATE DEFINER=`root`@`localhost` TRIGGER `Trig_***_insert` BEFORE INSERT ON `Pro****` FOR EACH ROW begin
- SET NEW.CreatedDate = case when NEW.CreatedDate ='0000-00-00 00:00:00' then NOW() when NEW.CreatedDate is null then NOW() end,
- NEW.LastModifiedDate =case when NEW.LastModifiedDate ='0000-00-00 00:00:00' then NOW() when NEW.LastModifiedDate is null then NOW() end;
- end | utf8 | utf8_general_ci | latin1_swedish_ci |
- +------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
- 1 row in set (0.04 sec)
粗略一看,没有问题,在触发器里判断Createdate是否是'0000-00-00'或NULL,如果是,设为now().LastModifiedDate也是做同样的判断。
等等,如果Createdate做了赋值,但是不是上面的2种情况,会如何呢?看下面这个简单的模拟例子
- mysql> set @A=10;
- Query OK, 0 rows affected (0.00 sec)
- mysql> set @A =case when @A=10 then 10 when @A=20 then 20 end;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select @A;
- +------+
- | @A |
- +------+
- | 10 |
- +------+
- 1 row in set (0.00 sec)
- mysql> set @A=100;
- Query OK, 0 rows affected (0.00 sec)
- mysql> set @A =case when @A=10 then 10 when @A=20 then 20 end;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select @A;
- +------+
- | @A |
- +------+
- | NULL |
- +------+
- 1 row in set (0.00 sec)
这就很明了了。当@A在判断条件里面没有匹配值时,它最终的结果是:NULL
在表定义里面,Createdate字段是不允许NULL的,所以报出错误。
修改:
- SET NEW.CreatedDate = case when NEW.CreatedDate ='0000-00-00 00:00:00' then NOW() when NEW.CreatedDate is null then NOW() else NEW.CreatedDate end
在判断条件后面加上else部分,这样即使匹配不到任何条件时,还能够使用这个else部分的值。
阅读(1444) | 评论(1) | 转发(0) |