Go从0开发(11)_数据库

数据库驱动下载

go get -u github.com/go-sql-driver/mysql

数据库链接

package main

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

//驱动安装
//go get -u github.com/go-sql-driver/mysql

//原生支持
//database/sql

func f1() {
dsn := "root:root@tcp(127.0.0.1:3306)/day10"

db,err := sql.Open("mysql",dsn) //只校验数据格式是否正确
if err != nil{
fmt.Println("数据库链接数据格式错误",err)
}
defer db.Close()

err = db.Ping() //校验数据库是否连接成功
if err != nil{
fmt.Println("数据库链接失败",err)
}

//设置数据库最大链接数
db.SetMaxOpenConns(10)
fmt.Println("数据库链接成功")
}

func main() {
f1()
}

数据库原生操作

package main

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

type user struct {
id int
name string
age int
}

var db *sql.DB

func initDb() (err error) {
dsn := "root:root@tcp(127.0.0.1:3306)/day10"

db,err = sql.Open("mysql",dsn) //只校验数据格式是否正确
if err != nil{
return
}

err = db.Ping() //校验数据库是否连接成功
if err != nil{
return
}
return
}

//单行查询
func f1() {
var u1 user
//sql
sql := `select * from user where id=1`
//执行
dataObj := db.QueryRow(sql) //查询单条数据
//fmt.Printf("%#v \n",dataObj)
//拿到结果
dataObj.Scan(&u1.id,&u1.name,&u1.age)
if u1.id == 0{
fmt.Println("无数据")
return
}
fmt.Printf("id: %d,name: %v, age: %d",u1.id,u1.name,u1.age)
}

//优化f1
func f2(id int) {
var u1 user
//sql
sql := `select * from user where id=?`
//执行
db.QueryRow(sql,id).Scan(&u1.id,&u1.name,&u1.age) //查询单条数据
if u1.id == 0{
fmt.Println("无数据")
return
}
fmt.Printf("%#v \n",u1)
}

//查多条数据
func f3(id int) {
sql := `select * from user where id > ?`
rows,err := db.Query(sql,id)
if err != nil{
fmt.Println("数据查询出错哦",err)
}
for rows.Next() {
var u1 user
err = rows.Scan(&u1.id,&u1.name,&u1.age)
if err != nil{
fmt.Println("数据出错哦",err)
}
fmt.Printf("%#v \n",u1)
}
}

//插入数据
func f4() {
sql := `INSERT INTO user(name, age) VALUES ('红色', 16)`
ret,err := db.Exec(sql)
if err != nil{
fmt.Println("数据出错哦",err)
}
//fmt.Println(ret.RowsAffected())
id,err :=ret.LastInsertId()
if err != nil{
fmt.Println("数据出错哦",err)
}
fmt.Println("id: ",id)
}

//更新数据
func f5(name string,id int) {
sql := `UPDATE user SET name = ? WHERE id = ? `
ret,err := db.Exec(sql,name,id)
if err != nil{
fmt.Println("数据出错哦",err)
}

n,err :=ret.RowsAffected()
if err != nil{
fmt.Println("数据出错哦",err)
}
fmt.Println("受影响的行数为: ",n)

}

//删除数据
func f6(id int) {
sql := `DELETE FROM user WHERE id = ?`
ret,err := db.Exec(sql,id)
if err != nil{
fmt.Println("数据操作出错哦",err)
}

n,err :=ret.RowsAffected()
if err != nil{
fmt.Println("数据出错哦",err)
}
fmt.Println("受影响的行数为: ",n)
}

//预处理
func f7() {
sql := `INSERT INTO user(name, age) VALUES (?, ?)`
stmt,err:=db.Prepare(sql) //先预处理一下
if err != nil{
fmt.Println("数据操作出错哦",err)
}
defer stmt.Close()

var m = map[string]int{
"绿色":56,
"黄色":56,
}
for k,v :=range m{
stmt.Exec(k,v)
}

}

//事务
func f8() {

//开始事务
tx,err := db.Begin()
if err != nil{
fmt.Println("事务开启出错",err)
}


sql := `INSERT INTO user(name, age,id) VALUES ('橙色', 22,1)`
ret,err := db.Exec(sql)
if err != nil{
tx.Rollback() //出错回滚
fmt.Println("数据出错哦",err)
}
//fmt.Println(ret.RowsAffected())
id,err :=ret.LastInsertId()
if err != nil{
fmt.Println("数据出错哦",err)
}
err = tx.Commit()
if err != nil{
tx.Rollback() //出错回滚
fmt.Println("数据出错哦",err)
}
fmt.Println("id: ",id)
}


func main() {
err := initDb()
if err != nil{
fmt.Println("数据库链接失败",err)
}
//f1()
//f2(1)
//f3(0)
//f4()
//f5("张三",2)
//f6(2)
//f7()
f8()
}

sqlx

package main

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

type user struct {
Id int
Name string
Age int
}

var db *sqlx.DB

func initDB() (err error) {
dsn := "root:root@tcp(127.0.0.1:3306)/day10?charset=utf8mb4&parseTime=True"
// 也可以使用MustConnect连接不成功就panic
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(10)
return
}

//查询单条数据
func f1() {
sql := `select * from user where id=1`
var u2 user
db.Get(&u2, sql)
fmt.Printf("%#v \n",u2)
}

//查询多条数据
func f2() {
sql := `select * from user `
var u2 []user
db.Select(&u2, sql)
fmt.Printf("%#v \n",u2)
}

//SQL注入
func f3(name string) {
sql := fmt.Sprintf(`select * from user where name = "%s"`,name)
fmt.Println(sql)
var u2 []user
db.Select(&u2, sql)
for _,v :=range u2{
fmt.Printf("%#v \n",v)
}
}
func f31() {
//f3("黄色")
//f3("xxx\" or 1=1 #")
f3("xxx\" union select * from user #")
}

func main() {
err := initDB()
if err != nil {
fmt.Println("数据库链接失败", err)
}
f31()
}





伍先生
  • 职业: 程序员,产品
  • 码龄: 4.1
  • 技能: PHP Go 前端
  • 微信: JwCode
  • 公众号/小程序: 渐悟分享