Mybatis日志SQL解析_mybatis日志 ==> preparing:-CSDN博客

本文介绍如何从Mybatis的日志中提取SQL语句及其参数，通过JavaScript实现动态参数替换，转化为可以直接执行的SQL。适合排查SQL错误并优化查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开发中，mybatis执行SQL打印了这么一段日志，我们想复制出来，验证一下，找找SQL哪里出错了，如果将日志解析为SQL呢。

2022-12-30 10:38:29.823 DEBUG 7568 --- [http-nio-48080-exec-2] [TID: N/A] c.i.y.s.m.S.selectDataStatisticsList     : ==>  Preparing: SELECT * FROM system_reimbursement AS reim WHERE reim.deleted = 0 AND reim.state = 11 AND reim.type = 2 AND reim.system_classification_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND reim.system_batch_id IN (?, ?, ?) AND reim.tenant_id = '1'
2022-12-30 10:38:29.824 DEBUG 7568 --- [http-nio-48080-exec-2] [TID: N/A] c.i.y.s.m.S.selectDataStatisticsList     : ==> Parameters: 24(Long), 567(Long), 568(Long), 569(Long), 570(Long), 571(Long), 572(Long), 573(Long), 574(Long), 575(Long), 576(Long), 577(Long), 578(Long), 579(Long), 580(Long), 581(Long), 582(Long), 583(Long), 584(Long), 585(Long), 586(Long), 587(Long), 588(Long), 589(Long), 590(Long), 591(Long), 592(Long), 593(Long), 594(Long), 595(Long), 596(Long), 597(Long), 598(Long), 599(Long), 600(Long), 601(Long), 602(Long), 603(Long), 604(Long), 605(Long), 606(Long), 607(Long), 608(Long), 609(Long), 610(Long), 611(Long), 612(Long), 613(Long), 614(Long), 615(Long), 616(Long), 617(Long), 618(Long), 619(Long), 620(Long), 621(Long), 622(Long), 623(Long), 626(Long), 627(Long), 628(Long), 629(Long), 630(Long), 631(Long), 632(Long), 633(Long), 634(Long), 635(Long), 636(Long), 637(Long), 638(Long), 639(Long), 640(Long), 641(Long), 642(Long), 643(Long), 644(Long), 645(Long), 646(Long), 647(Long), 648(Long), 649(Long), 650(Long), 651(Long), 652(Long), 653(Long), 654(Long), 655(Long), 656(Long), 657(Long), 658(Long), 659(Long), 660(Long), 661(Long), 662(Long), 663(Long), 664(Long), 665(Long), 666(Long), 667(Long), 668(Long), 669(Long), 670(Long), 671(Long), 672(Long), 673(Long), 674(Long), 675(Long), 676(Long), 677(Long), 678(Long), 679(Long), 680(Long), 681(Long), 682(Long), 683(Long), 684(Long), 693(Long), 694(Long), 740(Long), 741(Long), 742(Long), 743(Long), 744(Long), 745(Long), 746(Long), 305(Long), 306(Long), 307(Long), 308(Long), 309(Long), 310(Long), 311(Long), 312(Long), 313(Long), 314(Long), 315(Long), 316(Long), 317(Long), 318(Long), 319(Long), 320(Long), 321(Long), 322(Long), 323(Long), 324(Long), 839(Long), 840(Long), 841(Long), 842(Long), 843(Long), 844(Long), 845(Long), 846(Long), 847(Long), 848(Long), 849(Long), 854(Long), 421(Long), 422(Long), 423(Long), 424(Long), 425(Long), 426(Long), 427(Long), 428(Long), 429(Long), 430(Long), 431(Long), 432(Long), 433(Long), 460(Long), 461(Long), 462(Long), 463(Long), 464(Long), 465(Long), 466(Long), 467(Long), 468(Long), 469(Long), 470(Long), 471(Long), 472(Long), 473(Long), 474(Long), 475(Long), 476(Long), 477(Long), 478(Long), 479(Long), 480(Long), 481(Long), 482(Long), 483(Long), 484(Long), 485(Long), 486(Long), 487(Long), 488(Long), 489(Long), 490(Long), 491(Long), 492(Long), 493(Long), 494(Long), 495(Long), 496(Long), 497(Long), 498(Long), 499(Long), 500(Long), 32(Long), 35(Long), 40(Long)

