Golang如何从MySQL中读取和列出文件

在Golang中从MySQL读取和存储为BLOB的文件并实现下载功能，可以通过以下方式实现：

1. 数据库表结构示例

CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    file_data LONGBLOB NOT NULL,
    mime_type VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 文件结构定义

type FileRecord struct {
    ID       int
    Filename string
    FileData []byte
    MimeType string
}

3. 从数据库读取文件列表

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
    
    _ "github.com/go-sql-driver/mysql"
)

func listFiles(db *sql.DB) ([]FileRecord, error) {
    rows, err := db.Query("SELECT id, filename, mime_type FROM files")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var files []FileRecord
    for rows.Next() {
        var file FileRecord
        err := rows.Scan(&file.ID, &file.Filename, &file.MimeType)
        if err != nil {
            return nil, err
        }
        files = append(files, file)
    }
    return files, nil
}

4. 下载文件处理函数

func downloadFileHandler(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        fileID := r.URL.Query().Get("id")
        if fileID == "" {
            http.Error(w, "File ID required", http.StatusBadRequest)
            return
        }

        var file FileRecord
        err := db.QueryRow("SELECT filename, file_data, mime_type FROM files WHERE id = ?", fileID).
            Scan(&file.Filename, &file.FileData, &file.MimeType)
        if err != nil {
            if err == sql.ErrNoRows {
                http.Error(w, "File not found", http.StatusNotFound)
            } else {
                http.Error(w, "Database error", http.StatusInternalServerError)
            }
            return
        }

        // 设置响应头
        w.Header().Set("Content-Disposition", "attachment; filename="+file.Filename)
        w.Header().Set("Content-Type", file.MimeType)
        w.Header().Set("Content-Length", fmt.Sprintf("%d", len(file.FileData)))

        // 写入文件数据
        _, err = w.Write(file.FileData)
        if err != nil {
            http.Error(w, "Failed to write file", http.StatusInternalServerError)
            return
        }
    }
}

5. 完整示例

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    http.HandleFunc("/files", func(w http.ResponseWriter, r *http.Request) {
        files, err := listFiles(db)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }

        // 返回文件列表
        for _, file := range files {
            fmt.Fprintf(w, "File: %s (ID: %d) - <a href=\"/download?id=%d\">Download</a><br>", 
                file.Filename, file.ID, file.ID)
        }
    })

    http.HandleFunc("/download", downloadFileHandler(db))

    log.Println("Server started on :8080")
    log.Fatal(http.ListenAndServe(":8080", nil))
}

6. 上传文件到数据库

func uploadFileHandler(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        if r.Method != "POST" {
            http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
            return
        }

        file, header, err := r.FormFile("file")
        if err != nil {
            http.Error(w, "Failed to get file", http.StatusBadRequest)
            return
        }
        defer file.Close()

        fileData, err := io.ReadAll(file)
        if err != nil {
            http.Error(w, "Failed to read file", http.StatusInternalServerError)
            return
        }

        mimeType := http.DetectContentType(fileData)

        _, err = db.Exec("INSERT INTO files (filename, file_data, mime_type) VALUES (?, ?, ?)",
            header.Filename, fileData, mimeType)
        if err != nil {
            http.Error(w, "Failed to save file", http.StatusInternalServerError)
            return
        }

        w.Write([]byte("File uploaded successfully"))
    }
}

这个实现提供了完整的文件上传、列表显示和下载功能。文件以BLOB形式存储在MySQL中，下载时通过HTTP响应直接返回字节数据。