Go操作mysql数据库简单示例

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

package main

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

//mysql

var db *sql.DB


type User struct {
	ID int `json:"id"`
	Name string `json:"name"`
	Age int	`json:"age"`
}


//初始化数据库
func initDD() (err error) {
	ctx,cancel := context.WithTimeout(context.Background(),5*time.Second)
	defer cancel()
	dsn := "user:password@tcp(127.0.0.1:3306)/db?charset=utf8mb4&parseTime=true"
	db ,err = sql.Open("mysql",dsn)
	if err !=nil{
		return err
	}
	err = db.PingContext(ctx)
	if err !=nil{
		return err
	}
	return nil
}

//查询

func QueryRow() (m map[int]string,err error){
	sqlStr := "select id,name,age from user"
	rows,err := db.Query(sqlStr)
	if err !=nil{
		fmt.Println("query sql failed err :",err)
		return nil, err
	}
	defer rows.Close()
	m1 :=make(map[int]string)
	for rows.Next(){
		var u User
		err := rows.Scan(&u.ID,&u.Name,&u.Age)
		if err !=nil{
			fmt.Println("scan rows failed err :",err)
			return nil,err
		}
		//fmt.Println(u.ID,u.Name,u.Age)
		m1[u.ID] = u.Name+strconv.Itoa(u.Age)
	}
	return m1,nil
}

func InsertRow() (int64,error) {
	sqlStr := "insert into user(name,age) values(?,?)"
	ret,err := db.Exec(sqlStr,"ceshi",100)
	if err !=nil{
		fmt.Println("exec sql failed err :",err)
		return 0,err
	}
	theID,err := ret.LastInsertId()
	if err !=nil{
		log.Fatal(err)
		return 0,err
	}
	return theID,nil

}


//更新操作
func UpdateDemo(id int) (int64,error) {
	sqlStr :="update user set name=? where id=?"
	ret,err := db.Exec(sqlStr,"测试",id)
	if err !=nil{
		log.Fatal(err)
		return 0, err
	}
	theid,err := ret.RowsAffected()
	if err !=nil{
		log.Fatal(err)
		return 0,err
	}
	return theid,nil
}

func Delete(id int)(int64,error){
	sqlStr := "delete from user where id=?"
	ret,err := db.Exec(sqlStr,id)
	if err !=nil{
		log.Fatal(err)
		return 0, err
	}
	theid ,err := ret.RowsAffected()
	if err !=nil{
		log.Fatal(err)
		return 0, nil
	}
	return theid,nil
}


func main() {
	err := initDD()
	if err !=nil{
		fmt.Println("connect mysql failed err :",err)
		return
	}
	fmt.Println("connect mysql success...")

//	**************查询
	m1,err := QueryRow()
	if err !=nil{
		fmt.Println("find err failed err:",err)
		return
	}
	for k,v := range m1{
		fmt.Printf("用户ID:%d 信息:%s\n",k,v)
	}

//	***************插入
	id,err  := InsertRow()
	if err !=nil{
		fmt.Println("insert into table failed err:",err)
		return
	}
	fmt.Println("insert into table success ID:",id)


// ***************更新
	id := 213
	row,err := UpdateDemo(id)
	if err !=nil{
		fmt.Println("update failed err :",err)
		return
	}
	fmt.Println("update table sunccess ..affectRows:",row)


// ************ 删除
	row,err := Delete(213)
	if err !=nil{
		fmt.Println("delete table failed err:",err)
		return
	}
	fmt.Println("delete from table success rows:",row)
}

package main

import (
	"context"
	"database/sql"
	"github.com/go-redis/redis/v8"
	_"github.com/go-sql-driver/mysql"
	"fmt"
	"sync"
	"time"
)

//初始化数据库
var (
	db *sql.DB
	rdb *redis.Client
	wg sync.WaitGroup
)

func initDB() (err error) {
	dsn :="root:123456@tcp(127.0.0.1:3306)/new_zx?charset=utf8mb4&parseTime=true"
	db ,err = sql.Open("mysql",dsn)
	if err !=nil{
		return err
	}
	ctx,cancel := context.WithTimeout(context.Background(),5*time.Second)
	defer cancel()
	err = db.PingContext(ctx)
	if err !=nil{
		return err
	}
	return nil
}


func initRedis() (err error){
	rdb = redis.NewClient(&redis.Options{
		Addr: "10.41.11.131:6379",
		Password: "",
		DB:0,
	})
	ctx,cancel := context.WithTimeout(context.Background(),5*time.Second)
	defer cancel()
	_,err = rdb.Ping(ctx).Result()
	return err
}

// ******************redis string 类型

func PutDataToRedis(ctx context.Context)  {
	defer wg.Done()
	err := rdb.Set(ctx,"test_1","ndixulsxuexlajcjwyexioxuencxe",0).Err()
	if err!= nil{
		fmt.Println("set data failed err :",err)
		panic(err)
	}
}

func getDataByRedis(ctx context.Context)  {
	defer wg.Done()
	val,err := rdb.Get(ctx,"test_1").Result()
	if err !=nil{
		fmt.Println("get data failed err:",err)
		return
	}
	fmt.Println(val)
}


//*************put list in redis

func PutDataToRedisByList(ctx context.Context)  {
	defer wg.Done()
	s1 := []string{"a","b","c","d"}
	for _,v := range s1{
		rdb.LPush(ctx,"myNewList",v)
	}

}

func GetDataByRedisByList(ctx context.Context)  {
	defer wg.Done()
	val,err := rdb.LRange(ctx,"myNewList",0,-1).Result()
	if err !=nil{
		fmt.Println("get list data failed err:",err)
	}

	fmt.Printf("%T\n",val)
	for k,v := range val{
		fmt.Printf("key:%d value:%s\n",k,v)
	}
}

// ******************put hash in redis

func PutDataToRedisByHash(ctx context.Context)  {
	defer wg.Done()
	m1 := map[string]interface{}{"name":"tome","age":18,"gender":"女"}
	err := rdb.HSet(ctx,"myHash",m1).Err()
	if err !=nil{
		fmt.Println("hash redis failed:",err)
	}

}

func GetDataByRedisByHash(ctx context.Context)  {
	defer wg.Done()
	//names ,err := rdb.HGet(ctx,"myHash","age").Result()
	m1,err := rdb.HGetAll(ctx,"myHash").Result()
	if err !=nil{
		fmt.Println("get hash data failed err :",err)
		return
	}
	fmt.Println(m1)
}