我的 表结构a
id int
startdate datetime
endtdate datetime我插入一条数据
id startdate enddate
5 2009-01-01 00 nullselect * from a where enddate is null
发现不能找到这个数据
他的 结果:
id startdate enddate
null null null实际查看enddate
发现该条记录实际值为 00:00:00000
如何才能找到这条记录?
id int
startdate datetime
endtdate datetime我插入一条数据
id startdate enddate
5 2009-01-01 00 nullselect * from a where enddate is null
发现不能找到这个数据
他的 结果:
id startdate enddate
null null null实际查看enddate
发现该条记录实际值为 00:00:00000
如何才能找到这条记录?
解决这个小问题 现在有新问题。
如何在当新修改的startdate 大于 老的enddate的时候,
让enddate的值加上2小时, 这个语句不知道如何写
?
DATE_ADD(ENDdate,INTERVAL 2 HOUR)
FOR EACH ROW
BEGIN
IF NEW.startdate>OLD.enddate and old.enddate>0 THEN
update a set a.startdate=new.startdate,a.enddate=date_add(new.startdate,interval 2 hour) where a.id=old.id;
end if;
END1442-can't updte table 'a' in stored function/trigger
because it is already used by statement which
invoked this stored funciton/trigger
测试死活过不去
-> id int,
-> startdate datetime,
-> enddate datetime
-> );
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> insert into t_liyihongcug values (5,'2009-01-01 00:00:00',null);
Query OK, 1 row affected (0.05 sec)mysql> select * from t_liyihongcug;
+------+---------------------+---------+
| id | startdate | enddate |
+------+---------------------+---------+
| 5 | 2009-01-01 00:00:00 | NULL |
+------+---------------------+---------+
1 row in set (0.00 sec)mysql> select * from t_liyihongcug where enddate is null ;
+------+---------------------+---------+
| id | startdate | enddate |
+------+---------------------+---------+
| 5 | 2009-01-01 00:00:00 | NULL |
+------+---------------------+---------+
1 row in set (0.00 sec)mysql>
测试如下。mysql> delimiter //
mysql> CREATE TRIGGER tr_t_liyihongcug_update_a BEFORE UPDATE ON t_liyihongcug
-> FOR EACH ROW
-> BEGIN
-> IF NEW.startdate>OLD.enddate and old.enddate>0 THEN
-> set new.enddate=date_add(new.startdate,interval 2 hour);
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.13 sec)mysql> delimiter ;
mysql> insert into t_liyihongcug values (7,'2009-01-01 00:00:00','2009-02-01 00:00:00');
Query OK, 1 row affected (0.06 sec)mysql> select * from t_liyihongcug;
+------+---------------------+---------------------+
| id | startdate | enddate |
+------+---------------------+---------------------+
| 5 | 2009-01-01 00:00:00 | NULL |
| 7 | 2009-01-01 00:00:00 | 2009-02-01 00:00:00 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)mysql> update t_liyihongcug set startdate='2009-02-02 00:00:00' where id=5;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t_liyihongcug;
+------+---------------------+---------------------+
| id | startdate | enddate |
+------+---------------------+---------------------+
| 5 | 2009-02-02 00:00:00 | NULL |
| 7 | 2009-01-01 00:00:00 | 2009-02-01 00:00:00 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)mysql> update t_liyihongcug set startdate='2009-02-02 00:00:00' where id=7;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t_liyihongcug;
+------+---------------------+---------------------+
| id | startdate | enddate |
+------+---------------------+---------------------+
| 5 | 2009-02-02 00:00:00 | NULL |
| 7 | 2009-02-02 00:00:00 | 2009-02-02 02:00:00 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)mysql>