golang 資料庫批次更新工具備份

這是一個建立於 的文章，其中的資訊可能已經有所發展或是發生改變。

使用golang多線程批次更新資料資料。

共4個檔案

main.go

package mainimport ("bufio""fmt"_ "github.com/go-sql-driver/mysql""os". "pt")func main() {args := os.Argsif len(args) == 2 {switch args[1] {case "1":Cu.Run()case "2"://平台負債表資料靜態化Fz.Run()case "0":os.Exit(0)default:}}if len(args) == 1 {for {fmt.Println("操作目錄: ")fmt.Println("1、平台有效客戶更新（202:13306-platform）。 ")fmt.Println("2、平台負債資料靜態化（202:13306-platform）。")fmt.Println("0、退出。 ")inputReader := bufio.NewReader(os.Stdin)command, _, _ := inputReader.ReadLine()code := string(command)switch code {case "1":Cu.Run()case "2"://平台負債表資料靜態化Fz.Run()case "0":os.Exit(0)default:fmt.Println("default")}fmt.Println("-------處理完成-------")}}}

pt/lib.go      

package ptimport ("database/sql"_ "github.com/go-sql-driver/mysql""log""os""time")const (DSN string = "root:mysqladmin56@tcp(192.168.0.202:13306)/platform?charset=utf8"//DSN string = "root:@tcp(127.0.0.1:3306)/db_name?charset=utf8")/** * 資料庫連接 */func Mydb() *sql.DB {db, err := sql.Open("mysql", DSN)if err != nil {log.Fatalf("Open database error: %s\n", err)}//defer db.Close() //不關閉串連err = db.Ping()if err != nil {log.Fatal(err)}return db}/** * 升級日誌寫入  檔案追加參數奇葩 多 要3個 * @param  {[type]} log string        [description] * @return {[type]}     [description] */func writeResult(tag string, data string) {str_time := time.Now().Format("2006_01_02")filename := tag + "_" + str_time + ".log"fl, err := os.OpenFile(filename, os.O_APPEND|os.O_CREATE|os.O_RDWR, 0644)if err != nil {log.Println(err)}defer fl.Close()fl.WriteString(data)fl.WriteString("\n")}

pt/fz.go  

// 負債資料靜態化封裝package ptimport ("database/sql""fmt""log""time")//門店負債資料type FzData struct {mdid  intxjye  float32zsye  float32lcye  float32month string}type FzDataMap map[int]FzDatatype FzClass struct {list FzDataMap}//list 使用前需要被初始化  所以直接在new時初始化 以下2種初始化方式都可func NewFz() *FzClass {fz := &FzClass{list: make(FzDataMap)}//fz := new(FzClass)//fz.list = make(FzDataMap)return fz}func (fz *FzClass) Show() *FzClass {for i, value := range fz.list {fmt.Println(i)fmt.Println(value)}return fz}//暫無卵用 方便大資料擴充func (fz *FzClass) Add(row FzData) *FzClass {fz.list[row.mdid] = rowreturn fz}//門店資料靜態化 入庫func (fz *FzClass) toDb(db *sql.DB, row FzData) {var num intone, err := db.Query("SELECT  COUNT(*) AS num  FROM `static_fz` WHERE `md_id` = ? AND `month` = ? ", row.mdid, row.month)if err != nil {log.Println(err)}defer one.Close()for one.Next() {err := one.Scan(&num)if err != nil {log.Fatal(err)}}if num > 0 {//存在更新stmt, _ := db.Prepare("UPDATE `static_fz` SET `xjye`=?, `zsye`=?, `lcye`=? WHERE `md_id` = ? AND `month` = ? ")defer stmt.Close()stmt.Exec(row.xjye, row.zsye, row.lcye, row.mdid, row.month)} else {//不存在插入stmt, _ := db.Prepare("INSERT INTO `static_fz` (`md_id`, `xjye`, `zsye`, `lcye`,`month`) VALUES (?,?,?,?,?)")defer stmt.Close()stmt.Exec(row.mdid, row.xjye, row.zsye, row.lcye, row.month)}}/** * 某一公司負債處理 * @param  {[type]} c chan int            [日誌管道] * @param  {[type]} comp_id int           [公司ID] * @param  {[type]} fz_month int          [負債月份] */func (fz *FzClass) oneComp(c chan string, comp_id int, fz_month string) {db := Mydb()defer db.Close()sql := "SELECT ed.`id`, SUM(IF(cc.type = 1, t.balance, 0)) AS xjye, SUM(IF(cc.type = 2, t.balance, 0)) AS zsye, IFNULL(tt.`lcye`,0) AS lcye FROM `customer_capital` `t` LEFT JOIN company_capital cc ON cc.id = t.capital_id LEFT JOIN customer_info ci ON ci.id = t.cu_id LEFT JOIN employ_dept ed ON ed.id = ci.store_id LEFT JOIN (SELECT ed.`id`,SUM(TRUNCATE(osd.pay_price / num * t.re_num, 1)) AS lcye FROM `customer_re_project` `t` LEFT JOIN customer_info ci ON ci.id = t.cu_id LEFT JOIN employ_dept ed ON ed.id = ci.store_id LEFT JOIN order_sale_detail osd ON osd.id = t.detail_id WHERE (ed.comp_id = ?) GROUP BY ed.id) tt ON tt.id = ed.id WHERE (ed.comp_id = ?) GROUP BY ed.id"rows, err := db.Query(sql, comp_id, comp_id)if err != nil {log.Println(err)}defer rows.Close()var rowData FzDatarowData.month = fz_monthfor rows.Next() {err := rows.Scan(&rowData.mdid, &rowData.xjye, &rowData.zsye, &rowData.lcye)if err != nil {log.Fatal(err)}//fz.Add(rowData)fz.toDb(db, rowData)//返回管道資訊寫入c <- "公司:" + fmt.Sprintf("%d", comp_id) + "->門店:" + fmt.Sprintf("%d", rowData.mdid) + "(處理完成)"}err = rows.Err()if err != nil {log.Fatal(err)}close(c)}func (fz *FzClass) Run() {db := Mydb()defer db.Close()sql := "SELECT id FROM `company_info` WHERE `status` = 1"rows, err := db.Query(sql)if err != nil {log.Println(err)}defer rows.Close()chs := make([]chan string, 0) //開多個管道接受訊息fz_month := time.Now().Format("200601")var i int = 0var id intfor rows.Next() {c := make(chan string)chs = append(chs, c)err := rows.Scan(&id)if err != nil {log.Fatal(err)}go fz.oneComp(c, id, fz_month)i = i + 1}err = rows.Err()if err != nil {log.Fatal(err)}for _, ch := range chs { //多管道寫法for {x, ok := <-chif ok == false {break}writeResult("fz", x)fmt.Println(x) //訊息回收處理 可擴充寫入檔案日誌}}}var Fz *FzClassfunc init() {Fz = NewFz()}

 pt/custatus.go

// 客戶屬性自動更新封裝// 需要公司開啟自動更新並配置客戶到期周期時間package ptimport ("fmt""log""strconv""strings""time")type CuStatusClass struct {}func NewCu() *CuStatusClass {obj := new(CuStatusClass)return obj}/** *判斷客戶狀態 **/func (obj *CuStatusClass) getState(orders int, practs int, state int) int {if orders > 0 || practs > 0 {return 1 //最近有訂單OR有實操 為有效客戶}if state == 3 {return 3 //死檔客戶}if state == -1 {return -1 //無效客戶}//預設返回為久黨客戶return 2}/* * 擷取公司關於有效客戶的配置天數 預設30天 */func (obj *CuStatusClass) getConfig(str string) int {var num intn := strings.Index(str, "member_config")if n == -1 {num = 30} else {start := n + 20end := n + 22num2 := string([]byte(str)[start:end])num, _ = strconv.Atoi(num2)}return num}/** * 更新一個公司的客戶狀態 (PT) 考慮建立資料庫連接 提高效率 * @param  {[type]} db      *sql.DB       [description] * @param  {[type]} c       chan          int           [description] * @param  {[type]} comp_id int           [公司ID] * @param  {[type]} num int             [有效期間天數] * @return {[type]}           [description] */func (obj *CuStatusClass) updateOneComp(c chan string, comp_id int, num int) {db := Mydb()defer db.Close()end := time.Now().Unix()start := end - 3600*24*int64(num) //前推num天sql := "SELECT a.id,a.name,a.status,(SELECT COUNT(*) FROM `order_sale` WHERE `cu_id` = a.id AND `pay_time` > ? AND `pay_time` < ? AND `type` IN (1,2)) AS orders, (SELECT COUNT(*) FROM `practice_order` WHERE `cu_id` = a.id AND `pay_time` > ? AND `pay_time` < ?) AS practs FROM `customer_info` AS a LEFT JOIN `config_membership` AS m ON a.membership_id = m.id WHERE m.`is_member` = 1 AND a.`company_id` = ?"rows, err := db.Query(sql, start, end, start, end, comp_id)if err != nil {log.Println(err)}defer rows.Close()var id intvar orders intvar practs intvar name stringvar status intfor rows.Next() {err := rows.Scan(&id, &name, &status, &orders, &practs)if err != nil {log.Fatal(err)}new_status := obj.getState(orders, practs, status)if status != new_status {stmt, err := db.Prepare("UPDATE `customer_info` SET `status`=? WHERE `id`=?")defer stmt.Close()if err != nil {log.Println(err)return}stmt.Exec(new_status, id)//返回管道資訊寫入c <- fmt.Sprintf("%d", comp_id) + ":" + fmt.Sprintf("%d", id) + " " + name + " " + fmt.Sprintf("%d", status) + "->" + fmt.Sprintf("%d", new_status)}}err = rows.Err()if err != nil {log.Fatal(err)}close(c)}/** * 多公司並發處理 (PT) * @param  {[type]} db *sql.DB       [description] * @return {[type]}    [description] */func (obj *CuStatusClass) Run() {db := Mydb()defer db.Close()sql := "SELECT id , auto_cu_status, config FROM `company_info` WHERE `status` = 1"rows, err := db.Query(sql)if err != nil {log.Println(err)}defer rows.Close()chs := make([]chan string, 0) //開多個管道接受訊息var id intvar auto intvar config stringfor rows.Next() {err := rows.Scan(&id, &auto, &config)if err != nil {log.Fatal(err)}if auto == 1 {num := obj.getConfig(config) //客戶有效期間設定c := make(chan string)chs = append(chs, c)go obj.updateOneComp(c, id, num)}}err = rows.Err()if err != nil {log.Fatal(err)}for _, ch := range chs { //多管道寫法for {x, ok := <-chif ok == false {break}writeResult("cu_status", x)fmt.Println(x) //訊息回收處理 可擴充寫入檔案日誌}}}var Cu *CuStatusClassfunc init() {Cu = NewCu()}