我想用存儲過程寫一個。BEGIN#Routinebodygoeshere...DECLARElastSrcIPVARCHAR(255)DEFAULT"";DECLARElastDestIPINT;DECLAREsrcIPVARCHAR(255)DEFAULT"";DECLAREdestIPINT;DECLAREcountINTDEFAULT0;DECLAREdoneINTDEFAULT0;DECLAREip_cursorCURSORFORSELECTsrcip,INET_ATON(dstip)dest_ipfromipwheresrcipin(SELECTp.srcipfromippGROUPBYsrcipHAVINGCOUNT(p.dstip)>=5)ORDERBYsrcip,dest_ip;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENip_cursor;REPEATFETCHip_cursorINTOsrcIP,destIP;IFdone=0THENINSERTINTOtbl_src_ipVALUES(UUID(),srcIP);#計數開始IFsrcIP=lastSrcIPTHENIF(destIP-lastDestIP)=1THENSETCOUNT=COUNT+1;ENDIF;IF(destIP-lastDestIP)<>1THENSETCOUNT=1;ENDIF;ENDIF;IFsrcIP<>lastSrcIPTHEN#新的srcIP組SETcount=1;SETlastSrcIP=srcIP;SETlastDestIP=destIP;ENDIF;#如果COUNT==5，則找到IFCOUNT=5THEN##放入臨時表驗證INSERTINTOtbl_src_ipVALUES(UUID(),srcIP);ENDIF;ENDIF;UNTILdoneENDREPEAT;CLOSEip_cursor;END程序還有待調通。關鍵點：mysql>SELECTsrcip,INET_ATON(dstip)dest_ipfromipwheresrcipin(SELECTp.srcipfromippGROUPBYsrcipHAVINGCOUNT(p.dstip)>=5)ORDERBYsrcip,dest_ip;+-------------+------------+|srcip|dest_ip|+-------------+------------+|192.168.0.2|3232235778||192.168.0.2|3232235779||192.168.0.2|3232235780||192.168.0.2|3232235781||192.168.0.2|3232235782||192.168.0.2|3232235783|+-------------+------------+6rowsinset1.用GROUPBY和HAVING子句找出dstip大于等于5個的記錄2.使用INET_ATON函數將dstip轉成Integer類型。
