package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
type User struct {
id int64
name string
age int
}
func main() {
// DSN:Data Source Name
dsn := "root:@tcp(10.101.16.222:3306)/sql_test"
db, err := sql.Open("mysql", dsn)
if err != nil {
fmt.Println("connect err",err)
panic(err)
}
defer db.Close() // 注意这行代码要写在上面err判断的下面
err = db.Ping()
if err != nil {
fmt.Println("ping err",err)
return
}
//createDb(db)
createTable(db)
//insertRows(db,100)
rows, err := queryRows(db)
if err != nil {
fmt.Println("queryRows err",err)
return
}
for _, row := range rows {
fmt.Printf("%#v\n",row)
}
updateRow(db,78)
users, err := queryRows(db)
if err != nil {
fmt.Println("after update queryRows err",err)
return
}
for _, row := range users {
fmt.Printf("after update %#v\n",row)
}
txDemo(db)
}
func txDemo(db *sql.DB) {
timeout, _ := context.WithTimeout(context.Background(), time.Duration(time.Second*10))
tx, err := db.BeginTx(timeout, &sql.TxOptions{
Isolation: sql.LevelRepeatableRead,
ReadOnly: false,
})
if err != nil {
fmt.Printf("begin err%#v", err)
return
}
_, err = db.Exec("update user set name ='txupdate' where id >= 0")
querRowAndPrint(err, db)
if err != nil {
err.Error()
fmt.Printf("update err %#v", err)
tx.Rollback()
//panic(err)
}
tx.Rollback()
querRowAndPrint(err, db)
_, err = db.Exec("update user set age =age +10 where id >= 0")
if err != nil {
fmt.Printf("update err %#v", err)
tx.Rollback()
panic(err)
}
querRowAndPrint(err, db)
err = tx.Commit()
if err!=nil {
fmt.Println("commit error",err)
}
}
func querRowAndPrint(err error, db *sql.DB) bool {
rows, err := queryRows(db)
if err != nil {
fmt.Println("after update queryRows err", err)
return true
}
for _, row := range rows {
fmt.Printf("after update %#v\n", row)
}
return false
}
func insertRows(db *sql.DB, n int) {
insertSql := "insert into user (name,age) values(?,?)"
for i := 0; i < n; i++ {
var name = "houson" + string(i)
exec, err := db.Exec(insertSql, name, i)
if err!=nil {
fmt.Println("insert err",err)
return
}
fmt.Printf("insert succed%#v\n",exec)
}
}
func queryRows(db *sql.DB) ([]User, error) {
querySql := "select * from user where id >=0"
query, err := db.Query(querySql)
if err != nil {
fmt.Println("query err", err)
return nil, err
}
defer query.Close()
var res = make([]User, 0, 10)
for query.Next() {
usr := User{}
err := query.Scan(&usr.id,&usr.name,&usr.age)
if err != nil {
fmt.Println("scan err", err)
return nil, err
}
res = append(res, usr)
}
return res, nil
}
func updateRow(db *sql.DB,id int64) error {
updateSql:="update user set age=? where id =?"
exec, err := db.Exec(updateSql, 1024, id)
if err!= nil {
fmt.Println("updated err",err)
return err
}
affected, err := exec.RowsAffected()
fmt.Printf("updated %#v,%#v \n",affected, err)
return nil
}
func createTable(db *sql.DB) {
exec, err := db.Exec("CREATE TABLE IF NOT EXISTS `user`(" +
"`id` bigint not null primary key," +
"`name` varchar(10) not null default ''," +
"`age` int default 0)engine=innodb auto_increment=1 default charset=utf8mb4;")
if err!=nil {
fmt.Println("create table err",err)
panic(err)
}
fmt.Println(exec)
}
func createDb(db *sql.DB) {
createSql := "CREATE DATABASE sql_test;"
db.Exec(createSql)
selectedSql := "use sql_test;"
db.Exec(selectedSql)
}
go mysql使用
最新推荐文章于 2024-04-26 14:29:01 发布