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;建库脚本
解决方案 »
- update的时候会加锁吗
- mysql++如何处理中文?谢谢~!
- 请教各位,不完全复制同一表内的数据的sql语句是什么?百思不得其解
- 那位兄弟用过 EnterpriseDB 数据库,怎么用,谢谢!!!
- 咨询一条SQL语句
- 请教怎样是MYSQL安全?
- linux 9.0下安装mysql4.0.16了,但不知配置文件(my.cnf)安装到哪去了
- 通过dbExpress连接MYSQL或其它SQL服务器的问题
- mysql主从出错,网上搜不出来解决办法,求指导.
- 数据库设计中使用了太多的主外键是不是不太好?
- 执行动态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