在Go中使用SQL数据库-CSDN博客

In this article I list how to do common SQL database operations with Go.

在本文中，我列出了如何使用Go执行常见SQL数据库操作。

介绍`database/sql` (Introducing `database/sql`)

Go offers a clean SQL database API in its standard library database/sql package, but the specific database drivers must be installed separately.

Go在其标准库database/sql软件包中提供了一个干净SQL数据库API，但是必须单独安装特定的数据库驱动程序。

It’s a smart approach because it provides a common interface that nearly every DB driver implements.

这是一种明智的方法，因为它提供了几乎每个数据库驱动程序都实现的通用接口。

If you want to use MySQL, you can use https://github.com/go-sql-driver/mysql.

如果要使用MySQL，可以使用https://github.com/go-sql-driver/mysql 。

If you’re using PostgreSQL, use https://github.com/lib/pq.

如果您使用的是PostgreSQL，请使用https://github.com/lib/pq 。

You just need to include the lib using import _, and the database/sql API will be configured to enable that driver:

您只需要使用import _包含lib，然后将配置database/sql API来启用该驱动程序：

import "database/sql"
import _ "github.com/go-sql-driver/mysql"

打开数据库连接 (Open the database connection)

Although the goal is to have it abstracted, there are still differences in some things, for example in how you connect to a database:

尽管目标是使其抽象化，但在某些方面仍然存在差异，例如，如何连接数据库：

import "database/sql"
import _ "github.com/go-sql-driver/mysql"

//...

db, err := sql.Open("mysql", "theUser:thePassword@/theDbName")
if err != nil {
  panic(err)
}

import "database/sql"
import _ "github.com/lib/pq"

//...

db, err := sql.Open("postgres", "user=theUser dbname=theDbName sslmode=verify-full")
if err != nil {
  panic(err)
}

but much of the actual API is db-independent, and can be interchanged quite easily (not talking about SQL here, just referring to the database API).

但是实际的许多API都是与数据库无关的，并且可以很容易地互换(这里不谈论SQL，仅指数据库API)。

关闭数据库连接 (Close the database connection)

Where it makes sense, you should always close the database connection.

在有意义的地方，您应该始终关闭数据库连接。

You can as usual use defer to close it when the function that opens the db connection ends:

当打开数据库连接的函数结束时，您可以照常使用defer将其关闭：

db, err := sql.Open("postgres", psqlInfo)
defer db.Close()

从数据库中提取数据 (Extract data from the database)

选择单行 (Select a single row)

Querying a table is done in 2 steps. First you call db.QueryRow(), then you call Scan() on the result.

查询表分两个步骤。首先，您调用db.QueryRow() ，然后对结果调用Scan() 。

Example:

例：

id := 1
var col string
sqlStatement := `SELECT col FROM my_table WHERE id=$1`
row := db.QueryRow(sqlStatement, id)
err := row.Scan(&col)
if err != nil {
    if err == sql.ErrNoRows {
        fmt.Println("Zero rows found")
    } else {
        panic(err)
    }
}

db.QueryRow() is used to query a single value from a table.

db.QueryRow()用于查询表中的单个值。

Signature:

签名：

func (db *DB) QueryRow(query string, args ...interface{}) *Row

func (db *DB) QueryRow(query string, args ...interface{}) *Row

It returns a pointer to a db.Row value.

它返回一个指向db.Row值的指针。

(*Row) Scan scans the row, copying the column values to the parameter passed into it.

(*Row) Scan扫描行，将列值复制到传递给它的参数中。

Signature:

签名：

func (r *Row) Scan(dest ...interface{}) error

func (r *Row) Scan(dest ...interface{}) error

If more than one row was returned, it only scans the first one and ignore the rest.

如果返回了多个行，则仅扫描第一行，而忽略其余行。

If no row was returned, it returns a ErrNoRows error.

如果未返回任何行，则返回ErrNoRows错误 。

var ErrNoRows = errors.New("sql: no rows in result set")

var ErrNoRows = errors.New("sql: no rows in result set")

选择多行 (Select multiple rows)

To query a single row we used db.QueryRow(). To query multiple rows we use db.Query(), which returns a *Rows value.

要查询单行，我们使用了db.QueryRow() 。要查询多行，我们使用db.Query() ，它返回一个*Rows值。

From the docs:

从文档：

//Rows is the result of a query. Its cursor starts before  the first row of the result set. Use Next to advance through the rows:

    rows, err := db.Query("SELECT ...")
    ...
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        err = rows.Scan(&id, &name)
        ...
    }
     err = rows.Err() // get any error encountered ing iteration
    ...

// Err returns the error, if any, that was encountered during iteration. // Err may be called after an explicit or implicit Close.

// Err返回在迭代过程中遇到的错误(如果有)。 //可以在显式或隐式的Close之后调用Err。

We need to iterate on rows.Next(), which allows us to call rows.Scan() into the loop.

我们需要迭代rows.Next() ，这允许我们将rows.Scan()调用到循环中。

If any error arises when preparing the next row, the loop ends and we can get the error by calling rows.Err():

如果在准备下一行时发生任何错误，则循环结束，我们可以通过调用rows.Err()来获取错误：

type Timeline struct {
    Id int
    Content string
}
rows, err := db.Query(`SELECT id, content FROM timeline`)
if err != nil {
    panic(err)
}
defer rows.Close()
for rows.Next() {
    timeline := Timeline{}
    err = rows.Scan(&timeline.Id, &timeline.Content)
    if err != nil {
        panic(err)
    }
    fmt.Println(timeline)
}
err = rows.Err()
if err != nil {
    panic(err)
}