摘要：= nil { log.Fatal("open database failed: ", err) } defer db.Close() var id int var name string var age int var salary int var teamId int rows, err := db.Query("select id, name, age, salary, team_id from employees where id =。= nil { log.Fatal("open failed: ", err) } defer db.Close() stmt, err := db.Prepare("INSERT INTO employees(name, age, salary, team_id) VALUES(。

概述

數據庫用來存儲數據。只要不是玩具項目，每個項目都需要用到數據庫。現在用的最多的還是 MySQL， PostgreSQL 的使用也在快速增長中。

在 Web 開發中，數據庫也是必須的。本文將介紹如何在 Go 語言中操作數據庫，基於 MySQL。本文假定大家已經掌握了數據庫和 MySQL 的基礎知識。

關於 MySQL 有一個非常詳細的免費教程我放在中了，需要的自取。

Go 語言標準庫 database/sql 只是提供了一組查詢和操作數據庫的接口，沒有提供任何實現。在 Go 中操作數據庫只能使用第三方庫。

各種類型的數據庫都有對應的第三方庫。Go 中支持 MySQL 的驅動中最常見的是 go-sql-driver/mysql 。

該庫支持 database/sql ，全部採用 go 實現。

數據庫操作

準備工作

創建一個數據庫 department ，表示公司中的某個部門。

在該庫中創建兩張表 employees 和 teams 。 employees 記錄員工信息， teams 記錄小組信息。

每個員工都屬於一個小組，每個小組都有若干名員工。

SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `department`
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

USE `department`;

CREATE TABLE IF NOT EXISTS `employees` (
  `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL DEFAULT '',
  `age` INT(11) NOT NULL DEFAULT 0,
  `salary` INT(11) NOT NULL DEFAULT 0,
  `team_id` INT(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `teams` (
  `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

INSERT INTO `teams`(`name`)
VALUES
  ('策劃'),
  ('開發'),
  ('運營'),
  ('運維');

INSERT INTO `employees`(`name`, `age`, `salary`, `team_id`)
VALUES
  ('張三', 28, 1200, 1),
  ('李四', 38, 4000, 1),
  ('王五', 36, 3500, 1),
  ('趙六', 31, 3100, 2),
  ('田七', 29, 2900, 2),
  ('吳八', 27, 1500, 3),
  ('朱九', 26, 1600, 3),
  ('錢十', 27, 1800, 3),
  ('陶十一', 28, 1900, 4),
  ('汪十二', 25, 2000, 4),
  ('劍十三', 24, 30000, 4);

插入一些測試數據。將這個 department.sql 文件保存到某個目錄，然後在該目錄打開命令行： 

$ mysql -u root -p

輸入密碼連接到數據庫，然後輸入以下命令：

mysql> source department.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 4 warnings (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>

這樣數據庫和表就創建好了。

連接數據庫

go-sql-driver/mysql 是第三方庫，需要安裝： 

$ go get github.com/go-sql-driver/mysql

使用：

package main

import (
  "database/sql"
  "log"

  _ "github.com/go-sql-driver/mysql"
)

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("connect database failed: ", err)
  }
  defer db.Close()
}

我們操作數據庫並不是直接使用 mysql 庫，而是通過 database/sql 的接口。 

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

上面代碼導入 mysql ，但並不直接使用，而是利用導入的副作用執行 mysql 庫的 init 函數，將 mysql 驅動註冊到 database/sql 中： 

// go-sql-driver/mysql/driver.go
func init() {
  sql.Register("mysql", &MySQLDriver{})
}

然後在程序中使用 sql.Open 創建一個 sql.DB 結構，參數一即爲 mysql 庫註冊的名字，參數二實際上就是指定數據庫連接信息的。

每個數據庫接受的連接信息是不同的。對於 MySQL 來說，連接信息實際上是一個 DSN （Data Source Name）。DSN 的一般格式爲：

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

示例中使用的就是一個 DSN，指定用戶名爲 root ，密碼爲 12345 ，

通過 tcp 協議連接到 ip 爲 127.0.0.1 ，端口爲 3306 的 MySQL 的 department 數據庫上。

在使用完成後，需要調用 db.Close 關閉 sql.DB 。

**需要特別注意的是， sql.Open 並不會建立到數據庫的連接，它也不會檢測驅動的連接參數。它僅僅創建了一個數據庫抽象層給後面使用。

到數據庫的連接實際上會在需要的時候惰性地創建。**所以，我們使用一個非法的用戶名或密碼，連接一個主機上不存在的庫， sql.Open 也不會報錯。

將上面的 DSN 改爲 user:password@tcp(127.0.0.1:6666)/not_exist_department ，運行程序，沒有報錯。

如果想要檢測數據庫是否可訪問，可以使用 db.Ping() 函數： 

err = db.Ping()
if err != nil {
  log.Fatal("ping failed: ", err)
}

這時連接 not_exist_department 會報錯： 

2020/01/20 22:16:12 ping failed: Error 1049: Unknown database 'not_exist_department'
exit status 1

sql.DB 對象一般作爲某種形式的全局變量長期存活。不要頻繁打開、關閉該對象。這對性能會有非常大的影響。

查詢

先看一個簡單示例：

package main

import (
  "database/sql"
  "log"

  _ "github.com/go-sql-driver/mysql"
)

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open database failed: ", err)
  }
  defer db.Close()

  var id int
  var name string
  var age int
  var salary int
  var teamId int

  rows, err := db.Query("select id, name, age, salary, team_id from employees where id = ?", 1)
  if err != nil {
    log.Fatal("query failed: ", err)
  }
  defer rows.Close()

  for rows.Next() {
    err := rows.Scan(&id, &name, &age, &salary, &teamId)
    if err != nil {
      log.Fatal("scan failed: ", err)
    }
    log.Printf("id: %d name:%s age:%d salary:%d teamId:%d\n", id, name, age, salary, teamId)
  }

  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }
}

