mysql数据导出golang实现

转载

进击云原生 2021-07-07 14:55:52

文章标签 GO语言 文章分类 Go语言后端开发

Simple install the package to your $GOPATH with the go tool from shell:

$ go get -u /go-sql-driver/mysql

具体说明请看：

库地址：

https:///go-sql-driver/mysql

wiki说明：

https:///go-sql-driver/mysql/wiki/Examples

代码示例如下，用到了go的flag包的能力，传入命令行参数。具体看helpInfo：

flag包的使用可以参考：golang flag包使用笔记

Usage of mysqldataexport:
-port int
the port for mysql,default:32085
-addr string
the address for mysql,default:10.146.145.67
-user string
the username for login mysql,default:dbuser

-pwd string
the password for login mysql by the username,default:Admin@123
-db string
the port for me to listen on,default:auditlogdb
-tables string
the tables will export data, multi tables separator by comma, default:op_log,sc_log,sys_log

mysql数据导出golang实现_GO语言

代码：

package main

// 从Mysql中导出数据到CSV文件。

import (
"database/sql"
"encoding/csv"
"fmt"
"os"
_ "/go-sql-driver/mysql"
"flag"
"strings"
)

var (
tables = make([]string, 0)
dataSourceName = ""
)

const (
driverNameMysql = "mysql"

helpInfo = `Usage of mysqldataexport:
-port int
the port for mysql,default:32085
-addr string
the address for mysql,default:10.146.145.67
-user string
the username for login mysql,default:dbuser

-pwd string
the password for login mysql by the username,default:Admin@123
-db string
the port for me to listen on,default:auditlogdb
-tables string
the tables will export data, multi tables separator by comma, default:op_log,sc_log,sys_log
`
)

func init() {

port := ("port", 32085, "the port for mysql,default:32085")
addr := flag.String("addr", "10.146.145.67", "the address for mysql,default:10.146.145.67")
user := flag.String("user", "dbuser", "the username for login mysql,default:dbuser")
pwd := flag.String("pwd", "Admin@123", "the password for login mysql by the username,default:Admin@123")
db := flag.String("db", "auditlogdb", "the port for me to listen on,default:auditlogdb")
tabs := flag.String("tables", "op_log,sc_log,sys_log", "the tables will export data, multi tables separator by comma, default:op_log,sc_log,sys_log")

flag.Usage = usage

flag.Parse()

tables = append(tables, strings.Split(*tabs, ",")...)

dataSourceName = fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8", *user, *pwd, *addr, *port, *db)
}

func main() {

count := len(tables)
ch := make(chan bool, count)

db, err := sql.Open(driverNameMysql, dataSourceName)
defer db.Close()
if err != nil {
panic(err.Error())
}

// Open doesn't open a connection. Validate DSN data:
err = db.Ping()
if err != nil {
panic(err.Error())
}

for _, table := range tables {
go querySQL(db, table, ch)
}

for i := 0; i < count; i++ {
<-ch
}
fmt.Println("Done!")
}

func querySQL(db *sql.DB, table string, ch chan bool) {
fmt.Println("开始处理：", table)
rows, err := db.Query(fmt.Sprintf("SELECT * from %s", table))

if err != nil {
panic(err)
}

columns, err := rows.Columns()
if err != nil {
panic(err.Error())
}

//values：一行的所有值,把每一行的各个字段放到values中，values长度==列数
values := make([]sql.RawBytes, len(columns))
// print(len(values))

scanArgs := make([]interface{}, len(values))
for i := range values {
scanArgs[i] = &values[i]
}

//存所有行的内容totalValues
totalValues := make([][]string, 0)
for rows.Next() {

//存每一行的内容
var s []string

//把每行的内容添加到scanArgs，也添加到了values
err = rows.Scan(scanArgs...)
if err != nil {
panic(err.Error())
}

for _, v := range values {
s = append(s, string(v))
// print(len(s))
}
totalValues = append(totalValues, s)
}

if err = rows.Err(); err != nil {
panic(err.Error())
}
writeToCSV(table+".csv", columns, totalValues)
ch <- true
}

//writeToCSV
func writeToCSV(file string, columns []string, totalValues [][]string) {
f, err := os.Create(file)
// fmt.Println(columns)
defer f.Close()
if err != nil {
panic(err)
}
//f.WriteString("\xEF\xBB\xBF")
w := csv.NewWriter(f)
for i, row := range totalValues {
//第一次写列名+第一行数据
if i == 0 {
w.Write(columns)
w.Write(row)
} else {
w.Write(row)
}
}
w.Flush()
fmt.Println("处理完毕：", file)
}

func usage() {
fmt.Fprint(os.Stderr, helpInfo)
flag.PrintDefaults()
}