Go Mysql驱动

醉翁之意 發表於 2020-2-27 19:30:00

Golang中MYSQL驱动
<ul>
<li class="p2">Mysql库https://github.com/go-sql-driver/mysql</li>
<li class="p2">Go本身不提供具体数据库驱动，只提供驱动接口和管理。</li>
<li class="p2">各个数据库驱动需要第三方实现，并且注册到Go中的驱动管理中。</li>
</ul>
 
安装golang mysql drvier
go get github.com/go-sql-driver/mysql
代码中需要注册mysql数据库驱动，通过引入空白导入mysql包来完成。
为什么需要使用空白导入？是因为需要执行mysql包的初始化代码（代码位于%GOPATH%/github.com/go-sql-driver/mysql/driver.go）
<div class="cnblogs_code">
<pre>func init() {
sql.Register("mysql", &MySQLDriver{})
}</pre>
</div>
 
连接数据的DSN格式
<code>username:password@protocol(address)/dbname?param=value</code>
 
Prepared Statement  sql.Stmt支持预备表达式，可以用来优化SQL查询提高性能，减少SQL注入的风险, DB.Prepare()和Tx.Prepare()都提供了对于预备表达式的支持。
预处理的流程: step1. 将sql分为2部分.命令部分和数据部分. step2. 首先将命令部分发送给mysql服务器,mysql进行预处理.(如生成AST) step3. 然后将数据部分发送给mysql服务器,mysql进行占位符替换. step4. mysql服务器执行sql语句,把执行结果发送给客户端.
预处理的优势: 1.因为发送命令后,在mysql服务器端,就会将AST生成好,所以不需要对每一次值的更换都重新生成一次AST.对同样的数据不同的SQL来讲,只需生成1次AST,并缓存起来即可. 2.避免SQL注入.因为mysql知道再次发送过来的内容为”数据”,因此不会将这些数据解析为SQL,避免了SQL注入.
需要注意的点: 使用预处理进行查询操作时,不仅在defer时需要关闭结果集,而且还要关闭命令句柄,否则同样会占用连接,导致阻塞.
<div class="cnblogs_code">
package main
import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" )
type User struct { Id int `db:"id"` Name string `db:"name"` Age int `db:"age"` }
 func PrepareQuery(db *sql.DB, id int) { stmt, err := db.Prepare("select id, name, age from user where id>?") if err != nil { panic(err) }
 rows, err := stmt.Query(id) if err != nil { panic(err) }
 defer stmt.Close() defer rows.Close()
 for rows.Next(){ var user User err := rows.Scan(&user.Id, &user.Name, &user.Age) if err != nil { panic(err) } fmt.Printf("user: %#v\n", user) } }
func main() {
 dns := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sql.Open("mysql", dns) if err != nil { panic(err) }
 defer db.Close()
 PrepareQuery(db, 0)
}
</div>
 
 
Mysql创建表：
　　CREATE TABLE user (
　　id int(20) NOT NULL AUTO_INCREMENT,
　　name varchar(20) DEFAULT '',
　　age int(2) DEFAULT '0',
　　PRIMARY KEY (id))
　　ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT  CHARSET=utf8mb4;
 
数据库增删改查
insert
<div class="cnblogs_code">
package main
import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" )
func Insert(db *sql.DB) { name := "Vincent" age := 18
 result, err := db.Exec("insert into user(name, age) values (?,?)", name, age) if err != nil { panic(err) }
 id, err := result.LastInsertId() if err != nil { panic(err) }
 affected, err := result.RowsAffected() if err != nil { panic(err) }
 fmt.Printf("last insert id:%d affect rows:%d\n", id, affected) }
func main() { dns := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sql.Open("mysql", dns) if err != nil { panic(err) }
 err = db.Ping() if err != nil { panic(err) } fmt.Println("connect to db success!!!") Insert(db) }
 
<div id="cnblogs_code_open_46ed7098-2547-4d5f-ad3f-5ac0a66c8bdd" class="cnblogs_code_hide">
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
<pre>package main

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)

func Insert(db *sql.DB) {
name := "Vincent"
age := 18

result, err := db.Exec("insert into user(name, age) values (?,?)", name, age)
if err != nil {
 panic(err)
}

id, err := result.LastInsertId()
if err != nil {
 panic(err)
}

affected, err := result.RowsAffected()
if err != nil {
 panic(err)
}

fmt.Printf("last insert id:%d affect rows:%d\n", id, affected)
}

