06 go操作mysql(gorm-CRUD)
01gorm基础.go
package main
// 01gorm基础
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"time"
)
/*说明:
(1) gorm官方文档地址
https://gorm.io/zh_CN/docs/
(2) 安装包
go get -u gorm.io/driver/mysql
go get -u gorm.io/gorm
(3) 建库建表
使用docker启动一个mysql数据库
docker run -d --name db -p 3306:3306 -v /mysql/mysqldata:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=LiuChang@=123456 mysql:5.7 --character-set-server=utf8mb4
创建一个数据库
CREATE DATABASE gorm02 DEFAULT CHARSET=utf8mb4;
(4) 命名策略
1) 大驼峰命名法(CamelCase)
通常类名需要使用大驼峰命名法
2) 小驼峰命名法(lowerCamelCase)
通常函数方法名、参数名、成员变量、局部变量需要使用小驼峰命名法
3) 蛇形命名法(snake_case)
通常测试方法名、常量、枚举名称需要使用蛇形命名法
4) 串式命名法(kebab-case)
项目文件夹名称可以使用串式命名法
(5) null问题
1) NULL值 与任何值相加 返回结果均是NULL。
2) null和任何值都不能⽐较，null 只能⽤ is null 或者 is not null 来判断。
(6) 为表字段设置 unique 参数的同时也会建立 unique 索引，相反同理。
*/
// User 结构体
type User struct {
gorm.Model
Name string `gorm:"type:varchar(120);not null;unique"`
Age int8 `gorm:"type:int(2);not null"`
Birthday time.Time
Email sql.NullString `gorm:"type:varchar(120);uniqueIndex"`
Address *string `gorm:"type:varchar(120);index:addr"` // 给address字段创建名为addr的索引
MemberNumber string `gorm:"type:varchar(120);unique;not null"` // 设置会员号(member number)唯一并且不为空
Role string `gorm:"size:120;not null"` // 设置字段大小为120
IgnoreMe int `gorm:"-"` // 忽略本字段
}
/*1.1 连接mysql数据库*/
var db *gorm.DB
func initDB() (err error) {
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm02?charset=utf8mb4&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
fmt.Printf("connect db mysql failed: %v\n", err)
return err
}
return nil
}
func main() {
/*1.1 连接mysql数据库*/
err := initDB()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("mysql数据库连接成功!")
/*1.2 GORM Model定义*/
/*说明:
在使用ORM工具时，通常我们需要在代码中定义模型(Models)与数据库中的数据表进行映射，在GORM中模型(Models)通
常是正常定义的结构体、基本的go类型或它们的指针。同时也支持sql.Scanner及driver.Valuer接口(interfaces)。
*/
/*1.2.1 gorm.Model*/
// 为了方便模型定义，GORM内置了一个gorm.Model结构体。gorm.Model是一个包含了ID, CreatedAt, UpdatedAt, DeletedAt四个字段的Golang结构体。
// gorm.Model 定义
//type Model struct {
//ID uint `gorm:"primarykey"`
//CreatedAt time.Time
//UpdatedAt time.Time
//DeletedAt DeletedAt `gorm:"index"`
//}
// 可以将gorm.Model嵌入到你自己的模型中
// 将 `ID`, `CreatedAt`, `UpdatedAt`, `DeletedAt`字段注入到`User`模型中
//type User struct {
//gorm.Model
//Name string
//}
// 也可以完全自己定义模型
// 不使用gorm.Model，自行定义模型
//type User struct {
//ID int
//Name string
//}
/*1.2.2 结构体标记(tags)*/
/*使用结构体声明模型时，tag 是可选的，GORM 支持以下 tag，tag 名大小写不敏感，但建议使用 camelCase 风格:
结构体标记(tag) 描述
column 指定列名
type 指定列数据类型
size 指定列大小, 默认值255
primaryKey 将列指定为主键
unique 将列指定为唯一
default 指定列默认值
precision 指定列精度
not null 将列指定为非 NULL
autoIncrement 指定列是否为自增类型
index 创建具有或不带名称的索引, 如果多个索引同名则创建复合索引
uniqueIndex 和 index 类似，只不过创建的是唯一索引
embedded 将结构体设置为嵌入
embeddedPrefix 设置嵌入结构体的前缀
- 忽略此字段
*/
/*关联相关标记
结构体标记(tag) 描述
foreignKey 指定当前模型的列作为连接表的外键
references 指定引用表的列名，其将被映射为连接表外键
polymorphic 指定多态类型，比如模型名
polymorphicValue 指定多态值、默认表名
many2many 指定连接表表名
joinForeignKey 指定连接表的外键列名，其将被映射到当前表
joinReferences 指定连接表的外键列名，其将被映射到引用表
constraint 关系约束，例如：OnUpdate、OnDelete
*/
/*1.3 主键、表名、列名的约定*/
// GORM 倾向于约定，而不是配置。默认情况下，GORM 使用 ID 作为主键，使用结构体名的 蛇形复数 作为表名，字段名的 蛇形 作为列名，并使用 CreatedAt、UpdatedAt 字段追踪创建、更新时间
/*1.3.1 主键(Primary Key)*/
// 默认情况下，GORM 会使用 ID 作为表的主键
// 默认情况下，整型 PrioritizedPrimaryField 启用了 AutoIncrement，要禁用它，您需要为整型字段关闭 autoIncrement，例如: CategoryID uint64 `gorm:"primaryKey;autoIncrement:false"`
//type User struct {
//ID string // 默认情况下，名为 `ID` 的字段会作为表的主键
//Name string
//}
// 可以通过标签 primaryKey 将其它字段设为主键
// 将 `UUID` 设为主键
//type Animal struct {
//ID int64
//UUID string `gorm:"primaryKey"`
//Name string
//Age int64
//}
// 复合主键
//type Product struct {
//ID string `gorm:"primaryKey"`
//LanguageCode string `gorm:"primaryKey"`
//Code string
//Name string
//}
/*1.3.2 表名(Table Name)*/
// GORM 使用结构体名的 蛇形命名 作为表名，对于结构体 User，根据约定，其表名为 users。
// 默认表名: 大驼峰命名的结构体变为蛇形命名的表名，且该表名为复数。
//// TableName 实现 Tabler 接口来更改默认表名，TableName 会将 User 的表名重写为 `userLiuChang`
//func (User) TableName() string {
//return "userLiuChang"
//}
// 使用User结构体创建名为`user_liu_chang`的表
//err = db.Table("user_liu_chang").AutoMigrate(&User{})
//if err != nil {
//fmt.Println(err)
//return
//}
/*1.3.3 列名(Column Name)*/
// 根据约定，数据表的列名使用的是 struct 字段名的 蛇形命名。
// 默认列名: 大驼峰命名的结构体字段名变为蛇形命名的表字段名。
//type User struct {
//ID uint // 列名是 `id`
//Name string // 列名是 `name`
//Birthday time.Time // 列名是 `birthday`
//CreatedAt time.Time // 列名是 `created_at`
//}
// 可以使用 column 标签来覆盖列名
//type Animal struct {
//AnimalID int64 `gorm:"column:beast_id"` // 将列名设为 `beast_id`
//Birthday time.Time `gorm:"column:day_of_the_beast"` // 将列名设为 `day_of_the_beast`
//Age int64 `gorm:"column:age_of_the_beast"` // 将列名设为 `age_of_the_beast`
//}
/*1.3.4 时间戳跟踪*/
/*说明:
(1) CreatedAt
如果模型有CreatedAt字段，该字段的值将会是初次创建记录的时间。
(2) UpdatedAt
如果模型有UpdatedAt字段，该字段的值将会是每次更新记录的时间。
(3) DeletedAt
如果模型有DeletedAt字段，调用Delete删除该记录时，将会设置DeletedAt字段为当前时间，而不是直接将记录从数据库中删除。
*/
/*1.4 GORM操作MySQL*/
/*说明:
(1) AutoMigrate 用于自动迁移您的 schema，保持您的 schema 是最新的。
(2) AutoMigrate 会创建表、缺失的外键、约束、列和索引。如果大小、精度、是否为空可以更改，则 AutoMigrate 会改变列的
类型。出于保护您数据的目的，它不会删除未使用的列。
*/
err = db.AutoMigrate(&User{})
if err != nil {
fmt.Println(err)
return
}
fmt.Println("users表创建成功")
// 创建的表结构查看 TableStruct.txt 文件。
}
TableStruct.txt
// User 结构体
type User struct {
gorm.Model
Name string `gorm:"type:varchar(120);not null;unique"`
Age int8 `gorm:"type:int(2);not null"`
Birthday time.Time
Email sql.NullString `gorm:"type:varchar(120);uniqueIndex"`
Address *string `gorm:"type:varchar(120);index:addr"` // 给address字段创建名为addr的索引
MemberNumber string `gorm:"type:varchar(120);unique;not null"` // 设置会员号(member number)唯一并且不为空
Role string `gorm:"size:120;not null"` // 设置字段大小为120
IgnoreMe int `gorm:"-"` // 忽略本字段
}
// 表结构
mysql> show tables;
+------------------+
| Tables_in_gorm02 |
+------------------+
| users |
+------------------+
1 row in set
mysql> desc users;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| created_at | datetime(3) | YES | | NULL | |
| updated_at | datetime(3) | YES | | NULL | |
| deleted_at | datetime(3) | YES | MUL | NULL | |
| name | varchar(120) | NO | UNI | NULL | |
| age | int(2) | NO | | NULL | |
| birthday | datetime(3) | YES | | NULL | |
| email | varchar(120) | YES | UNI | NULL | |
| address | varchar(120) | YES | MUL | NULL | |
| member_number | varchar(120) | NO | UNI | NULL | |
| role | varchar(120) | NO | | NULL | |
+---------------+---------------------+------+-----+---------+----------------+
11 rows in set
mysql> show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime(3) DEFAULT NULL,
`updated_at` datetime(3) DEFAULT NULL,
`deleted_at` datetime(3) DEFAULT NULL,
`name` varchar(120) NOT NULL,
`age` int(2) NOT NULL,
`birthday` datetime(3) DEFAULT NULL,
`email` varchar(120) DEFAULT NULL,
`address` varchar(120) DEFAULT NULL,
`member_number` varchar(120) NOT NULL,
`role` varchar(120) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `member_number` (`member_number`),
UNIQUE KEY `idx_users_email` (`email`),
KEY `idx_users_deleted_at` (`deleted_at`),
KEY `addr` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql>
2 02gormCRUD
2.1 01Create
01Create.go
package main
// 01Create
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
// 02gorm操作
/*说明:
(1) 参考文档
https://gorm.io/zh_CN/docs/create.html
(2) CRUD通常指数据库的增删改查操作。
(3) 创建数据库
1) 使用docker启动一个mysql数据库
docker run -d --name db -p 3306:3306 -v /mysql/mysqldata:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=LiuChang@=123456 mysql:5.7 --character-set-server=utf8mb4
2) 创建一个库
CREATE DATABASE gorm02 DEFAULT CHARSET=utf8mb4;
*/
/*2.0 连接数据库*/
var db *gorm.DB
func initDB() (err error) {
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm02?charset=utf8mb4&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
fmt.Printf("connect db mysql failed: %v\n", err)
return err
}
return nil
}
func main() {
/*2.0 连接数据库*/
err := initDB()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("mysql数据库连接成功!")
/*2.1 创建*/
/*2.1.1 默认值*/
//// 可以通过 tag 定义字段的默认值
//type User struct {
// gorm.Model
// // Name字段传入的值是对应字段类型的零值时存入的值是default值。
// Name string `gorm:"type:varchar(100);default:'LiuC'"`
// // Age字段传入的值是对应字段类型的零值时存入的值是对应字段类型的零值。
// Age int64 `gorm:"type:int(2)"`
//}
//
//err = db.AutoMigrate(&User{})
//if err != nil {
// fmt.Println(err)
// return
//}
//fmt.Println("users 表创建成功")
//
//// 插入数据
//// 注意: 通过tag定义字段的默认值后，所有字段的零值, 比如0, "", false或者其它零值，都不会保存到数据库内，但会使用他们的默认值。
//var user = User{Name: "", Age: 99}
//db.Debug().Create(&user)
///*查看数据库内容:
//INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-14 14:40:59.469','2022-06-14 14:40:59.469',NULL,'LiuC',99)
//mysql> select * from users;
//+----+-------------------------+-------------------------+------------+------+-----+
//| id | created_at | updated_at | deleted_at | name | age |
//+----+-------------------------+-------------------------+------------+------+-----+
//| 1 | 2022-06-14 14:40:59.469 | 2022-06-14 14:40:59.469 | NULL | LiuC | 99 |
//+----+-------------------------+-------------------------+------------+------+-----+
//1 row in set
//mysql>
//*/
/*2.1.2 使用指针方式实现零值存入数据库*/
//// 使用指针
//type User struct {
// gorm.Model
// // Name字段传入的值是对应字段类型的零值时存入的值是default值。
// // Name字段传入的值是对应字段类型的new指针时存入的值是对应类型字段的零值。
// Name *string `gorm:"type:varchar(100);default:'LiuC'"`
// // Age字段传入的值是对应字段类型的零值时存入的值是对应字段类型的零值。
// Age int64 `gorm:"type:int(2)"`
//}
//
//err = db.AutoMigrate(&User{})
//if err != nil {
// fmt.Println(err)
// return
//}
//fmt.Println("users 表创建成功")
//
//user := User{Name: new(string), Age: 18}
//db.Debug().Create(&user) // 此时数据库中该条记录name字段的值就是''
///*查看结果:
//INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-14 15:25:57.88','2022-06-14 15:25:57.88',NULL,'',18)
//mysql> select * from users;
//+----+-------------------------+-------------------------+------------+------+-----+
//| id | created_at | updated_at | deleted_at | name | age |
//+----+-------------------------+-------------------------+------------+------+-----+
//| 1 | 2022-06-14 15:25:57.880 | 2022-06-14 15:25:57.880 | NULL | | 18 |
//+----+-------------------------+-------------------------+------------+------+-----+
//1 row in set
//mysql>
//*/
/*2.1.3 使用Scanner/Valuer接口方式实现零值存入数据库*/
//// 使用Scanner/Valuer
//type User struct {
// gorm.Model
// // sql.NullString 实现了Scanner/Valuer接口
// // Name字段传入的值是对应字段类型的零值时存入的值是default值。
// Name sql.NullString `gorm:"type:varchar(100);default:'LiuC'"`
// // Age字段传入的值是对应字段类型的零值时存入的值是对应字段类型的零值。
// Age int64 `gorm:"type:int(2)"`
//}
//
//err = db.AutoMigrate(&User{})
//if err != nil {
// fmt.Println(err)
// return
//}
//fmt.Println("users 表创建成功")
//
//// Valid is true if String is not NULL
//user1 := User{Name: sql.NullString{"", true}, Age: 18}
////INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-14 16:08:00.152','2022-06-14 16:08:00.152',NULL,'',18)
//user2 := User{Name: sql.NullString{"", false}, Age: 19}
////INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-14 16:08:00.262','2022-06-14 16:08:00.262',NULL,'LiuC',19)
//user3 := User{Name: sql.NullString{"liuChang", true}, Age: 20}
////INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-14 16:08:00.35','2022-06-14 16:08:00.35',NULL,'liuChang',20)
//user4 := User{Name: sql.NullString{"liuChang", false}, Age: 21}
////INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-14 16:08:00.415','2022-06-14 16:08:00.415',NULL,NULL,21)
//db.Debug().Create(&user1)
//db.Debug().Create(&user2)
//db.Debug().Create(&user3)
//db.Debug().Create(&user4)
///*查看结果:
//mysql> select * from users;
//+----+-------------------------+-------------------------+------------+----------+-----+
//| id | created_at | updated_at | deleted_at | name | age |
//+----+-------------------------+-------------------------+------------+----------+-----+
//| 1 | 2022-06-14 16:08:00.152 | 2022-06-14 16:08:00.152 | NULL | | 18 |
//| 2 | 2022-06-14 16:08:00.262 | 2022-06-14 16:08:00.262 | NULL | LiuC | 19 |
//| 3 | 2022-06-14 16:08:00.350 | 2022-06-14 16:08:00.350 | NULL | liuChang | 20 |
//| 4 | 2022-06-14 16:08:00.415 | 2022-06-14 16:08:00.415 | NULL | NULL | 21 |
//+----+-------------------------+-------------------------+------------+----------+-----+
//4 rows in set
//mysql>
//*/
}
2.2 02Query
02Query.go
package main
// 02Query
/*参考官方文档:
(1) 查询: https://gorm.io/zh_CN/docs/query.html
(2) 高级查询: https://gorm.io/zh_CN/docs/advanced_query.html
(3) 创建一个gorm02库
CREATE DATABASE gorm02 DEFAULT CHARSET=utf8mb4;
*/
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
/*2.1 连接数据库*/
var db *gorm.DB
func initDB() (err error) {
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm02?charset=utf8mb4&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
fmt.Printf("connect db mysql failed: %v\n", err)
return err
}
return nil
}
func main() {
/*2.1 连接数据库*/
err := initDB()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("mysql数据库连接成功!")
type User struct {
gorm.Model
// Name字段传入的值是对应字段类型的零值时存入的值是default值。
Name string `gorm:"type:varchar(100);default:'LiuC'"`
// Age字段传入的值是对应字段类型的零值时存入的值是对应字段类型的零值。
Age int64 `gorm:"type:int(2)"`
}
err = db.AutoMigrate(&User{})
if err != nil {
fmt.Println(err)
return
}
fmt.Println("users 表创建成功")
// 插入测试数据:
/*user1 := User{Name: "zhangSan", Age: 13}
user2 := User{Name: "liSi", Age: 14}
user3 := User{Name: "wangEr", Age: 13}
db.Debug().Create(&user1)
db.Debug().Create(&user2)
db.Debug().Create(&user3)
*/
/*查看插入的数据:
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
/***********************普通查询*******************/
/*2.2 一般查询*/
//2.2.1 根据主键查询第一条记录
//user1 := User{}
//db.Debug().First(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.2.2 随机获取一条记录
//user2 := User{}
//db.Debug().Take(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 1
{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.2.3 根据主键查询最后一条记录
//user3 := User{}
//db.Debug().Last(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY `users`.`id` DESC LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
//2.2.4 查询所有的记录
//user4 := []User{}
//db.Debug().Find(&user4)
//fmt.Printf("%v", user4)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} liSi 14} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.2.5 查询指定的某条记录(仅当主键为整型时可用)
//user5 := User{}
//db.Debug().First(&user5, 2)
//fmt.Printf("%v", user5)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`id` = 2 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} liSi 14}
*/
/*2.3 Where条件*/
//2.3.1 Get first matched record
//user1 := User{}
//db.Debug().Where("age = ?", "13").First(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE age = '13' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.3.2 Get all matched records
//user2 := []User{}
//db.Debug().Where("age = ?", "13").Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE age = '13' AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.3.3 <>
//user3 := []User{}
//db.Debug().Where("name <> ?", "liSi").Find(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE name <> 'liSi' AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.3.4 IN
//user4 := []User{}
//db.Debug().Where("name IN (?)", []string{"zhangSan", "wangEr"}).Find(&user4)
//fmt.Printf("%v", user4)
/*输出结果:
SELECT * FROM `users` WHERE name IN ('zhangSan','wangEr') AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.3.5 LIKE
//user5 := []User{}
//db.Debug().Where("name LIKE ?", "%a%").Find(&user5)
//fmt.Printf("%v", user5)
/*输出结果:
SELECT * FROM `users` WHERE name LIKE '%a%' AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.3.6 AND
//user6 := []User{}
//db.Debug().Where("name = ? AND age >= ?", "liSi", "10").Find(&user6)
//fmt.Printf("%v", user6)
/*输出结果:
SELECT * FROM `users` WHERE (name = 'liSi' AND age >= '10') AND `users`.`deleted_at` IS NULL
[{{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} liSi 14}]
*/
//2.3.7 Time
//user7 := []User{}
//db.Debug().Where("updated_at > ?", "2022-06-15 14:26:29.602").Find(&user7)
//fmt.Printf("%v", user7)
/*输出结果:
SELECT * FROM `users` WHERE updated_at > '2022-06-15 14:26:29.602' AND `users`.`deleted_at` IS NULL
[{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.3.8 BETWEEN
//user8 := []User{}
//db.Debug().Where("created_at BETWEEN ? AND ?", "2022-06-15 14:26:29.600", "2022-06-15 14:26:29.700").Find(&user8)
//fmt.Printf("%v", user8)
/*输出结果:
SELECT * FROM `users` WHERE (created_at BETWEEN '2022-06-15 14:26:29.600' AND '2022-06-15 14:26:29.700') AND `users`.`deleted_at` IS NULL
[{{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} liSi 14} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-0
1 00:00:00 +0000 UTC false}} wangEr 13}]
*/
/*2.4 Struct&Map查询*/
//2.4.1 Struct
//user1 := User{}
//db.Debug().Where(&User{Name: "zhangSan", Age: 13}).First(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'zhangSan' AND `users`.`age` = 13 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.4.2 Map
//user2 := User{}
//db.Debug().Where(map[string]interface{}{"name": "zhangSan", "age": 13}).First(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `age` = 13 AND `name` = 'zhangSan' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.4.3 主键的切片
//user3 := []User{}
//db.Debug().Where([]int64{1, 3}).Find(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`id` IN (1,3) AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.4.4 说明
// 当通过结构体进行查询时，GORM将会只通过非零值字段查询，这意味着如果你的字段值为0，''，false或者其他零值时，将不会被用于构建查询条件。
//user4 := User{}
//db.Debug().Where(&User{Name: "zhangSan", Age: 0}).First(&user4)
//fmt.Printf("%v", user4)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'zhangSan' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
/*补充: 可以使用指针或实现 Scanner/Valuer 接口来避免这个问题
// 使用指针
type User struct {
gorm.Model
Name string
Age *int
}
// 使用 Scanner/Valuer
type User struct {
gorm.Model
Name string
Age sql.NullInt64 // sql.NullInt64 实现了 Scanner/Valuer 接口
}
*/
/*2.5 Not条件*/
//2.5.1 Not
//user1 := []User{}
//db.Debug().Not("name", "zhangSan").Find(&user1)
//fmt.Printf("%v", user1)
/*输出内容:
SELECT * FROM `users` WHERE `name` <> 'zhangSan' AND `users`.`deleted_at` IS NULL
[{{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} liSi 14} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-0
1 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.5.2 Not In
//user2 := []User{}
//db.Debug().Not("name", []string{"zhangSan", "wangEr"}).Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `name` NOT IN ('zhangSan','wangEr') AND `users`.`deleted_at` IS NULL
{{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} liSi 14}
*/
//2.5.3 Not In slice of primary keys
//user3 := []User{}
//db.Debug().Not([]int64{1, 2}).Find(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`id` NOT IN (1,2) AND `users`.`deleted_at` IS NULL
[{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.5.4 Plain SQL
//user4 := []User{}
//db.Debug().Not("name = ?", "liSi").Find(&user4)
//fmt.Printf("%v", user4)
/*输出结果:
SELECT * FROM `users` WHERE NOT name = 'liSi' AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.5.5 Struct
//user5 := []User{}
//db.Debug().Not(User{Name: "liSi"}).Find(&user5)
//fmt.Printf("%v", user5)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` <> 'liSi' AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
/*2.6 Or条件*/
//2.6.1 Or
//user1 := []User{}
//db.Debug().Where("name = ?", "zhangSan").Or("name = ?", "wangEr").Find(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE (name = 'zhangSan' OR name = 'wangEr') AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.6.2 Struct
//user2 := []User{}
//db.Debug().Where("name = 'zhangSan'").Or(User{Name: "wangEr"}).Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE (name = 'zhangSan' OR `users`.`name` = 'wangEr') AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.6.3 Map
//user3 := []User{}
//db.Debug().Where("name = 'zhangSan'").Or(map[string]interface{}{"name": "wangEr"}).Find(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE (name = 'zhangSan' OR `name` = 'wangEr') AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
/*2.7 内联条件*/
// 作用与Where查询类似，当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法
//2.7.1 根据主键获取记录(只适用于整形主键)
//user1 := User{}
//db.Debug().First(&user1, 3)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`id` = 3 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
//2.7.2 根据主键获取记录, 如果它是一个非整形主键
//user2 := User{}
//db.Debug().First(&user2, "id = ?", "2")
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE id = '2' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} liSi 14}
*/
//2.7.3 Plain SQL
//user3 := []User{}
//db.Debug().Find(&user3, "age = ?", "13")
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE age = '13' AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.7.4 Plain SQL
//user4 := []User{}
//db.Debug().Find(&user4, "name <> ? AND age = ?", "liSi", 13)
//fmt.Printf("%v", user4)
/*输出结果:
SELECT * FROM `users` WHERE (name <> 'liSi' AND age = 13) AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.7.5 Struct
//user5 := []User{}
//db.Debug().Find(&user5, User{Age: 13})
//fmt.Printf("%v", user5)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`age` = 13 AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
//2.7.6 Map
//user6 := []User{}
//db.Debug().Find(&user6, map[string]interface{}{"age": 13})
//fmt.Printf("%v", user6)
/*输出结果:
SELECT * FROM `users` WHERE `age` = 13 AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} wangEr 13}]
*/
/*2.8 额外查询选项*/
//2.8.1 为查询 SQL 添加额外的 SQL 操作
//user1 := User{}
//db.Debug().Set("gorm:query_option", "FOR UPDATE").First(&user1, 3)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`id` = 3 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
/*2.9 FirstOrInit*/
// 获取匹配的第一条记录，否则根据给定的条件初始化一个新的对象(仅支持 struct 和 map 条件)，不会有写库的操作
//2.9.1 未找到 struct
//user1 := User{}
//db.Debug().FirstOrInit(&user1, User{Name: "maZi", Age: 20})
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'maZi' AND `users`.`age` = 20 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} maZi 20}
*/
//2.9.2 找到 struct
//user2 := User{}
//db.Debug().Where(User{Name: "wangEr"}).FirstOrInit(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'wangEr' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
//2.9.3 找到 map
//user3 := User{}
//db.Debug().FirstOrInit(&user3, map[string]interface{}{"name": "wangEr"})
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE `name` = 'wangEr' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
/*2.10 FirstOrInit Attrs*/
// 如果记录未找到，将使用参数初始化 struct，找到则不使用参数初始化struct
// 2.10.1 未找到
//user1 := User{}
//db.Debug().Where(User{Name: "maZi"}).Attrs(User{Age: 20}).FirstOrInit(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'maZi' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} maZi 20}
*/
// 2.10.2 未找到
//user2 := User{}
//db.Debug().Where(User{Name: "maZi"}).Attrs("age", 20).FirstOrInit(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'maZi' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} maZi 20}
*/
// 2.10.3 找到
//user3 := User{}
//db.Debug().Where(User{Name: "wangEr"}).Attrs(User{Age: 20}).FirstOrInit(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'wangEr' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
/*2.11 FirstOrInit Assign*/
// 不管记录是否找到，都将参数赋值给 struct
// 2.11.1 未找到
//user1 := User{}
//db.Debug().Where(User{Name: "maZi"}).Assign(User{Age: 20}).FirstOrInit(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'maZi' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} maZi 20}
*/
// 2.11.2 找到
//user2 := User{}
//db.Debug().Where(User{Name: "wangEr"}).Assign(User{Age: 20}).FirstOrInit(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'wangEr' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 20}
*/
/*2.12 FirstOrCreate*/
// 获取匹配的第一条记录, 否则根据给定的条件创建一个新的记录(仅支持 struct 和 map 条件)，会执行写库操作
//2.12.1 未找到
//user1 := User{}
//db.Debug().FirstOrCreate(&user1, User{Name: "paoPao", Age: 22})
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'paoPao' AND `users`.`age` = 22 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-20 09:25:46.316','2022-06-20 09:25:46.316',NULL,'paoPao',22)
{{4 2022-06-20 09:25:46.316 +0800 CST 2022-06-20 09:25:46.316 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} paoPao 22}
*/
//2.12.2 找到
//user2 := User{}
//db.Debug().Where(User{Name: "wangEr"}).FirstOrCreate(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'wangEr' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
/*2.13 FirstOrCreate Attrs*/
// 如果记录未找到将使用参数创建 struct 和记录，如果记录找到将不使用参数创建 struct 和记录
//2.13.1 未找到
//user1 := User{}
//db.Debug().Where(User{Name: "xiaoMeiMei"}).Attrs(User{Age: 23}).FirstOrCreate(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'xiaoMeiMei' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-20 09:57:29.29','2022-06-20 09:57:29.29',NULL,'xiaoMeiMei',23)
{{5 2022-06-20 09:57:29.29 +0800 CST 2022-06-20 09:57:29.29 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} xiaoMeiMei 23}
*/
//2.13.2 找到
//user2 := User{}
//db.Debug().Where(User{Name: "wangEr"}).Attrs(User{Age: 30}).FirstOrCreate(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'wangEr' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{3 2022-06-15 14:26:29.668 +0800 CST 2022-06-15 14:26:29.668 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} wangEr 13}
*/
/*2.14 FirstOrCreate Assign*/
// 不管记录是否找到，都将参数赋值给 struct 并保存至数据库(查询不到记录时创建一条新的记录到数据库，查询到记录时在数据库中更新该记录)
//2.14.1 未找到
//user1 := User{}
//db.Debug().Where(User{Name: "shuangMianGui"}).Assign(User{Age: 24}).FirstOrCreate(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'shuangMianGui' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2022-06-20 10:15:43.984','2022-06-20 10:15:43.984',NULL,'shuangMianGui',24)
{{6 2022-06-20 10:15:43.984 +0800 CST 2022-06-20 10:15:43.984 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} shuangMianGui 24}
*/
//2.14.2 找到
//user2 := User{}
//db.Debug().Where(User{Name: "shuangMianGui"}).Assign(User{Age: 188}).FirstOrCreate(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`name` = 'shuangMianGui' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
UPDATE `users` SET `age`=188,`updated_at`='2022-06-20 10:26:36.412' WHERE `users`.`name` = 'shuangMianGui' AND `users`.`deleted_at` IS NULL AND `id` = 6
{{6 2022-06-20 10:15:43.984 +0800 CST 2022-06-20 10:26:36.412 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} shuangMianGui 188}
*/
/***********************高级查询*******************/
/*2.15 子查询*/
//基于 *gorm.expr 的子查询
/*示例:
db.Where("amount > ?", db.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").SubQuery()).Find(&orders)
// SELECT * FROM "orders" WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders" WHERE (state = 'paid')));
*/
/*2.16 选择字段*/
//Select，指定你想从数据库中检索出的字段，默认会选择全部字段
//2.16.1 普通方式选择字段
//user1 := User{}
//db.Debug().Select("name, age").First(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT name, age FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.16.2 切片方式选择字段
//user2 := User{}
//db.Debug().Select([]string{"name", "age"}).First(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT `name`,`age` FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
{{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13}
*/
//2.16.3 COALESCE方式选择字段并命名别名
//rows, err := db.Debug().Table("users").Select("COALESCE(name,?)", "姓名").Rows()
//if err != nil {
// fmt.Println(err)
// return
//}
//
//defer func(rows *sql.Rows) {
// err := rows.Close()
// if err != nil {
// fmt.Println(err)
// return
// }
//}(rows)
//
//for rows.Next() {
// var u User
// // 表字段类型数据依次赋值到对应结构体字段类型上
// err := rows.Scan(&u.Name)
// if err != nil {
// fmt.Printf("scan failed, err:%v\n", err)
// return
// }
// fmt.Printf("name:%s\n", u.Name)
//}
/*输出结果:
SELECT COALESCE(name,'姓名') FROM `users`
name:zhangSan
name:liSi
name:wangEr
name:paoPao
name:xiaoMeiMei
name:shuangMianGui
*/
/*2.17 排序*/
//Order，指定从数据库中检索出记录的顺序，desc表示降序，asc表示升序(默认)。
//2.17.1 Order(对age字段进行降序排列，age字段数据重复的部分对name字段进行升序排列)
//user1 := []User{}
//db.Debug().Order("age desc, name").Find(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY age desc, name
+----+-------------------------+-------------------------+------------+---------------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+---------------+-----+
| 6 | 2022-06-20 10:15:43.984 | 2022-06-20 10:26:36.412 | NULL | shuangMianGui | 188 |
| 5 | 2022-06-20 09:57:29.290 | 2022-06-20 09:57:29.290 | NULL | xiaoMeiMei | 23 |
| 4 | 2022-06-20 09:25:46.316 | 2022-06-20 09:25:46.316 | NULL | paoPao | 22 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
+----+-------------------------+-------------------------+------------+---------------+-----+
6 rows in set
*/
//2.17.2 多字段排序(对age字段进行降序排列，age字段数据重复的部分对name字段进行升序排列)
//user2 := []User{}
//db.Debug().Order("age desc").Order("name").Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY age desc,name
+----+-------------------------+-------------------------+------------+---------------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+---------------+-----+
| 6 | 2022-06-20 10:15:43.984 | 2022-06-20 10:26:36.412 | NULL | shuangMianGui | 188 |
| 5 | 2022-06-20 09:57:29.290 | 2022-06-20 09:57:29.290 | NULL | xiaoMeiMei | 23 |
| 4 | 2022-06-20 09:25:46.316 | 2022-06-20 09:25:46.316 | NULL | paoPao | 22 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
+----+-------------------------+-------------------------+------------+---------------+-----+
6 rows in set
*/
//2.17.3 多字段排序(对age字段进行降序排列，age字段数据重复的部分对name字段进行降序排列)
//user3 := []User{}
//db.Debug().Order("age desc").Order("name desc").Find(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY age desc,name desc
+----+-------------------------+-------------------------+------------+---------------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+---------------+-----+
| 6 | 2022-06-20 10:15:43.984 | 2022-06-20 10:26:36.412 | NULL | shuangMianGui | 188 |
| 5 | 2022-06-20 09:57:29.290 | 2022-06-20 09:57:29.290 | NULL | xiaoMeiMei | 23 |
| 4 | 2022-06-20 09:25:46.316 | 2022-06-20 09:25:46.316 | NULL | paoPao | 22 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+---------------+-----+
6 rows in set
*/
/*2.18 数量*/
// Limit，指定从数据库检索出的最大记录数
//2.18.1 Limit
//user1 := []User{}
//db.Debug().Limit(3).Find(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 3
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
*/
//2.18.2 -1 取消Limit 条件
//user2 := []User{}
//db.Debug().Limit(3).Find(&user2).Limit(-1).Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 3
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL
+----+-------------------------+-------------------------+------------+---------------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+---------------+-----+
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
| 4 | 2022-06-20 09:25:46.316 | 2022-06-20 09:25:46.316 | NULL | paoPao | 22 |
| 5 | 2022-06-20 09:57:29.290 | 2022-06-20 09:57:29.290 | NULL | xiaoMeiMei | 23 |
| 6 | 2022-06-20 10:15:43.984 | 2022-06-20 10:26:36.412 | NULL | shuangMianGui | 188 |
+----+-------------------------+-------------------------+------------+---------------+-----+
6 rows in set
*/
/*2.19 偏移*/
// Offset，指定开始返回记录前要跳过的记录数
//2.19.1 Offset(跳过前3条数据，然后返回2条数据)
//user1 := []User{}
//db.Debug().Limit(2).Offset(3).Find(&user1)
//fmt.Printf("%v", user1)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 2 OFFSET 3
+----+-------------------------+-------------------------+------------+------------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+------------+-----+
| 4 | 2022-06-20 09:25:46.316 | 2022-06-20 09:25:46.316 | NULL | paoPao | 22 |
| 5 | 2022-06-20 09:57:29.290 | 2022-06-20 09:57:29.290 | NULL | xiaoMeiMei | 23 |
+----+-------------------------+-------------------------+------------+------------+-----+
2 rows in set
*/
//2.19.2 -1 取消Offset条件
//user2 := []User{}
//db.Debug().Limit(2).Offset(3).Find(&user2).Offset(-1).Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 2 OFFSET 3
SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 2
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
+----+-------------------------+-------------------------+------------+----------+-----+
2 rows in set
*/
/*2.20 总数*/
//Count，该 model 能获取记录的总数。
//2.20.1 统计表中符合条件的数据有多少条
//user1 := []User{}
//var count int64
//db.Debug().Where("name = ?", "zhangSan").Or("name = ?", "liSi").Find(&user1).Count(&count)
//fmt.Printf("%v\n", user1)
//fmt.Printf("%d\n", count)
/*输出结果:
SELECT * FROM `users` WHERE (name = 'zhangSan' OR name = 'liSi') AND `users`.`deleted_at` IS NULL
SELECT count(*) FROM `users` WHERE (name = 'zhangSan' OR name = 'liSi') AND `users`.`deleted_at` IS NULL
[{{1 2022-06-15 14:26:29.506 +0800 CST 2022-06-15 14:26:29.506 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} zhangSan 13} {{2 2022-06-15 14:26:29.602 +0800 CST 2022-06-15 14:26:29.602 +0800 CST {0001-
01-01 00:00:00 +0000 UTC false}} liSi 14}]
2
*/
//2.20.2 统计表中符合条件的数据有多少条
//var count int64
//db.Debug().Model(&User{}).Where("age = ?", 13).Count(&count)
//fmt.Printf("%d", count)
/*输出结果:
SELECT count(*) FROM `users` WHERE age = 13 AND `users`.`deleted_at` IS NULL
2
*/
//2.20.3 统计表共有多少条记录
//var count int64
//db.Debug().Table("users").Count(&count)
//fmt.Printf("%d", count)
/*输出结果:
SELECT count(*) FROM `users`
6
*/
//2.20.4 统计指定字段的记录数
//var count int64
//db.Debug().Table("users").Select("count(distinct(age))").Count(&count)
//fmt.Printf("%d", count)
/*输出结果:
SELECT count(distinct(age)) FROM `users`
5
*/
/*2.21 Group By&Having*/
// 参考: https://gorm.io/zh_CN/docs/query.html
/*2.22 连接 Joins*/
// 参考: https://gorm.io/zh_CN/docs/query.html
/*2.23 Pluck*/
// Pluck 用于从数据库查询单个列，并将结果扫描到切片。如果您想要查询多列，您应该使用 Select 和 Scan
//2.23.1 Pluck Model
//var ages []int64
//db.Debug().Model(&User{}).Pluck("age", &ages)
//fmt.Println(ages)
/*输出结果:
SELECT `age` FROM `users` WHERE `users`.`deleted_at` IS NULL
[13 14 13 22 23 188]
*/
//2.23.2 Pluck Table
//var names []string
//db.Debug().Table("users").Pluck("name", &names)
//fmt.Println(names)
/*输出结果:
SELECT `name` FROM `users`
[zhangSan liSi wangEr paoPao xiaoMeiMei shuangMianGui]
*/
//2.23.3 Distinct Pluck
//var ages []int64
//db.Debug().Model(&User{}).Distinct().Pluck("age", &ages)
//fmt.Println(ages)
/*输出结果:
SELECT DISTINCT `age` FROM `users` WHERE `users`.`deleted_at` IS NULL
[13 14 22 23 188]
*/
/*2.24 扫描 Scan*/
// Scan，扫描结果至一个 struct.
//type Result struct {
// Name string
// Age int
//}
//2.24.1 Scan
//var result []Result
//db.Debug().Table("users").Select("name", "age").Where("age = ?", "13").Scan(&result)
//fmt.Println(result)
/*输出结果:
SELECT name,age FROM `users` WHERE age = '13'
[{zhangSan 13} {wangEr 13}]
*/
//2.24.2 Raw SQL(原生SQL)
//var result []Result
//db.Debug().Raw("SELECT name, age FROM users WHERE age = ?", "13").Scan(&result)
//fmt.Println(result)
/*输出结果:
SELECT name, age FROM users WHERE age = '13'
[{zhangSan 13} {wangEr 13}]
*/
/*2.25 Distinct*/
// 从模型中选择不同的值
/*数据库数据:
mysql> use gorm02;
Database changed
mysql> select * from users;
+----+-------------------------+-------------------------+------------+---------------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+---------------+-----+
| 1 | 2022-06-15 14:26:29.506 | 2022-06-15 14:26:29.506 | NULL | zhangSan | 13 |
| 2 | 2022-06-15 14:26:29.602 | 2022-06-15 14:26:29.602 | NULL | liSi | 14 |
| 3 | 2022-06-15 14:26:29.668 | 2022-06-15 14:26:29.668 | NULL | wangEr | 13 |
| 4 | 2022-06-20 09:25:46.316 | 2022-06-20 09:25:46.316 | NULL | paoPao | 22 |
| 5 | 2022-06-20 09:57:29.290 | 2022-06-20 09:57:29.290 | NULL | xiaoMeiMei | 23 |
| 6 | 2022-06-20 10:15:43.984 | 2022-06-20 10:26:36.412 | NULL | shuangMianGui | 188 |
| 7 | NULL | NULL | NULL | zhangSan | 13 |
+----+-------------------------+-------------------------+------------+---------------+-----+
7 rows in set
mysql>
*/
//type Result struct {
// Name string
// Age int
//}
//var results []Result
//
//db.Debug().Table("users").Distinct("name", "age").Order("age desc").Find(&results)
//fmt.Println(results)
/*输出结果:
SELECT DISTINCT `name`,`age` FROM `users` ORDER BY age desc
[{shuangMianGui 188} {xiaoMeiMei 23} {paoPao 22} {liSi 14} {wangEr 13} {zhangSan 13}]
// from users表中select数据，依据age字段对数据进行降序排序，显示name，age字段数据并去重。
mysql> SELECT DISTINCT `name`,`age` FROM `users` ORDER BY age desc;
+---------------+-----+
| name | age |
+---------------+-----+
| shuangMianGui | 188 |
| xiaoMeiMei | 23 |
| paoPao | 22 |
| liSi | 14 |
| wangEr | 13 |
| zhangSan | 13 |
+---------------+-----+
6 rows in set
mysql>
*/
}
2.3 03Update
03Update.go
package main
import (
"fmt"
"gorm.io/gorm"
)
// 03Update
/*
(1) 参考文档
https://gorm.io/zh_CN/docs/update.html
(2) 创建一个gorm02库
CREATE DATABASE gorm02 DEFAULT CHARSET=utf8mb4;
*/
import (
"gorm.io/driver/mysql"
)
/*3.1 连接数据库*/
var db *gorm.DB
func initDB() (err error) {
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm02?charset=utf8mb4&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
fmt.Printf("connect db mysql failed: %v\n", err)
return err
}
return nil
}
func main() {
/*3.1 连接数据库*/
err := initDB()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("mysql数据库连接成功!")
type User struct {
gorm.Model
// Name字段传入的值是对应字段类型的零值时存入的值是default值。
Name string `gorm:"type:varchar(100);default:'LiuC'"`
// Age字段传入的值是对应字段类型的零值时存入的值是对应字段类型的零值。
Age int64 `gorm:"type:int(2)"`
}
err = db.AutoMigrate(&User{})
if err != nil {
fmt.Println(err)
return
}
fmt.Println("users 表创建成功")
// 插入测试数据:
/*user1 := User{Name: "zhangSan", Age: 13}
user2 := User{Name: "liSi", Age: 14}
user3 := User{Name: "wangEr", Age: 13}
db.Debug().Create(&user1)
db.Debug().Create(&user2)
db.Debug().Create(&user3)
*/
/*查看插入的数据:
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 10:30:50.698 | NULL | zhangSan | 13 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | liSi | 14 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
/*3.2 更新所有字段*/
// Save()默认会更新该对象的所有字段，即使你没有赋值。
//user1 := User{}
//db.Debug().Where("name = ?", "zhangSan").First(&user1)
//user1.Age = 99
//db.Debug().Save(&user1)
/*输出结果:
SELECT * FROM `users` WHERE name = 'zhangSan' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
UPDATE `users` SET `created_at`='2022-06-21 10:30:50.698',`updated_at`='2022-06-21 11:10:00.785',`deleted_at`=NULL,`name`='zhangSan',`age`=99 WHERE `users`.`deleted_at` IS NULL AND `i
d` = 1
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 11:10:00.785 | NULL | zhangSan | 99 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | liSi | 14 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
/*3.3 更新修改字段*/
// 如果你只希望更新指定字段，可以使用Update(更新单个字段)或者Updates(更新多个字段)
//3.3.1 更新单个属性，如果它有变化
//user1 := User{}
//db.Debug().Where("name = ?", "zhangSan").First(&user1)
//db.Debug().Model(&user1).Update("age", 100)
// 注意: 这里不能使用find，否则sql语句条件会变为如下，导致age字段全部更新
// UPDATE `users` SET `age`=100,`updated_at`='2022-06-22 12:16:28.87' WHERE `users`.`deleted_at` IS NULL OR `id` = 1
/*输出结果:
SELECT * FROM `users` WHERE name = 'zhangSan' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
UPDATE `users` SET `age`=100,`updated_at`='2022-06-21 15:15:47.536' WHERE `users`.`deleted_at` IS NULL AND `id` = 1
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 15:15:47.536 | NULL | zhangSan | 100 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | liSi | 14 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
//3.3.2 根据给定的条件更新单个属性
//user2 := User{}
//db.Debug().Model(&user2).Where("name = ?", "zhangSan").Update("age", 101)
/*输出结果:
UPDATE `users` SET `age`=101,`updated_at`='2022-06-21 15:44:36.491' WHERE name = 'zhangSan' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 15:44:36.491 | NULL | zhangSan | 101 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | liSi | 14 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
//3.3.3 使用 map 更新多个属性，只会更新其中有变化的属性
//user3 := User{}
//db.Debug().Model(&user3).Where("name = ?", "zhangSan").Updates(map[string]interface{}{"name": "maZi", "age": 102})
/*输出结果:
UPDATE `users` SET `age`=102,`name`='maZi',`updated_at`='2022-06-21 15:52:23.893' WHERE name = 'zhangSan' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+--------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+--------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 15:52:23.893 | NULL | maZi | 102 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | liSi | 14 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+--------+-----+
3 rows in set
mysql>
*/
//3.3.4 使用 struct 更新多个属性，只会更新其中有变化且为非零值的字段，若想更新所有字段，请使用map[string]interface{}
//user4 := User{}
//db.Debug().Model(&user4).Where("name = ?", "maZi").Updates(User{Name: "paoPao", Age: 103})
/*输出结果:
UPDATE `users` SET `updated_at`='2022-06-21 15:57:07.769',`name`='paoPao',`age`=103 WHERE name = 'maZi' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+--------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+--------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 15:57:07.769 | NULL | paoPao | 103 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | liSi | 14 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+--------+-----+
3 rows in set
mysql>
补充: 一次更新多条记录
db.Debug().Model(User{}).Where("id IN (?)", []int{1, 2}).Updates(User{Name: "xiYangYang", Age: 18})
UPDATE `users` SET `updated_at`='2022-06-21 20:14:52.095',`name`='xiYangYang',`age`=18 WHERE id IN (1,2) AND `users`.`deleted_at` IS NULL
补充: 使用map[string]interface{}更新字段为零值
db.Debug().Model(&User{}).Where("name = ?", "xiYangYang").Updates(map[string]interface{}{"name": "", "age": 0})
UPDATE `users` SET `age`=0,`name`='',`updated_at`='2022-06-21 20:34:45.009' WHERE name = 'xiYangYang' AND `users`.`deleted_at` IS NULL
*/
//3.3.5 当使用 struct 更新时，GORM只会更新那些非零值的字段，对于下面的操作，不会发生任何更新，"", 0, false 都是其类型的零值
//user5 := User{}
//db.Debug().Model(&user5).Where("name = ?", "paoPao").Updates(User{Name: "", Age: 0})
/*输出结果:
UPDATE `users` SET `updated_at`='2022-06-21 16:51:13.131' WHERE name = 'paoPao' AND `users`.`deleted_at` IS NULL
*/
//3.3.6 无Hooks更新
/*上面的更新操作会自动运行model的BeforeUpdate，AfterUpdate方法，更新UpdatedAt时间戳，在更新时保存其Associations，如果你不想调
用这些方法，你可以使用UpdateColumn，UpdateColumns。
*/
/*3.4 批量更新*/
// 批量更新时Hooks(钩子函数)不会运行
//3.4.1 批量更新map
//db.Debug().Table("users").Where("id IN (?)", []int{1, 2}).Updates(map[string]interface{}{"name": "LiuChang", "age": 200})
/*输出结果:
UPDATE `users` SET `age`=200,`name`='LiuChang' WHERE id IN (1,2)
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-21 15:57:07.769 | NULL | LiuChang | 200 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-21 10:30:50.794 | NULL | LiuChang | 200 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 18 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
/*3.5 使用SQL表达式更新*/
//3.5.1 sql Expr Update(更新单个字段)
//db.Debug().Model(&User{}).Where("name = ?", "LiuChang").Update("age", gorm.Expr("age * ? + ?", 2, 100))
/*输出结果:
UPDATE `users` SET `age`=age * 2 + 100,`updated_at`='2022-06-22 09:34:18.682' WHERE name = 'LiuChang' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-22 09:34:18.682 | NULL | LiuChang | 500 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-22 09:34:18.682 | NULL | LiuChang | 500 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 18 |
+----+-------------------------+-------------------------+------------+----------+-----+
3 rows in set
mysql>
*/
//3.5.2 sql Expr Updates map(更新多个字段)
//db.Debug().Model(&User{}).Where("name = ?", "LiuChang").Updates(map[string]interface{}{"age": gorm.Expr("age * ? + ?", 2, 100)})
/*输出结果:
UPDATE `users` SET `age`=age * 2 + 100,`updated_at`='2022-06-22 09:59:34.136' WHERE name = 'LiuChang' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+------+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+------+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-22 09:59:34.136 | NULL | LiuChang | 1100 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-22 09:59:34.136 | NULL | LiuChang | 1100 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 18 |
+----+-------------------------+-------------------------+------------+----------+------+
3 rows in set
mysql>
*/
//3.5.3 sql Expr UpdateColumn(更新单个字段并去除更新时间戳)
//db.Debug().Model(&User{}).Where("name = ?", "LiuChang").UpdateColumn("age", gorm.Expr("age - ?", 100))
/*输出结果:
UPDATE `users` SET `age`=age - 100 WHERE name = 'LiuChang' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+------+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+------+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-22 09:59:34.136 | NULL | LiuChang | 1000 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-22 09:59:34.136 | NULL | LiuChang | 1000 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 18 |
+----+-------------------------+-------------------------+------------+----------+------+
3 rows in set
mysql>
*/
//3.5.4 sql Expr UpdateColumn(更新单个字段并去除更新时间戳)
//db.Debug().Model(&User{}).Where("age > ?", "900").UpdateColumn("age", gorm.Expr("age + ?", 900))
/*输出结果:
UPDATE `users` SET `age`=age + 900 WHERE age > '900' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+------+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+------+
| 1 | 2022-06-21 10:30:50.698 | 2022-06-22 09:59:34.136 | NULL | LiuChang | 1900 |
| 2 | 2022-06-21 10:30:50.794 | 2022-06-22 09:59:34.136 | NULL | LiuChang | 1900 |
| 3 | 2022-06-21 10:30:50.858 | 2022-06-21 10:30:50.858 | NULL | wangEr | 18 |
+----+-------------------------+-------------------------+------------+----------+------+
3 rows in set
mysql>
*/
}
2.4 04Delete
04Delete.go
package main
// 04Delete
/*
(1) 参考文档
https://gorm.io/zh_CN/docs/delete.html
(2) 创建一个gorm02库
CREATE DATABASE gorm02 DEFAULT CHARSET=utf8mb4;
*/
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
/*4.1 连接数据库*/
var db *gorm.DB
func initDB() (err error) {
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm02?charset=utf8mb4&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
fmt.Printf("connect db mysql failed: %v\n", err)
return err
}
return nil
}
func main() {
/*4.1 连接数据库*/
err := initDB()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("mysql数据库连接成功!")
type User struct {
gorm.Model
// Name字段传入的值是对应字段类型的零值时存入的值是default值。
Name string `gorm:"type:varchar(100);default:'LiuC'"`
// Age字段传入的值是对应字段类型的零值时存入的值是对应字段类型的零值。
Age int64 `gorm:"type:int(2)"`
}
err = db.AutoMigrate(&User{})
if err != nil {
fmt.Println(err)
return
}
fmt.Println("users 表创建成功")
// 插入测试数据:
/*user1 := User{Name: "zhangSan", Age: 13}
user2 := User{Name: "liSi", Age: 14}
user3 := User{Name: "wangEr", Age: 13}
db.Debug().Create(&user1)
db.Debug().Create(&user2)
db.Debug().Create(&user3)
*/
/*查看插入的数据:
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | NULL | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | NULL | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | NULL | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | NULL | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | NULL | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
6 rows in set
mysql>
*/
/*4.2 删除记录*/
//警告:删除记录时，请确保主键字段有值，GORM会通过主键去删除记录，如果主键为空，GORM会删除该model的所有记录。
//4.2.1 删除现有记录
//user1 := []User{}
//db.Debug().Where("name", "zhangSan").Find(&user1)
//db.Debug().Delete(&user1)
/*输出结果:
SELECT * FROM `users` WHERE `name` = 'zhangSan' AND `users`.`deleted_at` IS NULL
UPDATE `users` SET `deleted_at`='2022-06-22 15:05:45.406' WHERE `users`.`id` IN (1,4) AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | 2022-06-22 15:05:45.406 | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | NULL | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | NULL | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | 2022-06-22 15:05:45.406 | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | NULL | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
6 rows in set
mysql>
*/
/*4.3 批量删除*/
//删除全部匹配的记录
//4.3.1 方式一
//db.Debug().Where("name LIKE ?", "%an%").Delete(&User{})
/*输出结果:
UPDATE `users` SET `deleted_at`='2022-06-22 15:23:01.503' WHERE name LIKE '%an%' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | 2022-06-22 15:05:45.406 | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | NULL | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | 2022-06-22 15:23:01.503 | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | 2022-06-22 15:05:45.406 | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | NULL | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | 2022-06-22 15:23:01.503 | wangEr | 13 |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
6 rows in set
mysql>
*/
//4.3.2 方式二
//db.Debug().Where("name = ?", "liSi").Delete(&User{})
/*输出结果:
UPDATE `users` SET `deleted_at`='2022-06-22 15:26:19.432' WHERE name = 'liSi' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | 2022-06-22 15:05:45.406 | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | 2022-06-22 15:26:19.432 | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | 2022-06-22 15:23:01.503 | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | 2022-06-22 15:05:45.406 | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | 2022-06-22 15:26:19.432 | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | 2022-06-22 15:23:01.503 | wangEr | 13 |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
6 rows in set
mysql>
*/
/*为了方便实验需要将删除的数据恢复:
mysql> update users set deleted_at = null where id in (1,2,3,4,5,6);
mysql> select * from users;
+----+-------------------------+-------------------------+------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | NULL | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | NULL | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | NULL | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | NULL | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | NULL | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+------------+----------+-----+
6 rows in set
mysql>
*/
//4.3.3 方式三
//db.Debug().Delete(&User{}, "name = ?", "liSi")
/*输出结果:
UPDATE `users` SET `deleted_at`='2022-06-22 15:56:11.828' WHERE name = 'liSi' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | NULL | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | 2022-06-22 15:56:11.828 | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | NULL | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | NULL | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | 2022-06-22 15:56:11.828 | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | NULL | wangEr | 13 |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
6 rows in set
mysql>
*/
/*4.4 软删除*/
//如果一个model有DeletedAt字段，他将自动获得软删除的功能！当调用Delete方法时，记录不会真正的从数据库中被删除，只会将DeletedAt字段的值会被设置为当前时间
//4.4.1 批量删除记录
//db.Debug().Where("name = ?", "wangEr").Delete(&User{})
/*输出结果:
UPDATE `users` SET `deleted_at`='2022-06-22 16:08:03.248' WHERE name = 'wangEr' AND `users`.`deleted_at` IS NULL
mysql> select * from users;
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
| 1 | 2022-06-22 14:59:43.306 | 2022-06-22 14:59:43.306 | NULL | zhangSan | 13 |
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | 2022-06-22 15:56:11.828 | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | 2022-06-22 16:08:03.248 | wangEr | 13 |
| 4 | 2022-06-22 15:00:07.488 | 2022-06-22 15:00:07.488 | NULL | zhangSan | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | 2022-06-22 15:56:11.828 | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | 2022-06-22 16:08:03.248 | wangEr | 13 |
+----+-------------------------+-------------------------+-------------------------+----------+-----+
6 rows in set
mysql>
*/
//4.4.2 查询记录时会忽略被软删除的记录
//user2 := []User{}
//db.Debug().Where("name = ?", "wangEr").Find(&user2)
//fmt.Printf("%v", user2)
/*输出结果:
SELECT * FROM `users` WHERE name = 'wangEr' AND `users`.`deleted_at` IS NULL
[]
*/
//4.4.3 Unscoped方法可以查询被软删除的记录
//user3 := []User{}
//db.Debug().Unscoped().Where("name = ?", "wangEr").Find(&user3)
//fmt.Printf("%v", user3)
/*输出结果:
SELECT * FROM `users` WHERE name = 'wangEr'
[{{3 2022-06-22 14:59:43.457 +0800 CST 2022-06-22 14:59:43.457 +0800 CST {2022-06-22 16:08:03.248 +0800 CST true}} wangEr 13} {{6 2022-06-22 15:00:07.612 +0800 CST 2022-06-22 15:00:07.612 +0800 CST {2022
-06-22 16:08:03.248 +0800 CST true}} wangEr 13}]
*/
/*4.5 物理删除*/
// Unscoped 方法可以物理删除记录
//db.Debug().Unscoped().Where("name = ?", "zhangSan").Delete(&User{})
/*输出结果:
DELETE FROM `users` WHERE name = 'zhangSan'
mysql> select * from users;
+----+-------------------------+-------------------------+-------------------------+--------+-----+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+-------------------------+--------+-----+
| 2 | 2022-06-22 14:59:43.394 | 2022-06-22 14:59:43.394 | 2022-06-22 15:56:11.828 | liSi | 14 |
| 3 | 2022-06-22 14:59:43.457 | 2022-06-22 14:59:43.457 | 2022-06-22 16:08:03.248 | wangEr | 13 |
| 5 | 2022-06-22 15:00:07.563 | 2022-06-22 15:00:07.563 | 2022-06-22 15:56:11.828 | liSi | 14 |
| 6 | 2022-06-22 15:00:07.612 | 2022-06-22 15:00:07.612 | 2022-06-22 16:08:03.248 | wangEr | 13 |
+----+-------------------------+-------------------------+-------------------------+--------+-----+
4 rows in set
mysql>
*/
}
