sqlca

简介

sqlca 是一个基于Go语言的ORM框架，它提供了一种简单的方式来生成数据库表模型，并支持多种数据库类型，如MySQL、PostgreSQL、Opengauss、MS-SQLServer、Sqlite v3等。 内置雪花算法生成主键ID、SSH隧道连接以及防SQL注入功能。支持各种数据库聚合方法和联表查询，例如: Sum/Max/Avg/Min/Count/GroupBy/Having/OrderBy/Limit等等。 同时将常用的操作符进行了包装，例如等于Eq、大于Gt、小于Lt等等，简化操作代码。其中And和Or方法既支持常规SQL格式化(占位符?方式），同时也支持map类型传参作为WHERE/AND/OR条件进行查询和以及更新。

sqlca与gorm差异

• sqlca不支持通过数据模型自动生成创建/更新时间（可由MySQL等数据库创建表时设置为由数据库自动维护生成/更新时间），当数据库自动维护创建/更新时间时，可通过 sqlca:"readonly" 标签将数据字段设置为只读

• sqlca由Model方法调用后，会自动克隆一个对象，后续所有的操作均不影响宿主对象。每当一个完整语句执行完毕（例如调用Query/Update/Delete方法后)，db对象会清理掉所有的查询/更新条件。

• 对于model结构嵌套的差异

type CommonFields struct {
    Id         int64  `db:"id"`          //数据库主键ID
    UpdateTime string `db:"update_time"` //更新时间
    CreateTime string `db:"create_time"` //创建时间
}

type User struct {
    CommonFields CommonFields //没有db标签
    Name string `db:"name"` //姓名
    Gender int32 `db:"gender"` //性别
}

对于上面的User结构，对CommonFields的处理sqlca插入和查询跟gorm保持一致，都是把id/update_time/create_time字段作为跟name，gender平级的字段处理。

type ExtraData struct {
    Address     string `json:"address"` //家庭住址
    Email       string `json:"email"`   //电子邮箱地址
}

type User struct {
    Id          int64       `db:"id"`          //数据库主键ID
    UpdateTime  string      `db:"update_time"` //更新时间
    CreateTime  string      `db:"create_time"` //创建时间
    Name        string      `db:"name"`        //姓名
    Gender      int32       `db:"gender"`      //性别
    ExtraData   ExtraData   `db:"extra_data"`  //额外数据
}

对于上面的User结构，ExtraData成员变量因为有db标签，sqlca把ExtraData作为user表的一个字段进行处理，插入时把ExtraData序列化为JSON文本存入extra_data字段。查询时反序列化到ExtraData结构中。 而gorm把ExtraData作为外键处理从而可能会导致查询失败。

• 数据ID条件判断

sqlca通过数据模型结构中是否包含db:"id"标签的字段自动在查询/更新条件中将id=xxx加入Where条件

var user =&User{
	Id: 10003,
}
//SELECT * FROM user WHERE id=10003
_, err := db.Model(&user).Query() //等同于db.Model(&user).Id(10003).Query()
if err != nil {
    log.Errorf(err.Error())
    return 
}

sqlca标签说明

• sqlca:"readonly" 只读标签，指定该标签的字段插入和更新操作均不参与
• sqlca:"isnull" 允许为空标签，指定该标签的字段允许为空(数据库字段允许为NULL)

db2go工具

db2go 是一个支持从MySQL、PostgreSQL、Opengauss数据库导出表结构到.go文件或.proto文件的命令行工具。支持将表字段指定为自定义类型并生成model文件和dao文件。

快速开始

支持数据库类型

• MySQL

"root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4"
"mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4"

• PostgreSQL

"postgres://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public"

• Opengauss

"opengauss://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public"

• MS-SQLServer

"mssql://sa:123456@127.0.0.1:1433/mydb?instance=SQLExpress&windows=false"

• Sqlite v3

"sqlite:///var/lib/test.db"

数据库表模型生成

• 创建数据库

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `test`;


