delete from 表名 where time(submitTime) between '00:00:00' and '08:00:00'
submitTime是datetime类型,0000-00-00 00:00:00楼上的命令between '00:00:00' and '08:00:00' 存在问题,记录的提交时间都是2005-07-15 00:10:00这种类型,如果只是00:00:00' and '08:00:00',是找不到记录的
#楼主测试过吗? 附上测试数据,你看下结果 CREATE TABLE `test0` ( `id` int(11) NOT NULL auto_increment, `submitTime` datetime, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8INSERT INTO `test0` VALUES (1,'2008-07-12 21:36:41'), (2,'2008-7-12 0:10:0'),(3,'2008-07-11 08:03:01'),(4,'2008-7-11 7:23:06');#删除0-8点的数据 delete from test0 where time(submitTime) between '00:00:00' and '08:00:00'
忘记说明了,time(submitTime):取submitTime中的时间部分
不好意思,ydage,我测试的时候没有用到time()这个函数, 我用这个命令:delete from 表名 where submitTime between '00:00:00' and '08:00:00'所以提示错误。 后来看到你的说明,,time(submitTime):取submitTime中的时间部分才明白你的意思, 不过我用delete from 表名 where time(submitTime) between '00:00:00' and '08:00:00',测试的时候,提示 time(submitTime)错误,我怀疑是time这个函数对于我这个MYSQL不支持,不过你给了我提示,我现在用delete from consume where date_format(submitTime,'%T') between '00:00:00' and '08:00:00';用date_format函数就可以了。谢谢你,结贴给分!
CREATE TABLE `test0` (
`id` int(11) NOT NULL auto_increment,
`submitTime` datetime,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8INSERT INTO `test0` VALUES (1,'2008-07-12 21:36:41'),
(2,'2008-7-12 0:10:0'),(3,'2008-07-11 08:03:01'),(4,'2008-7-11 7:23:06');#删除0-8点的数据
delete from test0 where time(submitTime) between '00:00:00' and '08:00:00'
我用这个命令:delete from 表名 where submitTime between '00:00:00' and '08:00:00'所以提示错误。
后来看到你的说明,,time(submitTime):取submitTime中的时间部分才明白你的意思,
不过我用delete from 表名 where time(submitTime) between '00:00:00' and '08:00:00',测试的时候,提示
time(submitTime)错误,我怀疑是time这个函数对于我这个MYSQL不支持,不过你给了我提示,我现在用delete from consume where date_format(submitTime,'%T') between '00:00:00' and '08:00:00';用date_format函数就可以了。谢谢你,结贴给分!