golang连接mysql数据库导出csv文件

golang连接mysql数据库导出csv文件

package main

import (
	"database/sql"
	"encoding/csv"
	"log"
	"os"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 连接数据库
	db, err := sql.Open("mysql", "username:password@tcp(host:3306)/数据库名?charset=utf8mb4&parseTime=True")
	if err != nil {
		log.Printf("open mysql failed: %v", err)
		return
	}

	defer db.Close()

	rows, err := db.Query("select id, name, email from user")
	if err != nil {
		log.Printf("query failed: %v", err)
		return
	}

	var csvData [][]string
	csvData = append(csvData, []string{"用户ID", "用户名", "邮箱"})

	for rows.Next() {
		var userID string
		var name string
		var email string

		if err := rows.Scan(&userID, &name, &email); err != nil {
			log.Printf("scan failed: %v", err)
			return
		}
    
    // 修改数据库
    // if _, err := db.Exec("UPDATE user SET name=? where id=?", "test", userID); err != nil {
		//	log.Printf("exec failed: %v, id=%v", err, userID)
		//	return
		// }

		lineData := []string{
			userID,
			name,
			email,
		}
		csvData = append(csvData, lineData)
	}

	fileName := time.Now().Format("20060102150405") + ".csv"
	filePath := "/tmp/" + fileName

	fileErr := WriteCsv(filePath, csvData)
	if fileErr != nil {
		log.Printf("writeCsv fialed: %v", fileErr)
		return
	}
  
  // 接口请求导出
  // ctx.FileAttachment(filePath, fileName)
}

//filePath: 文件全路径 绝对路径
//content: 包含行列 需要头部要增加第一列
func WriteCsv(filePath string, content [][]string) error {
	file, err := os.Create(filePath)
	if err != nil {
		return err
	}
	defer file.Close()
	// 写入UTF-8 BOM，防止中文乱码
	file.WriteString("\xEF\xBB\xBF")
	w := csv.NewWriter(file)

	for _, v := range content {
		err = w.Write(v)
		if err != nil {
			return err
		}
	}
	w.Flush()
	return nil
}