CREATE TABLE `inventory_data` (
                                  `id` bigint unsigned NOT NULL COMMENT '产品ID',
                                  `create_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建人ID',
                                  `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
                                  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                  `update_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新人ID',
                                  `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人姓名',
                                  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                  `is_frozen` tinyint(1) NOT NULL DEFAULT '0' COMMENT '冻结状态(0: 未冻结 1: 已冻结)',
                                  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '产品名称',
                                  `serial_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '产品编号',
                                  `quantity` decimal(16,3) NOT NULL DEFAULT '0.000' COMMENT '产品库存',
                                  `price` decimal(16,2) NOT NULL DEFAULT '0.00' COMMENT '产品均价',
                                  `product_extra` text COLLATE utf8mb4_unicode_ci COMMENT '产品附带数据(JSON文本)',
                                  `location` point DEFAULT NULL COMMENT '地理位置',
                                  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品库存数据表';

CREATE TABLE `inventory_in` (
                                `id` bigint unsigned NOT NULL COMMENT '主键ID',
                                `create_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建人ID',
                                `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
                                `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                `update_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新人ID',
                                `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人姓名',
                                `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除状态(0: 未删除 1: 已删除)',
                                `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
                                `product_id` bigint unsigned NOT NULL COMMENT '产品ID',
                                `order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '入库单号',
                                `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '交货人ID',
                                `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '交货人姓名',
                                `quantity` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '数量',
                                `weight` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '净重',
                                `remark` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
                                PRIMARY KEY (`id`) USING BTREE,
                                UNIQUE KEY `UNIQ_ORDER_NO` (`order_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='入库主表';

CREATE TABLE `inventory_out` (
                                 `id` bigint unsigned NOT NULL COMMENT '主键ID',
                                 `create_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建人ID',
                                 `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
                                 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                 `update_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新人ID',
                                 `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人姓名',
                                 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                 `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除状态(0: 未删除 1: 已删除)',
                                 `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
                                 `product_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '产品ID',
                                 `order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '出库单号',
                                 `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '收货人ID',
                                 `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '收货人姓名',
                                 `quantity` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '数量',
                                 `weight` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '净重',
                                 `remark` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
                                 PRIMARY KEY (`id`) USING BTREE,
                                 UNIQUE KEY `UNIQ_ORDER_NO` (`order_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='出库主表';

;

• 安装db2go工具

$ go install github.com/civet148/db2go@latest

• 自动生成go代码脚本

@echo off

rem 设置只读字段
set READ_ONLY="create_time, update_time"
rem 数据模型(models)和数据库操作对象(dao)文件输出基础目录
set OUT_DIR=.
rem 数据模型包名(数据模型文件目录名)
set PACK_NAME="models"
rem 指定某表的某字段为指定类型,多个表字段以英文逗号分隔（例如：user.create_time=time.Time表示指定user表create_time字段为time.Time类型; 如果不指定表名则所有表的create_time字段均为time.Time类型；支持第三方包类型，例如：user.weight=github.com/shopspring/decimal.Decimal）
set SPEC_TYPES="inventory_data.product_extra=ProductExtraData"
rem 指定其他orm的标签和值(以空格分隔)
set COMMON_TAGS="id=gorm:\"primarykey\" create_time=gorm:\"autoCreateTime\" update_time=gorm:\"autoUpdateTime\""
set DEPLOY_SQL="test.sql"

rem 判断本地系统是否已安装db2go工具，没有则进行安装
echo "searching db2go.exe ..."
echo "--------------------------------------------"
where db2go.exe
echo "--------------------------------------------"

IF "%errorlevel%" == "0" (
    echo db2go already installed.
) ELSE (
    echo db2go not found in system %%PATH%%, installing...
    go install github.com/civet148/db2go@latest
    If "%errorlevel%" == "0" (
        echo db2go install successfully.
    ) ELSE (
        rem 安装失败: Linux/Mac请安装gcc工具链，Windows系统可以安装msys64进行源码编译或通过链接直接下载二进制(最新版本v2.13 https://github.com/civet148/release/tree/master/db2go/v2)
        echo ERROR: Linux/Mac please install gcc tool-chain and windows download from https://github.com/civet148/release/tree/master/db2go/v2 (latest version is v2.13)
    )
)

rem ---------------------- 导出数据库表结构-------------------------
set DSN_URL="mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=true&loc=Local"
db2go   --url %DSN_URL% --out %OUT_DIR% --spec-type %SPEC_TYPES% --package %PACK_NAME%  --common-tags %COMMON_TAGS% --readonly %READ_ONLY% --export %DEPLOY_SQL%
gofmt -w %OUT_DIR%/%PACK_NAME%

pause

• 生成的代码示例

// Code generated by db2go. DO NOT EDIT.
// https://github.com/civet148/db2go

package models

const TableNameInventoryData = "`inventory_data`" //库存数据表

const (
	INVENTORY_DATA_COLUMN_ID            = "id"
	INVENTORY_DATA_COLUMN_CREATE_ID     = "create_id"
	INVENTORY_DATA_COLUMN_CREATE_NAME   = "create_name"
	INVENTORY_DATA_COLUMN_CREATE_TIME   = "create_time"
	INVENTORY_DATA_COLUMN_UPDATE_ID     = "update_id"
	INVENTORY_DATA_COLUMN_UPDATE_NAME   = "update_name"
	INVENTORY_DATA_COLUMN_UPDATE_TIME   = "update_time"
	INVENTORY_DATA_COLUMN_IS_FROZEN     = "is_frozen"
	INVENTORY_DATA_COLUMN_NAME          = "name"
	INVENTORY_DATA_COLUMN_SERIAL_NO     = "serial_no"
	INVENTORY_DATA_COLUMN_QUANTITY      = "quantity"
	INVENTORY_DATA_COLUMN_PRICE         = "price"
	INVENTORY_DATA_COLUMN_PRODUCT_EXTRA = "product_extra"
)

type InventoryData struct {
	Id           uint64           `json:"id" db:"id" gorm:"primarykey"`                                         //产品ID
	CreateId     uint64           `json:"create_id" db:"create_id" `                                            //创建人ID
	CreateName   string           `json:"create_name" db:"create_name" `                                        //创建人姓名
	CreateTime   string           `json:"create_time" db:"create_time" gorm:"autoCreateTime" sqlca:"readonly"`  //创建时间
	UpdateId     uint64           `json:"update_id" db:"update_id" `                                            //更新人ID
	UpdateName   string           `json:"update_name" db:"update_name" `                                        //更新人姓名
	UpdateTime   string           `json:"update_time" db:"update_time" gorm:"autoUpdateTime" sqlca:"readonly"`  //更新时间
	IsFrozen     int8             `json:"is_frozen" db:"is_frozen" `                                            //冻结状态(0: 未冻结 1: 已冻结)
	Name         string           `json:"name" db:"name" `                                                      //产品名称
	SerialNo     string           `json:"serial_no" db:"serial_no" `                                            //产品编号
	Quantity     float64          `json:"quantity" db:"quantity" `                                              //产品库存
	Price        float64          `json:"price" db:"price" `                                                    //产品均价
	ProductExtra ProductExtraData `json:"product_extra" db:"product_extra" sqlca:"isnull"`                      //产品附带数据(JSON文本)
}

func (do *InventoryData) GetId() uint64                      { return do.Id }
func (do *InventoryData) SetId(v uint64)                     { do.Id = v }
func (do *InventoryData) GetCreateId() uint64                { return do.CreateId }
func (do *InventoryData) SetCreateId(v uint64)               { do.CreateId = v }
func (do *InventoryData) GetCreateName() string              { return do.CreateName }
func (do *InventoryData) SetCreateName(v string)             { do.CreateName = v }
func (do *InventoryData) GetCreateTime() string              { return do.CreateTime }
func (do *InventoryData) SetCreateTime(v string)             { do.CreateTime = v }
func (do *InventoryData) GetUpdateId() uint64                { return do.UpdateId }
func (do *InventoryData) SetUpdateId(v uint64)               { do.UpdateId = v }
func (do *InventoryData) GetUpdateName() string              { return do.UpdateName }
func (do *InventoryData) SetUpdateName(v string)             { do.UpdateName = v }
func (do *InventoryData) GetUpdateTime() string              { return do.UpdateTime }
func (do *InventoryData) SetUpdateTime(v string)             { do.UpdateTime = v }
func (do *InventoryData) GetIsFrozen() int8                  { return do.IsFrozen }
func (do *InventoryData) SetIsFrozen(v int8)                 { do.IsFrozen = v }
func (do *InventoryData) GetName() string                    { return do.Name }
func (do *InventoryData) SetName(v string)                   { do.Name = v }
func (do *InventoryData) GetSerialNo() string                { return do.SerialNo }
func (do *InventoryData) SetSerialNo(v string)               { do.SerialNo = v }
func (do *InventoryData) GetQuantity() float64               { return do.Quantity }
func (do *InventoryData) SetQuantity(v float64)              { do.Quantity = v }
func (do *InventoryData) GetPrice() float64                  { return do.Price }
func (do *InventoryData) SetPrice(v float64)                 { do.Price = v }
func (do *InventoryData) GetProductExtra() ProductExtraData  { return do.ProductExtra }
func (do *InventoryData) SetProductExtra(v ProductExtraData) { do.ProductExtra = v }

连接数据库

package main

import (
    "github.com/civet148/log"
    "github.com/civet148/sqlca/v3"
)

const (
    MysqlDSN = "mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4"
	//MysqlDSN = "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4"
	//PostgresDSN  = "postgres://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public")
	//GaussDSN  = "opengauss://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public")
	//MssqlDSN  = "mssql://sa:123456@127.0.0.1:1433/mydb?instance=SQLExpress&windows=false")
	//SqliteDSN  = "sqlite:///var/lib/test.db")
)

func main() {
	var err error
	var db *sqlca.Engine
	var opts = []sqlca.Option{
		sqlca.WithDebug(),
		sqlca.WithMaxConn(100),
		sqlca.WithIdleConn(5),
		//sqlca.WithSSH(&sqlca.SSH{
		//	User:     "root",
		//	Password: "123456",
		//	Host:     "192.168.2.19:22",
		//}),
		sqlca.WithSnowFlake(&sqlca.SnowFlake{
			NodeId: 1,
		}),
	}
	db, err = sqlca.NewEngine("mysql://root:12345678@127.0.0.1:3306/test?charset=utf8mb4", opts...)
	if err != nil {
		log.Errorf("connect database error: %s", err)
		return
	}
    _ = db
}

数据库CURD示例

单条插入

func InsertSingle(db *sqlca.Engine) error {
	
	var do = &models.InventoryData{
		Id:         uint64(db.NewID()),
		CreateId:   1,
		CreateName: "admin",
		UpdateId:   1,
		UpdateName: "admin",
		IsFrozen:   0,
		Name:       "齿轮",
		SerialNo:   "SNO_001",
		Quantity:   1000,
		Price:      10.5,
	}

	var err error
	/*
		INSERT INTO inventory_data (`id`,`create_id`,`create_name`,`update_id`,`update_name`,`is_frozen`,`name`,`serial_no`,`quantity`,`price`,`product_extra`)
		VALUES ('1859078192380252161','1','admin','1','admin','0','轮胎','SNO_002','2000','210','{}')
	*/
	_, _, err = db.Model(&do).Insert()
	if err != nil {
		return log.Errorf("数据插入错误: %s", err)
	}
	return nil
}

批量插入

func InsertBatch(db *sqlca.Engine) error {

    var dos = []*models.InventoryData{
        {
            Id:         uint64(db.NewID()),
            CreateId:   1,
            CreateName: "admin",
            UpdateId:   1,
            UpdateName: "admin",
            IsFrozen:   0,
            Name:       "齿轮",
            SerialNo:   "SNO_001",
            Quantity:   1000,
            Price:      10.5,
            ProductExtra: models.ProductExtraData{
                SpecsValue: "齿数：32",
                AvgPrice:   decimal.NewDecimal(30.8),
            },
        },
        {
            Id:         uint64(db.NewID()),
            CreateId:   1,
            CreateName: "admin",
            UpdateId:   1,
            UpdateName: "admin",
            IsFrozen:   0,
            Name:       "轮胎",
            SerialNo:   "SNO_002",
            Quantity:   2000,
            Price:      210,
            ProductExtra: models.ProductExtraData{
                SpecsValue: "17英寸",
                AvgPrice:   decimal.NewDecimal(450.5),
            },
        },
    }

    var err error
    /*
        INSERT IGNORE INTO inventory_data
            (`id`,`create_id`,`create_name`,`create_time`,`update_id`,`update_name`,is_frozen`,`name`,`serial_no`,`quantity`,`price`,`product_extra`)
        VALUES
            ('1867379968636358656','1','admin','1','admin','0','齿轮','SNO_001','1000','10.5','{\"avg_price\":\".8\",\"specs_value\":\"齿数：32\"}'),
            ('1867379968636358657','1','admin','1','admin','0','轮胎','SNO_002','2000','210','{\"avg_price\":\"450.5\",\"specs_value\":\"17英寸\"}')
    */
    _, _,  err = db.Model(&dos).Ignore().Insert()
    if err != nil {
        return log.Errorf("数据插入错误: %s", err)
    }
    return nil
}

普通查询带LIMIT限制

func QueryLimit(db *sqlca.Engine) error {
	
    var err error
    var count int64
    var dos []*models.InventoryData
    
    //SELECT * FROM inventory_data ORDER BY create_time DESC LIMIT 1000
    count, err = db.Model(&dos).
        Limit(1000).
        Desc("create_time").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    return nil
}

查询无数据则报错

func QueryErrRecordNotFound(db *sqlca.Engine) error {
	
	var err error
	var count int64
	var do *models.InventoryData 

	//SELECT * FROM inventory_data WHERE id=1899078192380252160
	count, err = db.Model(&do).Id(1899078192380252160).MustFind() //MustFind方法记录没找到则提示ErrRecordNotFound错误（Query方法不会报错）
	if err != nil {
        if errors.Is(err, sqlca.ErrRecordNotFound) {
            return log.Errorf("根据ID查询数据库记录无结果：%s", err)
        }
        return log.Errorf("数据库错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)
	return nil
}

分页查询

func QueryByPage(db *sqlca.Engine) error {
    	
    var err error
    var count, total int64
    var dos []*models.InventoryData
    
    //SELECT  * FROM inventory_data WHERE 1=1 ORDER BY create_time DESC LIMIT 0,20
    count, total, err = db.Model(&dos).
        Page(1, 20).
        Desc("create_time").
        QueryEx()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果条数: %d 数据库总数：%v", count, total)
    return nil
}

多条件查询

func QueryByCondition(db *sqlca.Engine) error {
	
    var err error
    var count int64
    var dos []*models.InventoryData
    //SELECT * FROM inventory_data WHERE `quantity` > 0 and is_frozen=0 AND create_time >= '2024-10-01 11:35:14' ORDER BY create_time DESC
    count, err = db.Model(&dos).
        Gt("quantity", 0).
        Eq("is_frozen", 0).
        Gte("create_time", "2024-10-01 11:35:14").
        Desc("create_time").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    return nil
}

查询JSON内容字段到数据对象

/*
   models.InventoryData对象的ProductExtra是一个跟数据库JSON内容对应的结构体, 数据库中product_extra字段是json类型或text文本

    type ProductExtraData struct {
        AvgPrice   sqlca.Decimal `json:"avg_price"`   //均价
        SpecsValue string        `json:"specs_value"` //规格
    }
*/
func QueryWithJsonColumn(db *sqlca.Engine) error {
    var err error
    var do models.InventoryData
    var id = uint64(1867379968636358657)
    
    /*
        SELECT * FROM inventory_data WHERE id=1867379968636358657
    
        +-----------------------+-----------------------+-----------------------+------------------------------------------------+
        | id	                | name	| serial_no	    | quantity	| price	    |                 product_extra                  |
        +-----------------------+-------+---------------+-----------+-----------+------------------------------------------------+
        | 1867379968636358657	| 轮胎  	| SNO_002		| 2000.000 	| 210.00	| {"avg_price": "450.5", "specs_value": "17英寸"} |
        +------------------------------------------------------------------------------------------------------------------------+
    */
    _, err = db.Model(&do).
                Table("inventory_data").
                Select("id", "name", "serial_no", "quantity","price", "product_extra").
                Id(id).
                MustFind() //MustFind方法记录没找到则提示ErrRecordNotFound错误（Query方法不会报错）
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("ID: %v 数据：%+v", id, do)
    /*
        2024-12-18 15:15:03.560732 PID:64764 [INFO] {goroutine 1} <main.go:373 QueryWithJsonColumn()> ID: 1867379968636358657 数据：{Id:1867379968636358657 Name:轮胎 SerialNo:SNO_002 Quantity:2000 Price:210 ProductExtra:{AvgPrice:450.5 SpecsValue:17英寸}}
    */
    return nil
}

统计查询结果行数

// 获取查询结果行数
func QueryCountRows(db *sqlca.Engine) error {
    // SELECT COUNT(*) FROM inventory_data WHERE is_frozen = true
    count, err := db.Model(&models.InventoryData{}).Where("is_frozen", models.FrozenState_Ture).CountRows()
    if err != nil {
    return log.Errorf(err.Error())
    }
    log.Infof("select 统计总行数：%d", count)

    count, err = db.Model(&models.InventoryData{}).
    GroupBy("create_id").
    Where("create_time > ? AND is_frozen = ?", "2025-06-01 00:00:00", models.FrozenState_False).
    CountRows()
    log.Infof("group by 统计总行数：%d", count)
	return nil
}

原生SQL查询

func QueryRawSQL(db *sqlca.Engine) error {
    
    var rows []*models.InventoryData
    var sb = sqlca.NewStringBuilder()
    
    //SELECT * FROM inventory_data  WHERE is_frozen =  '0' AND quantity > '10'
    
    sb.Append("SELECT * FROM inventory_data")
    sb.Append("WHERE is_frozen = ?", 0)
    sb.Append("AND quantity > ?", 10)
    strQuery := sb.String()
    _, err := db.Model(&rows).QueryRaw(strQuery)
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    return nil
}

原生SQL执行

func ExecRawSQL(db *sqlca.Engine) error {
    var sb = sqlca.NewStringBuilder()
    
    //UPDATE inventory_data SET quantity = '10' WHERE id=1867379968636358657
    sb.Append("UPDATE inventory_data")
    sb.Append("SET quantity = ?", 10)
    sb.Append("WHERE id = ?", 1867379968636358657)
    
    strQuery := sb.String()
    affectedRows, lastInsertId, err := db.Model(nil).ExecRaw(strQuery)
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("受影响的行数：%d 最后插入的ID：%d", affectedRows, lastInsertId)
    return nil
}

查询带多个OR条件(map类型)

func QueryOr(db *sqlca.Engine) error {
	
    var err error
    var count int64
    var dos []*models.InventoryData
    
    //SELECT * FROM inventory_data WHERE create_id=1 AND name = '配件' OR serial_no = 'SNO_001' ORDER BY create_time DESC
    count, err = db.Model(&dos).
        And("create_id = ?", 1).
        Or("name = ?", "配件").
        Or("serial_no = ?", "SNO_001").
        Desc("create_time").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    
    //SELECT * FROM inventory_data WHERE create_id=1 AND is_frozen = 0 AND quantity > 0 AND (name = '配件' OR serial_no = 'SNO_001') ORDER BY create_time DESC
    var andConditions = make(map[string]interface{})
    var orConditions = make(map[string]interface{})
    
    andConditions["create_id"] = 1      //create_id = 1
    andConditions["is_frozen"] = 0      //is_frozen = 0
    andConditions["quantity > ?"] = 0   //quantity > 0
    
    orConditions["name = ?"] = "配件"
    orConditions["serial_no = ?"] = "SNO_001"
    
    count, err = db.Model(&dos).
        And(andConditions).
        Or(orConditions).
        Desc("create_time").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    return nil
}

分组查询

func QueryByGroup(db *sqlca.Engine) error {
	
    var err error
    var count int64
    var dos []*models.InventoryData
    /*
        SELECT  create_id, SUM(quantity) AS quantity
        FROM inventory_data
        WHERE 1=1 AND quantity>'0' AND is_frozen='0' AND create_time>='2024-10-01 11:35:14'
        GROUP BY create_id
    */
    count, err = db.Model(&dos).
        Select("create_id", "SUM(quantity) AS quantity").
        Gt("quantity", 0).
        Eq("is_frozen", 0).
        Gte("create_time", "2024-10-01 11:35:14").
        GroupBy("create_id").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    return nil
}

联表查询

type Product struct {
    ProductId       uint64  `db:"product_id"`
    ProductName     string  `db:"product_name"`
    Quantity        float64 `db:"quantity"`
    Weight          float64 `db:"weight"` 
}
func QueryJoins(db *sqlca.Engine) error {
	
    /*
        SELECT a.id as product_id, a.name AS product_name, b.quantity, b.weight
        FROM inventory_data a
        LEFT JOIN inventory_in b
        ON a.id=b.product_id
        WHERE a.quantity > 0 AND a.is_frozen=0 AND a.create_time>='2024-10-01 11:35:14'
    */
    var dos []*Product
    count, err := db.Model(&dos).
        Select("a.id as product_id", "a.name AS product_name", "b.quantity", "b.weight").
        Table("inventory_data a").
        LeftJoin("inventory_in b").
        On("a.id=b.product_id").
        Gt("a.quantity", 0).
        Eq("a.is_frozen", 0).
        Gte("a.create_time", "2024-10-01 11:35:14").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    return nil
}

普通变量取值查询

func QueryByNormalVars(db *sqlca.Engine) error {
	
    var err error
    var name, serialNo string
    var id = uint64(1859078192380252160)
    //SELECT name, serial_no FROM inventory_data WHERE id=1859078192380252160
    _, err = db.Model(&name, &serialNo).
                Table("inventory_data").
                Select("name, serial_no").
                Id(id).
                MustFind() //MustFind方法记录没找到则提示ErrRecordNotFound错误（Query方法不会报错）
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("数据ID: %v name=%s serial_no=%s", id, name, serialNo)
	
    var ids []uint64
    //SELECT id FROM inventory_data LIMIT 10
    _, err = db.Model(&ids).
                Table("inventory_data").
                Select("id").
                Limit(10).
                Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    return nil
}

数据更新

• 通过数据模型对象更新数据

/*
[数据更新]

SELECT * FROM inventory_data  WHERE `id`='1858759254329004032'
UPDATE inventory_data SET `quantity`='2300' WHERE `id`='1858759254329004032'
*/
func UpdateByModel(db *sqlca.Engine) error {
	
    var err error
    var do *models.InventoryData
    var id = uint64(1858759254329004032)
    _, err = db.Model(&do).Id(id).MustFind() //MustFind方法如果记录没找到则提示ErrRecordNotFound错误（Query方法不会报错）
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    
    do.Quantity = 2300 //更改库存
    _, err = db.Model(do).Select("quantity").Update() //do对象中id字段已有值，在更新/查询时自动作为Where条件
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
    return nil
}

• 通过变量/常量更新数据

/*
[通过普通变量更新数据]
*/
func UpdateByVars(db *sqlca.Engine) error {
	
    var err error
    var id = uint64(1858759254329004032)
    var quantity = 2300 //更改库存数
	
	//UPDATE inventory_data SET `quantity`='2300' WHERE `id`='1858759254329004032'
    _, err = db.Model(&quantity).Table("inventory_data").Id(id).Select("quantity").Update()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
    //UPDATE inventory_data SET `quantity`='2300' WHERE `id`='1858759254329004032'
    _, err = db.Model(2300).Table("inventory_data").Id(id).Select("quantity").Update()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
    return nil
}

• 通过map进行数据更新

func UpdateByMap(db *sqlca.Engine) error {
    var err error
    var updates = map[string]interface{}{
        "quantity": 2100, //更改库存
        "Price":    300,  //更改价格
		"product_extra": nil,  //设置产品扩展数据为NULL
    }
    //UPDATE inventory_data SET `product_extra`=NULL, `quantity`='2100',`price`=300 WHERE `id`='1858759254329004032'
    _, err = db.Model(&updates).Table("inventory_data").Id(1858759254329004032).Update()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
    return nil
}

• 通过表达式更新

//UPDATE inventory_data SET `location`=POINT(113.234, 22.39236) WHERE `id`='1939612151790440448'
func UpdatePointByExpress(db *sqlca.Engine) error {
    var upmap = map[string]any{
        "location": sqlca.NewExpr("POINT(?, ?)", 113.234, 22.39236),
    }
    rows, err := db.Model(upmap).Table("inventory_data").Id(productId).Update()
    if err != nil {
        return log.Errorf(err.Error())
    }
    log.Infof("rows affected: %d", rows)
    return nil
}

• 删除操作

func DeleteById(db *sqlca.Engine) error {
    var err error
    var id = uint64(1859078192380252160)
    //DELETE inventory_data WHERE `id`='1859078192380252160'
    _, err = db.Model(models.InventoryData{}).Id(id).Delete()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
    log.Infof("删除ID%v数据成功", id)
    return nil
}

事务处理

func Transaction(db *sqlca.Engine) error {

	/*
		-- TRANSACTION BEGIN

			INSERT INTO inventory_in (`user_id`,`quantity`,`remark`,`create_id`,`user_name`,`weight`,`create_time`,`update_name`,`is_deleted`,`product_id`,`id`,`create_name`,`update_id`,`update_time`,`order_no`) VALUES ('3','20','产品入库','1','lazy','200.3','2024-11-27 11:35:14','admin','0','1858759254329004032','1861614736295071744','admin','1','2024-11-27 1114','202407090000001')
			SELECT * FROM inventory_data  WHERE `id`='1858759254329004032'
			UPDATE inventory_data SET `quantity`='2320' WHERE `id`='1858759254329004032'

		-- TRANSACTION END
	*/

	now := time.Now().Format("2006-01-02 15:04:05")
	tx, err := db.TxBegin()
	if err != nil {
		return log.Errorf("开启事务失败：%s", err)
	}
	defer tx.TxRollback()

	productId := uint64(1858759254329004032)
	strOrderNo := time.Now().Format("20060102150405.000000000")
	//***************** 执行事务操作 *****************
	quantity := float64(20)
	weight := float64(200.3)
	_, _, err = tx.Model(&models.InventoryIn{
		Id:         uint64(db.NewID()),
		CreateId:   1,
		CreateName: "admin",
		CreateTime: now,
		UpdateId:   1,
		UpdateName: "admin",
		UpdateTime: now,
		ProductId:  productId,
		OrderNo:    strOrderNo,
		UserId:     3,
		UserName:   "lazy",
		Quantity:   quantity,
		Weight:     weight,
		Remark:     "产品入库",
	}).Insert()
	if err != nil {
		return log.Errorf("数据插入错误: %s", err)
	}
	var inventoryData = &models.InventoryData{}
	_, err = tx.Model(&inventoryData).Id(productId).MustFind() //MustFind方法如果记录没找到则提示ErrRecordNotFound错误（Query方法不会报错）
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	inventoryData.Quantity += quantity
	_, err = tx.Model(&inventoryData).Id(productId).Select("quantity").Update()
	if err != nil {
		return log.Errorf("更新错误：%s", err)
	}
	//***************** 提交事务 *****************
	err = tx.TxCommit()
	if err != nil {
		return log.Errorf("提交事务失败：%s", err)
	}
	return nil
}

事务处理封装

func TransactionWrapper(db *sqlca.Engine) error {

    /*
       -- TRANSACTION BEGIN
    
        INSERT INTO inventory_in (`user_id`,`quantity`,`remark`,`create_id`,`user_name`,`weight`,`create_time`,`update_name`,`is_deleted`,`product_id`,`id`,`create_name`,`update_id`,`update_time`,`order_no`) VALUES ('3','20','产品入库','1','lazy','200.3','2024-11-27 11:35:14','admin','0','1858759254329004032','1861614736295071744','admin','1','2024-11-27 1114','202407090000002')
        SELECT * FROM inventory_data  WHERE `id`='1858759254329004032'
        UPDATE inventory_data SET `quantity`='2320' WHERE `id`='1858759254329004032'
    
       -- TRANSACTION END
    */
    strOrderNo := time.Now().Format("20060102150405.000000000")
    err := db.TxFunc(func(tx *sqlca.Engine) error {
        var err error
        productId := uint64(1858759254329004032)
        now := time.Now().Format("2006-01-02 15:04:05")
    
        //***************** 执行事务操作 *****************
        quantity := float64(20)
        weight := float64(200.3)
        _, _, err = tx.Model(&models.InventoryIn{
            Id:         uint64(db.NewID()),
            CreateId:   1,
            CreateName: "admin",
            CreateTime: now,
            UpdateId:   1,
            UpdateName: "admin",
            UpdateTime: now,
            ProductId:  productId,
            OrderNo:    strOrderNo,
            UserId:     3,
            UserName:   "lazy",
            Quantity:   quantity,
            Weight:     weight,
            Remark:     "产品入库",
        }).Insert()
        if err != nil {
            return log.Errorf("数据插入错误: %s", err)
        }
        var inventoryData = &models.InventoryData{}
        _, err = tx.Model(&inventoryData).Id(productId).MustFind() //MustFind方法记录没找到则提示ErrRecordNotFound错误（Query方法不会报错）
        if err != nil {
            return log.Errorf("数据查询错误：%s", err)
        }
        inventoryData.Quantity += quantity
        _, err = tx.Model(&inventoryData).Id(productId).Select("quantity").Update()
        if err != nil {
            return log.Errorf("更新错误：%s", err)
        }
        return nil
    })
    
    //***************** 事务处理结果 *****************
    if err != nil {
        return log.Errorf("事务失败：%s", err)
    }
    return nil
}

其他方法说明

Table方法

设置数据库表名，通过Model方法传参时默认将结构体名称的小写蛇形命名作为表名， 当传入的结构体名称跟实际表名不一致时需要明确用Table方法指定表名或实现TableName()接口

Use方法

切换数据库

func SwitchDatabase(db *sqlca.Engine) (err error){
    var db2 *sqlca.Engine
	db2, err = db.Use("test2")
    if err != nil {
        log.Errorf(err.Error())
        return err
    }	
    return nil
}

前置和后置操作接口定义

//查询前置和后置hook接口
type BeforeQueryInterface interface {
    BeforeQuery(db *sqlca.Engine) error
}
type AfterQueryInterface interface {
    AfterQuery(db *sqlca.Engine) error
}
//插入前置和后置hook接口
type BeforeCreateInterface interface {
	BeforeCreate(db *sqlca.Engine) error
}
type AfterCreateInterface interface {
	AfterCreate(db *sqlca.Engine) error
}
//更新前置和后置hook接口
type BeforeUpdateInterface interface {
	BeforeUpdate(db *sqlca.Engine) error
}
type AfterUpdateInterface interface {
	AfterUpdate(db *sqlca.Engine) error
}
//删除前置和后置hook接口
type BeforeDeleteInterface interface {
	BeforeDelete(db *sqlca.Engine) error
}
type AfterDeleteInterface interface {
	AfterDelete(db *sqlca.Engine) error
}

NearBy

//数据库表restaurant对应模型结构
type Restaurant struct {
    Id          uint64  `db:"id"`       //主键ID
    Lng         float64 `db:"lng"`      //经度
    Lat         float64 `db:"lat"`      //纬度
    Name        string  `db:"name"`     //餐馆名称
}

//附近的餐馆和距离结构定义
type RestaurantLocation struct {
    Id          uint64  `db:"id"`       //主键ID
    Lng         float64 `db:"lng"`      //经度
    Lat         float64 `db:"lat"`      //纬度
    Name        string  `db:"name"`     //餐馆名称
    Distance    float64 `db:"distance"` //距离（米）
}

func QueryNearBy(db *sqlca.Engine) error {
    var dos []*RestaurantLocation
    //查询指定坐标点，查询距离小于1000米内的餐馆（查询出的距离取名distance）
    _, err := db.Model(&dos).Table("restaurant").NearBy("lng", "lat", "distance", 114.0545429, 22.5445741, 1000).Query()
    if err != nil {
        return logx.Error(err.Error())
    }
    return nil
}

GeoHash

给定坐标点，查询GEO HASH

Like

func QueryLike(db *sqlca.Engine) error {
    //SELECT * FROM inventory_data WHERE `serial_no` LIKE '%0001%'
    _, err := db.Model(&models.InventoryData{}).LIKE(serial_no, "0001").MustFind()
    if err != nil {
        return logx.Error(err.Error())
    }
	return nil
}

SlowQuery

开启或关闭慢查询日志，默认关闭，开启后，会记录超过规定时间（毫秒ms）的sql语句，并输出到控制台。

QueryJson

将查询结果转换为json字符串，并返回。

NewID

当调用NewEngine时，指定SnowFlake选项后，可以用NewID生成一个雪花ID

ForUpdate

在查询语句中添加FOR UPDATE关键字，用于查询时锁定记录，避免并发修改。仅用于MySQL数据库。

LockShareMode

在查询语句中添加 LOCK IN SHARE MODE关键字。仅用于MySQL数据库。

JSON查询方法

JsonExpr

MySQL数据库构造JSON查询表达式，用于查询JSON字段。

JsonEqual

MySQL数据库构造JSON等于查询表达式，用于查询JSON字段。

JsonGreater

MySQL数据库构造JSON大于查询表达式，用于查询JSON字段。

JsonLess

MySQL数据库构造JSON小于查询表达式，用于查询JSON字段。

JsonGreaterEqual

MySQL数据库构造JSON大于等于查询表达式，用于查询JSON字段。

JsonLessEqual

MySQL数据库构造JSON小于等于查询表达式，用于查询JSON字段。

JsonContainArray

MySQL数据库构造JSON包含数组查询表达式，用于查询JSON字段。