一、解析代码

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title></title>
</head>
<body>
 
<h2><font color="#00bfff"> 输入Mybatis SQL日志：</font></h2>
 
<textarea id="sqlLog" rows="13" cols="140" style="font-size:20px"></textarea>
 
 
<div style="border:0px deepskyblue solid;width:1425px;height:50px;text-align:right">
    <button style="color:mediumblue;width:100px;height:60px" type="button"
            onclick="clearLog(document.getElementById('sqlLog'))">
        清空
    </button>
    <button style="color:mediumblue;width:100px;height:60px" type="submit"
            onclick="f(document.getElementById('sqlLog'))">
        解析SQL
    </button>
</div>
 
<h2><font color="#32cd32">解析为可执行SQL：</font></h2>
 
<textarea id="d1" rows="13" cols="140" style="font-size:20px"></textarea>
<div style="border:0px deepskyblue solid;width:1425px;height:50px;text-align:right">
    <button style="color:mediumblue;width:100px;height:60px" type="button" onclick="copySQL()">复制SQL</button>
</div>
 
<div id="msg"
     style="color:cornflowerblue;border:0px black solid;width:800px;height:20px;text-align:right;font-style: initial;font-size: large">
</div>
<script type="text/javascript">
        function f(obj) {
            var textVa = obj.value;
 
            // 获取带问号的SQL语句
            var statementStartIndex = textVa.indexOf('Preparing: ');
            var statementEndIndex = textVa.length - 1;
            for (var i = statementStartIndex; i < textVa.length; i++) {
                if (textVa[i] == "") {
                    statementEndIndex = i;
                    break;
                }
            }
            var statementStr = textVa.substring(statementStartIndex + "Preparing: ".length, statementEndIndex);
            console.log(statementStr);
            //获取参数
            var parametersStartIndex = textVa.indexOf('Parameters: ');
            var parametersEndIndex = textVa.length - 1;
            for (var i = parametersStartIndex; i < textVa.length; i++) {
                if (textVa[i] == "") {
                    parametersEndIndex = i;
                    break;
                } else {
                    console.log(textVa[i]);
                }
            }
            var parametersStr = textVa.substring(parametersStartIndex + "Parameters: ".length, parametersEndIndex);
            parametersStr = parametersStr.split(",");
            console.log(parametersStr);
            for (var i = 0; i < parametersStr.length; i++) {
                // 如果数据中带括号将使用其他逻辑
                tempStr = parametersStr[i].substring(0, parametersStr[i].indexOf("("));
                // 获取括号中内容
                typeStr = parametersStr[i].substring(parametersStr[i].indexOf("(") + 1, parametersStr[i].indexOf(")"));
                // 如果为字符类型
                if (typeStr == "String" || typeStr == "Timestamp") {
                    statementStr = statementStr.replace("?", "'" + tempStr.trim() + "'");
                } else {
                    // 数值类型
                    statementStr = statementStr.replace("?", tempStr.trim());
                }
            }
            console.log(statementStr);
            document.getElementById("d1").innerHTML = statementStr.split('\n')[0];
            return textVa;
        }
 
        function copySQL() {
            var SQL = document.getElementById("d1");
            SQL.select(); // 选择对象
            document.execCommand("Copy"); // 执行浏览器复制命令
            var msg = document.getElementById("msg");
            msg.innerHTML = "已复制到剪切板";
            setTimeout(function () {
                msg.innerHTML = "";
            }, 3000);
 
        }
 
        function clearLog(obj) {
            obj.select();
            obj.value = "";
        }
 
    </script>
</body>
</html>

二、页面解析效果

