安装第三方库
go get -u github.com/go-sql-driver/mysql
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
var db * sql. DB
type user struct {
id, age int
name string
}
初始化数据库连接
func initDB ( ) ( err error ) {
dataSourceName := "root:12345678@tcp(localhost:3306)/test_db"
db, err = sql. Open ( "mysql" , dataSourceName)
if err != nil {
return
}
err = db. Ping ( )
if err != nil {
return
}
db. SetConnMaxLifetime ( time. Minute * 3 )
db. SetMaxOpenConns ( 10 )
db. SetMaxIdleConns ( 10 )
fmt. Println ( "database connection is successful" )
return
}
查询单行
func queryOne ( id int ) {
sqlStr := "select id,name,age from user where id=?;"
var u1 user
err := db. QueryRow ( sqlStr, id) . Scan ( & u1. id, & u1. name, & u1. age)
if err != nil {
fmt. Println ( err)
return
}
fmt. Printf ( "u1:%#v" , u1)
}
查询多行
func queryMore ( id int ) {
sqlStr := `select id,name,age from user where id>?;`
rowsObj, err := db. Query ( sqlStr, id)
if err != nil {
return
}
defer func ( rowsObj * sql. Rows) {
err := rowsObj. Close ( )
if err != nil {
return
}
} ( rowsObj)
for rowsObj. Next ( ) {
var u2 user
err := rowsObj. Scan ( & u2. id, & u2. name, & u2. age)
if err != nil {
return
}
fmt. Printf ( "u2:%#v\n" , u2)
}
}
插入数据
func insert ( name string , age int ) {
sqlStr := `insert into user(name,age) values(?,?);`
ret, err := db. Exec ( sqlStr, name, age)
if err != nil {
return
}
theID, err := ret. LastInsertId ( )
if err != nil {
return
}
fmt. Println ( "theID:" , theID)
}
更新数据
func update ( id int , age int ) {
sqlStr := `update user set age=? where id=?;`
ret, err := db. Exec ( sqlStr, age, id)
if err != nil {
return
}
n, err := ret. RowsAffected ( )
if err != nil {
return
}
fmt. Println ( "affect rows no:" , n)
}
删除数据
func delete ( name string ) {
sqlStr := `delete from user where name=?;`
ret, err := db. Exec ( sqlStr, name)
if err != nil {
return
}
n, err := ret. RowsAffected ( )
if err != nil {
return
}
fmt. Println ( "delete rows no:" , n)
}
执行
func main ( ) {
err := initDB ( )
if err != nil {
fmt. Printf ( "Initialized database connection failed:%v:" , err)
return
}
defer func ( db * sql. DB) {
err := db. Close ( )
if err != nil {
return
}
} ( db)
queryOne ( 1 )
queryMore ( 0 )
update ( 1 , 86 )
queryMore ( 0 )
delete ( "yangmi" )
queryMore ( 0 )
}