gorm 操作

ncccc1發表於2021-07-28

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    "time"
)

// 定義模型
type User struct {
    gorm.Model
    Name string
    Age  int64
}

func main() {
    db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    // 預設情況下，gorm建立的表將會是結構體名稱的複數形式，如果不想讓它自動複數，可以加一下禁用
    db.SingularTable(true)
    // 2, 把模型與資料庫中的表對應起來
    db.AutoMigrate(&User{})
    // 3, 建立
    //u1 := User{Name: "eryajf", Age: 20}
    //db.Create(&u1)
    //u2 := User{Name: "jinzhu", Age: 22}
    //db.Create(&u2)

    // 4,查詢
    var user []User
    db.Debug().First(&user) // SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("根據主鍵查詢第一條記錄：", user)

    db.Debug().Take(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL LIMIT 1
    fmt.Println("隨機獲取一條記錄：", user)

    db.Debug().Last(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` DESC LIMIT 1
    fmt.Println("根據主鍵查詢最後一條記錄：", user)

    db.Debug().Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL
    fmt.Println("查詢所有的記錄：", user)

    db.Debug().First(&user, 2)      //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` = 2)) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查詢指定的某條記錄：", user) //僅當主鍵為整型時可用

    Where
    db.Debug().Where("name = ?", "jinzhu").First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查詢第一條匹配條件記錄：", user)

    db.Debug().Where("name = ?", "jinzhu").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu'))
    fmt.Println("查詢所有匹配條件的記錄：", user)

    db.Debug().Where("name <> ?", "jinzhu").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name <> 'jinzhu'))
    fmt.Println("查詢name不等於jinzhu的所有記錄：", user)

    db.Debug().Where("name IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name IN ('jinzhu','jinzhu 2')))
    fmt.Println("查詢name在jinzhu和jinzhu 2的所有記錄：", user)

    db.Debug().Where("name LIKE ?", "%jin%").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name LIKE '%jin%'))
    fmt.Println("查詢name包含jin的所有記錄：", user)

    db.Debug().Where("name = ? AND age >= ?", "jinzhu", "20").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu' AND age >= '20'))
    fmt.Println("查詢兩個條件都符合的所有記錄：", user)

    oneDay, _ := time.ParseDuration("-24h")
    lastWeek := time.Now().Add(oneDay * 7)
    db.Debug().Where("updated_at > ?", lastWeek).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((updated_at > '2020-03-01 19:45:11'))
    fmt.Println("查詢一週內更新的使用者記錄：", user)

    today := time.Now()
    db.Debug().Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((created_at BETWEEN '2020-03-01 19:52:51' AND '2020-03-08 19:52:51'))
    fmt.Println("查詢一週內建立的記錄：", user)

    db.Debug().Where(&User{Name: "jinzhu", Age: 22}).First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu') AND (`user`.`age` = 22)) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通過結構體查詢：", user)

    db.Debug().Where(map[string]interface{}{"name": "jinzhu", "age": 22}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu') AND (`user`.`age` = 22))
    fmt.Println("通過map查詢：", user)

    db.Debug().Where([]int64{1, 2}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` IN (1,2)))
    fmt.Println("通過主鍵的切片查詢：", user)

    db.Debug().Not("name", "jinzhu").First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` NOT IN ('jinzhu'))) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查詢name不是jinzhu的第一條記錄：", user)

    db.Debug().Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` NOT IN ('jinzhu','jinzhu 2')))
    fmt.Println("查詢name不在jinzhu或jinzhu2的所有記錄：", user)

    db.Debug().Not([]int64{1, 2, 3}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` NOT IN (1,2,3)))
    fmt.Println("查詢主鍵不是1，2，3的所有記錄：", user)

    db.Debug().Not([]int64{}).First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查詢所有使用者中的第一個：", user)

    db.Debug().Not("name = ?", "jinzhu").First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND (NOT (name = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查詢name不是jinzhu的第一個使用者：", user)

    db.Debug().Not(User{Name: "jinzhu"}).First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` <> 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通過結構體查詢name不是jinzhu的第一個使用者：", user)

    db.Debug().Where("age > ?", 25).Or("age < ?", 23).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((age > 25) OR (age < 23))
    fmt.Println("查詢年齡小於23的或者大於25的所有記錄：", user)

    // struct
    db.Debug().Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu') OR (`user`.`name` = 'jinzhu 2'))
    fmt.Println("結構體：查詢名字是jinzhu的或者是jinzhu 2的所有記錄：", user)

    // map
    db.Debug().Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu') OR (`user`.`name` = 'jinzhu 2'))
    fmt.Println("map：查詢名字是jinzhu的或者是jinzhu 2的所有記錄：", user)

    db.Debug().First(&user, 3)          //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` = 3)) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("根據主鍵查詢指定的某條記錄：", user) //僅當主鍵為整型時可用

    db.Debug().First(&user, "id = ?", "string_primary_key") //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((id = 'string_primary_key')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("根據主鍵是非整形主鍵獲取記錄：", user)

    db.Debug().Find(&user, "name = ?", "jinzhu") //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu'))
    fmt.Println("查詢name為jinzhu的記錄：", user)

    db.Debug().Find(&user, "name <> ? AND age > ? ", "jinzhu", "20") //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name <> 'jinzhu' AND age > '20' ))
    fmt.Println("查詢name不是jinzhu且年齡大於20的記錄：", user)

    db.Debug().Find(&user, User{Age: 20}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`age` = 20))
    fmt.Println("通過結構體查詢年齡是20的所有記錄：", user)

    db.Debug().Find(&user, map[string]interface{}{"age": 20}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`age` = 20))
    fmt.Println("通過map查詢年齡是20的所有記錄：", user)

    db.Debug().FirstOrInit(&user, User{Name: "non_existing"}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查詢name為non_existing的記錄：", user)

    db.Debug().Where(User{Name: "jinzhu"}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通過結構體查詢name為jinzhu的記錄：", user)

    db.Debug().FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通過map查詢name為jinzhu的記錄：", user)

    // 未找到
    db.Debug().Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    // 找到
    db.Debug().Where(User{Name: "jinzhu"}).Attrs(User{Age: 50}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    // 找到
    db.Debug().Where(User{Name: "jinzhu"}).Assign(User{Age: 50}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().FirstOrCreate(&user, User{Name: "non_existing"}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "jinzhu"}).FirstOrCreate(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Select("name", "age").Find(&user) //SELECT name FROM `user`  WHERE `user`.`deleted_at` IS NULL'age'
    fmt.Println("查詢表中name欄位引數為age的記錄：", user)

    db.Debug().Select([]string{"name", "age"}).Find(&user) //SELECT name, age FROM `user`  WHERE `user`.`deleted_at` IS NULL
    fmt.Println("列出表中name與age欄位：", user)

    db.Debug().Order("age desc,name").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY age desc,name
    fmt.Println("根據年齡排序來查詢：", user)

    db.Debug().Order("age desc").Order("name").Find(&user)
    fmt.Println("根據多個條件排序查詢：", user)

}

來自連結

本作品採用《CC 協議》，轉載必須註明作者和本文連結

Gorm框架
2024-03-15
GoORM框架
gorm操作sqlite3，高併發讀寫如何避免鎖庫？
2023-03-09
GoORMSQLite
GORM基本用法
2024-10-17
GoORM
gorm 簡單使用
2018-05-23
GoORM
Gorm常用程式碼片段
2024-05-07
GoORM
GORM學習入門
2022-06-23
GoORM
GORM subquery 子查詢
2021-06-25
GoORM
基於GORM框架的web後端開發（一）: GORM連線MySQL
2022-06-17
GoORM框架Web後端MySql
GORM many2many、many2one、one2many關聯表的操作
2024-03-06
GoORM
golang 時間轉換，不注意將引發Gorm等mysql操作儲存時間多
2021-09-09
GolangORMMySql
Go微服務框架go-kratos實戰03：使用 gorm 實現增刪改查操作
2022-06-02
Go微服務框架ORM
gorm踩坑記錄(一)
2020-11-06
GoORM
gorm忽略報錯: record not found
2024-05-29
GoORM
GORM之ErrRecordNotFound採坑記錄
2019-12-22
GoORM
Golang雜談-gorm整合雪花id
2024-08-19
GolangORM
Gin + GORM 入門到實戰
2022-11-24
GoORM
使用 Mux, Go, PostgreSQL, GORM 構建 API
2019-11-15
UXGoSQLORMAPI
使用 gin + gorm + seesion 完成 Laravel 的 make:auth
2019-09-11
GoORMLaravel
Iris 使用 casbin + gorm 構建許可權
2020-01-11
GoORM
gorm是如何保證協程安全的
2022-04-02
GoORM
gin+gorm+router 快速搭建 crud restful API 介面
2018-05-24
GoORMRESTAPI
GORM 之 for (rows.Next) 提前退出別忘了 Close
2020-01-07
GoORM
Go之Gorm和BeegoORM簡介及配置使用
2020-08-09
GoORM
GORM之for(rows.Next)提前退出別忘了Close
2020-01-05
GoORM
gorm 使用map實現in 條件查詢用法
2022-07-11
GoORM
GORM 如何讀取別名欄位的值
2021-05-17
GoORM
寫了一個 gorm 樂觀鎖外掛
2021-03-15
GoORM
向Gorm大聲說拜拜，Aorm你值得擁有
2022-12-15
GoORM
GORM 自定義time.time日期時間輸出格式
2024-03-13
GoORM
go 連線資料庫 - GORM學習 - sql查詢
2020-12-05
Go資料庫ORMSQL
GO專案實戰 — Gorm格式化時間欄位
2022-07-04
GoORM
Golang 將資料庫轉換為gorm結構和RESTful api
2018-12-06
Golang資料庫ORMRESTAPI
Gorm 的使用心得和一些常用擴充套件 (一)
2019-09-12
GoORM套件
結合 gin+gorm+go-Redis 寫一個基礎 API（中篇）
2020-06-11
GoORMRedisAPI
結合 gin+gorm+go-Redis 寫一個基礎 API（下篇）
2020-06-15
GoORMRedisAPI
結合 gin+gorm+go-Redis 寫一個基礎 API（上篇）
2020-06-10
GoORMRedisAPI
go-carbon 1.1.0版本釋出了，增加對gorm的支援
2020-09-10
GoORM
gorm 關係一對一,一對多,多對多查詢
2019-12-30
GoORM

gorm 操作

相關文章