MySQL database operation in go language (i)

Data persistence is necessary in the program, so the operation of the database is very important in programming language, this article introduces the operation of the go language to MySQL database.

Basic operation to establish a connection

db, err := sql.Open("mysql", "root:[email protected](127.0.0.1:3306)/betting?charset=utf8")errDeal("连接数据库", err)defer db.Close()

Connection parameters generally have the following types of

[email protected](/path/to/socket)/dbname?charset=utf8user:[email protected](localhost:5555)/dbname?charset=utf8user:[email protected]/dbnameuser:[email protected]([de:ad:be:ef::ca:fe]:80)/dbname

Adding and removing changes operation

// 插入数据---------------------方法1result, err := db.Exec("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc)" +" VALUES(?,now(),?,?,?)", "admin", "127.0.0.1", "登录", "登录成功")errDeal("插入数据", err)// 查看返回信息count, err := result.RowsAffected()errDeal("查看插入数据条数", err)fmt.Printf("插入数据条数：%d\n", count)id, err := result.LastInsertId()errDeal("查看最后插入数据的id", err)fmt.Printf("最后插入数据的id：%d\n", id)// 插入数据---------------------方法2，先创建一个预处理语句，再执行stmt, err := db.Prepare("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc)" +" VALUES(?,now(),?,?,?)")result1, err1 := stmt.Exec("admin", "127.0.0.1", "登录", "登录成功")errDeal("方法2插入数据", err1)fmt.Println(result1.LastInsertId())// 删除数据result2, err := db.Exec("DELETE FROM admin_log WHERE id=?", 2014)fmt.Println(result2.RowsAffected())// 更新数据result3, err := db.Exec("UPDATE admin_log SET log_ip=? WHERE id=?", "192.168.8.9", 2017)fmt.Println(result3.RowsAffected())

Single data query

// 先定义存储查询结果的变量var rid intvar username, time, ip, logType stringvar desc, remark, spare interface{} // 如果字段中可能出现值为nil的情况，可以将变量申明为interface{}类型err2 := db.QueryRow("SELECT id,admin_username,log_time,log_ip,log_type,log_desc,remark,spare" +    " FROM admin_log WHERE id=?", 2017).Scan(&rid, &username, &time, &ip, &logType, &desc, &remark, &spare) // 传入的是变量的指针errDeal("查询单条数据", err2)fmt.Printf("id=%d,username=%s,time=%s,ip=%s,logType=%s,desc=%s,remark=%v,spare=%v\n", rid, username, time, ip, logType, desc, remark, spare)

Multiple data queries

Querying multiple data rows, ERR3: = db. Query ("Select Admin_username,log_time,log_ip,log_type,log_desc" + "from Admin_log WHERE id=? OR id=? ", 2019) Errdeal (" Query multiple Data ", ERR3)//iterate over multiple data for rows. Next () {err4: = rows. Scan (&username, &time, &ip, &logtype, &desc) errdeal ("Traverse multiple Data", ERR4) fmt. Printf ("username=%s,time=%s,ip=%s,logtype=%s,desc=%s\n", username, time, IP, LogType, desc)}//If a specific field is not specified in the query, use *rows, Err33: = db. Query ("SELECT *" + "from Admin_log WHERE id>?") Errdeal ("Querying Multiple Data", ERR33)//Querying all field names, returning a string slice Columes, err333: = Rows. Columns () errdeal ("Rows. Columns () method call ", err333) fmt. Printf ("%T----%v\n", Columes, columes) var scancolumes = make ([]interface{], Len (columes)) var values = make ([]interface{ }, Len (columes)) for index, _: = Range Scancolumes {Scancolumes[index] = &values[index]}for rows. Next () {err4: = rows.    Scan (Scancolumes ...) Errdeal ("Traverse multiple Data", ERR4) for I, Val: = range Values {if strings. Equalfold (Judgetype (Val), "[]uint8 ") {FMT. Printf ("%s (%T) ==%s\t", Columes[i], Val, Val)} else {fmt. Printf ("%s (%T) ==%v\t", Columes[i], Val, Val)}} fmt. Println ()}

Transaction Open Transaction

// 开启事务，tx是从连接池中取出一个连接，在关闭之前都是使用这个连接，提交事务和回滚事务都是操作txtx, err5 := db.Begin()errDeal("开启事务", err5)_, err6 := tx.Exec("UPDATE admin_log SET log_desc=? WHERE id=?", "测试事务222", 2019)//if err6 != nil {if err6 == nil {    tx.Rollback() // 回滚}tx.Commit() // 提交

