一个触发器里存在的bug

在做一个简单的插入测试时出现如下结果：

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部分的值。