sqlx简介
sqlx
是 Go 的软件包,它在出色的内置database/sql
软件包的基础上提供了一组扩展。该库兼容sql
原生包,同时又提供了更为强大的、优雅的查询、插入函数。
新建表
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
连接数据库
-
下载依赖包
go get "github.com/go-sql-driver/mysql" go get "github.com/jmoiron/sqlx"
-
连接数据库
import ( "fmt" "log" _ "github.com/go-sql-driver/mysql" //必须引入 否则报错:error sql: unknown driver "mysql" (forgotten import?) "github.com/jmoiron/sqlx" ) var db *sqlx.DB func init() { // 根据自己的数据库配置替代字符串中对应字串 var err error db, err = sqlx.Connect("mysql", "username:password@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True") if err != nil { log.Fatalf("open mysql error %v", err) } fmt.Printf("连接成功:%v", db) db.SetMaxOpenConns(20) //设置最大连接数 db.SetMaxIdleConns(10) //设置最大空闲连接数 }
插入数据
- 插入数据使用db.Exec方法
r, err := db.Exec("insert into people(name, age)values(?, ?)", "bxy", 100)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
id, err := r.LastInsertId()
if err != nil {
fmt.Println("exec err, ", err)
return
}
fmt.Println("insert succ: ", id)
执行结果:
查询数据
- 查询数据使用db.select方法和Get方法,第一个参数是接收结果的对象,第二个参数是sql语句,第三个参数及之后的参数是填充占位符的对象。
- Select返回所有满足条件的结果,需要使用对象列表接收。
- Get返回满足条件的第一条结果,需要使用对象接收。
type People struct {
Id int `db:"id"`
Name string `db:"name"`
Age int `db:"age"`
}
func main() {
var (
peopleList []People
people = People{}
)
err := db.Select(&peopleList, "select * from people where name = ?", "bxy")//查询全部记录
if err != nil {
fmt.Println("select failed ", err)
return
}
fmt.Printf("select succ %#v\n", peopleList)
err = db.Get(&people, "select * from people where name = ?", "bxy") //查询单条记录
if err != nil {
fmt.Println("Get failed ", err)
return
}
fmt.Printf("get succ %#v\n", people)
}
执行结果:
修改数据
func main() {
res, err := db.Exec("update people set age=? where name=?", 28, "bxy")
if err != nil {
fmt.Println("exec failed: ", err)
return
}
row, err := res.RowsAffected() //返回修改成功的条数
if err != nil {
fmt.Println("row failed: ", err)
return
}
fmt.Println("update succ: ", row)
}
执行结果:
删除数据
func main() {
res, err := db.Exec("delete from people where id=?", 1)
if err != nil {
fmt.Println("exec failed: ", err)
return
}
row, err := res.RowsAffected() //返回结果
if err != nil {
fmt.Println("row failed: ", err)
return
}
fmt.Println("delete succ: ", row)
}
执行结果:
事务
- db.Begin(): 开启事务
- tx.rollback(): 回滚事务
- tx.commit(): 提交事务
func main() {
conn, err := db.Begin()
if err != nil {
fmt.Println("begin failed: ", err)
return
}
r, err := conn.Exec("insert into people(name, age)values(?,?)", "如花", 20)
if err != nil {
fmt.Println("exec failed: ", err)
conn.Rollback()
return
}
id, err := r.LastInsertId()
if err != nil {
fmt.Println("lastinsertid err: ", err)
conn.Rollback()
return
}
fmt.Println("insert succ", id)
r, err = conn.Exec("insert into people(name, age)values(?,?)", "如花", 20)
if err != nil {
fmt.Println("exec failed: ", err)
conn.Rollback()
return
}
id, err = r.LastInsertId()
if err != nil {
fmt.Println("lastinsertid err: ", err)
conn.Rollback()
return
}
fmt.Println("insert succ", id)
conn.Commit()
}
执行结果: