golang使用gorm+postgres

gorm中文文档

安装gorm和数据库驱动

go get -u gorm.io/gorm
//sqlite驱动
go get -u gorm.io/driver/sqlite
//postgresql驱动
go get -u gorm.io/driver/postgres

链接数据库

var dsn = "host=127.0.0.1 port=5432 user=postgres dbname=SH password=123456"
var Connect *gorm.DB

func init() {
	ConnectionPg()
}

func ConnectionPg() {
	var err error
	Connect, err = gorm.Open(postgres.Open(dsn), &gorm.Config{
		DisableForeignKeyConstraintWhenMigrating: true,
		Logger:                                   logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		fmt.Printf("数据库连接失败----err",err)
	}
}

范围查询：

in

//指定字段的范围查询
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');
// 主键切片条件
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);

最值查询

根据时间–最新的记录

 config.DbConnect.Last(&record)

分页查询

Db.Offset((pageNum-1)*pageSize).Limit(pageSize).Find(...)

显示每次执行的sql

	sql := config.DbConnect.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Model(&record).Updates(record)
	})
	fmt.Println("sql=>",sql)
	//sql=> UPDATE "robot_resets" SET "id"=6,"modified_at"='2022-01-06 14:35:05.655',"finish"='0' WHERE "id" = 6

在postgre中给表定义字符串数组类型的字段

import "github.com/lib/pq"
type GeneralOrder struct {
	gorm.Model
	Type  string
	Key   string
	Param pq.StringArray `gorm:"type:text[]"`//定义字符串类型的字段
	Pid   uint
}

查询该字段中包含某个元素的记录

SELECT * FROM "line" WHERE param @> ARRAY[('4')]::text[]

gorm+golang写法

func (lg *lineGrom) GetRecords(companyKey []string) (error, []GeneralOrder) {
	result := []GeneralOrder{}
	return lg.DB.Debug().Where("param @> ARRAY[(?)]::text[]", companyKey).Find(&result).Error, result
}

根据指定字段值，查询表中某个字段的值，返回结果映射到数组

从表casbin_rule中查询v1等于空的记录，并只返回v0的值，要求返回的是v0组成的数组

	var parentRoles []string
	global.GVA_DB.Table("casbin_rule").Select("v0").Where("v1 = ? ","1").Find(&parentRoles)


var ages []int64
db.Model(&users).Pluck("age", &ages)

var names []string
db.Model(&User{}).Pluck("name", &names)

db.Table("deleted_users").Pluck("name", &names)

将记录按某字段分组，再同时统计每组中的记录数量

统计type为false的记录，并以line_id分组，统计每个line_id有几个数据

查询到map中似乎只能查询一条记录，并且是字段名为key，对应的值为value

func (ag *abnormityGorm) GetRecordsCountByType(abnormityType bool) (error, []map[string]interface{}) {
	var result []map[string]interface{}
	
	err := global.GVA_DB.Model(&do.AbnormityDo{}).Select("line_id","count(*)").Where("type = ? ",abnormityType).Group("line_id").Find(&result).Error
	return err,result
}

根据时间区间，和endpoint数组，查询各个endpoint在这个时间区间中各自最新的那条数据

// 根据时间区间，和endpoint数组，查询各个endpoint在这个时间区间中各自最新的那条数据
func (emdg *ElectricMeterDataGorm) GetLastRecordByEndpointIdListAndTimeBase(endpointIdList []string, start, end string) (error, []do.ElectricMeterDataDo) {
	var result []do.ElectricMeterDataDo
	err := emdg.DB.Raw(`
    WITH ranked_data AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY endpoint_id ORDER BY time DESC) AS rn
        FROM electric_meter_data
        WHERE time >= ?::timestamp AND time <= ?::timestamp
        AND endpoint_id IN (?)
    )
    SELECT * FROM ranked_data WHERE rn = 1;
`, start, end, endpointIdList).Scan(&result).Error
	return err, result
}

在这个查询中，我们使用了窗口函数 ROW_NUMBER() 来为每个 endpoint 的数据按时间降序排列，并给每行分配一个行号。然后在外部查询中，我们选择行号为 1 的数据，即每个 endpoint 在指定时间区间的最新数据。

将 :start_time、:end_time 和 :endpoint_ids 替换为实际的时间范围和 endpoint 数组。

WITH语句

WITH语句是SQL中的一种常见语法，也被称为"公共表表达式"（Common Table Expression, CTE）。该语法允许您在SQL查询中创建一个临时的命名结果集，然后在后续的查询中引用这个临时结果集。

在这种场景下，WITH ranked_data AS (…) 创建了一个名为 ranked_data 的临时表（或者更准确地说是临时结果集），其中包含了以下查询所生成的结果。在后续的查询中，您可以直接引用 ranked_data，就像它是一个表一样。

这种方法允许您将复杂的查询分解成更易维护和理解的部分，并且有助于提高查询的可读性和可维护性。

postgres时区问题

在容器中的postgres很可能的时区是UTC的或者是etc/utc的。可以通过sql语句查看show time zone;
我们使用golang+gorm往数据库插入数据时使用time.Now()给数据的时间字段赋值时，在数据库看见的就是少8小时的时间。如果要使用这个时间字段做查询，那么正常使用加8小时的北京时间就行。