Go SQL CRUD

Go CRUD

Query

query one row

// 查询单条数据示例 QueryRow
func queryOne(id int) {
	sqlStr := "select id, name, age from stu where id=?"
	var u Student
	// 非常重要: 确保QueryRow之后调用Scan方法, 否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, id).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)
}

query multi rows

// 查询多条数据示例 Query
func queryMulti(id int) {
	sqlStr := "select id, name, age from stu where id > ?"
	rows, err := db.Query(sqlStr, id)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 非常重要: 关闭rows释放持有的数据库链接
	defer rows.Close()

	// 循环读取结果集中的数据
	for rows.Next() {
		var u Student
		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)
	}
}

Insert

// 插入一条数据 Exec
func insert(s Student) {
	sql := "insert into stu(name, age) values (?,?)"
	ret, err := db.Exec(sql, s.name, s.age)

	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}

	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

Update

// 修改数据, Exec
func update(s Student) {
	sqlStr := "update stu set name = ?, age = ? where id = ?"
	ret, err := db.Exec(sqlStr, s.name, s.age, s.ID)

	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}

	n, err := ret.RowsAffected() // 操作影响的行数

	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

Delete

// 删除数据, Exec
func delete(id int) {
	sqlStr := "delete from stu where id = ?"
	ret, err := db.Exec(sqlStr, id)

	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}

	n, err := ret.RowsAffected() // 操作影响的行数

	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}