添加索引
alter table user add index id_time_index (id, createTime);
删除索引
alter table user drop index id_time_index;
删除多余的、误添加的重复数据(删除同一时间,相同邮箱的用户,保留最小id的用户)
delete from user where (createTime,email) in(
select t.* FROM (select createTime, email from user GROUP by createTime,email HAVING COUNT()>1 )t )
and id not in(select s. from (select min(id) from user GROUP BY createTime,email having count(*)>1)s)
日期想减
datediff(date1,date2) 两个日期相减,date1减去date2得到相减之后的天数
查询一小时内创建的用户两种方法
select * from user
where timestampdiff(hour, createTime, now()) = 0 //创建时间和当前时间以小时单位想减
select * from user
where receiveTime>date_sub(now(), interval 1 hour) //当前时间-1小时
查看当前线程处理情况
show full processlist