Golang中如何在文件中执行SQL命令

在Golang中执行文件中的SQL命令可以通过以下方式实现：

1. 读取并执行SQL文件的基本方法

package main

import (
    "database/sql"
    "fmt"
    "io/ioutil"
    "log"
    _ "github.com/lib/pq" // PostgreSQL驱动
)

func executeSQLFromFile(db *sql.DB, filename string) error {
    // 读取SQL文件
    sqlBytes, err := ioutil.ReadFile(filename)
    if err != nil {
        return fmt.Errorf("读取文件失败: %v", err)
    }
    
    sqlContent := string(sqlBytes)
    
    // 执行SQL命令
    _, err = db.Exec(sqlContent)
    if err != nil {
        return fmt.Errorf("执行SQL失败: %v", err)
    }
    
    return nil
}

func main() {
    db, err := sql.Open("postgres", "user=postgres dbname=test sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    err = executeSQLFromFile(db, "queries.sql")
    if err != nil {
        log.Fatal(err)
    }
}

2. 加密SQL文件并动态解密执行

package main

import (
    "crypto/aes"
    "crypto/cipher"
    "crypto/rand"
    "database/sql"
    "encoding/base64"
    "fmt"
    "io"
    "io/ioutil"
    "log"
)

// 加密SQL文件
func encryptSQLFile(inputFile, outputFile string, key []byte) error {
    plaintext, err := ioutil.ReadFile(inputFile)
    if err != nil {
        return err
    }
    
    block, err := aes.NewCipher(key)
    if err != nil {
        return err
    }
    
    ciphertext := make([]byte, aes.BlockSize+len(plaintext))
    iv := ciphertext[:aes.BlockSize]
    if _, err := io.ReadFull(rand.Reader, iv); err != nil {
        return err
    }
    
    stream := cipher.NewCFBEncrypter(block, iv)
    stream.XORKeyStream(ciphertext[aes.BlockSize:], plaintext)
    
    return ioutil.WriteFile(outputFile, ciphertext, 0644)
}

// 解密并执行SQL
func executeEncryptedSQL(db *sql.DB, encryptedFile string, key []byte) error {
    ciphertext, err := ioutil.ReadFile(encryptedFile)
    if err != nil {
        return err
    }
    
    block, err := aes.NewCipher(key)
    if err != nil {
        return err
    }
    
    if len(ciphertext) < aes.BlockSize {
        return fmt.Errorf("密文太短")
    }
    
    iv := ciphertext[:aes.BlockSize]
    ciphertext = ciphertext[aes.BlockSize:]
    
    stream := cipher.NewCFBDecrypter(block, iv)
    stream.XORKeyStream(ciphertext, ciphertext)
    
    sqlContent := string(ciphertext)
    
    // 执行解密后的SQL
    _, err = db.Exec(sqlContent)
    return err
}

func main() {
    key := []byte("32-byte-key-for-AES-256-encryption!")
    
    // 加密SQL文件
    err := encryptSQLFile("original.sql", "encrypted.sql", key)
    if err != nil {
        log.Fatal(err)
    }
    
    // 连接数据库
    db, err := sql.Open("postgres", "connection_string")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // 执行加密的SQL
    err = executeEncryptedSQL(db, "encrypted.sql", key)
    if err != nil {
        log.Fatal(err)
    }
}

3. 支持多条SQL语句和事务处理

package main

import (
    "database/sql"
    "fmt"
    "strings"
    "io/ioutil"
)

func executeMultipleSQLFromFile(db *sql.DB, filename string) error {
    content, err := ioutil.ReadFile(filename)
    if err != nil {
        return err
    }
    
    // 按分号分割SQL语句
    sqlStatements := strings.Split(string(content), ";")
    
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()
    
    for _, stmt := range sqlStatements {
        stmt = strings.TrimSpace(stmt)
        if stmt == "" {
            continue
        }
        
        _, err := tx.Exec(stmt)
        if err != nil {
            return fmt.Errorf("执行SQL失败: %v\nSQL: %s", err, stmt)
        }
    }
    
    return tx.Commit()
}

// 使用示例
func main() {
    // 假设已有数据库连接
    // err := executeMultipleSQLFromFile(db, "queries.sql")
}

4. 带缓存的SQL文件加载器

package main

import (
    "crypto/sha256"
    "database/sql"
    "encoding/hex"
    "fmt"
    "io/ioutil"
    "sync"
    "time"
)

type SQLFileExecutor struct {
    cache     map[string]string
    cacheTime map[string]time.Time
    mutex     sync.RWMutex
    key       []byte
}

func NewSQLFileExecutor(encryptionKey []byte) *SQLFileExecutor {
    return &SQLFileExecutor{
        cache:     make(map[string]string),
        cacheTime: make(map[string]time.Time),
        key:       encryptionKey,
    }
}

func (s *SQLFileExecutor) getFileHash(filename string) (string, error) {
    content, err := ioutil.ReadFile(filename)
    if err != nil {
        return "", err
    }
    
    hash := sha256.Sum256(content)
    return hex.EncodeToString(hash[:]), nil
}

func (s *SQLFileExecutor) ExecuteSQL(db *sql.DB, filename string) error {
    s.mutex.RLock()
    cachedSQL, exists := s.cache[filename]
    cacheTime, timeExists := s.cacheTime[filename]
    s.mutex.RUnlock()
    
    currentHash, err := s.getFileHash(filename)
    if err != nil {
        return err
    }
    
    // 检查缓存是否有效（5分钟有效期）
    if exists && timeExists && time.Since(cacheTime) < 5*time.Minute {
        _, err = db.Exec(cachedSQL)
        return err
    }
    
    // 读取并解密文件
    content, err := ioutil.ReadFile(filename)
    if err != nil {
        return err
    }
    
    // 这里可以添加解密逻辑
    sqlContent := string(content) // 实际使用时应解密
    
    s.mutex.Lock()
    s.cache[filename] = sqlContent
    s.cacheTime[filename] = time.Now()
    s.mutex.Unlock()
    
    _, err = db.Exec(sqlContent)
    return err
}

这些示例展示了如何在Golang中读取、加密、解密和执行文件中的SQL命令。可以根据具体需求选择合适的加密算法和缓存策略。