提取sql日志文件 · PHP筆記

``` //$content為日志文件的內容 $content=<<<EOL 2021-11-06T14:24:54.945359Z 644 Connect root@localhost on iwebshop using TCP/IP 2021-11-06T14:24:54.945636Z 644 Query SET NAMES utf8 2021-11-06T14:24:54.945806Z 644 Query SET SESSION sql_mode = '' 2021-11-06T14:24:54.945996Z 644 Query SET SESSION binlog_format='MIXED' 2021-11-06T14:24:54.946195Z 644 Query SELECT * FROM iwebshop_user WHERE username = 'dash' limit 1 2021-11-06T14:24:54.946493Z 644 Query SELECT * FROM iwebshop_member WHERE user_id = 1 and status = 1 limit 1 2021-11-06T14:24:54.948518Z 644 Quit 2021-11-06T14:25:04.593621Z 645 Connect root@localhost on iwebshop using TCP/IP 2021-11-06T14:24:54.945636Z 644 Query SET NAMES utf8 2021-11-06T14:24:54.945806Z 644 Query SET SESSION sql_mode = '' 2021-11-06T14:24:54.945996Z 644 Query SET SESSION binlog_format='MIXED' 2021-11-06T14:24:54.946195Z 644 Query SELECT * FROM iwebshop_user WHERE username = 'dash' limit 1 2021-11-06T14:24:54.946493Z 644 Query SELECT * FROM iwebshop_member WHERE user_id = 1 and status = 1 limit 1 2021-11-06T14:24:54.948518Z 644 Quit EOL; //分析字母開頭、Quit+空格結尾(因為后面有空格的存在以Quit結尾是匹配不到的)，默認一定會匹配到最后一個Quit位置 //解決辦法：修正符:U 只匹配最近的一個字符串;不重復匹配; $mode="/[\w]+[\S\s]+Quit[\s]??/U";//??匹配不到Quit空格后的換行符而+?可以 echo '<pre>'; preg_match_all($mode,$content ,$matches); var_export($matches); ``` 完整 ``` $filename="D:\programming\phpstudy_pro\WWW\sql.log"; if (!file_exists($filename)) { touch($filename); } $content=file_get_contents($filename); $mode="/[\w]+[\S\s]+Quit[\s]+?/U"; preg_match_all($mode,$content ,$matches); $html=<<<EOL <!DOCTYPE html> <html lang="zh-cn"> <head> <meta charset="utf-8"> <title>site title</title>  <meta name="renderer" content="webkit">  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />  <meta name="viewport" content="width=device-width, initial-scale=1.0">   <meta name="keywords" content="your keywords">  <meta name="description" content="your description">  <link rel="shortcut icon" type="image/ico" href="/favicon.ico" /> <link href="" rel="stylesheet"> <script src="" type="text/javascript"></script> <script src="http://libs.baidu.com/jquery/1.9.0/jquery.js"></script> <script src="http://libs.baidu.com/underscore/1.3.3/underscore.js"></script> </head> <body> <button id="clear" onclick="myclear()">清空</button> </table> <script type="text/javascript"> function myclear(){ $.ajax( { // 加載指定的js文件到當前文檔中（動態加載js文件） url: "{$url}" , type: "GET" , data: { clear:"1"} }); } $(function(){ }) </script> <div> <table> EOL; foreach ($matches[0] as $key => $value) { $mode="/(?P<sql>SELECT[\s\S]*\n|select[\s\S]*\n|INSERT[\s\S]*\n|insert[\s\S]*\n|UPDATE[\s\S]*\n|update[\s\S]*\n|DELETE[\s\S]*\n|delete[\s\S]*\n)/U"; $num=$key+1; $html.=<<<EOL <tr> <th>$num</th> </tr> EOL; preg_match_all($mode,$value ,$mats); foreach ($mats[0] as $key => $value) { $html.=<<<EOL <tr> <td align="center" valign="middle" style="text-align:center;vertical-align:middle;"><code>{$value}</code></td> </tr> EOL; } } $url='http://'.$_SERVER['SERVER_NAME'].':'.$_SERVER["SERVER_PORT"].$_SERVER['PHP_SELF']; $html.=<<<EOL </div> </body> </html> EOL; if ($_GET['clear']==1) { demo($filename); } function demo($filename){ file_put_contents($filename,''); } echo $html; ```