write ahead log

ロールフォワード用

golangでデータベース(RDBMS)を扱う[MySQLの例]

golang

まぁ, ドキュメント読めという話なんですが.

一々例を載せたら長くなってしまいました.

とはいえ, 僕の様なコピペプログラマにはこれくらいしておいた方が...

準備

まずは有難くパッケージをgo getします.

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

操作方法

では見ていきます.
テーブルは以下のようにしましょう.

DEPT(部署)

キー 列名 意味 型
主キー DNO 部署コード 文字列(2ケタ)
DNAME 部署名 文字列(20ケタ)
BUDGET 予算 数値(10ケタ, 内小数2ケタ)
LAST_UPDATE 最終更新日時 日付

EMP(従業員)

キー 列名 意味 型
主キー ENO 従業員コード 文字列(2ケタ)
ENAME 従業員名 文字列(20ケタ)
外部キー DNO 部署コード 文字列(2ケタ)
SALARY 給与 数値(10ケタ, 内小数2ケタ)
LAST_UPDATE 最終更新日時 日付

C.J Dateのデータベース実践講義を元に日付を足しています.

事前にDBは作っておきましょう.

C:\Users\twinbird>mysql -u root -ppassword
mysql> create database demo default character set utf8;
Query OK, 1 row affected (0.01 sec)

接続してみる

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
}

エラーが出ず終了すれば問題ないでしょう.

テーブルを作る(DDLを発行する)

結果を利用しないクエリの発行にはExecメソッドを使います.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // DDL発行
    // Resultが戻されるけど, DDLの場合は使い道がなさそう
    _, err = db.Exec(`
      CREATE TABLE dept (
          dno VARCHAR(20) PRIMARY KEY,
          dname VARCHAR(20),
          budget NUMERIC(10,2),
          lastupdate DATETIME
      )
  `)
    if err != nil {
        panic(err.Error())
    }
}

行を入れる(Insert)

LastInsertIdやRowsAffectedでは実行後の結果を取得できます.

LastInsertIdはAutoIncrementでIDなどを設定した際に値が入ってきます.

RowsAffectedは発行したクエリが影響を与えた行数を返してくれます.

今回はAutoIncrementのテーブルにしなかったので, 例を誤った感があります.

とはいえ行は入ります.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Insert文発行
    // Resultが戻される
    result, err := db.Exec(`
      INSERT INTO dept(dno, dname, budget) VALUES('D1', 'Marketing', 10000)
  `)
    if err != nil {
        panic(err.Error())
    }

    // AutoIncrementの型で使える
    // 最後に挿入したキーを返す(が, 今回は主キーをAutoIncrementにしていないので使えない.例を誤った感)
    id, err := result.LastInsertId()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(id)

    // 影響を与えた行数を返す
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

$ ./mysql.exe
0
1

探す(Select)

1行探すのにはQueryRowメソッドを使います.

DateTimeを取り扱おうとするとgolangではstringでは扱う必要があるようです.

package main

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

type Dept struct {
    DNo string
    DName string
    Budget float64
    LastUpdate string
}

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    var dept Dept

    // Select文発行
    err = db.QueryRow(`
      SELECT
           dno
          ,dname
          ,budget
          ,lastupdate
      FROM
          dept
  `).Scan(&(dept.DNo), &(dept.DName), &(dept.Budget), &(dept.LastUpdate))

    if err != nil {
        panic(err.Error())
    }

    fmt.Println(dept)
}

time.Timeを使いたいので, 調べると下記が見つかりました.

おおたの物置 - goでtime.Timeをmysqlから読む

どうやらDSNにparseTime=trueオプションをつける必要があるようです.

公式にも記載がありますね.

やってみましょう.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
    "time"
)

type Dept struct {
    DNo string
    DName string
    Budget float64
    LastUpdate time.Time
}

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    var dept Dept

    // Select文発行
    err = db.QueryRow(`
      SELECT
           dno
          ,dname
          ,budget
          ,lastupdate
      FROM
          dept
  `).Scan(&(dept.DNo), &(dept.DName), &(dept.Budget), &(dept.LastUpdate))

    if err != nil {
        panic(err.Error())
    }

    fmt.Println(dept)
}

あっさり読めました.

やはりDB毎の差分を完全に吸収できるわけではなさそうです.(そりゃそうだ)

