ããã¯ããªã«ãããããŠæžãããã®ïŒ
Goã䜿ã£ãŠãããŒã¿ããŒã¹ã«ã¢ã¯ã»ã¹ããã³ãŒããæžããŠã¿ãããªããšæããŸããŠã
sqlããã±ãŒãž
Goã§ããŒã¿ããŒã¹ã«ã¢ã¯ã»ã¹ããã«ã¯ãsqlããã±ãŒãžã䜿ãããã§ãã
sql - The Go Programming Language
sqlããã±ãŒãžã¯ãSQLïŒã©ã€ã¯ãªïŒããŒã¿ããŒã¹ã«ã¢ã¯ã»ã¹ããããã®ãæ±çšã€ã³ã¿ãŒãã§ãŒã¹ãæäŸããããã±ãŒãžã ããã§ãã
Package sql provides a generic interface around SQL (or SQL-like) databases.
åºæ¬çãªäœ¿ãæ¹ã¯ããã¡ããåç §ã
SQLInterface · golang/go Wiki · GitHub
ãããŠãsqlããã±ãŒãžã®ã€ã³ã¿ãŒãã§ãŒã¹ãå®è£ ãããã©ã€ããŒã¯ããã¡ãã®äžèŠ§ã§ç¢ºèªã§ããŸãã
SQLDrivers · golang/go Wiki · GitHub
ä»åã¯ãMySQLã®ãã©ã€ããŒã䜿ããããšæããŸãã
ç°å¢
ä»åã®ç°å¢ã¯ããã¡ãã§ãã
$ go version go version go1.16.2 linux/amd64
MySQLã¯8.0.23ã䜿ãã172.17.0.2
ã§åäœããŠãããã®ãšããŸãã
確èªçšã®ãããžã§ã¯ãã
$ go mod init mysql-example go: creating new go.mod: module mysql-example
åäœç¢ºèªã¯ããã¹ãã³ãŒãã§è¡ãããšã«ããŸãã
$ go get github.com/stretchr/testify
ããã«ãMySQLã®ãã©ã€ããŒãå ããgo.mod
ã¯ãã¡ãã§ãã
go.mod
module mysql-example go 1.16 require ( github.com/go-sql-driver/mysql v1.5.0 // indirect github.com/stretchr/testify v1.7.0 // indirect )
Goã®MySQLãã©ã€ããŒãã€ã³ã¹ããŒã«ãã
ã€ã³ã¹ããŒã«ã¯ãgo get
ããã°OKã§ãã
Go-MySQL-Driver / Installation
$ go get github.com/go-sql-driver/mysql
å
è¿°ããgo.mod
ã«èšèŒã®éããä»åã¯v1.5.0ã䜿ããŸãã
ãã¹ãã³ãŒãã®é圢
ãŸãã¯ããã¹ãã³ãŒãã®é圢ãèŒããŠãããŸãã
main_test.go
package main import ( "context" "database/sql" "testing" _ "github.com/go-sql-driver/mysql" "github.com/stretchr/testify/assert" ) // ããã«ããã¹ãã³ãŒããæžã
MySQLãã©ã€ããŒã䜿ã
sqlã®ãã©ã€ããŒã䜿ãæã®import
ã®æžãæ¹ã¯ããã¡ãã¿ããã§ããã
import ( _ "github.com/go-sql-driver/mysql" )
ãã®æžãæ¹ã¯ãå¯äœçšãç®çãšããŠimport
ããå Žåã«äœ¿ãããã§ãã
This table illustrates how Sin is accessed in files that import the package after the various types of import declaration.
The Go Programming Language Specification / Import declarations
import
ããããã±ãŒãžèªäœã¯ããœãŒã¹ã³ãŒãå
ã§ã¯äœ¿ããŸããã䜿ãã®ã¯sql
ããã±ãŒãžåŽã§ãã
ã§ã¯ã䜿ã£ãŠãã£ãŠã¿ãŸãããã
ãã©ã€ããŒãèªèãããŠãããã確èªãã
import
ããã ãã§ãsql#Drivers
ãèªèããŠãããã©ã€ããŒã®ååãè¿ãããã«ãªããŸãã
func TestGetRegisteredDriver(t *testing.T) { assert.Equal(t, []string{"mysql"}, sql.Drivers()) }
sqlããã±ãŒãžããã©ã€ããŒãèªèããŠãããã©ããã確èªããã®ã«è¯ãããã§ããã
ããŒã¿ããŒã¹ã«æ¥ç¶ãã
ããŒã¿ããŒã¹ã«æ¥ç¶ããã«ã¯ãsql#Open
ã䜿ããŸãã
func TestPingMySql(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) assert.NotNil(t, db) defer db.Close() err = db.Ping() assert.Nil(t, err) }
sql#Open
ã®ç¬¬1åŒæ°ã¯ãã©ã€ããŒåã第2åŒæ°ã¯dataSourceName
ãDSNãšãç¥ãããã¿ããã§ããïŒïŒïŒãæå®ããŸãã
db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice")
Go-MySQL-Driver / DSN (Data Source Name)
ã¢ãã¬ã¹ã®éšåãšããæåã¡ãã£ãšãã³ãšããªãã£ãã§ãâŠã
ã¢ãã¬ã¹ã¯()
ã§å²ããã§ãããã
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]
sql#Open
ã®çµæãšããŠDB
ãè¿ã£ãŠããã®ã§ãããä»åã¯çµäºæã«DB#Close
ããããã«ããŠããŸãã
ã§ãããããã¥ã¡ã³ããèŠãŠãããšãéåžžã¯èªåã§ã¯ããŒãºããããšã¯ãªãããã§ããã
The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.
æåŸã«pingããŠãæ¥ç¶ç¢ºèªã
err = db.Ping()
æ¥ç¶æã«ãã©ã¡ãŒã¿ãŒãæå®ãã
MySQLãã©ã€ããŒã®DSNã«ã¯ããã©ã¡ãŒã¿ãŒãä»äžã§ããŸãã
â»ä»ã®ãã©ã€ããŒã¯èŠãŠããªãã®ã§ãããããŸãã
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]
QueryStringãªåœ¢åŒã§ããã
ãããªæãã§ã
func TestConnectMysqlParameter(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice?charset=utf8mb4&interpolateParams=true") assert.Nil(t, err) defer db.Close() err = db.Ping() assert.Nil(t, err) }
ä»åã®äŸã§ã¯charset
ãšinterpolateParams
ãæå®ããŠããŸãã
ãšããã§ãcharset
ã¯éæšå¥šã§ãcollation
ã䜿ã£ãæ¹ããããšãã話ã®ããã§ãã
ãªã®ã§ãããutf8mb4_ja_0900_as_cs_ks
ã¿ãããªCollationãæå®ãããšã以äžã®ããã«ãšã©ãŒã«ãªã£ããããŸãã
unknown collation
ããã«å®çŸ©ãããŠããCollationã§ãªããšããã¡ããã§ãããâŠã
https://github.com/go-sql-driver/mysql/blob/v1.5.0/collations.go
https://github.com/go-sql-driver/mysql/blob/v1.5.0/packets.go#L342-L349
äœè«ã§ããã
DDLãå®è¡ããŠã¿ã
ããŒãã«ã®create ïŒ dropãããŠã¿ãŸããããDB#Exec
ã䜿ãããã§ãã
func TestExecuteDDL(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
ãã以éã¯ããã®create ïŒ dropã§æã¿èŸŒãæãã§æžããŠãããŸãã
selectæãinsertæãå®è¡ããŠã¿ã
次ã¯ãselectæãinsertæãå®è¡ããŠã¿ãŸãããã
func TestExecuteQueryUsingInterpolateParams(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice?interpolateParams=true") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) // insertæãselectæãæžã _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
ãŸãã¯insertæãããã¯ãšãªã§ã¯ãªãå Žåã¯ãDB#Exec
ã䜿ããŸãããã©ã¡ãŒã¿ãŒã¯?
ã§ãã€ã³ããããããã§ãã
// insert result, err := db.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) assert.Nil(t, err) rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) result, err = db.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4873116389", "å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", 5280) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) result, err = db.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected)
Result
ããã¯ã圱é¿ã®ãã£ãè¡æ°ãååŸã§ããŸãããŸããauto incrementãªã©ã䜿ã£ãŠããå Žåã¯ãLastInsertId
ã§ããŒã¿ããŒã¹ã
çæããIDãååŸã§ããããã§ãã
rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected)
ç¶ããŠãã¯ãšãªã§ãã1è¡ååŸããã°ãããã®ã¯ãDB#QueryRow
ã䜿ããŸããçµæã¯ãRow
åã§è¿ããŸãã
// query row row := db.QueryRow(`select count(*) from book`) assert.Nil(t, row.Err()) var count int row.Scan(&count) assert.Equal(t, 3, count) row = db.QueryRow(`select * from book where isbn = ?`, "978-4798161488") assert.Nil(t, row.Err()) var isbn, name string var price int row.Scan(&isbn, &name, &price) assert.Equal(t, "978-4798161488", isbn) assert.Equal(t, "MySQL培åºå ¥é 第4ç", name) assert.Equal(t, 4180, price)
å€ã®ååŸã¯ãRow#Scan
ã§è¡ããŸãã
var isbn, name string var price int row.Scan(&isbn, &name, &price)
è€æ°è¡ãè¿ãå¯èœæ§ãããå Žåã¯ãDB#Query
ã§ããããã®å Žåã¯ãRows
ãè¿ã£ãŠããŸãã
// query rows rows, err := db.Query(`select title from book where price > ? order by price desc`, 4000) assert.Nil(t, err) names := []string{} for rows.Next() { var name string err := rows.Scan(&name) assert.Nil(t, err) names = append(names, name) } assert.Equal(t, []string{"å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", "MySQL培åºå ¥é 第4ç"}, names)
çµæã»ããããååŸããè¡ãé²ããã«ã¯Rows#Next
ã
for rows.Next() {
å€ã®ååŸã¯ãRow
ãšåæ§ã«Rows#Scan
ã䜿ããŸãã
var name string err := rows.Scan(&name)
Rows
ã¯Close
ã¡ãœãããåããŠããã®ã§ãããååŸããè¡ããªããªããšèªåçã«ã¯ããŒãºããããšã¯æžãããŠããŸãã
if Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err.
Package sql / func (*Rows) Close
ãšããã§ããã©ã¡ãŒã¿ãŒããã€ã³ãããéã«?
ã䜿ã£ãŠããã®ã§ãããããã«ã¯interpolateParams=true
ãšæå®ããå¿
èŠã
ããããã§ãã
Go-MySQL-Driver / / interpolateParams
ãªã®ã§ããããã®ãã©ã¡ãŒã¿ãŒãæå®ããªããŠãåäœãããããªâŠïŒ
ããšãNamed Parameterã¯ãµããŒãããŠããŸããã§ããã
https://github.com/go-sql-driver/mysql/blob/v1.5.0/utils.go#L676-L686
DB#Prepareã䜿ãå Žå
ãã©ã¡ãŒã¿ãŒããã€ã³ãããéã«ãã¡ãããšæç¶ããèžãå Žåã¯DB#Prepare
ã§Stmt
ãååŸããStmt#Exec
ãStmt#Query
ã
䜿çšããŸãã
// insert stmt, err := db.Prepare(`insert into book(isbn, title, price) values(?, ?, ?)`) assert.Nil(t, err) result, err := stmt.Exec("978-4798161488", "MySQL培åºå ¥é 第4ç", 4180)
䜿ãçµããã£ããStmt#Close
ã
stmt.Close()
å
ã»ã©ã®äŸããDB#Exec
ãDB#Query
ã«äžæ°ã«ãã©ã¡ãŒã¿ãŒããã€ã³ããããã«ãDB#Prepare
ãšStmt
ã䜿ã£ãŠæžãçŽããã®ã
ãã¡ãã§ãã
func TestExecutePrepared(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) // insert stmt, err := db.Prepare(`insert into book(isbn, title, price) values(?, ?, ?)`) assert.Nil(t, err) result, err := stmt.Exec("978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) assert.Nil(t, err) rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) result, err = stmt.Exec("978-4873116389", "å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", 5280) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) result, err = stmt.Exec("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) stmt.Close() // query row row := db.QueryRow(`select count(*) from book`) assert.Nil(t, row.Err()) var count int row.Scan(&count) assert.Equal(t, 3, count) stmt, err = db.Prepare(`select * from book where isbn = ?`) assert.Nil(t, err) row = stmt.QueryRow("978-4798161488") assert.Nil(t, row.Err()) var isbn, name string var price int row.Scan(&isbn, &name, &price) assert.Equal(t, "978-4798161488", isbn) assert.Equal(t, "MySQL培åºå ¥é 第4ç", name) assert.Equal(t, 4180, price) stmt.Close() // query rows stmt, err = db.Prepare(`select title from book where price > ? order by price desc`) assert.Nil(t, err) rows, err := stmt.Query(4000) assert.Nil(t, err) names := []string{} for rows.Next() { var name string err := rows.Scan(&name) assert.Nil(t, err) names = append(names, name) } assert.Equal(t, []string{"å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", "MySQL培åºå ¥é 第4ç"}, names) stmt.Close() _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
interpolateParams=true
ãšããæã®éãã¯ïŒãšãããšãã©ãŠã³ãããªããã®åæ°ãæžãããã§ãã
his reduces the number of roundtrips, since the driver has to prepare a statement, execute it with given parameters and close the statement again with interpolateParams=false.
Go-MySQL-Driver / / interpolateParams
ã¯ã©ã€ã¢ã³ãã§ãšã¹ã±ãŒããããŠããã¿ããã§ããããã
https://github.com/go-sql-driver/mysql/blob/v1.5.0/connection.go#L183-L306
ãã©ã³ã¶ã¯ã·ã§ã³ã䜿ã£ãŠã¿ãïŒç°¡æïŒ
次ã¯ããã©ã³ã¶ã¯ã·ã§ã³ã䜿ã£ãŠã¿ãŸãããã
func TestTransactionSimply(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) // ããã«ããã©ã³ã¶ã¯ã·ã§ã³ã䜿ã£ãã³ãŒããæžã _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
ç°¡åã«äœ¿ãã«ã¯ãDB#Begin
ã§Tx
ãååŸããŠãTx
çµç±ã§Exec
ãQuery
ãå®è¡ããæåŸã«Tx#Commit
ãTx#Rollback
ããŸãã
insertããŠããŒã«ããã¯ã
tx, err := db.Begin() assert.Nil(t, err) // insert result, err := tx.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) assert.Nil(t, err) rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) err = tx.Rollback() assert.Nil(t, err)
Tx#Commit
ãŸãã¯Tx#Rollback
ããåŸã«ã¯ããã®Tx
ã¯ãã䜿ããªãããã§ãã
// transaction has already been committed or rolled back row := tx.QueryRow(`select count(*) from book`) assert.EqualError(t, row.Err(), "sql: transaction has already been committed or rolled back") assert.ErrorIs(t, sql.ErrTxDone, row.Err())
Tx
ã䜿ã£ãŠã¯ãšãªãŒã®å®è¡ããããããŒã¿ãinsertããŠã³ãããã
tx, err = db.Begin() assert.Nil(t, err) // query row row = tx.QueryRow(`select count(*) from book`) assert.Nil(t, row.Err()) var count int row.Scan(&count) assert.Equal(t, 0, count) result, err = tx.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4873116389", "å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", 5280) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) result, err = tx.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) err = tx.Commit() assert.Nil(t, err) tx, err = db.Begin() assert.Nil(t, err) // query row row = tx.QueryRow(`select count(*) from book`) assert.Nil(t, row.Err()) row.Scan(&count) assert.Equal(t, 2, count) row = tx.QueryRow(`select * from book where isbn = ?`, "978-4873116389") assert.Nil(t, row.Err()) var isbn, name string var price int row.Scan(&isbn, &name, &price) assert.Equal(t, "978-4873116389", isbn) assert.Equal(t, "å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", name) assert.Equal(t, 5280, price) // query rows rows, err := tx.Query(`select title from book where price > ? order by price desc`, 4000) assert.Nil(t, err) names := []string{} for rows.Next() { var name string err := rows.Scan(&name) assert.Nil(t, err) names = append(names, name) } assert.Equal(t, []string{"å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç"}, names) err = tx.Commit() assert.Nil(t, err)
ãã©ã³ã¶ã¯ã·ã§ã³ã䜿ã
å
ã»ã©ã¯ç°¡æççãªæãã§çŽ¹ä»ããŸãããããã£ãšã¡ãããšäœ¿ãã«ã¯DB#BeginTx
ã䜿ããŸãããã¡ãã䜿ããšã
Context
ããã©ã³ã¶ã¯ã·ã§ã³ã®ãªãã·ã§ã³ãæå®ã§ããããã§ãã
ctx := context.Background() tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted}) assert.Nil(t, err)
DB#BeginTx
ã®æ»ãå€ã¯ãTx
ãªã®ã¯DB#Begin
ãšåããªã®ã§ããšã®æµãã¯å€ãããŸããã
DB#Begin
ã§æžããŠããã³ãŒãããDB#BeginTx
ã§æžãçŽããã®ããã¡ãã
func TestTransaction(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) ctx := context.Background() tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted}) assert.Nil(t, err) // insert result, err := tx.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) assert.Nil(t, err) rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) err = tx.Rollback() assert.Nil(t, err) // transaction has already been committed or rolled back row := tx.QueryRow(`select count(*) from book`) assert.EqualError(t, row.Err(), "sql: transaction has already been committed or rolled back") assert.ErrorIs(t, sql.ErrTxDone, row.Err()) ctx = context.Background() tx, err = db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted}) assert.Nil(t, err) // query row row = tx.QueryRow(`select count(*) from book`) assert.Nil(t, row.Err()) var count int row.Scan(&count) assert.Equal(t, 0, count) result, err = tx.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4873116389", "å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", 5280) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) result, err = tx.Exec(`insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960) assert.Nil(t, err) rowsAffected, err = result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) err = tx.Commit() assert.Nil(t, err) ctx = context.Background() tx, err = db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted}) assert.Nil(t, err) // query row row = tx.QueryRow(`select count(*) from book`) assert.Nil(t, row.Err()) row.Scan(&count) assert.Equal(t, 2, count) row = tx.QueryRow(`select * from book where isbn = ?`, "978-4873116389") assert.Nil(t, row.Err()) var isbn, name string var price int row.Scan(&isbn, &name, &price) assert.Equal(t, "978-4873116389", isbn) assert.Equal(t, "å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç", name) assert.Equal(t, 5280, price) // query rows rows, err := tx.Query(`select title from book where price > ? order by price desc`, 4000) assert.Nil(t, err) names := []string{} for rows.Next() { var name string err := rows.Scan(&name) assert.Nil(t, err) names = append(names, name) } assert.Equal(t, []string{"å®è·µãã€ããã©ãŒãã³ã¹MySQL 第3ç"}, names) err = tx.Commit() assert.Nil(t, err) _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
ã³ãã¯ã·ã§ã³ããŒã«ã®èšå®ããã
ãšããã§ããããŸã§ãã£ãšDB
ã䜿ã£ãŠããã®ã§ããã説æãèªããšã©ãããã³ãã¯ã·ã§ã³ããŒã«ãè£ã«ããããã§ãã
The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has a concept of per-connection state, such state can be reliably observed within a transaction (Tx) or connection (Conn). Once DB.Begin is called, the returned Tx is bound to a single connection. Once Commit or Rollback is called on the transaction, that transaction's connection is returned to DB's idle connection pool.
ãã©ã³ã¶ã¯ã·ã§ã³ã䜿ã£ãå Žåã¯ãã³ãã¯ã·ã§ã³ã¯ãã®Tx
ã«çŽä»ããããŸãããšãã ãããTx
çµç±ã§ã¯ãšãªãŒãå®è¡ããã
ãããã§ããããã
ã³ãã¯ã·ã§ã³ããŒã«ã®èšå®ã¯ãDB
ã«å¯ŸããŠè¡ãããã§ãã
func TestConfigurationPool(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() assert.Zero(t, db.Stats().MaxOpenConnections) db.SetMaxOpenConns(10) assert.Equal(t, 10, db.Stats().MaxOpenConnections) }
ã³ãã¯ã·ã§ã³ã䜿ã
æåŸã«ãæ瀺çã«ã³ãã¯ã·ã§ã³ã䜿ã£ãŠã¿ãŸãããã
ã³ãã¯ã·ã§ã³ïŒConn
ïŒãååŸããã«ã¯ãDB#Conn
ã䜿ããŸãããã®æãContext
ãå¿
èŠã«ãªããŸãã
// handle connection
ctx := context.Background()
conn, err := db.Conn(ctx)
䜿ãçµãã£ãConn
ã¯ã¯ããŒãºããŸããããããŒã«ã«è¿åŽããããšãæå³ããŸãã
defer conn.Close()
ã¯ãšãªãŒã®äœ¿ãæ¹ãªã©ã¯Conn
çµç±ãšãªããããã§å€§ããã¯å€ãããŸããããã¡ãœããåã«Context
ãå
¥ããåŒæ°ã«ãContext
ã
å¿
èŠã«ãªããŸãã
// insert result, err := conn.ExecContext(ctx, `insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) // query row row := conn.QueryRowContext(ctx, `select count(*) from book`) // query rows rows, err := conn.QueryContext(ctx, `select title from book where price > ? order by price desc`, 4000)
ãã©ã³ã¶ã¯ã·ã§ã³ã䜿ãå Žåã
// handle connection & tx
ctx := context.Background()
conn, err := db.Conn(ctx)
tx, err := conn.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
Context
ã䜿ãã®ã¯å€ãããŸããã
// insert result, err := tx.ExecContext(ctx, `insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) // query row row := tx.QueryRowContext(ctx, `select count(*) from book`) // query rows rows, err := tx.QueryContext(ctx, `select title from book where price > ? order by price desc`, 4000)
Conn
ã䜿ã£ãã³ãŒãäŸã¯ããã¡ãã
func TestConnectionPool(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) // handle connection ctx := context.Background() conn, err := db.Conn(ctx) assert.Nil(t, err) defer conn.Close() // insert result, err := conn.ExecContext(ctx, `insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) assert.Nil(t, err) rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) // query row row := conn.QueryRowContext(ctx, `select count(*) from book`) assert.Nil(t, row.Err()) var count int row.Scan(&count) assert.Equal(t, 1, count) row = conn.QueryRowContext(ctx, `select * from book where isbn = ?`, "978-4798161488") assert.Nil(t, row.Err()) var isbn, name string var price int row.Scan(&isbn, &name, &price) assert.Equal(t, "978-4798161488", isbn) assert.Equal(t, "MySQL培åºå ¥é 第4ç", name) assert.Equal(t, 4180, price) // query rows rows, err := conn.QueryContext(ctx, `select title from book where price > ? order by price desc`, 4000) assert.Nil(t, err) names := []string{} for rows.Next() { var name string err := rows.Scan(&name) assert.Nil(t, err) names = append(names, name) } assert.Equal(t, []string{"MySQL培åºå ¥é 第4ç"}, names) _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
ãã©ã³ã¶ã¯ã·ã§ã³ã䜿ã£ãå Žåã
func TestConnectionPoolTx(t *testing.T) { db, err := sql.Open("mysql", "kazuhira:password@(172.17.0.2:3306)/practice") assert.Nil(t, err) defer db.Close() _, err = db.Exec(`create table if not exists book(isbn varchar(14), title varchar(200), price int, primary key(isbn))`) assert.Nil(t, err) // handle connection & tx ctx := context.Background() conn, err := db.Conn(ctx) tx, err := conn.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted}) assert.Nil(t, err) defer conn.Close() // insert result, err := tx.ExecContext(ctx, `insert into book(isbn, title, price) values(?, ?, ?)`, "978-4798161488", "MySQL培åºå ¥é 第4ç", 4180) assert.Nil(t, err) rowsAffected, err := result.RowsAffected() assert.Nil(t, err) assert.Equal(t, int64(1), rowsAffected) err = tx.Commit() assert.Nil(t, err) ctx = context.Background() tx, err = conn.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted}) // query row row := tx.QueryRowContext(ctx, `select count(*) from book`) assert.Nil(t, row.Err()) var count int row.Scan(&count) assert.Equal(t, 1, count) row = tx.QueryRowContext(ctx, `select * from book where isbn = ?`, "978-4798161488") assert.Nil(t, row.Err()) var isbn, name string var price int row.Scan(&isbn, &name, &price) assert.Equal(t, "978-4798161488", isbn) assert.Equal(t, "MySQL培åºå ¥é 第4ç", name) assert.Equal(t, 4180, price) // query rows rows, err := tx.QueryContext(ctx, `select title from book where price > ? order by price desc`, 4000) assert.Nil(t, err) names := []string{} for rows.Next() { var name string err := rows.Scan(&name) assert.Nil(t, err) names = append(names, name) } assert.Equal(t, []string{"MySQL培åºå ¥é 第4ç"}, names) err = tx.Commit() assert.Nil(t, err) _, err = db.Exec(`drop table if exists book`) assert.Nil(t, err) }
ãšããã§ãDB#Query
ãªã©ã䜿ã£ãå Žåã¯ã©ããªã£ãŠãããã§ãããïŒ
ãœãŒã¹ã³ãŒããèŠããšãè£ã§Conn
ãååŸããŠããããã§ãã
https://github.com/golang/go/blob/go1.16.2/src/database/sql/sql.go#L1622-L1629
ããã«èšããšãContext
ããã®å Žã§ååŸããŠããããã§ãã
https://github.com/golang/go/blob/go1.16.2/src/database/sql/sql.go#L1619
ãã®Context
ããªã«ã«äœ¿ããã§ããããïŒãšå°ãèŠãŠã¿ãã®ã§ããããã£ã³ã»ã«ãŸããã¿ããã§ããã
ãŸãšã
ãã£ãããšãGoã䜿ã£ãŠMySQLã«ã¢ã¯ã»ã¹ããŠã¿ãŸããã
sqlããã±ãŒãžãŸããã®äœ¿ãæ¹ãšããã©ã€ããŒã®ååšãªã©ãããã£ãæãã§ãã
ïŒéäžã§ã ãã ãé¢åã«ãªã£ãŠéã«ãªã£ãŠãŸããïŒãšããããé°å²æ°ã¯ããã£ãã®ã§è¯ããšããŸãããã