easydb项目需要将采集的全表扫描数据按照每个ip 就是每个主机分组,发送给相关负责人,我的设计是动态可配置的,这个后续再贴出来。
遇到的问题:由于当期有的数据达到7000条,如果直接发送给相关负责人的话邮件可能会僵住,影响心情。。。
数据库为mysql,要解决的问题是如何按照ip分组,并且其他字段不能丢,每个ip暂时先发送200条数据。
努力历程:
先试试分组吧,比如 select * from v_db_sql_scan_current group by ip limit 200;
本地mysql数据库可以执行该sql,但是测试环境报错
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'v_db_sql_scan_current.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
查询mysql系统变量
select @@sql_mode;
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
再查查测试环境系统变量
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
发现测试环境多了个:only_full_group_by
如果改sql_mode参考:
当然测试库也能改,先不讨论,继续思考其他方案:
select b.rownum,a.* from v_db_sql_scan_current a inner join (
SELECT id, ip,case when @ip = ip then @order_num:=@order_num+1 else @order_num:=1 end rownum,
@ip:=ip FROM v_db_sql_scan_current order by ip
) b on b.id=a.id and b.ip=a.ip where b.rownum<=200;
注意:要有排序 order by ip 就是你要统计的每个东东多少的那个字段,因为我要实现每个ip只取200条数据
比如我之前写的:
SELECT DISTINCT h.DBSERVICENAME,h.DBINSTANCENAME,sc.IP,sc.TYPE,sc.OBJECT_OWNER,sc.OBJECT_NAME,sc.SIZE_MB,sc.SQL_ID,sc.PARSING_SCHEMA_NAME,sc.FIRST_LOAD_TIME,sc.LAST_ACTIVE_TIME,sc.MODULE,sc.EXECUTIONS,sc.ROWS_PER_EXEC,sc.ELAPSED_MS_PER_EXEC,sc.SQL_TEXT,sc.COLLTIME,sc.BATCHNO,sc.RUNENVID,c.DBUID,c.CNNAME,c.NICKNAME,c.RECEIVER,c.REMARK,c.DESCR,c.CC
FROM edb_contact c
INNER JOIN edb_host h ON c.IP=h.IP AND c.RUNENVID=h.RUNENVID AND h.USEFLAG='0'
INNER JOIN (select * from v_db_sql_scan_current where id in (select aa.id from (select a.IP,count(1),max(a.id) id from v_db_sql_scan_current a group by a.ip having count(1)<=200)aa)) sc ON sc.IP=c.IP
WHERE c.RUNENVID='%s'
这种写法显然不对了,having count <200 是只取200条以内的ip ,实际是我也要超过200的,只不过是要截取到200,并非不显示了。
©声明:本站原创文章采用BY-NC-SA共享协议,受法律保护,转载请注明出处;转载文章版权归原作者所有。
©转载请注明来源: 最优质网-最有指望