Go Web 程式設計之資料庫

darjun發表於2020-01-21

概述

資料庫用來儲存資料。只要不是玩具專案，每個專案都需要用到資料庫。現在用的最多的還是 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的介面並不複雜，但是很多細節需要注意。一不留神可能就有資源洩露。

參考

我

我的部落格

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

本文由部落格一文多發平臺 OpenWrite 釋出！

Go Web 程式設計--應用資料庫
2020-02-13
GoWeb程式設計資料庫
Go Web 程式設計入門--應用資料庫
2020-02-13
GoWeb程式設計資料庫
Go Web 程式設計之 Hello World
2021-09-09
GoWeb程式設計
程式設計師面試之MySQL資料庫表的設計
2021-08-27
程式設計師面試MySql資料庫
資料庫設計之思考
2021-06-25
資料庫
Go之資料庫操作
2020-12-31
Go資料庫
Go Web 程式設計--超詳細的模板庫應用指南
2020-03-04
GoWeb程式設計
資料庫實驗八資料庫程式設計
2020-12-24
資料庫程式設計
《Go web程式設計》ChitChat論壇
2017-12-11
GoWeb程式設計
【資料庫設計】資料庫的設計
2018-06-21
資料庫
MySQL之規範資料庫設計
2020-12-05
MySql資料庫
Go Web 程式設計入門--應用 ORM
2020-02-16
GoWeb程式設計ORM
Go Web 程式設計入門--路由器
2020-02-05
GoWeb程式設計路由器
Go 併發程式設計之 Mutex
2020-11-15
Go程式設計Mutex
《Let It Go》之程式設計師之歌
2014-06-20
Go程式設計師
Go Web 程式設計入門--編寫 Web 中介軟體
2020-02-08
GoWeb程式設計
抽獎小程式資料庫設計
2020-12-09
資料庫
python mysql資料庫程式設計
2014-12-22
PythonMySql資料庫程式設計
WINDOWS CE 資料庫程式設計 (轉)
2008-05-17
Windows資料庫程式設計
資料庫程式設計注意事項
2007-07-16
資料庫程式設計
JAVA程式與資料庫設計困惑~
2009-11-12
Java資料庫
資料庫程式設計方法總結
2009-10-25
資料庫程式設計
資料庫設計之欄位冗餘
2020-08-26
資料庫
資料庫表設計之儲存引擎
2021-06-28
資料庫儲存引擎
資料庫設計之area區域表
2016-04-22
資料庫
資料庫主鍵設計之思考(ZT)
2008-03-07
資料庫
Go進階之網路程式設計
2024-03-19
Go程式設計
好程式設計師大資料培訓分享之MySQL資料庫SQL簡介
2020-05-27
程式設計師大資料MySql資料庫
資料庫設計
2007-09-02
資料庫
大話資料庫程式設計規範
2011-03-19
資料庫程式設計
Delphi資料庫程式設計教程（九） (轉)
2007-12-14
資料庫程式設計
GO web 開發實戰二，資料庫相關
2023-01-29
GoWeb資料庫
Go Web 程式設計--深入學習解析 HTTP 請求
2020-02-25
GoWeb程式設計HTTP
PostgreSQL資料庫擴充套件語言程式設計之plpgsql-1
2017-01-13
SQL資料庫套件程式設計
好程式設計師大資料培訓分享之《MySQL資料庫》常用函式整理
2020-05-27
程式設計師大資料MySql資料庫函式
Java 程式設計技巧之資料結構
2019-10-22
Java程式設計資料結構
網路程式設計之 Udp接收資料
2019-08-22
程式設計UDP
程式設計優化之管道資料流
2022-03-10
程式設計優化

Go Web 程式設計之 資料庫

概述