go使用SQLX操作MySQL數(shù)據(jù)庫的教程詳解

更新時間：2023年11月18日 10:37:16 作者：考慮考慮

sqlx 是 Go 語言中一個流行的操作數(shù)據(jù)庫的第三方包,它提供了對 Go 標(biāo)準(zhǔn)庫 database/sql 的擴(kuò)展,簡化了操作數(shù)據(jù)庫的步驟,下面我們就來學(xué)習(xí)一下go如何使用SQLX實(shí)現(xiàn)MySQL數(shù)據(jù)庫的一些基本操作吧

前言

sqlx 是 Go 語言中一個流行的操作數(shù)據(jù)庫的第三方包，它提供了對 Go 標(biāo)準(zhǔn)庫 database/sql 的擴(kuò)展，簡化了操作數(shù)據(jù)庫的步驟，并且擁有很大的數(shù)據(jù)庫交互方法，本文使用mysql

sqlx使用

1.下載sqlx包

使用以下命令下載sqlx

 go get github.com/jmoiron/sqlx

2.引入驅(qū)動

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

3.連接數(shù)據(jù)庫說明

其連接數(shù)據(jù)庫使用

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

其中user為數(shù)據(jù)庫用戶，password為數(shù)據(jù)庫密碼

4.連接數(shù)據(jù)庫代操作

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("連接數(shù)據(jù)庫異常:", err)
		return
	}
	db = mysqlDb
}

5.插入數(shù)據(jù)

創(chuàng)建數(shù)據(jù)庫表位student

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;

插入數(shù)據(jù)代碼為:

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("連接數(shù)據(jù)庫異常:", 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("數(shù)據(jù)插入異常, ", err1)
		return
	}
	id, err2 := r.LastInsertId()
	if err2 != nil {
		fmt.Println("獲取id異常:, ", err2)
		return
	}

	fmt.Println("id為:", id)
}

6.查詢數(shù)據(jù)

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("連接數(shù)據(jù)庫異常:", 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("查詢數(shù)據(jù)為:", user)
}

之所以password使用類型sql.NullString，是因?yàn)槿绻袨閚ull的話，會報錯，所以其會返回

遍歷false去判斷

7.更新數(shù)據(jù)

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("連接數(shù)據(jù)庫異常:", 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("更新成功行數(shù):", row)
}

8.刪除數(shù)據(jù)

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("連接數(shù)據(jù)庫異常:", 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("刪除行數(shù)為:", err)
		return
	}

	fmt.Println("刪除行數(shù)為: ", row)
}