Go database/sql sql-driver/mysql operation

The Github.com/go-sql-driver/mysql is used here,

So you need to download a github.com/go-sql-driver/mysql

Introduction of Database/sql and Github.com/go-sql-driver/mysql

The implementation of the database of the increase, deletion, change, check, business

Here directly on the code, the code has a detailed explanation. And these operations have been actually manipulated by me.

Introduce the required packages

Import

"Database/sql" _ "Github.com/go-sql-driver/mysql" "Log" "StrConv"

"Reflect"//get variable type with

)

Increase:

Func Main () {

Insert Name: = "Name" pwd: = "password" nickname: = "nickname" Db,err: = sql. Open ("MySQL", "go_mysql_user:go_mysql_pwd@tcp (localhost:3306)/go_mysql?charset=utf8")

Iferr! = nil{

Panic (err. Error ())

Log. PRINTLN (ERR)

return}

Defer db. Close ()//panic[throws an exception in front of it, defer can work, and if there is a problem linking the data, he writes the data to err. Defer: delay, here immediately apply for a close SQL link to the grass error, defer after the method, or deferred execution.    The function throws an exception one will be executed insert_sql: = "INSERT into users (name,pwd,nickname) value (?,?,?), (?,?,?), (?,?,?), (?,?,?)" Stmt,err: = db. Prepare (Insert_sql)//Prepare a SQL operation, return a *stmt, execute after the user, this Stmt can be executed multiple times, or concurrently execute/* * This Stmt Main method: Exec, Query, Queryrow, Close

*/iferr! = Nil {

Log. PRINTLN (ERR)

Return

}

Res,err: = stmt. Exec (Name,pwd,nickname,name,pwd,nickname,name,pwd,nickname,name,pwd,nickname)

Iferr! = Nil {

Log. PRINTLN (ERR)

Return

}

Lastinsertid,err: = Res. Lastinsertid ()///BULK INSERT when Lastinserid returns the first ID, a single insert returns the ID of this bar

Lastinsertid,err: = Res. Rowsaffected ()///insert is after rowsaffected return is the number of inserted bars Iferr! = Nil {

Log. PRINTLN (ERR)

Return

}

Log. Println (reflect. TypeOf (Lastinsertid))//print variable type last_insert_id_string: = StrConv. Formatint (lastinsertid,10)//int64 to string requires the introduction of the StrConv packet log. Println ("Lastinsertid =" + last_insert_id_string)

}

By deleting:

Func Main () {

Deletedel_sql: = "Delete from users where id=?" Del_stmt,del_err: = db. Prepare (Del_sql)

Del_stmt. Exec (6)//Do not return any results

}

Change:

Func Main () {

Updateupdate_sql: = "Update users set name=?"    where id=? " Update_stmt,update_err: = db. Prepare (Update_sql)

Ifupdate_err! = Nil {

Log. Println (Update_err)

Return

}

Update_res,update_err: = update_stmt. Exec ("username", 9)

Ifupdate_err! = Nil {

Log. Printf ("%v", Update_err)

return}

Affect_count,_: = Update_res. Rowsaffected ()//Returns the number of affected bars, noting that there are two return values log. Printf ("%v", Affect_count)

}

Check One:

Type userstruct{

ID int name string pwd string nickname string}

Func Main () {

Selectvar User User

Select_sql: = "SELECT * from users where ID >?" Select_err: = db. Queryrow (select_sql,11). Scan (&user.id,&user.name,&user.pwd,&user.nickname)//Query a bar, return a result. and assign to the user this struct type of variable, even if the query to more than a single return is a ifselect_err! = nil {//If no data is queried into the IF in the Err:no rows in result set log. Println (Select_err)

return}

Log. PRINTLN (user)

}

Check Multiple articles:

Func Main () {

Query multiple Select_rows,select_err: = db. Query (select_sql,16)

Ifselect_err! = Nil {

Log. Println (Select_err)

return}

Defer select_rows. Close ()

For Select_rows. Next () {

Varidintvarnamestringvarpwdstringvarnicknamestringiferr: = Select_rows. Scan (&id,&name,&pwd,&nickname); Err! = Nil {

Log. PRINTLN (ERR)

return}

Log. Printf ("Id=%v,name=%v,pwd=%v,nickname=%v", Id,name,pwd,nickname)

}

}

Transaction:

Func Main () {

Transaction Tx,err: = db. Begin ()//Declare the beginning of a transaction Iferr! = Nil {

Log. PRINTLN (ERR)

return}

Insert_sql: = "INSERT into users (name,pwd,nickname) value (?,?,?)" Insert_stmt,insert_err: = Tx. Prepare (Insert_sql)

Ifinsert_err! = Nil {

Log. Println (Insert_err)

return}

Insert_res,insert_err: = insert_stmt. Exec ("Tx_name", "Tx_pwd", "Tx_nickname")

Last_insert_id,_: = Insert_res. Lastinsertid ()

Log. Println (last_insert_id)

Defer TX. Rollback ()///rollback before the last_login_id is there, but after the rollback the operation has not been committed, is rolled back, so the above printed last_login_id this data is not present with the database table

Tx.commit ()//The above operation is submitted here, so the SQL executed above will produce a data in the database

}

Knowledge points Summary and precautions:

Db,err: = sql. Open ("MySQL", "Database login: Database password @tcp (server: Port)/database name Charset=utf8") corresponding to the modification;

Defer db. The close () defer is a delay or an exception or an action that is performed at the end of the method. This indicates that there is an exception to turn off DB with panic (err. Error ()) = "Throws an exception. Use it together. A situation is not enforceable = "Your method enters a dead loop, and the method does not end with an exception and cannot be executed.

Lastinsertid () Gets the ID that inserts the first article is useful

Rowsaffected () Gets the number of affected/inserted bars useful

Reflect. TypeOf (i) The type of variable i is returned in the reflect package TypeOf

StrConv. Formatint (Int64 bit variable, ten) StrConv package formatint The variable of the int64 bit into a string specifically with another article summarizing

Others are returned in code with some return values and are used.

Comparison of Mymysql and Go-mysql_driver

1 Go-mysql-driver is the product of realizing the Golang standard library database/sql. Lower level implementations are guaranteed

2 Go-mysql-driver Although the running time of each command is longer than that of mymysql, the memory usage is very obvious, and the two sides are flat.

3 Go-mysql-driver implements the Database/sql and does not need to change the code of the application logic if the database is replaced by another.

4 Go-mysql-driver realized the Database/sql, this interface is also very good design, basic and PHP in the same as PDO, start and learning cost is low.