CREATE TABLE `sys_log` (
`LogDate` datetime DEFAULT NULL,
`Level` varchar(255) DEFAULT NULL,
`Logger` varchar(255) DEFAULT NULL,
`Message` varchar(4000) DEFAULT NULL,
`Exception` varchar(2000) DEFAULT NULL,
`IP` varchar(300) DEFAULT NULL,
`Port` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;建库脚本
`LogDate` datetime DEFAULT NULL,
`Level` varchar(255) DEFAULT NULL,
`Logger` varchar(255) DEFAULT NULL,
`Message` varchar(4000) DEFAULT NULL,
`Exception` varchar(2000) DEFAULT NULL,
`IP` varchar(300) DEFAULT NULL,
`Port` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;建库脚本
解决方案 »
- autocommit与事务的隔离级别
- mysql数据库datetime出现乱码
- 向各位大侠求教一个MySQL删除特定行的问题
- 用Update更新满足Select嵌套语句的数据
- mysql4.0.20a 创建视图
- 如何把PowerDesigner连接到MySQL上去呢?
- WIN2000下如何配置MYSQL启动参数?
- 求一个简单的SQL问题
- 大家说说更新Mysql到5.6如何进行数据库方面的测试
- 如何把A表中的B表中不存在的数据插入到B表中,谢谢
- 执行动态sql出错Error 1347 ‘****’ is not BASE TABLE
- 刚开始做数据统计,请问大神们怎么快速查询好多年的日志数据?比如去重操作?
MYSQL不支持递归查询,假设所有IP、时间在工作表中均有
select * from
(
select * from
(select ip from tt group by ip),(select 时间 from tt group by 时间)) a
left join tt b on a.ip=b.ip and a.时间=b.时间 where b.时间 is null
(
select * from
(select ip from tt group by ip) a1,(select 时间 from tt group by 时间) a2) a
left join tt b on a.ip=b.ip and a.时间=b.时间 where b.时间 is null
没出来,想要的结果呀
mysql> select *
from log8;
显示结果如下:
+---------------------+-------+-------------------------------+------------------------+-----------+------+-------------------+-------+
| LogDate | Level | Logger | Message | Exception | User | Category | Port |
+---------------------+-------+-------------------------------+------------------------+-----------+------+-------------------+-------+
| 2014-01-10 12:27:54 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:28:54 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:29:54 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:30:54 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:31:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:32:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:33:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:34:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:35:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:37:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:38:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:39:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:40:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:41:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:42:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:43:55 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:44:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:45:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:46:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:47:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:48:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:49:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:50:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
| 2014-01-10 12:51:57 | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
+---------------------+-------+-------------------------------+------------------------+-----------+------+-------------------+-------+
24 rows in set红色字体的记录下面,即 36分时,,少了一条记录希望通过下面的SQL语句,,把这个时段内,少的一条记录给挑出来(这个时段内,每秒必须有一条记录)
mysql> select * from
(
select * from
(select User from log8 group by User) a1,(select LogDate from log8 group by LogDate) a2) a
left join log8 b on a.User=b.User and a.LogDate=b.LogDate where b.LogDate is null;
Empty set
上面的SQL执行后,应该出现一条记录
| 2014-01-10 12:36:55(秒数不一定,只要在36:00--36:59之间即可) | INFO | RFS6006Test.Program[Main(52)] | RFS6006Test.LogMessage | | 0 | 192.168.7.23:8080 | 60000 |
create table x (t datetime);
insert into x values ('2014-01-10 12:48:57'),
('2014-01-10 12:48:58'),
('2014-01-10 12:48:59'),
('2014-01-10 12:49:00'),
...
哇,有没有便捷的办法,生成时间表? 要是一个一个写,要写1440个....可以用存储过程,或者简单地在EXCEL表中生成,然后导入到数据库中。
BEGIN
DROP TABLE IF EXISTS temp_c1;
CREATE TEMPORARY TABLE temp_c1(logDate datetime);
SET dfrom = DATE_SUB(dfrom,INTERVAL SECOND(dfrom) SECOND);
SET dto = DATE_SUB(dto,INTERVAL SECOND(dto) SECOND);
WHILE dfrom <= dto DO
INSERT INTO temp_c1 SELECT dfrom;
SET dfrom = dfrom + INTERVAL 1 MINUTE;
END WHILE;
END;
执行过程,取得漏掉的记录时间:
SELECT MIN(logDate), MAX(logDate) INTO @tf, @tt FROM sys_log;
CALL dorepeat(@tf, @tt);
SELECT A.logDate
FROM temp_c1 A LEFT JOIN sys_log B
ON A.logDate=DATE_SUB(B.LogDate,INTERVAL SECOND(B.LogDate) SECOND)
WHERE B.LogDate IS NULL;
SELECT a.logId, a.LogDate, b.LogDate
FROM sys_log1 a, sys_log1 b
WHERE A.logId = B.logId+1
AND TIME_TO_SEC(TIMEDIFF(a.LogDate, b.LogDate))+SECOND(b.LogDate)>=120