// 初始化数据库funcinitMySQL()(errerror){dsn:="root:password@tcp(127.0.0.1:3306)/database"db,err=sqlx.Open("mysql",dsn)iferr!=nil{fmt.Printf("connect server failed, err:%v\n",err)return}db.SetMaxOpenConns(200)db.SetMaxIdleConns(10)return}
dest 是用户声明变量接收查询结果,query 为查询 SQL 语句,args 为绑定参数的赋值。
1
2
3
4
5
6
7
8
9
10
11
// 查询一行数据funcqueryRow(){sqlStr:="SELECT id, name, age FROM user WHERE id = ?"varuuseriferr:=db.Get(&u,sqlStr,1);err!=nil{fmt.Printf("get data failed, err:%v\n",err)return}fmt.Printf("id:%d, name:%s, age:%d\n",u.Id,u.Name,u.Age)}
// 查询多行数据funcqueryMultiRow(){sqlStr:="SELECT id, name, age FROM user WHERE id > ?"varusers[]useriferr:=db.Select(&users,sqlStr,0);err!=nil{fmt.Printf("get data failed, err:%v\n",err)return}fori:=0;i<len(users);i++{fmt.Printf("id:%d, name:%s, age:%d\n",users[i].Id,users[i].Name,users[i].Age)}}
// 插入数据funcinsertRow(){sqlStr:="INSERT INTO user(name, age) VALUES(?, ?)"result,err:=db.Exec(sqlStr,"Meng小羽",22)iferr!=nil{fmt.Printf("exec failed, err:%v\n",err)return}insertID,err:=result.LastInsertId()iferr!=nil{fmt.Printf("get insert id failed, err:%v\n",err)return}fmt.Printf("insert data success, id:%d\n",insertID)}
更新操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 更新数据funcupdateRow(){sqlStr:="UPDATE user SET age = ? WHERE id = ?"result,err:=db.Exec(sqlStr,22,6)iferr!=nil{fmt.Printf("exec failed, err:%v\n",err)return}affectedRows,err:=result.RowsAffected()iferr!=nil{fmt.Printf("get affected failed, err:%v\n",err)return}fmt.Printf("update data success, affected rows:%d\n",affectedRows)}
删除操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 删除一行funcdeleteRow(){sqlStr:="DELETE FROM user WHERE id = ?"result,err:=db.Exec(sqlStr,4)iferr!=nil{fmt.Printf("exec failed, err:%v\n",err)return}affectedRows,err:=result.RowsAffected()iferr!=nil{fmt.Printf("get affected failed, err:%v\n",err)return}fmt.Printf("delete data success, affected rows:%d\n",affectedRows)}
// 绑定查询funcselectNamedQuery(){sqlStr:="SELECT id, name, age FROM user WHERE age = :age"rows,err:=db.NamedQuery(sqlStr,map[string]interface{}{"age":22,})iferr!=nil{fmt.Printf("named query failed failed, err:%v\n",err)return}deferrows.Close()forrows.Next(){varuuseriferr:=rows.StructScan(&u);err!=nil{fmt.Printf("struct sacn failed, err:%v\n",err)continue}fmt.Printf("%#v\n",u)}}
NamedExec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// 使用 named 方法插入数据funcinsertNamedExec(){sqlStr:="INSERT INTO user(name, age) VALUES(:name, :age)"result,err:=db.NamedExec(sqlStr,map[string]interface{}{"name":"里斯","age":18,})iferr!=nil{fmt.Printf("named exec failed, err:%v\n",err)return}insertId,err:=result.LastInsertId()iferr!=nil{fmt.Printf("get last insert id failed, err:%v\n",err)return}fmt.Printf("insert data success, id:%d\n",insertId)}
// 事务操作funcupdateTransaction()(errerror){tx,err:=db.Begin()iferr!=nil{fmt.Printf("transaction begin failed, err:%v\n",err)returnerr}deferfunc(){ifp:=recover();p!=nil{_=tx.Rollback()panic(p)}elseiferr!=nil{fmt.Printf("transaction rollback")_=tx.Rollback()}else{err=tx.Commit()fmt.Printf("transaction commit")return}}()sqlStr1:="UPDATE user SET age = ? WHERE id = ? "reuslt1,err:=tx.Exec(sqlStr1,18,1)iferr!=nil{fmt.Printf("sql exec failed, err:%v\n",err)returnerr}rows1,err:=reuslt1.RowsAffected()iferr!=nil{fmt.Printf("affected rows is 0")return}sqlStr2:="UPDATE user SET age = ? WHERE id = ? "reuslt2,err:=tx.Exec(sqlStr2,19,5)iferr!=nil{fmt.Printf("sql exec failed, err:%v\n",err)returnerr}rows2,err:=reuslt2.RowsAffected()iferr!=nil{fmt.Printf("affected rows is 0\n")return}ifrows1>0&&rows2>0{fmt.Printf("update data success\n")}return}