一、 我们在查询时发现表中数据出现重复怎么办,即多条返回信息
例如:
+----+--------------+------------+------------+---------------------+--------------+
| id | name | ISBN | translator | date | publisher_id |
+----+--------------+------------+------------+---------------------+--------------+
| 1 | 翦商 | 132456789 | qiqi | 2020-01-23 | 110 |
| 3 | 三国演义 | 875131532 | wangwu | 2002-08-23 | 103 |
| 4 | 爱 | 746464878 | 海燕 | 2023-07-17 16:47:41 | 110 |
| 5 | 朝花夕拾 | 265748961 | sunqi | 1902-12-21 | 105 |
..............
| 12 | 变量 | 201320385 | shishisi | 2008-07-19 | 112 |
| 13 | 光荣与梦想 | 78654132 | kkkk | 2020-01-23 | 110 |
| 14 | 光荣与梦想 | NULL | kkkk | 2020-01-23 | 110 |
| 15 | 人生海海 | 464646465 | 麦家 | 2010-11-23 | 310 |
| 18 | 许三观卖血记 | 78945646 | 杜甫 | 1976-02-10 | 8936 |
+----+--------------+------------+------------+---------------------+--------------+
上表中出现两个“光荣与梦想”的书名,如何在请求返回的时候把这两条数据都返回?就像这样:
{
"message": "",
"Data": [
{
"name": "光荣与梦想",
"cc": "dd"
},
{
"name": "光荣与梦想",
"cc": "dd"
}
]
}
用数组承载返回值,分成一下几种情况:
1、集合类型的数组
// 集合类型的数组,InsertRes是一个集合
var Se []v1.InsertRes
Sr1 := new(v1.InsertRes)
Sr1.Message = "number 1"
Sr1.Date.ISBN = "132134564"
Sr1.Date.Name = "az"
Sr1.Date.Publisher_id = 456456
Sr2 := new(v1.InsertRes)
Sr2.Message = "number 2"
Sr2.Date.ISBN = "9637894625"
Sr2.Date.Name = "tj"
Sr2.Date.Publisher_id = 123456
Se = append(Se, *Sr1)
Se = append(Se, *Sr2)
fmt.Println(Se)
fmt.Println(Se[0])
fmt.Println(Se[1])
fmt.Println(Se[0].Date.Name)
fmt.Println(Se[0].Date.ISBN)
// 输出
[{number 1 {0 az 132134564 456456}} {number 2 {0 tj 9637894625 123456}}]
{number 1 {0 az 132134564 456456}}
{number 2 {0 tj 9637894625 123456}}
az
132134564
2、切片类型的数组
all, _ := g.Model("library").Where("name", "光荣与梦想").WhereOr("ISBN", "").WhereOr("publisher_id", 0).All()
//var Se []v1.BookInformation
s3 := make([]map[string]interface{}, 0)
for _, record := range all {
s2 := make(map[string]interface{}, 1)
for j, value := range record {
s2[gconv.String(j)] = value
}
fmt.Println(s2)
s3 = append(s3, s2)
}
fmt.Println(s3)
// 输出
map[ISBN:78654132 date:2020-01-23 id:13 name:光荣与梦想 publisher_id:110 translator:kkkk]
map[ISBN: date:2020-01-23 id:14 name:光荣与梦想 publisher_id:110 translator:kkkk]
[map[ISBN:78654132 date:2020-01-23 id:13 name:光荣与梦想 publisher_id:110 translator:kkkk] map[ISBN: date:2020-01-23 id:14 name:光荣与梦想 publisher_id:110 translator:kkkk]]
--- PASS: TestU (0.01s)
二、 通过一个字段查询到表中多条重复的数据,为了降重,再添加额外限制字段,以此锁定目标数据
采用数据库的链式操作
// pub := 输入的publisher_id
pub := 10086
obj := g.Model("library").Where("name", "许三观卖血记")
// 是0就不执行AND (`publisher_id`=10086),
// 只执行WHERE (`name`='许三观卖血记') OR (`ISBN`='')
// 不是0的话,就把这句加上,并且在Where("name", "许三观卖血记")与Where("publisher_id", pub)之间构造and
if pub != 0 {
obj = obj.Where("publisher_id", pub)
}
obj = obj.WhereOr("ISBN", "")
// 不管之前怎么样,在这里统一才执行
all, _ := obj.All()
//var Se []v1.BookInformation
fmt.Println(all)
**踩坑:**在构造查询语句的时候,要注意and和or执行的优先级
# 这时候,程序会先查询满足(`name`='枪花') or (`id`=23)的数据,然后将匹配结果再与(`ISBN`='')
# (`ISBN`='')执行and匹配,故结果为两条数据。
mysql> SELECT * FROM `library` WHERE (`name`='枪花') or (`id`=23) and (`ISBN`='')
(`ISBN`='');
+----+------+-----------+------------+------------+--------------+
| id | name | ISBN | translator | date | publisher_id |
+----+------+-----------+------------+------------+--------------+
| 22 | 枪花 | 078994323 | 李白 | 1996-12-10 | 135 |
| 23 | 枪花 | 078994323 | 李白 | | 135 |
+----+------+-----------+------------+------------+--------------+
如果想通过添加and来限制的话,应该:
mysql> SELECT * FROM `library` WHERE (`name`='枪花') AND (`id`=23) OR (`ISBN`='');
+----+------+-----------+------------+------+--------------+
| id | name | ISBN | translator | date | publisher_id |
+----+------+-----------+------------+------+--------------+
| 23 | 枪花 | 078994323 | 李白 | | 135 |
+----+------+-----------+------------+------+--------------+
最终实现:
object := g.Model("library").Where("name", name)
if PublisherId != 0 {
object = object.Where("publisher_id", PublisherId)
}
object = object.WhereOr("ISBN", ISBN)
all, err := object.All()
三、 数据库事务操作据
// 开启事务
// 当给定的闭包方法返回的error为nil时,闭包执行结束后当前事务自动执行Commit提交操作;否则自动执行Rollback回滚操作。
dao.GfUser.Transaction(ctx, func(ctx context.Context, tx gdb.TX) error {
// 为了防止空字段覆盖原始字段,提前设置一个map对象备份(倒腾)
data := gmap.New()
// 不为"",即证明有值,也就是需要修改,其余的不用修改,晕了
if gconv.String(in.Information.Name) != "" {
data.Set("name", in.Information.Name)
}
if gconv.String(in.Information.ISBN) != "" {
data.Set("ISBN", in.Information.ISBN)
}
if gconv.String(in.Information.Translator) != "" {
data.Set("translator", in.Information.Translator)
}
if gconv.String(in.Information.Date) != "" {
data.Set("date", in.Information.Date)
}
if gconv.Int(in.Information.Publisher_id) != 0 {
data.Set("publisher_id", in.Information.Publisher_id)
}
_, err = g.Model("library").Data(data.Map()).Where("id", val).Update()
if err != nil {
return err
} else {
return nil
}
})
注意:这里的条件判断操作和 “二、采用数据库的链式操作” 中的操作很类似,通过设置一个变量来测试是否满足条件