1.连接
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
"time"
)
func main() {
db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?parseTime=true&charset=utf8&loc=Local")
checkErr(err)
// after using, release conn
defer db.Close() // if conn pool, no need
// test database conn
err = db.Ping()
checkErr(err)
// set conn pool
db.SetMaxIdleConns(10) // if 0, after release back to pool, will close, frequent new conn and close conn
db.SetMaxOpenConns(100) // if 0, no limit
db.SetConnMaxLifetime(time.Second * 600) // set conn timeout
// create table
_, err = db.Exec("create table if not exists test.hello(world varchar(50))")
checkErr(err)
// insert data
// results obj
res, err := db.Exec("insert into test.hello(world) values('hello world-3')")
checkErr(err)
rowsCnt, _ := res.RowsAffected()
recordId, err := res.LastInsertId() // 0, for not define id field
checkErr(err)
log.Printf("inserted %d rows, record-id: %d", rowsCnt, recordId)
// query data
// 1.Exec sql, 2.iterate Next, 3.Scan -> &s
rows, err := db.Query("select world from test.hello")
checkErr(err)
for rows.Next() { // iteration, pop one record a time
var s string
err = rows.Scan(&s)
checkErr(err)
log.Printf("found row containing %q", s)
}
rows.Close() // close rows obj, release to pool if has
}
func checkErr(err error) {
if err != nil {
fmt.Println(err)
return
}
}
2.CRUD
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
"time"
)
func main() {
// new instance
db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?charset=utf8&parseTime=true&loc=Local")
checkErr(err)
// test conn
err = db.Ping()
checkErr(err)
// set pool
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(time.Second * 60)
// query data
// 1.req a conn, 2.exec sql, 3.pass conn to Result set
rows, err := db.Query("select world from test.hello")
checkErr(err)
defer rows.Close()
for rows.Next() {
var s string
err = rows.Scan(&s)
checkErr(err)
log.Printf("found row containing %q", s)
}
rows.Close() // ensure if err, always can close
if err = rows.Err(); err != nil {
log.Fatal(err)
}
// query one record
var s string
// most of cases are query one result
err = db.QueryRow("select world from test.hello limit 1").Scan(&s)
if err != nil {
if err == sql.ErrNoRows {
log.Println("there is no row")
} else {
log.Fatal(err)
}
}
log.Printf("found a row: %q", s)
// err handle
var world sql.NullString
err = db.QueryRow("select world from hello where id = ?", 2).Scan(&world)
if world.Valid {
} else {
}
// auto match fields
_, err = db.Exec("create table if not exists `user` (`id` int auto_increment primary key, `user` varchar(20))")
checkErr(err)
// insert some data
_, err = db.Exec("insert into `user` (`user`) values ('Nancy'), ('Lily'), ('Mary')")
// query for auto_match_fields
rows, err = db.Query("select * from user")
checkErr(err)
cols, err := rows.Columns()
checkErr(err)
fmt.Println(cols)
vals := make([][]byte, len(cols))
scans := make([]interface{}, len(cols))
for i := range vals {
scans[i] = &vals[i]
}
var results []map[string]string
for rows.Next() {
err = rows.Scan(scans...)
checkErr(err)
row := make(map[string]string)
for k, v := range vals {
key := cols[k]
row[key] = string(v)
}
results = append(results, row)
}
for k, v := range results {
fmt.Println(k, v)
}
// insert or update or delete
// e.g. insert
res, err := db.Exec("insert into `user` (`user`) values ('Maria')")
checkErr(err)
affectedRows, err := res.RowsAffected() // if ok, ret > 0
lastId, err := res.LastInsertId() // if ok, ret > 0
if lastId > 0 || affectedRows != 0 {
fmt.Printf("insert data successful, id: %d, affected rows: %d", lastId, affectedRows)
}
}
func checkErr(err error) {
if err != nil {
log.Fatal(err)
}
}
3.Prepare
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
"time"
)
func main() {
// new instance
db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?charset=utf8&parseTime=true&loc=Local")
checkErr(err)
// test conn
err = db.Ping()
checkErr(err)
// set pool
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(time.Second * 60)
// prepare -> bulk operation && prevent sql insert
// 1.preparing 2.executing 3.closing
// e.g.
// plaintext query
rows, err := db.Query("select * from user where id = 3")
// prepared query -> mysql server will interpreter prepared
rows, err = db.Query("select * from user where id = ?", 3)
// user define prepare
stmt, err := db.Prepare("select user from user where id = ?")
checkErr(err)
defer stmt.Close()
rows, err = stmt.Query(3)
checkErr(err)
var val string
for rows.Next() {
rows.Scan(&val)
fmt.Println(val)
}
}
func checkErr(err error) {
if err != nil {
log.Fatal(err)
}
}
4.事务
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
"time"
)
func main() {
// new instance
db, err := sql.Open("mysql", "root:root@tcp(192.168.19.50:3306)/test?charset=utf8&parseTime=true&loc=Local")
checkErr(err)
// test conn
err = db.Ping()
checkErr(err)
// set pool
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(time.Second * 60)
// tx
// e.g.
/*
tx, err := db.Begin()
tx.Exec(query1)
tx.Exec(query2)
tx.Commit()
*/
tx, err := db.Begin()
checkErr(err)
defer clearTransaction(tx)
res, err := tx.Exec("update `user` set gold = 50 where user = Maria")
checkErr(err)
rowsAffected, err := res.RowsAffected()
checkErr(err)
fmt.Println(rowsAffected)
res, err = tx.Exec("update `user` set gold = 150 where id = 2")
checkErr(err)
if err = tx.Commit(); err != nil {
log.Fatal(err)
}
}
func checkErr(err error) {
if err != nil {
log.Fatal(err)
}
}
func clearTransaction(tx *sql.Tx) {
err := tx.Rollback()
if err != sql.ErrTxDone && err != nil {
log.Fatal(err)
}
}
