sqlca

package module
v3.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 10, 2025 License: MIT Imports: 33 Imported by: 0

README

简介

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方法既支持常规的字符串格式化（含占位符?方式），同时也支持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` json DEFAULT NULL COMMENT '产品附带数据(JSON文本)',
    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/v2"
)

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 options = &sqlca.Options{
        Debug: true, //是否开启调试日志输出
        Max:   150,  //最大连接数
        Idle:  5,    //空闲连接数
        SnowFlake: &sqlca.SnowFlake{ //雪花算法配置（不使用可以赋值nil）
            NodeId: 1, //雪花算法节点ID 1-1023
        },
        //SSH: &sqlca.SSH{ //SSH隧道连接配置
        //	User:     "root",
        //	Password: "123456",
        //	Host:     "192.168.2.19:22",
        //},
        //DefaultLimit: 100,  //默认查询条数限制
    }
    db, err = sqlca.NewEngine(MysqlDSN, options)
    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:   sqlca.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:   sqlca.NewDecimal(450.5),
            },
        },
    }

    var err error
    /*
        INSERT 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).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
}

原生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
}
  • 删除操作
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字段。

Documentation

Index

Constants

View Source 
const (
	DefaultConnMax  = 150
	DefaultConnIdle = 5
)
View Source 
const (
	JoinType_Inner = 0 //inner join
	JoinType_Left  = 1 //left join
	JoinType_Right = 2 //right join
)

Variables

View Source 
var (
	ErrRecordNotFound = errors.New("record not found")
)

Functions

func NewContext added in v3.1.0 

func NewContext(ctx context.Context, e *Engine) context.Context

func NewSqlClause 

func NewSqlClause(fmts string, args ...any) *types.SqlClauseValue

func Url2MySql 

func Url2MySql(strUrl string) (string, error)

Types

type AfterCreateInterface 

type AfterCreateInterface interface {
	AfterCreate(db *Engine) error
}

type AfterDeleteInterface 

type AfterDeleteInterface interface {
	AfterDelete(db *Engine) error
}

type AfterQueryInterface 

type AfterQueryInterface interface {
	AfterQuery(db *Engine) error
}

type AfterUpdateInterface 

type AfterUpdateInterface interface {
	AfterUpdate(db *Engine) error
}

type BeforeCreateInterface 

type BeforeCreateInterface interface {
	BeforeCreate(db *Engine) error
}

type BeforeDeleteInterface 

type BeforeDeleteInterface interface {
	BeforeDelete(db *Engine) error
}

type BeforeQueryInterface 

type BeforeQueryInterface interface {
	BeforeQuery(db *Engine) error
}

type BeforeUpdateInterface 

type BeforeUpdateInterface interface {
	BeforeUpdate(db *Engine) error
}

type CaseWhen 

type CaseWhen struct {
	// contains filtered or unexported fields
}

func (*CaseWhen) Case 

func (c *CaseWhen) Case(strThen string, strWhen string, args ...interface{}) *CaseWhen

func (*CaseWhen) Else 

func (c *CaseWhen) Else(strElse string) *CaseWhen

func (*CaseWhen) End 

func (c *CaseWhen) End(strName string) *Engine

type Decimal 

type Decimal struct {
	// contains filtered or unexported fields
}

func NewDecimal 

func NewDecimal(v interface{}) (d Decimal)

func (Decimal) Abs 

func (d Decimal) Abs() Decimal

Abs returns the absolute value of the decimal.

func (Decimal) Add 

func (d Decimal) Add(d2 interface{}) Decimal

Add returns d + d2

func (Decimal) Amount2Btc 

func (d Decimal) Amount2Btc() Decimal

func (Decimal) Amount2Coin 

func (d Decimal) Amount2Coin(prec int) Decimal

func (Decimal) Amount2Ether 

func (d Decimal) Amount2Ether() Decimal

func (Decimal) Amount2FIL 

func (d Decimal) Amount2FIL() Decimal

func (Decimal) BigInt 

func (d Decimal) BigInt() (b *big.Int, ok bool)

func (Decimal) Btc2Amount 

func (d Decimal) Btc2Amount() Decimal

func (Decimal) Cmp 

func (d Decimal) Cmp(d2 interface{}) int

Cmp compares the numbers represented by d and d2 and returns: 

-1 if d <  d2
 0 if d == d2
+1 if d >  d2

func (Decimal) Coin2Amount 

func (d Decimal) Coin2Amount(prec int) Decimal

func (Decimal) Cos 

func (d Decimal) Cos() Decimal

Cos returns the cosine of the radian argument x.

func (Decimal) Div 

func (d Decimal) Div(d2 interface{}) Decimal

Div returns d / d2. If it doesn't divide exactly, the result will have DivisionPrecision digits after the decimal point.

func (Decimal) Equal 

func (d Decimal) Equal(d2 interface{}) bool

Equal returns whether the numbers represented by d and d2 are equal.

func (Decimal) Ether2Amount 

func (d Decimal) Ether2Amount() Decimal

func (Decimal) FIL2Amount 

func (d Decimal) FIL2Amount() Decimal

func (Decimal) Float64 

func (d Decimal) Float64() (f float64)

Float64 returns the nearest float64 value for d and a bool indicating whether f represents d exactly.

func (*Decimal) FromFloat 

func (d *Decimal) FromFloat(v float64)

func (*Decimal) FromInt 

func (d *Decimal) FromInt(v int64)

func (*Decimal) FromString 

func (d *Decimal) FromString(v string)

func (Decimal) GetDecimal 

func (d Decimal) GetDecimal() decimal.Decimal

GetDecimal returns the decimal.Decimal type

func (Decimal) GreaterThan 

func (d Decimal) GreaterThan(d2 interface{}) bool

GreaterThan (GT) returns true when d is greater than d2.

func (Decimal) GreaterThanOrEqual 

func (d Decimal) GreaterThanOrEqual(d2 interface{}) bool

GreaterThanOrEqual (GTE) returns true when d is greater than or equal to d2.

func (Decimal) IntPart 

func (d Decimal) IntPart() int64

IntPart returns the integer component of the decimal.

func (Decimal) IsNegative 

func (d Decimal) IsNegative() bool

IsNegative return 

true if d < 0
false if d == 0
false if d > 0

func (Decimal) IsPositive 

func (d Decimal) IsPositive() bool

IsPositive return 

true if d > 0
false if d == 0
false if d < 0

func (Decimal) IsZero 

func (d Decimal) IsZero() bool

IsZero return 

true if d == 0
false if d > 0
false if d < 0

func (Decimal) LessThan 

func (d Decimal) LessThan(d2 interface{}) bool

LessThan (LT) returns true when d is less than d2.

func (Decimal) LessThanOrEqual 

func (d Decimal) LessThanOrEqual(d2 interface{}) bool

LessThanOrEqual (LTE) returns true when d is less than or equal to d2.

func (Decimal) Marshal 

func (d Decimal) Marshal() ([]byte, error)

func (Decimal) MarshalBSON 

func (d Decimal) MarshalBSON() ([]byte, error)

MarshalBSON implements the bson.Marshaler interface.

func (Decimal) MarshalBinary 

func (d Decimal) MarshalBinary() (data []byte, err error)

MarshalBinary implements the encoding.BinaryMarshaler interface.

func (Decimal) MarshalJSON 

func (d Decimal) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface.

func (Decimal) MarshalText 

func (d Decimal) MarshalText() (text []byte, err error)

MarshalText implements the encoding.TextMarshaler interface for XML serialization.

func (Decimal) Max 

func (d Decimal) Max(rest ...Decimal) Decimal

Max returns the largest Decimal that was passed in the arguments. To call this function with an array, you must do: This makes it harder to accidentally call Max with 0 arguments.

func (Decimal) Min 

func (d Decimal) Min(rest ...Decimal) Decimal

Min returns the smallest Decimal that was passed in the arguments. To call this function with an array, you must do: This makes it harder to accidentally call Min with 0 arguments.

func (Decimal) Mod 

func (d Decimal) Mod(d2 interface{}) Decimal

Mod returns d % d2.

func (Decimal) Mul 

func (d Decimal) Mul(d2 interface{}) Decimal

Mul returns d * d2.

func (Decimal) Neg 

func (d Decimal) Neg() Decimal

Neg returns -d.

func (Decimal) Pow 

func (d Decimal) Pow(d2 interface{}) Decimal

Pow returns d to the power d2

func (Decimal) Round 

func (d Decimal) Round(places int32) Decimal

Round rounds the decimal to places decimal places. If places < 0, it will round the integer part to the nearest 10^(-places).

Example: 

NewFromFloat(5.45).Round(1).String() // output: "5.5"
NewFromFloat(545).Round(-1).String() // output: "550"

func (*Decimal) Scan 

func (d *Decimal) Scan(src interface{}) error

Scan implements the sql.Scanner interface for database deserialization.

func (Decimal) Sign 

func (d Decimal) Sign() int

Sign returns: 

-1 if d <  0
 0 if d == 0
+1 if d >  0

func (Decimal) Sin 

func (d Decimal) Sin() Decimal

Sin returns the sine of the radian argument x.

func (Decimal) String 

func (d Decimal) String() string

String returns the string representation of the decimal with the fixed point.

Example: 

d := New(-12345, -3)
println(d.String())

Output: 

-12.345

func (Decimal) StringFixed 

func (d Decimal) StringFixed(places int32) string

StringFixed returns a rounded fixed-point string with places digits after the decimal point.

Example: 

NewFromFloat(0).StringFixed(2) // output: "0.00"
NewFromFloat(0).StringFixed(0) // output: "0"
NewFromFloat(5.45).StringFixed(0) // output: "5"
NewFromFloat(5.45).StringFixed(1) // output: "5.5"
NewFromFloat(5.45).StringFixed(2) // output: "5.45"
NewFromFloat(5.45).StringFixed(3) // output: "5.450"
NewFromFloat(545).StringFixed(-1) // output: "550"

func (Decimal) StringScaled 

func (d Decimal) StringScaled(exp int32) string

StringScaled first scales the decimal then calls .String() on it. NOTE: buggy, unintuitive, and DEPRECATED! Use StringFixed instead.

func (Decimal) Sub 

func (d Decimal) Sub(d2 interface{}) Decimal

Sub returns d - d2.

func (Decimal) Sum 

func (d Decimal) Sum(rest ...Decimal) Decimal

Sum returns the combined total of the provided first and rest Decimals

func (Decimal) Tan 

func (d Decimal) Tan() Decimal

Tan returns the tangent of the radian argument x.

func (Decimal) Truncate 

func (d Decimal) Truncate(precision int32) Decimal

Truncate truncates off digits from the number, without rounding.

NOTE: precision is the last digit that will not be truncated (must be >= 0).

Example: 

decimal.NewFromString("123.456").Truncate(2).String() // "123.45"

func (*Decimal) Unmarshal 

func (d *Decimal) Unmarshal(data []byte) error

func (*Decimal) UnmarshalBSON 

func (d *Decimal) UnmarshalBSON(data []byte) error

func (*Decimal) UnmarshalBinary 

func (d *Decimal) UnmarshalBinary(data []byte) error

UnmarshalBinary implements the encoding.BinaryUnmarshaler interface. As a string representation is already used when encoding to text, this method stores that string as []byte

func (*Decimal) UnmarshalJSON 

func (d *Decimal) UnmarshalJSON(decimalBytes []byte) error

UnmarshalJSON implements the json.Unmarshaler interface.

func (*Decimal) UnmarshalText 

func (d *Decimal) UnmarshalText(text []byte) error

UnmarshalText implements the encoding.TextUnmarshaler interface for XML deserialization.

func (Decimal) Value 

func (d Decimal) Value() (driver.Value, error)

Value implements the driver.Valuer interface for database serialization.

type Engine 

type Engine struct {
	// contains filtered or unexported fields
}

func FromContext added in v3.1.0 

func FromContext(ctx context.Context) *Engine

func NewEngine 

func NewEngine(strUrl string, options ...*Options) (*Engine, error)

// [mysql] "mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4" // [postgres] "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" // [mssql] "mssql://sa:123456@127.0.0.1:1433/mydb?instance=SQLExpress&windows=false" // [sqlite] "sqlite:///var/lib/test.db"

func (*Engine) And 

func (e *Engine) And(query any, args ...any) *Engine

func (*Engine) Asc 

func (e *Engine) Asc(columns ...string) *Engine

Asc order by [field1,field2...] asc

func (*Engine) Attach 

func (e *Engine) Attach(strDatabaseName string, db *sqlx.DB) *Engine

Attach attach from a exist sqlx db instance

func (*Engine) AutoRollback 

func (e *Engine) AutoRollback() *Engine

func (*Engine) Avg 

func (e *Engine) Avg(strColumn string, as ...string) *Engine

func (*Engine) Case 

func (e *Engine) Case(strThen string, strWhen string, args ...any) *CaseWhen

func (*Engine) Close 

func (e *Engine) Close() *Engine

Close disconnect all database connections

func (*Engine) Count 

func (e *Engine) Count(strColumn string, as ...string) *Engine

func (*Engine) CountRows added in v3.1.0 

func (e *Engine) CountRows(query string, args ...any) (count int64, err error)

func (*Engine) Counter 

func (e *Engine) Counter() *counter

func (*Engine) Debug 

func (e *Engine) Debug(ok bool)

Debug log debug mode on or off

func (*Engine) Delete 

func (e *Engine) Delete() (rowsAffected int64, err error)

Delete orm delete record(s) from db

func (*Engine) Desc 

func (e *Engine) Desc(columns ...string) *Engine

Desc order by [field1,field2...] desc

func (*Engine) Distinct 

func (e *Engine) Distinct() *Engine

Distinct set distinct when select

func (*Engine) Eq 

func (e *Engine) Eq(strColumn string, value any) *Engine

Eq alias of Equal

func (*Engine) Equal 

func (e *Engine) Equal(strColumn string, value any) *Engine

func (*Engine) Exclude 

func (e *Engine) Exclude(columns ...string) *Engine

Exclude exclude orm select/update columns

func (*Engine) ExecRaw 

func (e *Engine) ExecRaw(query string, args ...any) (rowsAffected, lastInsertId int64, err error)

ExecRaw use raw sql to insert/update database, results can not be cached to redis/memcached/memory... return rows affected and error, if err is not nil must be something wrong

func (*Engine) ForUpdate 

func (e *Engine) ForUpdate() *Engine

func (*Engine) Force 

func (e *Engine) Force() *Engine

Force force update/insert read only column(s)

func (*Engine) From 

func (e *Engine) From(exprs ...string) *Engine

From alias of Table method

func (*Engine) GeoHash 

func (e *Engine) GeoHash(lng, lat float64, precision int) (strGeoHash string, strNeighbors []string)

GeoHash encode geo hash string (precision 1~8) 

returns geo hash and neighbors areas

func (*Engine) GetAdapter 

func (e *Engine) GetAdapter() types.AdapterType

func (*Engine) GetPkName 

func (e *Engine) GetPkName() string

func (*Engine) GreaterEqual 

func (e *Engine) GreaterEqual(strColumn string, value any) *Engine

func (*Engine) GreaterThan 

func (e *Engine) GreaterThan(strColumn string, value any) *Engine

func (*Engine) GroupBy 

func (e *Engine) GroupBy(columns ...string) *Engine

GroupBy group by [field1,field2...]

func (*Engine) Gt 

func (e *Engine) Gt(strColumn string, value any) *Engine

Gt alias of GreaterThan

func (*Engine) Gte 

func (e *Engine) Gte(strColumn string, value any) *Engine

Gte alias of GreaterEqual

func (*Engine) GteLte 

func (e *Engine) GteLte(strColumn string, value1, value2 any) *Engine

GteLte greater than equal and less than equal

func (*Engine) Having 

func (e *Engine) Having(strFmt string, args ...any) *Engine

Having having [condition]

func (*Engine) Id 

func (e *Engine) Id(value any) *Engine

Id set orm primary key's value

func (*Engine) In 

func (e *Engine) In(strColumn string, args ...any) *Engine

In `field_name` IN ('1','2',...)

func (*Engine) InnerJoin 

func (e *Engine) InnerJoin(strTableName string) *Join

func (*Engine) Insert 

func (e *Engine) Insert() (lastInsertId int64, err error)

Insert orm insert return last insert id and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) IsNULL 

func (e *Engine) IsNULL(strColumn string) *Engine

func (*Engine) IsNull 

func (e *Engine) IsNull(strColumn string) *Engine

func (*Engine) JsonEqual 

func (e *Engine) JsonEqual(strColumn, strPath string, value any) *Engine

func (*Engine) JsonGreater 

func (e *Engine) JsonGreater(strColumn, strPath string, value any) *Engine

func (*Engine) JsonGreaterEqual 

func (e *Engine) JsonGreaterEqual(strColumn, strPath string, value any) *Engine

func (*Engine) JsonLess 

func (e *Engine) JsonLess(strColumn, strPath string, value any) *Engine

func (*Engine) JsonLessEqual 

func (e *Engine) JsonLessEqual(strColumn, strPath string, value any) *Engine

func (*Engine) JsonMarshal 

func (e *Engine) JsonMarshal(v any) (strJson string)

func (*Engine) JsonUnmarshal 

func (e *Engine) JsonUnmarshal(strJson string, v any) (err error)

func (*Engine) LeftJoin 

func (e *Engine) LeftJoin(strTableName string) *Join

func (*Engine) LessEqual 

func (e *Engine) LessEqual(strColumn string, value any) *Engine

func (*Engine) LessThan 

func (e *Engine) LessThan(strColumn string, value any) *Engine

func (*Engine) Like 

func (e *Engine) Like(strColumn, keyword string) *Engine

func (*Engine) Likes 

func (e *Engine) Likes(kvs map[string]any) *Engine

func (*Engine) Limit 

func (e *Engine) Limit(args ...int) *Engine

Limit query limit Limit(10) - query records limit 10 (mysql/postgres)

func (*Engine) LockShareMode 

func (e *Engine) LockShareMode() *Engine

func (*Engine) Lt 

func (e *Engine) Lt(strColumn string, value any) *Engine

Lt alias of LessThan

func (*Engine) Lte 

func (e *Engine) Lte(strColumn string, value any) *Engine

Lte alias of LessEqual

func (*Engine) Max 

func (e *Engine) Max(strColumn string, as ...string) *Engine

func (*Engine) Min 

func (e *Engine) Min(strColumn string, as ...string) *Engine

func (*Engine) Model 

func (e *Engine) Model(args ...interface{}) *Engine

Model orm model use to get result set, support single struct object or slice [pointer type] notice: will clone a new engine object for orm operations(query/update/insert/upsert)

func (*Engine) MustFind 

func (e *Engine) MustFind() (rowsAffected int64, err error)

MustFind orm find data records, returns error if not found

func (*Engine) Ne 

func (e *Engine) Ne(strColumn string, value any) *Engine

Ne not equal

func (*Engine) NearBy 

func (e *Engine) NearBy(strLngCol, strLatCol, strAS string, lng, lat, distance float64) *Engine

NearBy -- select geo point as distance where distance <= n km (float64) SELECT 

a.*,
(
6371 * ACOS (
COS( RADIANS( a.lat ) ) * COS( RADIANS( 28.8039097230 ) ) * COS(
  RADIANS( 121.5619236231 ) - RADIANS( a.lng )
 ) + SIN( RADIANS( a.lat ) ) * SIN( RADIANS( 28.8039097230 ) )
)
) AS distance

FROM 

t_address a

HAVING distance <= 200 -- less than or equal 200km ORDER BY 

distance
LIMIT 10

func (*Engine) NewContext added in v3.1.0 

func (e *Engine) NewContext(ctx context.Context) context.Context

func (*Engine) NewID 

func (e *Engine) NewID() ID

func (*Engine) NoVerbose 

func (e *Engine) NoVerbose() *Engine

func (*Engine) NotIn 

func (e *Engine) NotIn(strColumn string, args ...any) *Engine

NotIn `field_name` NOT IN ('1','2',...)

func (*Engine) NotNULL 

func (e *Engine) NotNULL(strColumn string) *Engine

func (*Engine) Offset 

func (e *Engine) Offset(offset int) *Engine

Offset query offset (for mysql/postgres)

func (*Engine) Omit 

func (e *Engine) Omit(columns ...string) *Engine

Omit same as Exclude

func (*Engine) OnConflict 

func (e *Engine) OnConflict(columns ...string) *Engine

OnConflict set the conflict columns for upsert only for postgresql

func (*Engine) Or 

func (e *Engine) Or(query any, args ...any) *Engine

func (*Engine) OrderBy 

func (e *Engine) OrderBy(orders ...string) *Engine

OrderBy order by [field1,field2...] [ASC]

func (*Engine) Page 

func (e *Engine) Page(pageNo, pageSize int) *Engine

Page page query 

SELECT ... FROM ... WHERE ... LIMIT (pageNo*pageSize), pageSize

func (*Engine) Ping 

func (e *Engine) Ping() (err error)

Ping ping database

func (*Engine) Query 

func (e *Engine) Query() (rowsAffected int64, err error)

Query orm query return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function if slave == true, try query from a slave connection, if not exist query from master

func (*Engine) QueryEx 

func (e *Engine) QueryEx() (rowsAffected, total int64, err error)

QueryEx orm query with total count return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function if slave == true, try query from a slave connection, if not exist query from master

func (*Engine) QueryJson 

func (e *Engine) QueryJson() (s string, err error)

QueryJson query result marshal to json

func (*Engine) QueryMap 

func (e *Engine) QueryMap(query string, args ...any) (rowsAffected int64, err error)

QueryMap use raw sql to query results into a map slice (model type is []map[string]string) return results and error NOTE: Model function is must be called before call this function

func (*Engine) QueryRaw 

func (e *Engine) QueryRaw(query string, args ...any) (rowsAffected int64, err error)

QueryRaw use raw sql to query results return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) RightJoin 

func (e *Engine) RightJoin(strTableName string) *Join

func (*Engine) Round 

func (e *Engine) Round(strColumn string, round int, as ...string) *Engine

func (*Engine) Select 

func (e *Engine) Select(columns ...string) *Engine

Select orm select/update columns

func (*Engine) SetCustomTag 

func (e *Engine) SetCustomTag(tagNames ...string) *Engine

SetCustomTag set your customer tag for db query/insert/update (eg. go structure generated by protobuf not contain 'db' tag) this function must calls before Model()

func (*Engine) SetLogFile 

func (e *Engine) SetLogFile(strPath string)

SetLogFile set log file

func (*Engine) SetPkName 

func (e *Engine) SetPkName(strName string) *Engine

SetPkName set orm primary key's name, default named 'id'

func (*Engine) SetReadOnly 

func (e *Engine) SetReadOnly(columns ...string)

SetReadOnly set read only columns

func (*Engine) SlowQuery 

func (e *Engine) SlowQuery(on bool, ms int)

SlowQuery slow query alert on or off 

on -> true/false
ms -> milliseconds (can be 0 if on is false)

func (*Engine) Sum 

func (e *Engine) Sum(strColumn string, as ...string) *Engine

func (*Engine) Table 

func (e *Engine) Table(exprs ...string) *Engine

Table set orm query table name(s) expression when your struct type name is not a table name

func (*Engine) ToSQL 

func (e *Engine) ToSQL(operType types.OperType) (strSql string)

make SQL from orm model and operation type

func (*Engine) TxBegin 

func (e *Engine) TxBegin() (*Engine, error)

func (*Engine) TxCommit 

func (e *Engine) TxCommit() error

func (*Engine) TxExec 

func (e *Engine) TxExec(query string, args ...any) (lastInsertId, rowsAffected int64, err error)

func (*Engine) TxFunc 

func (e *Engine) TxFunc(fn func(tx *Engine) error) (err error)

TxFunc execute transaction by customize function 

auto rollback when function return error

func (*Engine) TxFuncContext 

func (e *Engine) TxFuncContext(ctx context.Context, fn func(ctx context.Context, tx *Engine) error) (err error)

TxFuncContext execute transaction by customize function with context 

auto rollback when function return error

func (*Engine) TxGet 

func (e *Engine) TxGet(dest any, strSql string, args ...any) (count int64, err error)

func (*Engine) TxHandle 

func (e *Engine) TxHandle(handler TxHandler) (err error)

TxHandle execute transaction by customize handler auto rollback when handler return error

func (*Engine) TxRollback 

func (e *Engine) TxRollback() error

func (*Engine) Update 

func (e *Engine) Update() (rowsAffected int64, err error)

Update orm update from model columns... if set, columns will be updated, if none all columns in model will be updated except primary key return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) Upsert 

func (e *Engine) Upsert(strCustomizeUpdates ...string) (lastInsertId int64, err error)

Upsert orm insert or update if key(s) conflict return last insert id and error, if err is not nil must be something wrong, if your primary key is not a int/int64 type, maybe id return 0 NOTE: Model function is must be called before call this function and call OnConflict function when you are on postgresql updates -> customize updates condition when key(s) conflict [MySQL] INSERT INTO messages(id, message_type, unread_count) VALUES('10000', '2', '1', '3') ON DUPLICATE KEY UPDATE message_type=values(message_type), unread_count=unread_count+values(unread_count) --------------------------------------------------------------------------------------------------------------------------------------- e.Model(&do).Table("messages").Upsert("message_type=values(message_type)", "unread_count=unread_count+values(unread_count)") ---------------------------------------------------------------------------------------------------------------------------------------

func (*Engine) Use 

func (e *Engine) Use(strDatabaseName string) (*Engine, error)

Use switch database (returns a new instance)

func (*Engine) Where 

func (e *Engine) Where(query any, args ...any) *Engine

Where orm where condition

type Fetcher 

type Fetcher struct {
	// contains filtered or unexported fields
}

type ID 

type ID = snowflake.ID

type Id 

type Id = ID

type Join 

type Join struct {
	// contains filtered or unexported fields
}

func (*Join) On 

func (j *Join) On(strOn string, args ...interface{}) *Engine

type JoinType 

type JoinType int

func (JoinType) GoString 

func (t JoinType) GoString() string

func (JoinType) String 

func (t JoinType) String() string

func (JoinType) ToKeyWord 

func (t JoinType) ToKeyWord() string

type ModelReflector 

type ModelReflector struct {
	Dict    map[string]interface{} //dictionary of structure tag and value
	Columns []string               //column names
	// contains filtered or unexported fields
}

func (*ModelReflector) ParseModel 

func (s *ModelReflector) ParseModel(tagNames ...string) *ModelReflector

parse struct tag and value to map

type Options 

type Options struct {
	Debug         bool       //enable debug mode
	Max           int        //max active connections
	Idle          int        //max idle connections
	SSH           *SSH       //ssh tunnel server config
	SnowFlake     *SnowFlake //snowflake id config
	DisableOffset bool       //disable page offset for LIMIT (default page no is 1, if true then page no start from 0)
	DefaultLimit  int32      //limit default (0 means no limit)
}

type SSH 

type SSH struct {
	User       string //SSH tunnel server login account
	Password   string //SSH tunnel server login password
	PrivateKey string //SSH tunnel server private key, eg. "/home/test/.ssh/private-key.pem"
	Host       string //SSH tunnel server host [ip or domain], default port 22 if not specified
	// contains filtered or unexported fields
}

func (*SSH) GoString 

func (s *SSH) GoString() string

func (*SSH) String 

func (s *SSH) String() string

type SnowFlake 

type SnowFlake struct {
	NodeId int64 //node id (0~1023)
}

type StringBuilder 

type StringBuilder struct {
	// contains filtered or unexported fields
}

func NewStringBuilder 

func NewStringBuilder() *StringBuilder

func (*StringBuilder) Append 

func (s *StringBuilder) Append(query string, args ...any) *StringBuilder

func (*StringBuilder) Args 

func (s *StringBuilder) Args() []interface{}

func (*StringBuilder) String 

func (s *StringBuilder) String() string

type TxHandler 

type TxHandler interface {
	OnTransaction(tx *Engine) error
}

type UrlInfo 

type UrlInfo struct {
	Scheme     string
	Host       string // host name and port like '127.0.0.1:3306'
	User       string
	Password   string
	Path       string
	Fragment   string
	Opaque     string
	ForceQuery bool
	Queries    map[string]string
}

func ParseUrl 

func ParseUrl(strUrl string) (ui *UrlInfo)

URL have some special characters in password(支持URL中密码包含特殊字符)

func (*UrlInfo) Url 

func (ui *UrlInfo) Url() string

Source Files

View all Source files

Directories

Path Synopsis
models

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.