09-golang连接mysql数据库-02-预处理

文章目录

• 1. 查询
• 2. 插入

说明：Prepare() 方法会先将sql语句发送给MySQL服务器，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

1. 查询

• 定义查询函数

type userTab struct {
	Id int `json:"id"`
	Name string `json:"name"`
	Age int `json:"age"`
}
 
var UsersInfo []userTab
 
func PrepareQueryDemo() {
	UsersInfo = UsersInfo[0:0]
	sqlStr := "select id, name, age from user where id > ?"
	stmt, err := DBName.Prepare(sqlStr)
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer func(stmt *sql.Stmt) {
		err := stmt.Close()
		if err != nil {
		}
	}(stmt)
	rows, err := stmt.Query(0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	// 循环读取结果集中的数据
	for rows.Next() {
		var u userTab
		err := rows.Scan(&u.Id, &u.Name, &u.Age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		//fmt.Printf("id:%d name:%s age:%d\n", u.Id, u.Name, u.Age)
		UsersInfo = append(UsersInfo,u)
	}
}

• 查看结果
  

2. 插入

func PrepareInsertDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	stmt, err := DBName.Prepare(sqlStr)
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	_, err = stmt.Exec("ZhaoYun", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	_, err = stmt.Exec("ZhuGeLiang", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Println("insert success.")
}