BULK INSERT Data

// 批量数据插入tx, err7 := db.Begin()errDeal("数据批量插入，开启事务", err7)insertValues := [][]interface{}{{"admin", "127.0.0.1", "登录", "登录成功"},{"admin", "127.0.0.1", "删除", "删除数据"},{"admin", "127.0.0.1", "退出", "退出系统"}}stmt, err8 := tx.Prepare("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc) VALUES(?,now(),?,?,?)")errDeal("数据批量插入，预处理", err8)for _, val := range  insertValues {    _, err := stmt.Exec(val...)    if err != nil {        fmt.Printf("出现错误回滚，错误信息：%v", err)        tx.Rollback()    }}tx.Commit()

Use of SQLX and bulk insertion

xdb, err9 := sqlx.Open("mysql", "root:[email protected](127.0.0.1:3306)/betting?charset=utf8")errDeal("sqlx连接数据库", err9)txx, err10 := xdb.Beginx()errDeal("sqlx开启事务", err10)insertValuesx := [][]interface{}{{"admin", "127.0.0.1", "登录", "登录成功X"},{"admin", "127.0.0.1", "删除", "删除数据X"},{"admin", "127.0.0.1", "退出", "退出系统X"}}stmtx, err11 := txx.Preparex("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc) VALUES(?,now(),?,?,?)")errDeal("数据批量插入，预处理", err11)for _, val := range insertValuesx {    _, err := stmtx.Exec(val...)    if err != nil {        fmt.Printf("sqlx出现错误回滚，错误信息：%v", err)        txx.Rollback()    }}txx.Commit()

Full code

