GORM 中如何使用原生 SQL？ - 面试题

GORM 提供了多种方法来处理原生 SQL 查询，当 ORM 的功能无法满足需求时，可以使用原生 SQL。

执行原生 SQL

Exec() - 执行不返回数据的 SQL

go
// 创建表
db.Exec("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100))")

// 插入数据
db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "John", "john@example.com")

// 更新数据
db.Exec("UPDATE users SET name = ? WHERE id = ?", "Jane", 1)

// 删除数据
db.Exec("DELETE FROM users WHERE id = ?", 1)

Raw() - 执行返回数据的 SQL

go
// 查询单条记录
var user User
db.Raw("SELECT * FROM users WHERE id = ?", 1).Scan(&user)

// 查询多条记录
var users []User
db.Raw("SELECT * FROM users WHERE age > ?", 18).Scan(&users)

// 查询特定字段
var results []struct {
    Name  string
    Email string
}
db.Raw("SELECT name, email FROM users").Scan(&results)

原生 SQL 与 ORM 混合使用

在查询中使用原生 SQL

go
// 使用原生 SQL 作为子查询
var users []User
db.Where("age > (?)", db.Raw("SELECT AVG(age) FROM users")).Find(&users)

// 使用原生 SQL 条件
db.Where(db.Raw("DATE(created_at) = ?", "2024-01-01")).Find(&users)

使用 Joins 执行原生 SQL

go
var users []User
db.Joins("LEFT JOIN profiles ON users.id = profiles.user_id").
   Where("profiles.status = ?", "active").
   Find(&users)

高级原生 SQL 查询

复杂聚合查询

go
type Result struct {
    UserName string
    PostCount int
}

var results []Result
db.Raw(`
    SELECT u.name as user_name, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.age > ?
    GROUP BY u.id
    HAVING COUNT(p.id) > ?
    ORDER BY post_count DESC
    LIMIT ?
`, 18, 5, 10).Scan(&results)

使用 CTE (Common Table Expression)

go
var results []struct {
    UserName string
    TotalAmount float64
}

db.Raw(`
    WITH user_orders AS (
        SELECT user_id, SUM(amount) as total
        FROM orders
        WHERE created_at > ?
        GROUP BY user_id
    )
    SELECT u.name as user_name, o.total as total_amount
    FROM users u
    JOIN user_orders o ON u.id = o.user_id
`, time.Now().AddDate(0, -1, 0)).Scan(&results)

使用窗口函数

go
var results []struct {
    UserName string
    Amount   float64
    Rank     int
}

db.Raw(`
    SELECT 
        u.name as user_name,
        o.amount,
        RANK() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) as rank
    FROM orders o
    JOIN users u ON o.user_id = u.id
`).Scan(&results)

原生 SQL 事务

在事务中使用原生 SQL

go
err := db.Transaction(func(tx *gorm.DB) error {
    // 使用原生 SQL 插入
    if err := tx.Exec("INSERT INTO users (name) VALUES (?)", "John").Error; err != nil {
        return err
    }

    // 使用原生 SQL 更新
    if err := tx.Exec("UPDATE users SET email = ? WHERE name = ?", "john@example.com", "John").Error; err != nil {
        return err
    }

    return nil
})

命名参数

使用命名参数

go
// MySQL
db.NamedExec("INSERT INTO users (name, email) VALUES (:name, :email)", 
    map[string]interface{}{"name": "John", "email": "john@example.com"})

// PostgreSQL
db.NamedExec("INSERT INTO users (name, email) VALUES ($name, $email)", 
    map[string]interface{}{"name": "John", "email": "john@example.com"})

使用结构体作为参数

go
type UserParams struct {
    Name  string `db:"name"`
    Email string `db:"email"`
}

params := UserParams{Name: "John", Email: "john@example.com"}
db.NamedExec("INSERT INTO users (name, email) VALUES (:name, :email)", params)

原生 SQL 最佳实践

1. 使用参数化查询防止 SQL 注入

go
// 不安全
db.Raw(fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", userInput))

// 安全
db.Raw("SELECT * FROM users WHERE name = ?", userInput)

2. 使用 Scan 映射结果

go
type UserSummary struct {
    Name  string
    Count int
}

var summaries []UserSummary
db.Raw(`
    SELECT name, COUNT(*) as count
    FROM users
    GROUP BY name
`).Scan(&summaries)

3. 使用 Rows 处理大量数据

go
rows, err := db.Raw("SELECT * FROM users").Rows()
if err != nil {
    panic(err)
}
defer rows.Close()

for rows.Next() {
    var user User
    if err := db.ScanRows(rows, &user); err != nil {
        panic(err)
    }
    // 处理用户数据
}

4. 使用 Row 处理单条记录

go
var name string
row := db.Raw("SELECT name FROM users WHERE id = ?", 1).Row()
if err := row.Scan(&name); err != nil {
    panic(err)
}

性能优化

1. 使用索引提示

go
// MySQL 索引提示
db.Raw("SELECT * FROM users USE INDEX (idx_name) WHERE name = ?", "John").Scan(&users)

// PostgreSQL 索引提示
db.Raw("SELECT * FROM users WHERE name = ?", "John").Scan(&users)

2. 批量操作

go
// 批量插入
values := []interface{}{"John", "john@example.com"}, {"Jane", "jane@example.com"}
query := "INSERT INTO users (name, email) VALUES "
placeholders := make([]string, len(values))
for i := range values {
    placeholders[i] = "(?, ?)"
}
query += strings.Join(placeholders, ", ")

args := make([]interface{}, 0, len(values)*2)
for _, v := range values {
    args = append(args, v.([]interface{})...)
}
db.Exec(query, args...)

注意事项

SQL 注入：始终使用参数化查询，不要拼接 SQL 字符串
数据库兼容性：不同数据库的 SQL 语法可能不同
错误处理：正确处理原生 SQL 执行的错误
性能考虑：复杂的原生 SQL 可能影响性能，需要优化
可维护性：原生 SQL 代码较难维护，尽量使用 ORM
事务一致性：在事务中使用原生 SQL 时要注意事务的一致性

常见问题

Q: 什么时候应该使用原生 SQL？

A: 当 ORM 无法满足需求时，如复杂聚合查询、窗口函数、性能优化等场景。

Q: 如何防止 SQL 注入？

A: 始终使用参数化查询（? 或命名参数），不要直接拼接 SQL 字符串。

Q: 原生 SQL 和 ORM 混合使用会影响性能吗？

A: 不会，GORM 会正确处理混合查询，但要注意查询的复杂度。

Q: 如何处理原生 SQL 的错误？

A: 检查 db.Error 或使用 Error() 方法获取错误信息。