How to use raw SQL in GORM? - 面试题

GORM provides various methods to handle raw SQL queries. When ORM functionality cannot meet requirements, you can use raw SQL.

Executing Raw SQL

Exec() - Execute SQL that doesn't return data

go
// Create table
db.Exec("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100))")

// Insert data
db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "John", "john@example.com")

// Update data
db.Exec("UPDATE users SET name = ? WHERE id = ?", "Jane", 1)

// Delete data
db.Exec("DELETE FROM users WHERE id = ?", 1)

Raw() - Execute SQL that returns data

go
// Query single record
var user User
db.Raw("SELECT * FROM users WHERE id = ?", 1).Scan(&user)

// Query multiple records
var users []User
db.Raw("SELECT * FROM users WHERE age > ?", 18).Scan(&users)

// Query specific fields
var results []struct {
    Name  string
    Email string
}
db.Raw("SELECT name, email FROM users").Scan(&results)

Mixing Raw SQL with ORM

Use raw SQL in queries

go
// Use raw SQL as subquery
var users []User
db.Where("age > (?)", db.Raw("SELECT AVG(age) FROM users")).Find(&users)

// Use raw SQL condition
db.Where(db.Raw("DATE(created_at) = ?", "2024-01-01")).Find(&users)

Use Joins to execute raw SQL

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

Advanced Raw SQL Queries

Complex Aggregation Queries

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)

Using 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)

Using Window Functions

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)

Raw SQL Transactions

Using raw SQL in transactions

go
err := db.Transaction(func(tx *gorm.DB) error {
    // Insert using raw SQL
    if err := tx.Exec("INSERT INTO users (name) VALUES (?)", "John").Error; err != nil {
        return err
    }

    // Update using raw SQL
    if err := tx.Exec("UPDATE users SET email = ? WHERE name = ?", "john@example.com", "John").Error; err != nil {
        return err
    }

    return nil
})

Named Parameters

Using named parameters

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"})

Using struct as parameters

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)

Raw SQL Best Practices

1. Use parameterized queries to prevent SQL injection

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

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

2. Use Scan to map results

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. Use Rows to handle large amounts of data

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)
    }
    // Process user data
}

4. Use Row to handle single record

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

Performance Optimization

1. Use index hints

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

// PostgreSQL index hints
db.Raw("SELECT * FROM users WHERE name = ?", "John").Scan(&users)

2. Batch operations

go
// Batch insert
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...)

Notes

SQL Injection: Always use parameterized queries, don't concatenate SQL strings
Database Compatibility: SQL syntax may vary across different databases
Error Handling: Properly handle errors from raw SQL execution
Performance Considerations: Complex raw SQL may affect performance, needs optimization
Maintainability: Raw SQL code is harder to maintain, try to use ORM
Transaction Consistency: Pay attention to transaction consistency when using raw SQL in transactions

Common Questions

Q: When should I use raw SQL?

A: When ORM cannot meet requirements, such as complex aggregation queries, window functions, performance optimization, etc.

Q: How to prevent SQL injection?

A: Always use parameterized queries (? or named parameters), don't directly concatenate SQL strings.

Q: Will mixing raw SQL and ORM affect performance?

A: No, GORM will correctly handle mixed queries, but pay attention to query complexity.

Q: How to handle raw SQL errors?

A: Check db.Error or use Error() method to get error information.