Preparing: SELECT * FROM system_reimbursement AS reim WHERE reim.deleted = 0 AND reim.state = 11 AND reim.type = 2 AND reim.system_classification_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND reim.system_batch_id IN (?, ?, ?) AND reim.tenant_id = '1'
2022-12-30 10:38:29.824 DEBUG 7568 --- [http-nio-48080-exec-2] [TID: N/A] c.i.y.s.m.S.selectDataStatisticsList     : ==> Parameters: 24(Long), 567(Long), 568(Long), 569(Long), 570(Long), 571(Long), 572(Long), 573(Long), 574(Long), 575(Long), 576(Long), 577(Long), 578(Long), 579(Long), 580(Long), 581(Long), 582(Long), 583(Long), 584(Long), 585(Long), 586(Long), 587(Long), 588(Long), 589(Long), 590(Long), 591(Long), 592(Long), 593(Long), 594(Long), 595(Long), 596(Long), 597(Long), 598(Long), 599(Long), 600(Long), 601(Long), 602(Long), 603(Long), 604(Long), 605(Long), 606(Long), 607(Long), 608(Long), 609(Long), 610(Long), 611(Long), 612(Long), 613(Long), 614(Long), 615(Long), 616(Long), 617(Long), 618(Long), 619(Long), 620(Long), 621(Long), 622(Long), 623(Long), 626(Long), 627(Long), 628(Long), 629(Long), 630(Long), 631(Long), 632(Long), 633(Long), 634(Long), 635(Long), 636(Long), 637(Long), 638(Long), 639(Long), 640(Long), 641(Long), 642(Long), 643(Long), 644(Long), 645(Long), 646(Long), 647(Long), 648(Long), 649(Long), 650(Long), 651(Long), 652(Long), 653(Long), 654(Long), 655(Long), 656(Long), 657(Long), 658(Long), 659(Long), 660(Long), 661(Long), 662(Long), 663(Long), 664(Long), 665(Long), 666(Long), 667(Long), 668(Long), 669(Long), 670(Long), 671(Long), 672(Long), 673(Long), 674(Long), 675(Long), 676(Long), 677(Long), 678(Long), 679(Long), 680(Long), 681(Long), 682(Long), 683(Long), 684(Long), 693(Long), 694(Long), 740(Long), 741(Long), 742(Long), 743(Long), 744(Long), 745(Long), 746(Long), 305(Long), 306(Long), 307(Long), 308(Long), 309(Long), 310(Long), 311(Long), 312(Long), 313(Long), 314(Long), 315(Long), 316(Long), 317(Long), 318(Long), 319(Long), 320(Long), 321(Long), 322(Long), 323(Long), 324(Long), 839(Long), 840(Long), 841(Long), 842(Long), 843(Long), 844(Long), 845(Long), 846(Long), 847(Long), 848(Long), 849(Long), 854(Long), 421(Long), 422(Long), 423(Long), 424(Long), 425(Long), 426(Long), 427(Long), 428(Long), 429(Long), 430(Long), 431(Long), 432(Long), 433(Long), 460(Long), 461(Long), 462(Long), 463(Long), 464(Long), 465(Long), 466(Long), 467(Long), 468(Long), 469(Long), 470(Long), 471(Long), 472(Long), 473(Long), 474(Long), 475(Long), 476(Long), 477(Long), 478(Long), 479(Long), 480(Long), 481(Long), 482(Long), 483(Long), 484(Long), 485(Long), 486(Long), 487(Long), 488(Long), 489(Long), 490(Long), 491(Long), 492(Long), 493(Long), 494(Long), 495(Long), 496(Long), 497(Long), 498(Long), 499(Long), 500(Long), 32(Long), 35(Long), 40(Long)

SELECT * FROM system_reimbursement AS reim WHERE reim.deleted = 0 AND reim.state = 11 AND reim.type = 2 AND reim.system_classification_id IN (24, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582, 583, 584, 585, 586, 587, 588, 589, 590, 591, 592, 593, 594, 595, 596, 597, 598, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 626, 627, 628, 629, 630, 631, 632, 633, 634, 635, 636, 637, 638, 639, 640, 641, 642, 643, 644, 645, 646, 647, 648, 649, 650, 651, 652, 653, 654, 655, 656, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 680, 681, 682, 683, 684, 693, 694, 740, 741, 742, 743, 744, 745, 746, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 839, 840, 841, 842, 843, 844, 845, 846, 847, 848, 849, 854, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500) AND reim.system_batch_id IN (32, 35, 40) AND reim.tenant_id = '1'