mysql資料匯出golang實現

mysql資料匯出為excel檔案，golang實現：

首先下載依賴到的三方庫：

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

$ go get -u github.com/go-sql-driver/mysql

具體說明請看：
庫地址
wiki說明

程式碼範例如下，用到了go的flag包的能力，傳入命令列參數。具體看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

代碼：

package main// 從Mysql中匯出資料到CSV檔案。import (    "database/sql"    "encoding/csv"    "fmt"    "os"    _ "github.com/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 := flag.Int("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}//writeToCSVfunc 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()}