func main() {
dns := "root:123456@tcp(172.16.65.200:3306)/golang"
db, err := sql.Open("mysql", dns)
if err != nil {
 panic(err)
}

err = db.Ping()
if err != nil {
 panic(err)
}
fmt.Println("connect to db success!!!")
Insert(db)
}</pre>
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
</div>
</div>
delete
<div class="cnblogs_code">
package main
import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" )
func Delete(db *sql.DB, id int) { result, err := db.Exec("delete from user where id=?", id) if err != nil { panic(err) }
 rowsAffected, err := result.RowsAffected() if err != nil { panic(err) } fmt.Printf("delect id:%d, affect rows:%d\n", id, rowsAffected) }
func main() { dns := "root:123456@tcp(172.16.65.200:3306)/golang" db, _ := sql.Open("mysql", dns) Delete(db, 2) }
 
<div id="cnblogs_code_open_b1d87df4-94f6-441d-a601-bd212f80b19b" class="cnblogs_code_hide">
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
<pre>package main

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)

func Delete(db *sql.DB, id int) {
result, err := db.Exec("delete from user where id=?", id)
if err != nil {
 panic(err)
}

rowsAffected, err := result.RowsAffected()
if err != nil {
 panic(err)
}
fmt.Printf("delect id:%d, affect rows:%d\n", id, rowsAffected)
}

func main() {
dns := "root:123456@tcp(172.16.65.200:3306)/golang"
db, _ := sql.Open("mysql", dns)
Delete(db, 2)
}</pre>
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
</div>
</div>
update
<div class="cnblogs_code">
package main
import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" )
func Update(db *sql.DB) { name := "Miles" age := 88 id := 3
 result, err := db.Exec("update user set name=?, age=? where id=?", name, age, id) if err != nil { panic(err) }
 // RowsAffected returns the number of rows affected by an // update, insert, or delete. rowsAffected, err := result.RowsAffected() if err != nil { panic(err) }
 fmt.Printf("update id:%d, affect rows:%d\n", id, rowsAffected)
}
func main() { dns := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sql.Open("mysql", dns) if err != nil { panic(err) }
 err = db.Ping() if err != nil { panic(err) } fmt.Println("connect to db success!!!") Update(db) }
 
<div id="cnblogs_code_open_214abfe6-6b22-4963-ab28-704e96f487cb" class="cnblogs_code_hide">
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
<pre>package main

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)

func Update(db *sql.DB) {
name := "Miles"
age := 88
id := 3

result, err := db.Exec("update user set name=?, age=? where id=?", name, age, id)
if err != nil {
 panic(err)
}

// RowsAffected returns the number of rows affected by an
// update, insert, or delete.
rowsAffected, err := result.RowsAffected()
if err != nil {
 panic(err)
}

fmt.Printf("update id:%d, affect rows:%d\n", id, rowsAffected)

}

func main() {
dns := "root:123456@tcp(172.16.65.200:3306)/golang"
db, err := sql.Open("mysql", dns)
if err != nil {
 panic(err)
}

err = db.Ping()
if err != nil {
 panic(err)
}
fmt.Println("connect to db success!!!")
Update(db)
}</pre>
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
</div>
</div>
query
<div class="cnblogs_code">
package main
import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" "log" )
type User struct { Id int `db:"id"` Name string `db:"name"` Age int `db:"age"` }
 
// 单行查询，如果查询到多个结果，只返回第一行，查询不到结果就ErrNoRows错误。 func QueryRow(db *sql.DB) { id:= 2 row := db.QueryRow("select id, name, age from user where id=?", id)
 var user User err := row.Scan(&user.Id, &user.Name, &user.Age)
 if err == sql.ErrNoRows { log.Printf("not found data of the id:%d",id) }
 if err != nil { panic(err) }
 fmt.Printf("user: %#v\n", user) }
 // 多行查询, 查询不到任何记录也不会报错。 func Query(db *sql.DB) { id := 0 rows, err := db.Query("select id, name, age from user where id>?", id) if err != nil { panic(err) } if err == sql.ErrNoRows { log.Printf("not found data of id:%d\n", id) return } defer rows.Close()
 for rows.Next() { var user User err := rows.Scan(&user.Id, &user.Name, &user.Age) if err != nil { panic(err) } fmt.Printf("user: %#v\n", user) }
}
func main() { dns := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sql.Open("mysql", dns) if err != nil { panic(err) }
 err = db.Ping() if err != nil { panic(err) }
 fmt.Printf("connect to db success\n")
 //QueryRow(db)
 Query(db) }
 
<div id="cnblogs_code_open_70b268d6-3efd-4a4a-9c26-ba84d6a1c7b9" class="cnblogs_code_hide">
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
<pre>package main

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
"log"
)

type User struct {
Id int `db:"id"`
Name string `db:"name"`
Age int `db:"age"`
}

