如何在Go / MySQL中记录所有的出站SQL语句?

huangapple go评论91阅读模式
英文:

How can I log all outgoing SQL statements from Go / MySQL?

问题

我正在使用一个非框架的Go堆栈,其中包括sqlxMySQL,用于一个Web项目。

我想要记录所有的出站SQL语句以进行调试。这个有可能吗?希望能够获得类似以下输出的结果(从一个Rails项目中复制):

  User Load (94.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'bondnewyork' LIMIT 1
  User Load (16.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'mkovarik' LIMIT 1
  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'mkovarik' LIMIT 1
  User Load (0.3ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
英文:

I'm using a non-framework Go stack with sqlx, and MySQL for a web project.

I would like to log all outgoing SQL statements for debugging. Is it possible to do this? Hopefully looking to get output like this (copied from a Rails project):

  User Load (94.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'bondnewyork' LIMIT 1
  User Load (16.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'mkovarik' LIMIT 1
  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`login` = 'mkovarik' LIMIT 1
  User Load (0.3ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1

答案1

得分: 20

sqlx在以下接口的形式中具有非常有趣的抽象:

它们在整个库中被用作表示使用字符串作为SQL查询功能的接口。

例如:

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

// 执行模式或失败的模式;多语句Exec行为因数据库驱动而异;pq会执行它们所有,sqlite3不会,结果可能会有所不同
db.MustExec("CREATE TABLE person (first_name text)")

事实上,最后一行等同于:

sqlx.MustExec(db, "CREATE TABLE person (first_name text)")

其中db被用作Execer

同样地,这个:

people := []Person{}
db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")

等同于:

sqlx.Select(db, &people, "SELECT * FROM person ORDER BY first_name ASC")

其中db被用作Queryer

因此,如果你愿意不直接使用DB类型,而是使用库中的底层自由函数,你可以使用以下结构将你的db封装到执行日志的对象中:

type QueryLogger struct {
    queryer sqlx.Queryer
    logger  *log.Logger
}

func (p *QueryLogger) Query(query string, args ...interface{}) (*sql.Rows, error) {
    p.logger.Print(query, args...)
    return p.queryer.Query(query, args...)
}

func (p *QueryLogger) Queryx(query string, args ...interface{}) (*Rows, error) {
    p.logger.Print(query, args...)
    return p.queryer.Queryx(query, args...)
}

func (p *QueryLogger) QueryRowx(query string, args ...interface{}) *Row {
    p.logger.Print(query, args...)
    return p.queryer.QueryRowx(query, args...)
}

当连接到数据库时:

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

ql := &QueryLogger{db, yourLogger}

sqlx.Select(ql, &people, "SELECT * FROM person ORDER BY first_name ASC")

当然,这仅适用于使用sqlx库的自由函数时,所以如果你的代码有大量使用sqlx.DB类型的调用,这可能不够方便。

英文:

sqlx has a very interesting abstraction in the form of the following interfaces:

They are used all through the library as the interfaces representing the functionality of using strings as SQL queries.

For example:

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

// exec the schema or fail; multi-statement Exec behavior varies between
// database drivers;  pq will exec them all, sqlite3 won't, ymmv
db.MustExec("CREATE TABLE person (first_name text)")

The last line is in fact equivalent to:

sqlx.MustExec(db, "CREATE TABLE person (first_name text)")

Where db is used as an Execer.

In the same vein, this:

people := []Person{}
db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")

is equivalent to:

sqlx.Select(db, &people, "SELECT * FROM person ORDER BY first_name ASC")

where db is used as a Queryer.

So if you are willing not to use the DB type directly but instead use the underlying free functions of the library, you can use the following structure to wrap your db into objects that perform logging:

type QueryLogger struct {
    queryer sqlx.Queryer
    logger  *log.Logger
}

func (p *QueryLogger) Query(query string, args ...interface{}) (*sql.Rows, error) {
    p.logger.Print(query, args...)
    return p.queryer.Query(query, args...)
}

func (p *QueryLogger) Queryx(query string, args ...interface{}) (*Rows, error) {
    p.logger.Print(query, args...)
    return p.queryer.Queryx(query, args...)
}

func (p *QueryLogger) QueryRowx(query string, args ...interface{}) *Row {
    p.logger.Print(query, args...)
    return p.queryer.QueryRowx(query, args...)
}

And when connecting to your database:

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

ql := &QueryLogger{db, yourLogger}

sqlx.Select(ql, &people, "SELECT * FROM person ORDER BY first_name ASC")

Of course this only works when using the free functions of the sqlx library, so if your code has a large amount of calls using the sqlx.DB type, this will probably not be convenient enough.

huangapple
  • 本文由 发表于 2015年10月9日 22:39:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/33041063.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定