导出mysql数据为csv格式

一、mysql

方式1：

select * into outfile 'E:\\xxx\\test.csv' 
fields terminated by ',' 
lines terminated by '\n' 
from (select *from test) b;

执行上述语句可能会报错：https://blog.csdn.net/weixin_44595372/article/details/88723191

方式2：

mysql -uroot test -e "sql语句"  -N -s | sed -e 's/^/"/g;s/$/"\n/g' > /tmp/test.csv

#或者
mysql -uroot test -e "sql语句" > /tmp/test.csv

详见：https://www.jb51.net/article/73735.htm

二、php处理

查询数据，通过逗号拼接字段数据，\n分割每一行数据

<?php

$host = 'localhost';
$user = '用户名';
$password = '密码';
$database = 'test';
$port = '端口';

$conn = mysqli_init();
$conn->connect($host, $user, $password, $database, $port);

$sql = 'select id,`name`,course,score from score';
$query = $conn->query($sql);

$data = [];
while ($row = $query->fetch_array(MYSQLI_NUM)) {
    $data[] = $row;
}

$content = "id,name,course,score\n";
foreach ($data as $d) {
    $content .= implode(',', $d) . "\n";
}
file_put_contents('file.csv', $content);
// 如果数据量大的话，请将数据循环写入文件，
// 而不是通过file_put_contents一次性写入文件

如果想将生成的文件转换为xls文件，只要打开csv文件另存为xls文件即可

三、python处理

使用workbook相关......

详见：https://blog.csdn.net/helloxiaozhe/article/details/77849932

四、go处理

下载github.com/tealeg/xlsx包，然后进行数据处理，另存为xlsx后缀的文件

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/tealeg/xlsx"
	"strconv"
	"time"
)

// mysql操作：https://www.jianshu.com/p/9b5cd762e256
// excel操作；https://studygolang.com/articles/5259
const (
	DRIVER   = "mysql"
	USERNAME = "用户名"
	PASSWORD = "密码"
	HOST     = "localhost"
	PORT     = "端口"
	DATABASE = "test"
	CHARSET  = "utf8"
)

var db *sql.DB
var err error

type Score struct {
	Id     int64
	Name   string
	Course string
	Score  int64
}

// db连接
func DbConn() {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", USERNAME, PASSWORD, HOST, PORT, DATABASE, CHARSET)
	db, err = sql.Open(DRIVER, dsn)
	if err != nil {
		panic("数据源配置不正确：" + err.Error())
	}

	db.SetMaxOpenConns(100)                  // 最大连接数
	db.SetMaxIdleConns(20)                   // 闲置连接数
	db.SetConnMaxLifetime(100 * time.Second) // 最大连接周期

	if err = db.Ping(); err != nil {
		panic("数据库连接失败：" + err.Error())
	}
}

var data = make([]Score, 0)

// 从db获取数据
func GetDataFromDb() {
	sqlstr := "select * from score"
	rows, _ := db.Query(sqlstr)

	var result Score
	for rows.Next() {
		rows.Scan(&result.Id, &result.Name, &result.Course, &result.Score)
		data = append(data, result)
	}

	//fmt.Println(data)
	//fmt.Printf("%+v", data)
}

// 导出到csv文件
func ExportToCsvFile() {
	file := xlsx.NewFile()
	sheet, _ := file.AddSheet("Sheet1")
	//row := sheet.AddRow()
	//row.SetHeightCM(1) //设置每行的高度
	//cell := row.AddCell()
	//cell.Value = "hello"
	//cell = row.AddCell()
	//cell.Value = "world"
	row := sheet.AddRow()
	cell := row.AddCell()
	cell.Value = "Id"
	cell = row.AddCell()
	cell.Value = "Name"
	cell = row.AddCell()
	cell.Value = "Course"
	cell = row.AddCell()
	cell.Value = "Score"
	for _, item := range data {
		row = sheet.AddRow()
		cell = row.AddCell()
		cell.Value = strconv.FormatInt(item.Id, 10)
		cell = row.AddCell()
		cell.Value = item.Name
		cell = row.AddCell()
		cell.Value = item.Course
		cell = row.AddCell()
		cell.Value = strconv.FormatInt(item.Score, 10)
	}

	err := file.Save("file.xlsx")
	if err != nil {
		panic(err)
	}
}

func main() {
	DbConn()          //数据库连接
	GetDataFromDb()   // 从数据库获取数据
	ExportToCsvFile() // 导出到csv文件
}

详见：https://studygolang.com/articles/5259