go如何使用SQLX操作MySQL数据库？

前言

sqlx 是 Go 语言中一个流行的操作数据库的第三方包，它提供了对 Go 标准库 database/sql 的扩展，简化了操作数据库的步骤，并且拥有很大的数据库交互方法，本文使用mysql

sqlx使用

（1）下载sqlx包

使用以下命令下载sqlx

arduino

代码解读

复制代码

go get github.com/jmoiron/sqlx

安装数据库驱动依赖

go

代码解读

复制代码

go get github.com/go-sql-driver/mysql

（2）引入驱动

arduino

代码解读

复制代码

_ "github.com/go-sql-driver/mysql"

（3）连接数据库说明

其连接数据库使用

ini

代码解读

复制代码

user:password@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True

其中user为数据库用户，password为数据库密码

（4）连接数据库代操作

go

代码解读

复制代码

package main import ( "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) var db *sqlx.DB func main() { mysqlDb, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True") if err != nil { fmt.Println("连接数据库异常:", err) return } db = mysqlDb }

（5）插入数据

创建数据库表位student

sql

代码解读

复制代码

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `sex` int(11) NULL DEFAULT NULL, `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1690290594064429066 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;

插入数据代码为:

go

代码解读

复制代码

package main import ( "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) var db *sqlx.DB func main() { mysqlDb, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True") if err != nil { fmt.Println("连接数据库异常:", err) return } db = mysqlDb r, err1 := db.Exec("insert into user(mobile, password, sex, user_name)values(?, ?, ?, ?)", "aaa", "123456", 1, "aaa") if err1 != nil { fmt.Println("数据插入异常, ", err1) return } id, err2 := r.LastInsertId() if err2 != nil { fmt.Println("获取id异常:, ", err2) return } fmt.Println("id为:", id) }

(6)查询数据

go

代码解读

复制代码

package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) type User struct { Id int64 `db:"id"` Name string `db:"user_name"` Sex int `db:"sex"` Mobile string `db:"mobile"` Password sql.NullString `db:"password"` } var db *sqlx.DB func main() { mysqlDb, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True") if err != nil { fmt.Println("连接数据库异常:", err) return } db = mysqlDb var user []User err1 := db.Select(&user, "select id, user_name, sex, mobile, password from user where id=?", 1690290594064429065) if err1 != nil { fmt.Println("查询异常, ", err1) return } fmt.Println("查询数据为:", user) }

之所以password使用类型sql.NullString，是因为如果列为null的话，会报错，所以其会返回

遍历false去判断

（7）更新数据

go

代码解读

复制代码

package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) type User struct { Id int64 `db:"id"` Name string `db:"user_name"` Sex int `db:"sex"` Mobile string `db:"mobile"` Password sql.NullString `db:"password"` } var db *sqlx.DB func main() { mysqlDb, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True") if err != nil { fmt.Println("连接数据库异常:", err) return } db = mysqlDb res, err := db.Exec("update user set user_name=? where id=?", "hello11", 1690290594064429065) if err != nil { fmt.Println("更新失败:", err) return } row, err1 := res.RowsAffected() if err1 != nil { fmt.Println("获取失败, ", err1) return } fmt.Println("更新成功行数:", row) }

(8)删除数据

go

代码解读

复制代码

package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) type User struct { Id int64 `db:"id"` Name string `db:"user_name"` Sex int `db:"sex"` Mobile string `db:"mobile"` Password sql.NullString `db:"password"` } var db *sqlx.DB func main() { mysqlDb, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True") if err != nil { fmt.Println("连接数据库异常:", err) return } db = mysqlDb res, err := db.Exec("delete from user where id=?", 1690290594064429065) if err != nil { fmt.Println("删除失败, ", err) return } row, err := res.RowsAffected() if err != nil { fmt.Println("删除行数为:", err) return } fmt.Println("删除行数为: ", row) }

总结

go有很多优秀的操作数据库组件，都能提高开发效率，至于在开发中怎么选择组件，看个人开发效率和习惯