複数件探す(Select)

複数件を検索するにはQueryメソッドを使います.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
    "time"
)

type Dept struct {
    DNo string
    DName string
    Budget float64
    LastUpdate time.Time
}

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Select文発行
    rows, err := db.Query(`
      SELECT
           dno
          ,dname
          ,budget
          ,lastupdate
      FROM
          dept
  `)
    if err != nil {
        panic(err.Error())
    }
    defer rows.Close()

    // 1行ずつ取得
    for rows.Next() {
        var dept Dept
        err := rows.Scan(&(dept.DNo), &(dept.DName), &(dept.Budget), &(dept.LastUpdate))
        if err != nil {
            panic(err.Error())
        }
        fmt.Println(dept)
    }

    // 上のイテレーション内でエラーがあれば表示
    if err := rows.Err(); err != nil {
        panic(err.Error())
    }
}

[実行結果]

$ ./mysql.exe
{D1 Marketing 10000 2017-03-29 00:00:00 +0000 UTC}
{D2 Development 12000 2017-03-29 00:00:00 +0000 UTC}
{D3 Research 5000 2017-03-29 00:00:00 +0000 UTC}

ちゃんと読めますね.

列名を取得する(Select)

列名を取得することもできます.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Select文発行
    rows, err := db.Query(`
      SELECT
           dno
          ,dname
          ,budget
          ,lastupdate
      FROM
          dept
  `)
    if err != nil {
        panic(err.Error())
    }
    defer rows.Close()

    // 列名取得
    cols, err := rows.Columns()
    if err != nil {
        panic(err.Error())
    }
    // 列名表示
    for _, name := range cols {
        fmt.Println(name)
    }
}

[実行結果]

$ ./mysql.exe
dno
dname
budget
lastupdate

更新する(Update)

Execを使います.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Update文発行
    result, err := db.Exec(`
      UPDATE
          dept
      SET
           budget = 20000
          ,lastupdate = '2017/03/31'
      WHERE
          dno = 'D1'
  `)
    if err != nil {
        panic(err.Error())
    }
    // 影響を与えた件数を取得して表示
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

$ ./mysql.exe
1

削除する(Delete)

Updateと同じでExecを使います.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // DELETE文発行
    result, err := db.Exec(`
      DELETE
      FROM
          dept
      WHERE
          dno = 'D1'
  `)
    if err != nil {
        panic(err.Error())
    }
    // 影響を与えた件数を取得
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

$ ./mysql.exe
1

引数を使う

引数の取り扱いで一番簡単なのは, ExecやQuery, QueryRowのメソッドの第2引数以降に
順番にパラメータを並べてやる方法です.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // 引数付きでInsert文発行
    result, err := db.Exec(`
      INSERT INTO dept(dno, dname, budget) VALUES(?, ?, ?)
  `, "D1", "Marketing", 10000)
    if err != nil {
        panic(err.Error())
    }
    // 影響を与えた件数を取得
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

$ ./mysql.exe
1

名前付き引数を使う(Named Parameter)

しかしまぁ現実問題, 名前付き引数なしでは業務では辛いパターンが多いでしょう.
goも1.8から名前付き引数をサポートするようになりました.

名前付き引数は
sql.Named(引数名, 引数)
の形でパラメータを作り, 上の例と同様, クエリを発行するメソッドの第2引数以降に渡してやります.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // 引数付きでInsert文発行
    result, err := db.Exec(`
      INSERT INTO dept(dno, dname, budget) VALUES(@DNO, @DNAME, @BUDGET)
  `, sql.Named("DNO", "D1"), sql.Named("DNAME", "Marketing"), sql.Named("BUDGET", 10000))
    if err != nil {
        panic(err.Error())
    }
    // 影響を与えた件数を取得
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

$ ./mysql.exe
panic: sql: driver does not support the use of Named Parameters

goroutine 1 [running]:
main.main()
        C:/msys64/home/twinbird/dropbox/lab/mysql/sample.go:23 +0x452

ぐわぁ.

この記事を書いている時点ではこのMySQLドライバは名前付き引数をサポートしていないようです.残念.

Issueは上がっていますけど, どうでしょう.

SQLite3はサポートされているとの事ですので, 試してみました.

公式の説明ではSQL内では
@引数名
の形で引数を埋め込む例があります.

が, :で埋め込むと動きました.(ここら辺はDBMS依存なのかもしれません)

package main

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
    "fmt"
)

func main() {
    // 第2引数はDBファイル名
    db, err := sql.Open("sqlite3", "sample.db")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // 引数付きでInsert文発行
    result, err := db.Exec(`
      INSERT INTO dept(dno, dname, budget) VALUES(:DNO, :DNAME, :BUDGET)
  `, sql.Named("DNO", "D1"), sql.Named("DNAME", "Marketing"), sql.Named("BUDGET", 10000))
    if err != nil {
        panic(err.Error())
    }
    // 影響を与えた件数を取得
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

C:\Users\twinbird\Dropbox\lab\sqlite3>sqlite3.exe
1

動きます.

プリペアードステートメントを使う

普通に使えます.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // プリペアードステートメント作成
    stmt, err := db.Prepare("INSERT INTO dept(dno, dname, budget) VALUES(?, ?, ?)")
    if err != nil {
        panic(err.Error())
    }
    defer stmt.Close()

    // プリペアードステートメントを使ってInsert文発行
    result, err := stmt.Exec("D1", "Marketing", 10000)
    if err != nil {
        panic(err.Error())
    }
    // 影響を与えた件数を取得
    n, err := result.RowsAffected()
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(n)
}

[実行結果]

$ ./mysql.exe
1

トランザクションを扱う

業務でやるなら, トランザクションは必須でしょう.
(Web系とかは知らないけど)

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
    "math/rand"
    "time"
)

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // 疑似乱数の種作っておく
    rand.Seed(time.Now().UnixNano())

    // トランザクション開始
    tx, err := db.Begin()
    if err != nil {
        panic(err.Error())
    }

    // recoverでロールバックするように設定しておく
    defer func() {
        if err := recover(); err != nil {
            if err := tx.Rollback(); err != nil {
                panic(err.Error())
            }
            fmt.Println("Rollbacked")
        }
    }()

    // Insert文発行
    _, err = db.Exec(`
      INSERT INTO dept(dno, dname, budget) VALUES('D1', 'Marketing', 10000)
  `)
    if err != nil {
        panic(err.Error())
    }

    // ランダムでパニクらせる
    if n := rand.Intn(10); n < 5 {
        panic("opps!!")
    }

    // コミット
    if err = tx.Commit(); err != nil {
        panic(err.Error())
    }
    fmt.Println("Commited")
}

[実行結果]

$ ./mysql.exe
Commited

$ ./mysql.exe
Commited

$ ./mysql.exe
Rollbacked

$ ./mysql.exe
Rollbacked

$ ./mysql.exe
Commited

isolation levelも扱えるっぽいんですが, よくわからなかったです.無能.

クエリをキャンセルする(context)

go1.8からはcontextをサポートしたのでクエリのキャンセルが出来るようになりました.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
    "context"
)

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Cancel可能Context作る
    ctx, cancel := context.WithCancel(context.Background())
    // そっこーキャンセル
    go func() {
        cancel()
    }()

    // Select文発行
    row := db.QueryRowContext(ctx, `
      SELECT
          dno
      FROM
          dept
  `)
    // 読み取り
    var dno string
    if err := row.Scan(&dno); err != nil {
        fmt.Println(err.Error())
    } else {
        fmt.Println(dno)
    }
}

[実行結果]

$ ./mysql.exe
context canceled

ホントはもうちょっとクエリ実行中感のある例が良いのですが.

Execとかにも同様にContextを扱うメソッドがあります.

接続状態を確認する(ping)

DBの接続状態を判断することができるpingメソッドが用意されています.

package main

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

func main() {
    // 第2引数の形式は "user:password@tcp(host:port)/dbname"
    db, err := sql.Open("mysql", "root:password@/demo?parseTime=true")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        fmt.Println("疎通エラー")
    } else {
        fmt.Println("疎通してるよ")
    }
}

[実行結果]

$ ./mysql.exe
疎通してるよ

他のDBではどうするか

Driverが多数用意されているので他のDBでも同様の事が出来ると思います.

(もちろん程度はあるでしょうが)

ORMapperもいいのがあるのかもしれませんが, 標準でとりあえずやりたいことは事足りそうです.