運行程序，輸出：

2020/01/20 22:27:21 id: 1 name:張三 age:28 salary:1200 teamId:1

從上面程序中，我們看到一個查詢操作的基本流程：

  • 使用 db.Query() 查詢數據庫；
  • 在循環中遍歷返回的行， rows.Scan() 讀取各列的值， rows.Next() 將“指針”移動到下一行；
  • 遍歷完所有行時， rows.Next() 將返回 false，循環退出。

數據庫操作可能會遇到各種各樣的錯誤，所以錯誤處理很重要。例如，在循環中調用 rows.Scan 可能產生錯誤。

遍歷結束後，一定要關閉 rows 。因爲它持有連接的指針，不關閉會造成資源泄露。 rows.Next() 遇到最後一行時會返回一個 EOF 錯誤，並關閉連接。

另外，如果 rows.Next() 由於產生錯誤返回 false， rows 也會自動關閉。其它情況下，如果提前退出循環，可能會忘記關閉 rows 。

所以一般使用 defer rows.Close() 確保正常關閉。

Tips:

調用 Scan 方法時，其內部會根據傳入的參數類型執行相應的數據類型轉換。利用這個特性可以簡化代碼。

例如，MySQL 中某一列是 VARCHAR/CHAR 或類似的文本類型，但是我們知道它保存的是一個整數。

那麼就可以傳入一個 int 類型的變量， Scan 內部會幫助我們將字符串轉爲 int 。免除了我們手動調用 strconv 相關方法的麻煩。

database/sql 中函數的命名特別講究：

  • Query* 這種以 Query 開頭的函數，肯定返回若干行（可能爲 0）數據；
  • 不返回行數據的語句，不能使用 Query* 函數，應該使用 Exec 。

Prepare

當我們需要多次執行同一條語句時，最好的做法是先創建一個 PreparedStatement 。這個 PreparedStatement 可以包含參數佔位符，後續執行時再提供參數。

每種數據庫都有自己參數佔位符，MySQL 使用的是 ? 。使用參數佔位符有一個明顯的好處：能避免 SQL 注入攻擊 。

需要執行 SQL 時，傳入參數調用 PreparedStatement 的 Query 方法即可： 

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("select id, name, age, salary from employees where id = ?")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  rows, err := stmt.Query(2)
  if err != nil {
    log.Fatal("query failed: ", err)
  }
  defer rows.Close()

  var (
    id int
    name string
    age int
    salary int
  )
  for rows.Next() {
    err := rows.Scan(&id, &name, &age, &salary)
    if err != nil {
      log.Fatal("scan failed: ", err)
    }
    log.Printf("id:%d name:%s age:%d salary:%d\n", id, name, age, salary)
  }

  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }
}

實際上，在 db.Query() 函數內部，會先創建一個 PreparedStatement ，執行它，然後關閉。這會與數據庫產生 3 次通信。所以儘量先創建 PreparedStatement ，再使用。

單行查詢

如果查詢最多隻返回一行數據，我們不用寫循環處理，使用 QueryRow 可以簡化代碼編寫。

直接調用 db.QueryRow ： 