// 单行查询，如果查询到多个结果，只返回第一行，查询不到结果就ErrNoRows错误。
func QueryRow(db *sql.DB) {
id:= 2
row := db.QueryRow("select id, name, age from user where id=?", id)

var user User
err := row.Scan(&user.Id, &user.Name, &user.Age)

if err == sql.ErrNoRows {
 log.Printf("not found data of the id:%d",id)
}

if err != nil {
 panic(err)
}

fmt.Printf("user: %#v\n", user)
}

// 多行查询, 查询不到任何记录也不会报错。
func Query(db *sql.DB) {
id := 0
rows, err := db.Query("select id, name, age from user where id>?", id)
if err != nil {
 panic(err)
}
if err == sql.ErrNoRows {
 log.Printf("not found data of id:%d\n", id)
 return
}
defer rows.Close()

for rows.Next() {
 var user User
 err := rows.Scan(&user.Id, &user.Name, &user.Age)
 if err != nil {
 panic(err)
 }
 fmt.Printf("user: %#v\n", user)
}

}

func main() {
dns := "root:123456@tcp(172.16.65.200:3306)/golang"
db, err := sql.Open("mysql", dns)
if err != nil {
 panic(err)
}

err = db.Ping()
if err != nil {
 panic(err)
}

fmt.Printf("connect to db success\n")

//QueryRow(db)

Query(db)
}</pre>
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
</div>
</div>
 
事务支持 
事务(transaction)
<ul>
<li>transaction, err := Db.Begin() 开启事务</li>
<li>transaction.Exec() 执行事务</li>
<li>transaction.Commit() 提交事务</li>
<li>transaction.Rollback() 回滚事务</li>
</ul>
A. 事务的应用场景 　　1. 同时更新多个表 　　2. 同时更新多行数据 B. 事务的ACID 　　1. 原子性 　　2. 一致性 　　3. 隔离性 　　4. 持久性
 
需要注意的点: 1. 执行失败要回滚 2. 提交失败要回滚
<div class="cnblogs_code">
package main
import ( _ "github.com/go-sql-driver/mysql" "database/sql" "fmt" )
 func Transaction(db *sql.DB) {
 // 开启事务 tx, err := db.Begin()
 if err != nil { panic(err) }
 result, err := tx.Exec("insert into user(name, age)values(?,?)", "Jack", 98) if err != nil { // 失败回滚 tx.Rollback() panic(err) } fmt.Println("result", result)
 exec, err := tx.Exec("update user set name=?, age=? where id=?", "Jack", 98, 1) if err != nil { // 失败回滚 tx.Rollback() panic(err) } fmt.Println("exec", exec)
 // 提交事务 err = tx.Commit() if err != nil { // 失败回滚 tx.Rollback() panic(err) } }
func main() {
 dns := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sql.Open("mysql", dns) if err != nil { panic(err) }
 err = db.Ping() if err != nil { panic(err) }
 Transaction(db) }
 
<div id="cnblogs_code_open_2d67ac15-6ad2-4481-8320-c1b45e421f5c" class="cnblogs_code_hide">
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
<pre>package main

import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
)

func Transaction(db *sql.DB) {

// 开启事务
tx, err := db.Begin()

if err != nil {
 panic(err)
}

result, err := tx.Exec("insert into user(name, age)values(?,?)", "Jack", 98)
if err != nil {
 // 失败回滚
 tx.Rollback()
 panic(err)
}

fmt.Println("result", result)

exec, err := tx.Exec("update user set name=?, age=? where id=?", "Jack", 98, 1)
if err != nil {
 // 失败回滚
 tx.Rollback()
 panic(err)
}
fmt.Println("exec", exec)

// 提交事务
err = tx.Commit()

if err != nil {
 // 失败回滚
 tx.Rollback()
 panic(err)
}
}

func main() {

dns := "root:123456@tcp(172.16.65.200:3306)/golang"
db, err := sql.Open("mysql", dns)
if err != nil {
 panic(err)
}

err = db.Ping()
if err != nil {
 panic(err)
}

Transaction(db)
}</pre>
<div class="cnblogs_code_toolbar"><img src="https://common.cnblogs.com/images/copycode.gif"></div>
</div>
</div>
 
<h3>Mysql日期时间类型报错</h3>
<pre>sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *time.Time</pre>
原因是在调用sql.Open()时没有将parseTime设置为True。加入parseTime即可修复问题：
<div class="cnblogs_code">
<pre>db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname?parseTime=true")</pre>
</div> 
来源：https://www.cnblogs.com/show58/p/12373837.html

頁: [1]

琼殿技术论坛's Archiver

Go Mysql驱动