数据库驱动下载

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()
}