如何将查询记录到数据库驱动程序? [英] How to log queries to database drivers?

问题描述

我正在尝试编写一个简单的数据库应用程序，该应用程序可以访问多个数据服务器，一些MySQL，MSSQL和SqlLite3. 我正在使用数据库/SQL"包来访问它们.

I am trying to write a simple Database application in go which access multiple data servers, some MySQL, MSSQL and SqlLite3. I am using the "database/sql" package to access them.

db, err := sql.Open(driver, dataSourceName)
result, err := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

我需要将SQL查询记录到各个服务器上，以进行调试和审计. 我该如何实现?

I need to log the SQL queries to the individual servers for debugging and auditing. How can I achieve that?

推荐答案

假定您不想使用服务器日志记录功能，显而易见的解决方案是在进行所有查询时简单地记录它们.

Assuming that you don't want to use the servers logging facilities, the obvious solution would be to simply log all queries as they are made.

db, err := sql.Open(driver, dataSourceName)
log.Println(dataSourceName, "INSERT INTO users (name, age) VALUES (?, ?)", "gopher", 27)
result, err := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

这是您的问题的基本解决方案.您可以通过多种方式对其进行优化:

This is the basic solution for your problem. You can refine it in multiple ways:

• 专门为您的查询创建log.Logger，因此您可以将其定向到特定的输出目标
• 将所述log.Logger和sql.DB对象包装在特殊的结构中，该结构将在完成查询时记录日志

• Create a log.Logger exclusively for your queries, so you can direct it to a particular output destination
• Wrap the said log.Logger and the sql.DB objects in a special struct that will log queries as they are done

以下是上述结构的粗略示例:

Here is a rough example of the said struct:

type DB struct {
    db *sql.DB
    dsn string
    log *log.Logger
}

func NewDB(driver, dsn string, log *log.Logger) (*DB, error) {
    db, err := sql.Open(driver, dsn)
    if err != nil {
        return nil, err
    }

    return &DB {
        db: db,
        dsn: dsn,
        log: log,
    }
}

func (d DB) Exec(query string, args ...interface{}) (sql.Result, err) {
    d.log.Println(dsn, query, args)
    return d.db.Exec(query, args...)
}

以及如何使用它:

l := log.New(os.Stdout, "[sql]", log.LstdFlags)

db, _ := NewDB(driver, dataSourceName, l)
result, _ := db.Exec(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    "gopher",
    27,
)

很显然，您可以通过添加错误报告，查询持续时间等来再次完善此设计.

Obviously, you can refined this design again, by adding error reporting, duration of the queries, etc.

这篇关于如何将查询记录到数据库驱动程序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！