vlambda博客
学习文章列表

package main
import ( "fmt" "strconv" "strings" "time" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx")
func main() { accountings := initData(15000)  //insert00(accountings)  //insert01(accountings)  //insert02(accountings)  insert03(accountings)}func initData(length int) []Accounting { accountings := make([]Accounting, length)
 for i := 0; i < length; i++ { ii, _ := strconv.Atoi(time.Now().Format("20060102150405") + fmt.Sprint(i)) accountings[i] = Accounting{ No: uint64(ii), AccountNo: 132079455772000256, AccountTime: time.Now(), Amount: 1000, Balance: 0, Currency: "JPY", Direction: 1, Operation: "charge", IsDischarge: false, BusinessID: "b" + fmt.Sprint(i), BusinessTime: time.Now(), BusinessCode: "StarPay", CreateTime: time.Now(), GroupID: "group000" + fmt.Sprint(i), } } return accountings}
func insert00(accountings []Accounting) { begin := time.Now() insertSQL := `INSERT INTO accounting (no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)` db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8")
 conn, _ := db.Beginx() for _, e := range accountings { conn.Exec(insertSQL, e.No, e.AccountNo, e.AccountTime, e.Amount, e.Balance, e.Currency, e.Direction, e.Operation, e.IsDischarge, e.BusinessID, e.BusinessTime, e.BusinessCode, e.CreateTime, e.GroupID)  } conn.Commit() fmt.Println(time.Now().Sub(begin).Microseconds()) defer db.Close()}
func insert01(accountings []Accounting) { begin := time.Now()
 insertSQL := `INSERT INTO accounting (no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id) VALUES (:no,:account_no,:account_time,:amount,:balance,:currency,:direction,:operation,:is_discharge,:business_id,:business_time,:business_code,:create_time,:group_id)` db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8") conn, _ := db.Beginx() ind := len(accountings) / 4000 yu := len(accountings) % 4000 if yu > 0 { ind = ind + 1 } for i := 0; i < ind; i++ {
 end := (i + 1) * 4000 if i == ind-1 && yu != 0 { end = (i * 4000) + yu } accs := accountings[i*4000 : end] conn.NamedExec(insertSQL, accs)
 } conn.Commit() fmt.Println(time.Now().Sub(begin).Microseconds()) defer db.Close()}
func insert02(accountings []Accounting) { begin := time.Now() values := make([]string, len(accountings)) for i := 0; i < len(values); i++ { account := accountings[i] value := `(` + fmt.Sprint(account.No) + "," + fmt.Sprint(account.AccountNo) + ",'" + account.AccountTime.Format("2006-01-02 15:04:05.000") + "'," + strconv.FormatFloat(account.Amount, 'f', 4, 64) + "," + strconv.FormatFloat(account.Balance, 'f', 4, 64) + ",'" + account.Currency + "'," + strconv.Itoa(int(account.Direction)) + ",'" + account.Operation + "'," + strconv.FormatBool(account.IsDischarge) + ",'" + account.BusinessID + "','" + account.BusinessTime.Format("2006-01-02 15:04:05.000") + "','" + account.BusinessCode + "','" + account.CreateTime.Format("2006-01-02 15:04:05.000") + "','" + account.GroupID + "')" values[i] = value } insertSQL := `INSERT INTO accounting (no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id) VALUES` for i := 0; i < len(values); i++ { insertSQL = insertSQL + values[i] + "," } insertSQL = strings.TrimRight(insertSQL, ",") fmt.Println(time.Now().Sub(begin).Microseconds()) db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8") db.Exec(insertSQL) fmt.Println(time.Now().Sub(begin).Microseconds()) defer db.Close()}func insert03(accountings []Accounting) { begin := time.Now() insertSQL := `INSERT INTO accounting(no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id) VALUES` sumSql := "" for i := 0; i < len(accountings); i++ { account := accountings[i] sumSql = sumSql + insertSQL + `(` + fmt.Sprint(account.No) + "," + fmt.Sprint(account.AccountNo) + ",'" + account.AccountTime.Format("2006-01-02 15:04:05.000") + "'," + strconv.FormatFloat(account.Amount, 'f', 4, 64) + "," + strconv.FormatFloat(account.Balance, 'f', 4, 64) + ",'" + account.Currency + "'," + strconv.Itoa(int(account.Direction)) + ",'" + account.Operation + "'," + strconv.FormatBool(account.IsDischarge) + ",'" + account.BusinessID + "','" + account.BusinessTime.Format("2006-01-02 15:04:05.000") + "','" + account.BusinessCode + "','" + account.CreateTime.Format("2006-01-02 15:04:05.000") + "','" + account.GroupID + "');" }
 fmt.Println(time.Now().Sub(begin).Microseconds()) db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8&multiStatements=true")
 db.Exec(sumSql)
 fmt.Println(time.Now().Sub(begin).Microseconds()) defer db.Close()}
type Accounting struct { No uint64 `db:"no"` AccountNo uint64 `db:"account_no"` AccountTime time.Time `db:"account_time"` Amount float64 `db:"amount"` Balance float64 `db:"balance"` Currency string `db:"currency"` Direction int8 `db:"direction"` Operation string `db:"operation"` IsDischarge bool `db:"is_discharge"` DischaregID string `db:"dischareg_id"` DischaregAccountNo string `db:"dischareg_account_no"` BusinessID string `db:"business_id"` BusinessTime time.Time `db:"business_time"` BusinessCode string `db:"business_code"` CreateTime time.Time `db:"create_time"` GroupID string `db:"group_id"`}