Package Mainimport ("Database/sql" _ "Github.com/go-sql-driver/mysql"//Here is very important, import their own local use of the database driver, the front is underlined, or will error: SQL:UNKN    Own driver "MySQL" (Forgotten import?) "FMT" "Strings" "GITHUB.COM/JMOIRON/SQLX") func main () {///connection database, user name: password @ protocol (address: port)/database? parameter = parameter value, common "Username: password @tcp (IP: Port) /database name? charset= Character Set "db, err: = SQL. Open ("MySQL", "root:[email protected" (127.0.0.1:3306)/betting?charset=utf8 ") errdeal (" Connect database ", err) defer db. Close ()//========================================================================================= additions/deletions// Insert Data---------------------Method 1 result, err: = db.  Exec ("INSERT into Admin_log (ADMIN_USERNAME,LOG_TIME,LOG_IP,LOG_TYPE,LOG_DESC)" + "VALUES (?, now (),?,?,?)", "admin", "127.0.0.1", "Login", "Login Succeeded") errdeal ("Insert Data", err)///View return information count, err: = result. Rowsaffected () errdeal ("View the number of inserted data bars", err) fmt. Printf ("Number of inserted data bars:%d\n", count) ID, err: = result. Lastinsertid () errdeal ("View last inserted Data id", err) fmt.    Printf ("id:%d\n of last inserted data", ID)Insert the data---------------------Method 2, first create a preprocessing statement, and then execute stmt, err: = db.    Prepare ("INSERT into Admin_log (ADMIN_USERNAME,LOG_TIME,LOG_IP,LOG_TYPE,LOG_DESC)" + "VALUES (?, now (),?,?,?)") RESULT1, err1: = stmt. Exec ("admin", "127.0.0.1", "Login", "Login Successful") Errdeal ("Method 2 Insert Data", ERR1) fmt. Println (RESULT1. Lastinsertid ())//delete data result2, err: = db. Exec ("DELETE from Admin_log WHERE id=?") fmt. Println (RESULT2. Rowsaffected ())//Update data RESULT3, err: = db. Exec ("UPDATE admin_log SET log_ip=?") WHERE id=? "," 192.168.8.9 "," FMT ". Println (RESULT3. Rowsaffected ())//============================================================================================= Query//Query single data//define variables that store query results var rid int var username, time, IP, logtype string var desc, remark, spare int erface{}//If a value of nil is possible in a field, the variable can be declared as interface{} type ERR2: = db. Queryrow ("Select Id,admin_username,log_time,log_ip,log_type,log_desc,remark,spare" + "from Admin_log WHERE id=?", 2017). Scan (& RIDs, &username, &time, &ip, &logtype, &desc, &remark, &spare)//passed in is a pointer to a variable errdeal ("Querying a single data ", ERR2) fmt. Printf ("id=%d,username=%s,time=%s,ip=%s,logtype=%s,desc=%s,remark=%v,spare=%v\n", RID, username, time, IP, LogType, DESC, remark, spare)//Querying multiple data rows, ERR3: = db. Query ("Select Admin_username,log_time,log_ip,log_type,log_desc" + "from Admin_log WHERE id=? Or id=?, 2019) Errdeal ("Query multiple Data", ERR3)//iterate over multiple data for rows. Next () {err4: = rows. Scan (&username, &time, &ip, &logtype, &desc) errdeal ("Traverse multiple Data", ERR4) fmt.    Printf ("username=%s,time=%s,ip=%s,logtype=%s,desc=%s\n", username, time, IP, LogType, desc)}//If a specific field is not specified in the query, use * Rows, Err33: = db. Query ("SELECT *" + "from Admin_log WHERE id>?") Errdeal ("Querying Multiple Data", ERR33)//Querying all field names, returning a string slice C Olumes, err333: = rows. Columns () errdeal ("Rows. Columns () method call ", err333) fmt. Printf ("%T----%v\n", Columes, ColuMES) var scancolumes = make ([]interface{}, Len (columes)) var values = make ([]interface{], Len (columes)) for index , _: = Range Scancolumes {Scancolumes[index] = &values[index]} for rows. Next () {err4: = rows.        Scan (Scancolumes ...) Errdeal ("Traverse multiple Data", ERR4) for I, Val: = range Values {if strings. Equalfold (Checktype (Val), "[]uint8") {FMT. Printf ("%s (%T) ==%s\t", Columes[i], Val, Val)} else {fmt. Printf ("%s (%T) ==%v\t", Columes[i], Val, Val)}} fmt. PRINTLN ()}//================================================================================================== Transaction//Open transaction, TX is a connection from the connection pool, the connection is used before the shutdown, the commit transaction and the ROLLBACK TRANSACTION are operations TX TX, ERR5: = db. Begin () errdeal ("Open transaction", ERR5) _, Err6: = Tx. Exec ("UPDATE admin_log SET log_desc=?") WHERE id=? "," Test Transaction 222 ", 2019)//if ERR6! = Nil {if err6 = nil {tx. Rollback ()//rollback} tx.commit ()//submit//Bulk Data Insert TX, ERR7: = db. Begin () errdeal ("Data Bulk INSERT, open transaction", ERR7) Insertvalues: = [][]interface{}{{"admin", "127.0.0.1", "Login", "Login Successful"},{"admin", "127.0.0.1", "delete", "Delete data"},{"admin", "127.0.0.1", "Exit", "Exit System"}} stmt, Err8: = Tx.    Prepare ("INSERT into Admin_log (ADMIN_USERNAME,LOG_TIME,LOG_IP,LOG_TYPE,LOG_DESC) VALUES (?, now (),?,?,?)") Errdeal ("Data Bulk INSERT, preprocessing", ERR8) defer stmt.    Close ()//writes each SQL parameter to the target table buffer through a loop. For _, Val: = Range Insertvalues {_, Err: = stmt.        Exec (Val ...) If err! = Nil {fmt. Printf ("Error rollback occurred, error message:%v", err) Tx. Rollback ()}} tx.commit ()//Use SQLX bulk data to insert Xdb, ERR9: = Sqlx. Open ("MySQL", "root:[email protected" (127.0.0.1:3306)/betting?charset=utf8 ") errdeal (" SQLX Connection Database ", ERR9) defer Xdb.close () txx, Err10: = Xdb.  BeginX () errdeal ("Sqlx open Transaction", Err10) Insertvaluesx: = [][]interface{}{{"admin", "127.0.0.1", "Login", "Login success X"},{"admin", "127.0.0.1", "delete", "Delete data X"},{"admin", "127.0.0.1", "Exit", "Exit System X"}} STMTX, errOne by one: = Txx.    Preparex ("INSERT into Admin_log (ADMIN_USERNAME,LOG_TIME,LOG_IP,LOG_TYPE,LOG_DESC) VALUES (?, now (),?,?,?)") Errdeal ("Data Bulk INSERT, preprocessing", Err11) defer stmtx.    Close ()//writes each SQL parameter to the target table buffer through a loop. For _, Val: = Range Insertvaluesx {_, Err: = Stmtx.        Exec (Val ...) If err! = Nil {fmt. Printf ("SQLX error rollback, error message:%v", err) txx. Rollback ()}} txx.commit ()}func errdeal (info string, err error) {if err! = Nil {panic (FMT).        Sprintf ("%s, error message:%v", info, err))}}func Checktype (Val interface{}) string {switch val. (type) {case []uint8: Return "[]uint8"} Return ""}

MySQL database operations in the Go Language (i)