原生 SQL
原生查询 SQL 和 Scan
type Result struct { |
Exec
原生 SQL
db.Exec("DROP TABLE users") |
注意 GORM 允许缓存预编译 SQL 语句来提高性能,查看 性能 获取详情
命名参数
GORM 支持 sql.NamedArg
、map[string]interface{}{}
或 struct 形式的命名参数,例如:
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user) |
DryRun 模式
在不执行的情况下生成 SQL
及其参数,可以用于准备或测试生成的 SQL,详情请参考 Session
stmt := db.Session(&Session{DryRun: true}).First(&user, 1).Statement |
ToSQL
返回生成的 SQL
但不执行。
GORM使用 database/sql 的参数占位符来构建 SQL 语句,它会自动转义参数以避免 SQL 注入,但我们不保证生成 SQL 的安全,请只用于调试。
sql := DB.ToSQL(func(tx *gorm.DB) *gorm.DB { |
Row
& Rows
获取 *sql.Row
结果
// 使用 GORM API 构建 SQL |
获取 *sql.Rows
结果
// 使用 GORM API 构建 SQL |
转到 FindInBatches 获取如何在批量中查询和处理记录的信息, 转到 Group 条件 获取如何构建复杂 SQL 查询的信息
将 sql.Rows
扫描至 model
使用 ScanRows
将一行记录扫描至 struct,例如:
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error) |
Connection
Run mutliple SQL in same db tcp connection (not in a transaction)
db.Connection(func(tx *gorm.DB) error { |
Advanced
子句(Clause)
GORM uses SQL builder generates SQL internally, for each operation, GORM creates a *gorm.Statement
object, all GORM APIs add/change Clause
for the Statement
, at last, GORM generated SQL based on those clauses
For example, when querying with First
, it adds the following clauses to the Statement
clause.Select{Columns: "*"} |
Then GORM build finally querying SQL in the Query
callbacks like:
Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR") |
Which generate SQL:
SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1 |
You can define your own Clause
and use it with GORM, it needs to implements Interface
Check out examples for reference
子句构造器
For different databases, Clauses may generate different SQL, for example:
db.Offset(10).Limit(5).Find(&users) |
Which is supported because GORM allows database driver register Clause Builder to replace the default one, take the Limit as example
子句选项
GORM defined Many Clauses, and some clauses provide advanced options can be used for your application
Although most of them are rarely used, if you find GORM public API can’t match your requirements, may be good to check them out, for example:
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user) |
StatementModifier
GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example
import "gorm.io/hints" |