MySQL表数据均匀分布到24小时内

//测试
set @mycnt = 0;
set @avgtime=0;
select (@avgtime:=1440*60/count(1)) avgtime,count(1) from wbswry;
     select (@mycnt := @mycnt + 1) as ROWNUM , DATE_FORMAT(CONCAT(CURRENT_DATE(),' ',SEC_TO_TIME(@mycnt*@avgtime-1)),'%Y%m%e%H%i%s') as tt,gxsj
      from wbswry

//执行更新
//由于在update中，变量累加需要进行赋值，故添加一个行号字段
alter table wbswry
add rn int;

set @mycnt = 0;
set @avgtime=0;
//计算表中每一行所占的时间，单位：秒
select (@avgtime:=1440*60/count(1)) avgtime,count(1) from wbswry;
//进行更新
update  wbswry set rn=@mycnt := @mycnt + 1,gxsj=CONCAT(CURRENT_DATE(),' ',SEC_TO_TIME(@mycnt*@avgtime-1))

评论

1 楼 yin_bp 2011-07-29  

学习了，支持