mysql group by json字段合并(干货)

原始SQL

查询原始 SQLScan

type Result struct { 
  ID    int
   Name string
   Age   int
 }

var result Result 
db.Raw( "从用户中选择 id、姓名、年龄，其中 id = ?" , 3 ). Scan(&result)

db.Raw( "从用户中选择 id、姓名、年龄 WHERE name = ?" , "jinzhu" ).Scan(&result)

var Age int
 db.Raw( "从用户中选择 SUM(年龄)，其中角色 = ?" , "admin" ).Scan(&age)

var users []User 
db.Raw( "UPDATE users SET name = ? WHEREage = ? RETURNING id, name" , "jinzhu" , 20 ).Scan(&users)

Exec使用原始 SQL

db.Exec( "DROP TABLE users" ) 
db.Exec( "更新订单 SETshipped_at = ? WHERE id IN ?" , time.Now(), [] int64 { 1 , 2 , 3 })


db.Exec( "UPDATE users SET Money = ? WHERE name = ?" , gorm.Expr( "money * ? + ?" , 10000 , 1 ), "jinzhu" )

注意GORM 允许缓存准备好的语句来提高性能，查看性能了解详细信息

命名参数

GORM 支持使用sql.NamedArg,map[string]interface{}{}或 struct 命名参数，例如：

db.Where( "name1 = @name OR name2 = @name" , sql.Named( "name" , "jinzhu" )).Find(&user) 


db.Where( "name1 = @name OR name2 = @name" , map [ string ] interface {}{ "name" : "jinzhu2" }).First(&result3) 



db.Raw( "SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name" , 
   sql.Named( "name" , "jinzhu1" ), sql.Named( "name2" , "jinzhu2" )).Find(&user) 


db.Exec( "UPDATE users SET name1 = @name, name2 = @name2, name3 = @name" , 
   sql.Named( "name" , "jinzhunew" ), sql.Named( "name2" , "jinzhunew2" )) 


db.Raw( "SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2" , map [ string ]接口{}{ "name" : "jinzhu" , "name2" : "jinzhu2 " }).Find(&user) 
   


type NamedArgument struct {
  名称字符串
  Name2字符串
}

db.Raw( "SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2" , 
   NamedArgument{Name: "jinzhu" , Name2: "jinzhu2" }).Find(&user)

试运行模式

生成SQL及其参数而不执行，可用于准备或测试生成的 SQL，查看Session了解详细信息

stmt := db.Session(&gorm.Session{DryRun: true }).First(&user, 1 ).Statement 
stmt.SQL.String() 
 stmt.Vars

ToSQL

返回SQL未执行而生成的。

GORM使用database/sql的参数占位符来构造SQL语句，它会自动转义参数以避免SQL注入，但生成的SQL不提供安全保证，请仅用于调试。

sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
  return tx.Model(&User{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&[]User{})
})
sql

Row & Rows

Get result as *sql.Row

row := db.Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row()
row.Scan(&name, &age)


row := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row()
row.Scan(&name, &age, &email)

Get result as *sql.Rows

rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows()
defer rows.Close()
for rows.Next() {
  rows.Scan(&name, &age, &email)

  
}


rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows()
defer rows.Close()
for rows.Next() {
  rows.Scan(&name, &age, &email)

  
}

Checkout FindInBatches for how to query and process records in batch
Checkout Group Conditions for how to build complicated SQL Query

Scan *sql.Rows into struct

Use ScanRows to scan a row into a struct, for example:

rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() 
defer rows.Close()

var user User
for rows.Next() {
  
  db.ScanRows(rows, &user)

  
}

Connection

Run mutliple SQL in same db tcp connection (not in a transaction)

db.Connection(func(tx *gorm.DB) error {
  tx.Exec("SET my.role = ?", "admin")

  tx.First(&User{})
})

Advanced

Clauses

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

var limit = 1
clause.Select{Columns: []clause.Column{{Name: "*"}}}
clause.From{Tables: []clause.Table{{Name: clause.CurrentTable}}}
clause.Limit{Limit: &limit}
clause.OrderBy{Columns: []clause.OrderByColumn{
  {
    Column: clause.Column{
      Table: clause.CurrentTable,
      Name:  clause.PrimaryKey,
    },
  },
}}

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

Clause Builder

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

Clause Options

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"

db.Clauses(hints.New("hint")).Find(&User{})