var name string
err = db.QueryRow("select name from employees where id = ?", 1).Scan(&name)
if err != nil {
  log.Fatal(err)
}
fmt.Println(name)

也可以在 PreparedStatement 上調用 QueryRow ： 

stmt, err := db.Prepare("select name from employees where id = ?").Scan(&name)
if err != nil {
  log.Fatal(err)
}
defer stmt.Close()
var name string
err = stmt.QueryRow(1).Scan(&name)
if err != nil {
  log.Fatal(err)
}
fmt.Println(name)

注意， QueryRow 遇到的錯誤會延遲到調用 Scan 時才返回。

插入/修改/刪除

INSERT/UPDATE/DELETE 這些操作，由於都不返回行，應該使用 Exec 函數。建議先創建 PreparedStatement 再執行。

現在“策劃組”新加入了一名員工：

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("INSERT INTO employees(name, age, salary, team_id) VALUES(?,?,?,?)")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  res, err := stmt.Exec("柳十四", 32, 5000, 1)
  if err != nil {
    log.Fatal("exec failed: ", err)
  }
  lastId, err := res.LastInsertId()
  if err != nil {
    log.Fatal("fetch last insert id failed: ", err)
  }
  rowCnt, err := res.RowsAffected()
  if err != nil {
    log.Fatal("fetch rows affected failed: ", err)
  }
  log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}

Exec 方法返回一個 sql.Result 接口類型的值： 

// src/database/sql/sql.go
type Result interface {
  LastInsertId() (int64, error)
  RowsAffected() (int64, error)
}

有些表設置了自增的 id，插入時不需要設置 id，數據庫會自動生成一個返回。 LastInsertId() 返回插入時生成的 id。

RowsAffected() 返回受影響的行數。

運行程序，輸出：

2020/01/21 07:20:26 ID = 12, affected = 1

事務

在 Go 中，事務本質上是一個對象，它持有一個到數據庫的連接。通過該對象執行我們上面介紹的方法時，

都會使用這個相同的連接。調用 db.Begin() 創建一個事務對象，然後在該對象上執行上面的方法，

最後成功調用 Commit() ，失敗調用 Rollback() 關閉事務。 

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  tx, err := db.Begin()
  if err != nil {
    log.Fatal("begin failed: ", err)
  }
  defer tx.Rollback()


  stmt, err := tx.Prepare("UPDATE employees SET team_id=? WHERE id=?")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  _, err = stmt.Exec(2, 1)
  if err != nil {
    log.Fatal("exec failed: ", err)
  }

  tx.Commit()
}

注意，在事務內部不能再直接調用 db 的方法了，因爲 db 使用的是與事務不同的連接，可能會導致執行結果的不一致。

錯誤處理

database/sql 中幾乎所有的操作最後一個返回值都是一個 error 類型。數據庫會出現各種各樣的錯誤，我們應該時刻檢查是否出現了錯誤。下面介紹幾種特殊情況產生的錯誤。

遍歷結果集

for rows.Next() {
  // ...
}

if err = rows.Err(); err != nil {
}

rows.Err() 返回的錯誤可能是 rows.Next() 循環中的多種錯誤。循環可能由於某些原因提前退出了。我們應該檢測循環是否正常退出。

異常退出時， database/sql 會自動調用 rows.Close() 。提前退出時，我們需要手動調用 rows.Close() 。 可以多次調用 rows.Close() 。

關閉結果集

實際上， rows.Close() 也返回一個錯誤。但是，對於這個錯誤，我們能做的事情比較有限。通常就是記錄日誌。

如果不需要記錄日誌，通常會忽略這個錯誤。

QueryRow

考慮下面的代碼：

var name string
err = db.QueryRow("SELECT name FROM employees WHERE id = ?", 1).Scan(&name)
if err != nil {
  log.Fatal(err)
}
fmt.Println(name)

如果沒有 id = 1 的員工， Scan() 要如何處理？

Go 定義了一個特殊的錯誤常量， sql.ErrNoRows 。如果沒有符合要求的行， QueryRow 將返回這個錯誤。

這個錯誤在大多數情況下需要特殊處理，因爲沒有結果在應用層通常不認爲是錯誤。

var name string
err = db.QueryRow("SELECT name FROM employees WHERE id = ?", 1).Scan(&name)
if err != nil {
  if err == sql.ErrNoRows {
  } else {
    log.Fatal(err)
  }
}
fmt.Println(name)

那爲什麼 QueryRow 在沒有符合要求的行時返回一個錯誤？

因爲要區分是否返回了行，如果返回空結果集，由於 Scan() 不會做任何時間，我們就不能區分 name 讀取到了空字符串，還是初始值。

