golang 操作mysql的CRUD

￡神魔★判官ぃ 2022-09-26 15:49 178阅读 0赞

# golang 操作mysql的CRUD #

// test2 project main.go
    package main
    
    import (
    	"container/list"
    	"database/sql"
    	"fmt"
    	"log"
    	"strconv"
    
    	_ "github.com/go-sql-driver/mysql" //mysql驱动
    )
    
    type customer struct {
    	id    string
    	pname string
    	price float32
    	pnum  int
    }
    
    func main() {
    	ct := customer{"2", "dabao", 123, 23}
    	result := updateCustomer(ct)
    	//b := insert()
    	//query()
    	//deleteCustomer("0")
    	fmt.Println("mmmmm", result)
    }
    
    func getDB() (*sql.DB, error) {
    	db, err := sql.Open("mysql", "root:root@tcp(192.168.126.129:8066)/mycatTest?charset-utf-8")
    	if err != nil {
    		log.Println(err)
    		return nil, err
    	}
    	db.SetMaxOpenConns(30) //设置最大打开的连接数
    	db.SetMaxIdleConns(1)  // 设置最大闲置连接数
    	db.Ping()
    	return db, nil
    }
    
    func query() {
    	customers := list.New()
    	db, err := getDB()
    	rows, err := db.Query("select * from deal_product")
    	if err != nil {
    		log.Println("error-44 : ", err)
    	}
    	for rows.Next() {
    		var id string
    		var pname string
    		var price float32
    		var pnum int
    		if err := rows.Scan(&id, &pname, &price, &pnum); err != nil {
    			log.Println("error-52 : ", err)
    		}
    		c := customer{id, pname, price, pnum}
    		customers.PushBack(c)
    	}
    	defer rows.Close()
    	defer db.Close()
    	for cs := customers.Front(); cs != nil; cs = cs.Next() {
    		cu := cs.Value.(customer)
    		fmt.Println("custromer : ", cu.id, cu.pname, cu.price, cu.pnum)
    
    	}
    }
    
    func insert() (b bool) {
    	db, err := getDB()
    	db.Begin() // 开启事务
    	stm, err := db.Prepare("insert into deal_product(id, pname, price, pnum) values(?, ?, ?, ?)")
    	if err != nil {
    		log.Println(err)
    		return false
    	}
    	for i := 0; i < 10; i++ {
    		stm.Exec(strconv.Itoa(i), "name"+strconv.Itoa(i), i, i)
    	}
    	stm.Close()
    	defer db.Close()
    	return true
    }
    
    func deleteCustomer(id string) (b bool) {
    	db, err := getDB()
    	db.Begin()
    	stm, err := db.Prepare("delete from deal_product where id = ?")
    	if err != nil {
    		log.Println("error-86 : ", err)
    		return false
    	}
    	result, err1 := stm.Exec(id)
    	if err1 != nil {
    		log.Println("error-92", err1)
    		return false
    	}
    	fmt.Println("result: ", result)
    	defer stm.Close()
    	defer db.Close()
    	return true
    }
    
    func updateCustomer(c customer) (b bool) {
    	db, err := getDB()
    	db.Begin()
    	stm, err := db.Prepare("update deal_product set pname=?, price=? where id = ?")
    	if err != nil {
    		log.Println("error-107", err)
    		return false
    	}
    	stm.Exec(c.pname, c.price, c.id)
    	if err != nil {
    		log.Println("error-112", err)
    		return false
    	}
    	stm.Close()
    	db.Close()
    	return true
    }