mysql order by 遇到的问题

mysql> select * from receivePokeMessage49 where toId=654321 order by  delayTime desc limit 1,5;
Empty set (0.00 sec)
表结构如下：

CREATE TABLE `receivePokeMessage49` (
  `id` int(11) NOT NULL,
  `fromId` int(11) NOT NULL default '0',
  `fromName` char(40) NOT NULL default '',
  `toId` int(11) NOT NULL default '1000',
  `toName` char(40) NOT NULL default '',
  `pokeContent` varchar(128) NOT NULL default '',
  `pictureURL` char(128) NOT NULL default '',
  `foundTime` datetime default NULL,
  `delayTime` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `toId` (`toId`,`delayTime`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |

 

当执行如下操作：

 insert into receivePokeMessage49.....以后

mysql> select * from receivePokeMessage49 where toId=654321 order by  delayTime desc limit 1,5;

结果显示正常

解决方法：

mysql> alter table receivePokeMessage0 modify delayTime datetime not null;
Query OK, 26351 rows affected (1.54 sec)
Records: 26351  Duplicates: 0  Warnings: 0

 

mysql在建表的时候如果时间字段为datetime,且要加索引，不要用default null,最好写成default not null