Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to connect Golang to MySQL database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how Golang connects to the MySQL database.

Go natively supports connecting to databases, so when developing with Golang, you can use the database/sql package when you need database interaction.

Sql.DB interface is needed to access DB in Go: you can create statements (statement) and transactions (transaction), execute queries, and get results.

When using DB, in addition to the database/sql package, you need to introduce the specific DB driver you want to use. The official implementation is not available. You need to download the tripartite implementation first. Click here to view various versions of the implementation.

Usually MySQL is selected for DB, so the driver for selection is: github.com/go-sql-driver/mysql, and the package needs to be introduced:

"database/sql" _ "github.com/go-sql-driver/mysql"

"_" before package name

The role of import underscores (such as import _ github/demo): when importing a package, all init () functions in the file under the package will be executed, however, sometimes we don't need to import the whole package, we just want it to execute the init () function. At this point, you can use import _ to reference the package.

What is introduced in the MySQL driver above are the init () methods in the MySQL package, and you cannot call other functions in the package through the package name. On import, the driver's initialization function calls sql.Register to register itself in the global variable sql.drivers of the database/sql package for later access through sql.Open.

Case data sheet

Initialize the database connection

The format of the database connection string in sql.Open () is: "username: password @ tcp (IP: port) / database? charset=utf8". The type of DB is: * sql.DB. Once you have DB, you can execute CRUD.

Go divides database operations into two categories: Query and Exec

Query represents a query, and it gets the query results (a series of rows, possibly empty) from the database.

Exec means to execute the statement, and it does not return rows.

Common database operation modes:

QueryRow returns only one row of queries as a common special case of Query.

Prepare prepares a statement that needs to be used multiple times for subsequent execution.

Query operation

Var user User rows, e: = DB.Query ("select * from user where id in") if e = = nil {errors.New ("query incur error")} for rows.Next () {e: = rows.Scan (user.sex, user.phone, user.name, user.id User.age) if e! = nil {fmt.Println (json.Marshal (user))}} rows.Close () / / single-line query operation DB.QueryRow ("select * from user where id=1") .scan (user.age, user.id, user.name, user.phone, user.sex)

Execution process

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Use db.Query () to send the query to the database, get the result set Rows, and check for errors.

Use rows.Next () as a loop condition to iterate over the result set.

Use rows.Scan to get a row of results from the result set.

Use rows.Err () to check for errors after exiting the iteration.

Use rows.Close () to close the result set and release the connection.

Add, delete, revise and Exec

It usually does not constrain your query to use Query, but Query will return the result set, but Exec will not. So if you are performing add, delete and modify operations, it will be better to use Exec. The result returned by Exec is that the Result,Result API allows you to obtain the metadata of the execution result:

Type Result interface {/ / is used to return self-incrementing ID, which is not available in all relational databases. LastInsertId () (int64, error) / / returns the number of rows affected. RowsAffected () (int64, error)}

Prepare for query

If you want to use the placeholder function now, and the conditions of where want to be passed in as parameters, Go provides a db.Prepare statement to help you bind. The result of preparing a query is a prepared statement (prepared statement) that can contain placeholders (that is, bound values) of the parameters required for execution. It's a much better way to prepare a query than a string, which escapes parameters and avoids SQL injection. At the same time, preparing queries for some databases also saves the overhead of parsing and generating execution plans, which is good for performance.

Placeholder

PostgreSQL uses $N as a placeholder, and N is an integer incremented from 1 to represent the position of the parameter, making it easy to reuse the parameter. MySQL use? As placeholders, SQLite can use both placeholders, while Oracle uses the form of: param1.

MySQL PostgreSQL Oracle = WHERE col =? WHERE col = $1 WHERE col =: col VALUES VALUES ($1, $2, $3) VALUES (: val1,: val2,: val3) stmt, e: = DB.Prepare ("select * from user where id=?") Query, e: = stmt.Query (1) query.Scan ()

Use of transactions

Start a transaction with db.Begin (), and the Begin method returns a transaction object, Tx. Calling the Commit () or Rollback () method on the result variable Tx commits or rolls back the change and closes the transaction. At the bottom, Tx takes a connection from the connection pool and maintains exclusive ownership of it during the transaction. The methods on the transaction object Tx correspond to the methods of the database object sql.DB, such as Query,Exec, etc. A transaction object can also prepare a query, and the prepare statement created by the transaction is explicitly bound to the transaction that created it.

/ / Open transaction tx, err: = DB.Begin () if err! = nil {fmt.Println ("tx fail")} / / prepare sql statement stmt, err: = tx.Prepare ("DELETE FROM user WHERE id =?") If err! = nil {fmt.Println ("Prepare fail") return false} / / set parameters and execute sql statement res Err: = stmt.Exec (user.id) if err! = nil {fmt.Println ("Exec fail") return false} / / commit transaction tx.Commit () Let's do a complete sql operation: package main import ("database/sql"encoding/json"fmt" _ "github.com/go-sql-driver/mysql"github.com/pkg/errors"strings" ) / / Database configuration const (userName = "root" password = "123456" ip = "127.0.0.1" port = "3306" dbName = "test") / / Db database connection pool var DB * sql.DB type User struct {id int64 name string age int8 sex int8 phone string} / / Note method name capitalization It is public func InitDB () {/ / build the connection: "user name: password @ tcp (IP: port) / database? charset=utf8" path: = strings.Join ([] string {userName, ":, password," @ tcp (", ip,": ", port,") / ", dbName,"? charset=utf8 "},") / / Open the database. The former is the driver name. So to import: _ "github.com/go-sql-driver/mysql" DB, _ = sql.Open ("mysql", path) / / set the maximum number of database connections DB.SetConnMaxLifetime (100) / / set the maximum number of idle database connections DB.SetMaxIdleConns (10) / / verify the connection if err: = DB.Ping () Err! = nil {fmt.Println ("open database fail") return} fmt.Println ("connnect success")} / / query operation func Query () {var user User rows, e: = DB.Query ("select * from user where id in (1mem2)" 3) ") if e = = nil {errors.New (" query incur error ")} for rows.Next () {e: = rows.Scan (user.sex, user.phone, user.name, user.id) User.age) if e! = nil {fmt.Println (json.Marshal (user))}} rows.Close () DB.QueryRow ("select * from user where id=1") .Scan (user.age, user.id, user.name, user.phone, user.sex) stmt, e: = DB.Prepare ("select * from user where id=?") Query, e: = stmt.Query (1) query.Scan ()} func DeleteUser (user User) bool {/ / Open transaction tx, err: = DB.Begin () if err! = nil {fmt.Println ("tx fail")} / / prepare sql statement stmt, err: = tx.Prepare ("DELETE FROM user WHERE id =?") If err! = nil {fmt.Println ("Prepare fail") return false} / / set parameters and execute sql statement res Err: = stmt.Exec (user.id) if err! = nil {fmt.Println ("Exec fail") return false} / / commit transaction tx.Commit () / / get the id fmt.Println (res.LastInsertId ()) return true} func InsertUser (user User) bool of the previous insert {/ / Open transaction tx Err: = DB.Begin () if err! = nil {fmt.Println ("tx fail") return false} / / prepare the sql statement stmt, err: = tx.Prepare ("INSERT INTO user (`name`, `phone`) VALUES (?,?") If err! = nil {fmt.Println ("Prepare fail") return false} / / pass parameters into the sql statement and execute res, err: = stmt.Exec (user.name) User.phone) if err! = nil {fmt.Println ("Exec fail") return false} / / commit the transaction to tx.Commit () / / get the last id fmt.Println (res.LastInsertId ()) return true} func main () {InitDB () Query () defer DB.Close ()} to this point I believe you have a deeper understanding of "how Golang connects to MySQL databases", so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.