Go语言sqlx库操作SQLite3数据库增删改查

0. 引用

sqlx比database/sql要好用一点点，其实差不多的…
sqlx地址:https://github.com/jmoiron/sqlx

import (
	"database/sql"
	"fmt"
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

1. 连接数据库

• sql.Open
• sql.Close

var db *sql.DB

func InitDB() (err error) {
	dsn := "./data.db"
	db, err = sql.Open("sqlite3",dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	return
}

func Close()  {
	db.Close()
}

连接数据库一般是不会报错的，文件不存在则会创建

2. 创建数据表

SQLite一个文件就是一个数据库，在数据库下新建数据表就可以了

func testCreateTable() error {
	sqlc := `
	CREATE TABLE "user_info" (
	  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	  "uid" INTEGER(8) NOT NULL,
	  "name" text(255) NOT NULL,
	  "group" TEXT(255) NOT NULL,
	  "balance" integer(8) NOT NULL,
	  "proportion" real(8) NOT NULL,
	  "create_time" integer(4) NOT NULL,
	  "comments" TEXT(255)
	);
	
	CREATE INDEX "indexs"
	ON "user_info" (
	  "name",
	  "group"
	);
	
	CREATE UNIQUE INDEX "uniques"
	ON "user_info" (
	  "uid"
	);
    `
	_, err := db.Exec(sqlc)
	if err != nil {
		return err
	}
	return nil
}

3. 增删改

增删改和MySQL PostgreSQL的操作差不多
参考之前的文章：https://blog.youkuaiyun.com/xuehu96/article/details/124648300
代码几乎都不用改

经过测试，占位符用$1, $2, $3和 ?,?,?都是可以的

3.1 insert

func testInsert() error {
	sqli := `INSERT INTO "main"."user_info" 
    ("uid", "name", "group", "balance", "proportion", "create_time", "comments") 
	VALUES ($1, $2, $3, $4, $5, $6, $7);`
	_, err := db.Exec(sqli, 100, "xuehu96", "组", 2.33, 27.148, "2022-06-27 18:11:22", nil)
	if err != nil {
		return err
	}
	fmt.Printf("insert success\n")
	return nil
}

3.2 delete

删除id为2的行

func testDelete() error {
	sqld := `DELETE FROM "main"."user_info" WHERE "id" = $1`
	ret, err := db.Exec(sqld, 2)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return err
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return err
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
	return nil
}

3.3 update

把uid为3的name改为张三

func testUpdate() error {
	sqlu := `UPDATE "main"."user_info" SET "name" = $1 WHERE "uid" = $2`
	//sqlu := `UPDATE "main"."user_info" SET "name" = ? WHERE "uid" = ?` // 用?占位也行
	ret, err := db.Exec(sqlu, "张三", 3)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return err
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return err
	}
	fmt.Printf("update success, affected rows:%d\n", n)
	return nil
}

4. 查

查询前先写一个结构体，查到后直接绑定

type UserInfoType struct {
	Id         int            `db:"id"`
	Uid        int64          `db:"uid"`
	Name       string         `db:"name"`
	Group      string         `db:"group"`
	Balance    float64        `db:"balance"`
	Proportion float64        `db:"proportion"`
	CreateTime string         `db:"create_time"` // SQLite似乎不支持time.Time
	Comments   sql.NullString `db:"comments"`
}

4.1 查询单行数据

func testSelectOne() error {
	sqls := `SELECT * FROM "main"."user_info"  WHERE "uid" = $1`
	var user UserInfoType
	err := db.Get(&user, sqls, 3)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return err
	}
	fmt.Printf("one user: %#v\n", user)
	return nil
}

4.2 查询多行数据

func testSelectAll() error {
	sqls := `SELECT * FROM "main"."user_info" WHERE "uid" > $1`
	var users []UserInfoType
	err := db.Select(&users, sqls, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return err
	}
	fmt.Printf("all users: %#v\n", users)
	return nil
}

5. main函数

func main() {
	err := InitDB()
	if err != nil {
		panic(err)
	}
	defer CloseDB()

	err = testSelectAll()
	if err != nil {
		panic(err)
	}
}