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)
左手的ㄟ右手
系统管理员
向右看齐
一时失言乱红尘
刺骨的言语ヽ痛彻心扉
我会带着你远行
还没有评论，来说两句吧...