webman的控制台输出最终的SQL语句

Ace华

先看效果,不是想要的可以直接划走
效果图
直接在控制台打印解析后的sql语句,可复制到navicat执行,而不是带有?的prepare sql
可打印出产生sql语句的文件行号,方便排查

一、生成初始化文件SqlDebug

php webman make:bootstrap SqlDebug

需要先安装命令行插件
详细见 https://www.workerman.net/doc/webman/others/bootstrap.html

二、安装dump组件

composer require symfony/var-dumper ^6.0.*

三、编辑SqlDebug

<?php

namespace app\bootstrap;

use Illuminate\Database\Events\QueryExecuted;
use support\Db;
use Symfony\Component\VarDumper\Cloner\VarCloner;
use Symfony\Component\VarDumper\Dumper\CliDumper;
use Webman\Bootstrap;

/**
 * 在控制台打印执行的SQL语句
 */
class SqlDebug implements Bootstrap
{
    /**
     * 自定义输出格式,否则输出前面会带有当前文件,无用信息
     * @param $var
     * @return void
     */
    public static function dumpvar($var): void
    {
        $cloner = new VarCloner();
        $dumper = new CliDumper();
        $dumper->dump($cloner->cloneVar($var));
    }

    public static function start($worker)
    {
        // Is it console environment ?
        $is_console = !$worker;
        if ($is_console) {
            // If you do not want to execute this in console, just return.
            return;
        }
        if (!config("app.debug") || config("app.debug") === 'false') return;
        $appPath = app_path();

        Db::connection()->listen(function (QueryExecuted $queryExecuted) use ($appPath) {
            if (isset($queryExecuted->sql) and $queryExecuted->sql !== "select 1") {
                $bindings = $queryExecuted->bindings;
                $sql = array_reduce(
                    $bindings,
                    function ($sql, $binding) {
                        return preg_replace('/\?/', is_numeric($binding) ? $binding : "'" . $binding . "'", $sql, 1);
                    },
                    $queryExecuted->sql
                );

                // self::dumpvar("[sql] [time:{$queryExecuted->time} ms] [{$sql}]"); // 这句话是打印所有的sql
                // 下面是只打印app目录下产生的sql语句
                $traces = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
                foreach ($traces as $trace) {
                    if (isset($trace['file']) && isset($trace["function"])) {
                        if (str_contains($trace['file'], $appPath)) {
                            $file = str_replace(base_path(), '', $trace['file']);
                            $str = "[file] {$file}:{$trace['line']} [function]:{$trace["function"]}";
                            self::dumpvar("[sql] [time:{$queryExecuted->time} ms] [{$sql}]");
                            self::dumpvar($str);
                        }
                    }
                }
            }
        });
    }

}

四、重启服务,执行model或Db类,效果如上图

效果图

1873 5 2
5个评论

Chance

在上个月 laravel 已经支持输出完整的 SQL 语句了
https://github.com/laravel/framework/releases/tag/v10.15.0

  • Ace华 2023-08-08

    可这是webman,不是laravel

lee

Thanks, I change the code to this and it works well

                $bindings = $queryExecuted->bindings;
                $sql = $queryExecuted->connection->getQueryGrammar()->substituteBindingsIntoRawSql(
                    $queryExecuted->sql,
                    $queryExecuted->connection->prepareBindings($bindings)
                );
  • Ace华 2023-08-10

    Thanks,and are you Chinese?

  • Ace华 2023-08-10

    But unfortunately, I run an error here, prompting:BadMethodCallException: Method Illuminate\Database\Query\Grammars\MySqlGrammar::substituteBindingsIntoRawSql does not exist

  • lee 2023-08-16

    I am using Laravel DB 10.15, maybe you should upgrade

    "illuminate/database": "^10.15",

  • Ace华 2023-08-19

    Thank you,now,i want to record the sql exec file line,but,the script not ,can you have the method?

  • lee 2023-08-25

    you can find caller file/function by debug_backtrace below

lee

example if you want file:line


                self::dumpvar("[sql] [time:{$queryExecuted->time} ms] [{$sql}]");
                // print file:line, filter only app folder
                $traces = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
                foreach ($traces as $trace) {
                    if (stristr($trace['file'], app_path())) {
                        self::dumpvar(str_replace(app_path(), 'app', $trace['file']) . ":" . $trace['line']);
                    }
                }
  • Ace华 2023-08-26

    thank you very much,it work well

古树

太棒了~~👍🏻

  • 暂无评论
z

没效果

        $article=new Posts();
        $article->post_author=$loginUser['userid'];
        $article->post_date=date('Y-m-d H:i:s');
        $article->post_date_gmt=date('Y-m-d H:i:s');
        $article->save();

这种的不输出?

  • 暂无评论

Ace华

500
积分
0
获赞数
0
粉丝数
2023-02-14 加入
🔝