特定的數據庫錯誤

爲了辨別發生了何種錯誤，有一種做法是檢查錯誤描述中是否有特定的文本：

rows, err := db.Query("SELECT someval FROM sometable")
if err != nil {
  if strings.Contains(err.Error(), "Access denied") {
  }
}

但是不推薦這種做法，因爲不同的數據庫版本，這些描述不一定能保持一致。

比較好的做法是將錯誤轉成特定數據庫驅動的錯誤，然後比較錯誤碼：

if driverErr, ok := err.(*mysql.MySQLError); ok {
  if driverErr.Number == 1045 {
  }
}

不同驅動間判斷方法可能不同。另外，直接寫數字 1045 也不太好，VividCortex 整理了 MySQL 錯誤碼，GitHub 倉庫爲 mysqlerr 。使用庫後續便於修改： 

if driverErr, ok := err.(*mysql.MySQLError); ok {
  if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {
  }
}

處理未知列

有時候，可能我們不能確定查詢返回多少列。但是 Scan() 要求傳入正確數量的參數。爲此，我們可以先使用 rows.Columns() 返回所有列名，然後創建同樣大小的字符串指針切片傳給 Scan() 函數： 

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("SELECT * FROM employees")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  rows, err := stmt.Query()
  if err != nil {
    log.Fatal("exec failed: ", err)
  }
  defer rows.Close()

  cols, err := rows.Columns()
  if err != nil {
    log.Fatal("columns failed: ", err)
  }

  data := make([]interface{}, len(cols), len(cols))
  for i := range data {
    data[i] = new(string)
  }

  for rows.Next() {
    err = rows.Scan(data...)
    if err != nil {
      log.Fatal("scan failed: ", err)
    }

    for i := 0; i < len(cols); i++ {
      fmt.Printf("%s: %s ", cols[i], *(data[i].(*string)))
    }
    fmt.Println()
  }

  if err = rows.Err(); err != nil {
    log.Fatal(err)
  }
}

運行程序：

id: 1 name: 張三 age: 28 salary: 1200 team_id: 2 
id: 2 name: 李四 age: 38 salary: 4000 team_id: 1
id: 3 name: 王五 age: 36 salary: 3500 team_id: 1
id: 4 name: 趙六 age: 31 salary: 3100 team_id: 2
id: 5 name: 田七 age: 29 salary: 2900 team_id: 2 
id: 6 name: 吳八 age: 27 salary: 1500 team_id: 3
id: 7 name: 朱九 age: 26 salary: 1600 team_id: 3
id: 8 name: 錢十 age: 27 salary: 1800 team_id: 3
id: 9 name: 陶十一 age: 28 salary: 1900 team_id: 4
id: 10 name: 汪十二 age: 25 salary: 2000 team_id: 4
id: 11 name: 劍十三 age: 24 salary: 30000 team_id: 4
id: 12 name: 柳十四 age: 32 salary: 5000 team_id: 1

連接池

database/sql 實現了一個基本的連接池。連接池有一些有趣的特性，瞭解一下，避免踩坑：

  • 對同一個數據庫連續執行兩個語句，這兩個語句可能在不同的數據庫連接上進行的。結果可能讓人誤解。例如先 LOCK TABLES ，然後執行 INSERT 可能會阻塞；
  • 需要新的連接且池中沒有空閒連接時，創建一個新連接；
  • 默認，連接數沒有限制。如果同時執行很多操作，可能會同時創建很多連接。數據庫可能出現 too many connections 錯誤；
  • 調用 db.SetMaxIdleConns(N) 限制池中最大空閒連接數； db.SetMaxOpenConns(N) 限制所有打開的連接數；
  • 一個連接很長時間不使用可能會出現問題，如果遇到連接超時，可以試試將最大空閒連接數設置爲 0；
  • 重用長時間存活的連接可能會導致網絡問題，可以調用 db.SetConnMaxLifeTime(duration) 設置連接最大存活時間。

總結

本文介紹瞭如何在 Go 中查詢和修改數據庫，主要是 database/sql 和 go-sql-driver/mysql 庫的用法。 database/sql 的接口並不複雜，但是很多細節需要注意。一不留神可能就有資源泄露。

參考

  1. MySQL 教程 ，非常詳細的教程
  2. Go database/sql 教程
  3. Build Web Application with Golang

我

我的博客

歡迎關注我的微信公衆號【GoUpUp】，共同學習，一起進步~

本文由博客一文多發平臺 OpenWrite 發佈！

相關文章