go 语言 mysql_使用go语言数据库

package main

import (

"strconv"

"database/sql"

_ "github.com/go-sql-driver/mysql"

"fmt"

"time"

"log"

)

var db = &sql.DB{}

func init(){

db,_ = sql.Open("mysql", "root:root@/book")

}

func main() {

insert()

query()

update()

query()

delete()

}

func update(){

//方式1 update

start := time.Now()

for i := 1001;i<=1100;i++{

db.Exec("UPdate user set age=? where uid=? ",i,i)

}

end := time.Now()

fmt.Println("方式1 update total time:",end.Sub(start).Seconds())

//方式2 update

start = time.Now()

for i := 1101;i<=1200;i++{

stm,_ := db.Prepare("UPdate user set age=? where uid=? ")

stm.Exec(i,i)

stm.Close()

}

end = time.Now()

fmt.Println("方式2 update total time:",end.Sub(start).Seconds())

//方式3 update

start = time.Now()

stm,_ := db.Prepare("UPdate user set age=? where uid=?")

for i := 1201;i<=1300;i++{

stm.Exec(i,i)

}

stm.Close()

end = time.Now()

fmt.Println("方式3 update total time:",end.Sub(start).Seconds())

//方式4 update

start = time.Now()

tx,_ := db.Begin()

for i := 1301;i<=1400;i++{

tx.Exec("UPdate user set age=? where uid=?",i,i)

}

tx.Commit()

end = time.Now()

fmt.Println("方式4 update total time:",end.Sub(start).Seconds())

//方式5 update

start = time.Now()

for i := 1401;i<=1500;i++{

tx,_ := db.Begin()

tx.Exec("UPdate user set age=? where uid=?",i,i)

tx.Commit()

}

end = time.Now()

fmt.Println("方式5 update total time:",end.Sub(start).Seconds())

}

func delete(){

//方式1 delete

start := time.Now()

for i := 1001;i<=1100;i++{

db.Exec("DELETE FROM USER WHERE uid=?",i)

}

end := time.Now()

fmt.Println("方式1 delete total time:",end.Sub(start).Seconds())

//方式2 delete

start = time.Now()

for i := 1101;i<=1200;i++{

stm,_ := db.Prepare("DELETE FROM USER WHERE uid=?")

stm.Exec(i)

stm.Close()

}

end = time.Now()

fmt.Println("方式2 delete total time:",end.Sub(start).Seconds())

//方式3 delete

start = time.Now()

stm,_ := db.Prepare("DELETE FROM USER WHERE uid=?")

for i := 1201;i<=1300;i++{

stm.Exec(i)

}

stm.Close()

end = time.Now()

fmt.Println("方式3 delete total time:",end.Sub(start).Seconds())

//方式4 delete

start = time.Now()

tx,_ := db.Begin()

for i := 1301;i<=1400;i++{

tx.Exec("DELETE FROM USER WHERE uid=?",i)

}

tx.Commit()

end = time.Now()

fmt.Println("方式4 delete total time:",end.Sub(start).Seconds())

//方式5 delete

start = time.Now()

for i := 1401;i<=1500;i++{

tx,_ := db.Begin()

tx.Exec("DELETE FROM USER WHERE uid=?",i)

tx.Commit()

}

end = time.Now()

fmt.Println("方式5 delete total time:",end.Sub(start).Seconds())

}

func query(){

//方式1 query

start := time.Now()

rows,_ := db.Query("SELECT uid,username FROM USER")

defer rows.Close()

for rows.Next(){

var name string

var id int

if err := rows.Scan(&id,&name); err != nil {

log.Fatal(err)

}

//fmt.Printf("name:%s ,id:is %d\n", name, id)

}

end := time.Now()

fmt.Println("方式1 query total time:",end.Sub(start).Seconds())

//方式2 query

start = time.Now()

stm,_ := db.Prepare("SELECT uid,username FROM USER")

defer stm.Close()

rows,_ = stm.Query()

defer rows.Close()

for rows.Next(){

var name string

var id int

if err := rows.Scan(&id,&name); err != nil {

log.Fatal(err)

}

// fmt.Printf("name:%s ,id:is %d\n", name, id)

}

end = time.Now()

fmt.Println("方式2 query total time:",end.Sub(start).Seconds())

//方式3 query

start = time.Now()

tx,_ := db.Begin()

defer tx.Commit()

rows,_ = tx.Query("SELECT uid,username FROM USER")

defer rows.Close()

for rows.Next(){

var name string

var id int

if err := rows.Scan(&id,&name); err != nil {

log.Fatal(err)

}

//fmt.Printf("name:%s ,id:is %d\n", name, id)

}

end = time.Now()

fmt.Println("方式3 query total time:",end.Sub(start).Seconds())

}

func insert() {

//方式1 insert

//strconv,int转string:strconv.Itoa(i)

start := time.Now()

for i := 1001;i<=1100;i++{

//每次循环内部都会去连接池获取一个新的连接，效率低下

db.Exec("INSERT INTO user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)

}

end := time.Now()

fmt.Println("方式1 insert total time:",end.Sub(start).Seconds())

//方式2 insert

start = time.Now()

for i := 1101;i<=1200;i++{

//Prepare函数每次循环内部都会去连接池获取一个新的连接，效率低下

stm,_ := db.Prepare("INSERT INTO user(uid,username,age) values(?,?,?)")

stm.Exec(i,"user"+strconv.Itoa(i),i-1000)

stm.Close()

}

end = time.Now()

fmt.Println("方式2 insert total time:",end.Sub(start).Seconds())

//方式3 insert

start = time.Now()

stm,_ := db.Prepare("INSERT INTO user(uid,username,age) values(?,?,?)")

for i := 1201;i<=1300;i++{

//Exec内部并没有去获取连接，为什么效率还是低呢？

stm.Exec(i,"user"+strconv.Itoa(i),i-1000)

}

stm.Close()

end = time.Now()

fmt.Println("方式3 insert total time:",end.Sub(start).Seconds())

//方式4 insert

start = time.Now()

//Begin函数内部会去获取连接

tx,_ := db.Begin()

for i := 1301;i<=1400;i++{

//每次循环用的都是tx内部的连接，没有新建连接，效率高

tx.Exec("INSERT INTO user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)

}

//最后释放tx内部的连接

tx.Commit()

end = time.Now()

fmt.Println("方式4 insert total time:",end.Sub(start).Seconds())

//方式5 insert

start = time.Now()

for i := 1401;i<=1500;i++{

//Begin函数每次循环内部都会去连接池获取一个新的连接，效率低下

tx,_ := db.Begin()

tx.Exec("INSERT INTO user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)

//Commit执行后连接也释放了

tx.Commit()

}

end = time.Now()

fmt.Println("方式5 insert total time:",end.Sub(start).Seconds())

}

导入包

import (

"database/sql"

"fmt"

_ "github.com/go-sql-driver/mysql"

)

连接数据库

db, err := sql.Open("mysql", "root:wangshubo@/test?charset=utf8")

checkErr(err)

插入数据

stmt, err := db.Prepare("INSERT user_info SET id=?,name=?")

checkErr(err)

res, err := stmt.Exec(1, "wangshubo")

checkErr(err)

更新数据

stmt, err = db.Prepare("update user_info set name=? where id=?")

checkErr(err)

res, err = stmt.Exec("astaxieupdate", id)

checkErr(err)

查询

rows, err := db.Query("SELECT * FROM user_info")

checkErr(err)

删除

stmt, err = db.Prepare("delete from user_info where id=?")

checkErr(err)

res, err = stmt.Exec(id)

checkErr(err)