Go语言数据库管理：深入gorm版本1

github地址:

https://github.com/jinzhu/gorm

文档:

https://v1.gorm.io/docs/index.html

中文文档

https://v1.gorm.io/zh_CN/docs/

实例1: 数据库连接

package mainimport (    "fmt"    "github.com/jinzhu/gorm"    _ "github.com/jinzhu/gorm/dialects/mysql")func main() {    db, err := gorm.Open("mysql", "root:guo456@tcp(127.0.0.1:3306)/go_gateway?charset=utf8&parseTime=True&loc=Local")    if err != nil {    fmt.Println("数据库操作失败", err)    }    defer db.Close()}

使用 MySQL 的时间字段遇到如下两个问题

1.使用 go-sql-driver 来连接 MySQL 数据库, 获取的时区默认是 UTC +0 的, 与本地的东八区是有区别, 在业务处理中会出现问题

2.获取 MySQL 中的日期, 是 string 类型, 需要在代码中用 time.Parse 进行转化

解决方案：

在连接的 dsn 中, 添加 parseTime=true 和 loc=Local, 此处的 local 可以换为具体的时区(Asia/Shanghai)

数据库链接：

root:root@tcp(localhost:3306)/abc?charset=utf8

改为

root:root@tcp(localhost:3306)/abc?charset=utf8&parseTime=True&loc=Local

timezone := "'Asia/Shanghai'"db, err := gorm.Open("mysql", "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8&parseTime=True&loc=Local&time_zone="+url.QueryEscape(timezone)

实例2: 增删改查

package mainimport (//"net/url""github.com/jinzhu/gorm"_ "github.com/jinzhu/gorm/dialects/mysql")type Product struct {gorm.ModelCode stringPrice uint}func main() {db, err := gorm.Open("mysql", "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8&parseTime=True&loc=Local")//timezone := "'Asia/Shanghai'"//db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8mb4&parseTime=true&loc=Local&time_zone="+url.QueryEscape(timezone))if err != nil {panic("failed to connect database")}defer db.Close()// Migrate the schemadb.AutoMigrate(&Product{})// Createdb.Create(&Product{Code: "L1212", Price: 1000})// Readvar product Productdb.First(&product, 1) // find product with id 1db.First(&product, "code = ?", "L1212") // find product with code l1212// Update - update product's price to 2000db.Model(&product).Update("Price", 2000)// Delete - delete productdb.Delete(&product)}/*mysql> CREATE DATABASE IF NOT EXISTS blog DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;mysql> use blogmysql> show tables;+----------------+| Tables_in_blog |+----------------+| products |+----------------+1 row in set (0.00 sec)mysql> select * from products;+----+---------------------+---------------------+---------------------+-------+-------+| id | created_at | updated_at | deleted_at | code | price |+----+---------------------+---------------------+---------------------+-------+-------+| 19 | 2020-12-16 15:44:25 | 2020-12-16 15:44:25 | 2020-12-16 15:44:25 | L1212 | 2000 || 20 | 2020-12-16 15:44:33 | 2020-12-16 15:44:33 | 2020-12-16 15:44:33 | L1212 | 2000 |+----+---------------------+---------------------+---------------------+-------+-------+2 rows in set (0.00 sec)注意 以上 create_at、updated_at、deleted_at三个参数 由DSN 中 loc=Local 参数决定时间为本地时区表名是结构体名称的复数形式 如: Product-> productstype User struct {} // 默认表名是`users`基本模型定义gorm.Model，包括字段ID，CreatedAt，UpdatedAt，DeletedAt*/

数据表和字段的大小写和驼峰式写法

package mainimport (//"net/url""github.com/jinzhu/gorm"_ "github.com/jinzhu/gorm/dialects/mysql")type Product struct { //数据库名称 -> Products Address -> addresses    gorm.Model //id created_at updated_at deleted_at    Code string //code    Price uint //price    TotalAmount int64 //total_amount}func main() {    db, err := gorm.Open("mysql", "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8&parseTime=True&loc=Local")    //timezone := "'Asia/Shanghai'"    //db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8mb4&parseTime=true&loc=Local&time_zone="+url.QueryEscape(timezone))    if err != nil {        panic("failed to connect database")    }    defer db.Close()    // Migrate the schema    db.AutoMigrate(&Product{})    // Create    db.Create(&Product{Code: "L1212", Price: 1000})    // Read    var product Product    db.First(&product, 1) // find product with id 1    db.First(&product, "code = ?", "L1212") // find product with code l1212    // Update - update product's price to 2000    // db.Model(&product).Update("price", 2000)    // Read 读取多条记录    var list []Product    res := db.Find(&list)    fmt.Println(list)    db.Model(&product).Update("total_amount", "8000") // 此时写 total_amount 和 TotalAmount 均可以    // db.Model(&product).Update("TotalAmount", 8000)    // Delete - delete product    db.Delete(&product)}

mysql> use blogDatabase changedmysql> show tables;+----------------+| Tables_in_blog |+----------------+| addresses || products |+----------------+2 rows in set (0.00 sec)mysql> desc products;+--------------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+------------------+------+-----+---------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || created_at | datetime | YES | | NULL | || updated_at | datetime | YES | | NULL | || deleted_at | datetime | YES | MUL | NULL | || code | varchar(255) | YES | | NULL | || price | int(10) unsigned | YES | | NULL | || total_amount | bigint(20) | YES | | NULL | |+--------------+------------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

如何获取影响行数?

package mainimport ("fmt"//"net/url""github.com/jinzhu/gorm"_ "github.com/jinzhu/gorm/dialects/mysql")type Product struct { //数据库名称 -> Products Address -> addresses    gorm.Model //id created_at updated_at deleted_at    Code string //code    Price uint //price    TotalAmount int64 //total_amount}func main() {    db, err := gorm.Open("mysql", "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8&parseTime=True&loc=Local")    if err != nil {        panic("failed to connect database")    }    defer db.Close()    // 加价一条记录    //db.Create(&Product{Code: "L1212", Price: 1000})    // Read    var product Product    db.First(&product, 1)    res := db.First(&product, "code = ?", "L1212")    fmt.Println(res.Error, res.RowsAffected) // Error:<nil> RowsAffected:1;    /*    RowsAffected 用于增删改查影响的行数 Error 用于判断是否查询到    record not found RowsAffected:0 ;    Error:<nil> RowsAffected:1;    */    // 判断是否查询到记录的方式    if res.Error != nil {        fmt.Println("没有查询到记录")    }    // 判断是否查询到记录的方式    if res.RowsAffected > 0 {        fmt.Println("查询到记录")    }    /*    重要的结论:    RowsAffected 包括增删改查    */}

其他常用的方法:

//删除数据, 软删除(存在deleted_at字段自动软删除, 给deleted_at字段赋值就标记为删除)db.Delete(&user)

//物理删除db.Unscoped().Delete(&user)

//自增: price字段名称DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))

gorm日志库

https://github.com/moul/zapgorm

zapgorm - Uber 的 Zap 日志驱动程序, 适用版本gorm V1

注意:

GORM V2 moved to https://github.com/go-gorm/gorm

GORM V1 Doc https://v1.gorm.io/