1. s.db.Table("tag") 就拿不到Model的字段了
db := s.db.Table("tag") //不能,拿不到字段,用Model(&model.Tag{})
if req.TagKeyword != "" {
db = db.Where("title like ? ", "%"+req.TagKeyword+"%")
}
var total int64
db.Count(&total)
2. 连表时用Scan,find报错
err := db.Scan(&tags).Error
3. count顺序
db := s.db.Model(&model.ReasonTemplate{})
if reasonTemplateListReq.Id != 0 {
db = db.Where("id=?", reasonTemplateListReq.Id)
}
db = db.Order("created_at desc")
var total int64
db.Count(&total) //顺序顺序顺序顺序顺序顺序
if reasonTemplateListReq.Limit != 0 {
db = db.Limit(reasonTemplateListReq.Limit)
} else {
db = db.Limit(20)
}
if reasonTemplateListReq.Offset != 0 {
db = db.Offset(reasonTemplateListReq.Offset)
}
if err := db.Scan(&reasonTemplates).Error; err != nil {
logrus.Errorf("DAO层 理由模板列表失败 error: %v", err)
return reasonTemplates, total, err
}
若count在limit和offset后的话,使用count 不能用 Offset 或将Offset值设为 -1(-1代表取消offset限制)。否则会报 :sql: no rows in result set 的错误。
4. .Where("deleted_at is NULL").Count
指定表名的count 不会包含删除排外条件
db.Where("deleted_at is NULL").Count(&total)
连表时要用db.Where("tag.deleted_at is NULL").Count(&total)
5.SELECT count(DISTINCT `author_id`) FROM articles_0;
6. RowsAffected 加 scan
if db.Where("user_id = ? AND game_id = ? ", c.UserId, c.GameId).Scan(&model.ChampionTpl{}).RowsAffected > 99 {
tx.Rollback()
return commonErrors.ErrCreateRecordTooMany()
}
7. 当取出来时,再更新,需要指定字段,防止写入旧数据
if err := s.db.Model(&model.Order{}).Where(&model.Order{ID: order.ID}).Update(&model.Order{
OrderStatus: order.OrderStatus,
}).Error; err != nil {
return err
}
8. gorm:"primary_key"
gorm:"primary_key" 当使用table时
CREATE TABLE `medals` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`icon` varchar(100) DEFAULT NULL,
`desc` varchar(500) DEFAULT NULL,
`type` int(2) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `champion_template` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` varchar(255) DEFAULT '' COMMENT '用户ID',
`game_id` int(11) DEFAULT NULL COMMENT '游戏ID',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`version` varchar(50) DEFAULT NULL,
`champions` varchar(2048) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) USING BTREE,
KEY `index_game_id` (`game_id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4;
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL,
id == 0
func (s *medalStore) UpdateMedal(id model.MedalIDType, medal *model.Medal) error {
***
if id == 0 {
return nil
}
***
return s.db.Model(&model.Medal{}).
Where("id=?", id).
Update(medal).Error
}
func (s *medalStore) DeleteMedal(id model.MedalIDType) error {
if id == 0 {
return nil
}
return s.db.Model(&model.Medal{}).Delete(&model.Medal{ID: id}).Error
}
RowsAffected > 99
func (s *championTplMysqlStore) CreateChampionTpl(c *model.ChampionTpl) error {
tx := s.db.Begin()
db := tx.Model(&model.ChampionTpl{})
if db.Where("user_id = ? AND game_id = ? ", c.UserId, c.GameId).Scan(&model.ChampionTpl{}).RowsAffected > 99 {
tx.Rollback()
return commonErrors.ErrCreateRecordTooMany()
}
if err := db.Create(c).Error; err != nil {
tx.Rollback()
logrus.Errorf("创建阵容模板失败 error: %v", err)
return err
}
if err := tx.Commit().Error; err != nil {
logrus.Errorf("创建阵容模板失败 error: %v", err)
return err
}
return nil
}
必填字段
Mobile string `json:"mobile" binding:"required"`
c.JSON(http.StatusOK, response.Ok(gin.H{"items": medals, "total": total}))
func (s *deliveryStore) UpdateDeliveryInfo(id int, delivery *model.Delivery) error {
if id == 0 {
return nil
}
return s.db.Model(&model.Delivery{}).
Where(&model.Delivery{ID: id}). //只有当使用table的时候才需要tag: gorm:"primary_key"
Update(&model.Delivery{
Company: delivery.Company,
CourierNO: delivery.CourierNO,
}).Error
}
First时会出现notfound err 而获取列表不会
func (s *championTplMysqlStore) GetChampionTplByID(id int64) (*model.ChampionTpl, error) {
championTpl := &model.ChampionTpl{}
err := s.db.Model(&model.ChampionTpl{}).Where("id=?", id).First(championTpl).Error
if err == gorm.ErrRecordNotFound {
return nil, commonErrors.ErrNotFound() //直接返回特定的errcode
}
return championTpl, err
}
测试看打印sql
mysql 批量插入时 每条插入前判断
月表
创建 使用当前时间
更新 使用创建时间
事务是有开销的,所以尽量减少使用
sql能搞定的不用service代码,service代码分页是大问题
Debug:
db := s.db.Table("tag")
db = db.Debug().Joins("left join plate_tag ON plate_tag.tag_id = tag.id")
Keyword拼接:
if req.Keyword != "" {
db = db.Where("tag.title like '%" + req.Keyword + "%' or tag.alias like '%" + req.Keyword + "%'")
}