Golang Database Batch Update Tool backup

This is a creation in Article, where the information may have evolved or changed.

Bulk update data data using Golang multithreading.

A total of 4 files

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"://Platform balance table data static fz.run () "0": Os.exit (0) Default:}}if len (args) = = 1 {for {fmt. PRINTLN ("Operating directory:") fmt. Println ("1, Platform effective customer update (202:13306-platform). ") Fmt. Println ("2, Platform debt data static (202:13306-platform). ") Fmt. Println ("0, exit. ") Inputreader: = Bufio. Newreader (OS. Stdin) Command, _, _: = Inputreader.readline () Code: = string (command) switch code {case "1": Cu.run () Case "2":// Platform balance sheet data Static fz.run () case "0": Os.exit (0) default:fmt. PRINTLN ("Default")}fmt. PRINTLN ("-------Processing completed-------")}}}

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 ")/** * Database connection */func Mydb () *sql. DB {db, err: = SQL. Open ("MySQL", DSN) if err! = Nil {log. Fatalf ("Open Database error:%s\n", err)}//defer db. Close ()//Do not close connection err = db. Ping () if err! = Nil {log. Fatal (ERR)}return db}/** * Upgrade log write  file append parameter wonderful more than 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  

Debt Data Static encapsulation package Ptimport ("Database/sql" "FMT" "Log" "Time")//Store liability data type fzdata struct {mdid Intxjye float32zsye float 32lcye float32month string}type fzdatamap map[int]fzdatatype fzclass struct {list fzdatamap}//list needs to be initialized before it is used, so it is initialized directly at new The following 2 methods of initialization can be Func NEWFZ () *fzclass {FZ: = &fzclass{list:make (fzdatamap)}//fz: = new (Fzclass)//fz.list = Make (Fzdatam AP) return Fz}func (FZ *fzclass) Show () *fzclass {for I, Value: = Range fz.list {fmt. Println (i) fmt. Println (value)}return fz}//no OVA convenient big Data extension func (FZ *fzclass) Add (row fzdata) *fzclass {Fz.list[row.mdid] = Rowreturn fz}//Store Data is statically inbound 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 {//There is an update 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 {//does not exist insert 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)}}/** * A company liability processing * @param {[type]} c chan int [log Pipeline] * @param {[Type]} comp_id int [company ID] * @param {[type]} fz_month int [debt month] */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, IFN ULL (TT. ' Lcye ', 0) as Lcye from ' customer_capital "T ' left join company_capital cc on cc.id = t.capital_id LEFT JOIN custom Er_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.P Ay_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 o SD 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)//Return pipeline information written to C <-"Company:" + FMT. Sprintf ("%d", comp_id) + "store:" + FMT. Sprintf ("%d", Rowdata.mdid) + "(processing done)"}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)//Open multiple pipes Accept message 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 {//multi-pipe notation for {x, OK: = <-chif OK = = False {Break}writeresult ("FZ", X) fmt. PRINTLN (x)//message recycling processing extensible write file log}}}var FZ *fzclassfunc init () {FZ = NEWFZ ()}

Pt/custatus.go

Customer attributes Auto-update package//require the company to turn on Automatic Updates and configure customer expiration cycle time for the Ptimport ("FMT" "Log" "StrConv" "Strings" "Times") type Custatusclass struct { }func NEWCU () *custatusclass {obj: = new (Custatusclass) return obj}/** * Determine customer status **/func (obj *custatusclass) getState (order s int, practs int, state int) int {if orders > 0 | | practs > 0 {return 1//recent order or have actual operation as valid customer}if state = = 3 {return 3 Dead client}if state = =-1 {return-1//invalid customer}//default return for long party customer return 2}/* * Get company about active customer configuration days default 30 days */func (obj *custatusclass) getconf IG (str string) int {var num intn: = strings. Index (str, "Member_config") if n = =-1 {num = +} else {start: = n + 20end: = n + 22num2: = String ([]byte (str) [start:end]) n Um, _ = StrConv. Atoi (num2)}return num}/** * Update a company's customer status (PT) consider creating a new database connection for increased efficiency * @param {[type]} db *sql.            DB [Description] * @param {[type]} c chan int [description] * @param {[type]} comp_id int [Company ID] * @param {[type]} num int [days of validity] * @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)//forward num days sql: = "Select A.id,a.name,a.status, (select COUNT (*) from ' Order_sale ' WHE RE ' cu_id ' = a.id and ' Pay_time ' >? and ' Pay_time ' <? 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 in a.membership_id = m.id WHERE m. ' is_member ' = 1 A ND 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)//Return pipeline information written to 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)}/** * Multi-company concurrent Processing (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)//Open multiple pipes to accept the message 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)//customer lifetime setting c: = Make (chan string) CHS = Append (CHS, c) Go Obj.updateonec OMP (c, id, num)}}err = rows. ERR () if err! = Nil {log. Fatal (ERR)}for _, ch: = range CHS {//multi-pipe notation for {x, OK: = <-chif OK = = False {Break}writeresult ("Cu_status", X) fmt. PRINTLN (x)//message recycling processing extensible write file log}}}var cu *custatusclassfunc init () {cu = Newcu ()}