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

Today, I would like to share with you the relevant knowledge about how to connect golang to mysql database. The content is detailed and the logic is clear. I believe most people still know too much about this, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

Golang operation mysql installs go get "github.com/go-sql-driver/mysql" go get "github.com/jmoiron/sqlx" connection database var Db * sqlx.DBdb, err: = sqlx.Open ("mysql", "username:password@tcp (ip:port) / database?charset=utf8") Db = db

Connection 2

Package mainimport ("database/sql"fmt" _ "github.com/go-sql-driver/mysql") var db * sql.DB / / Global object dbfunc initDB () (err error) {db, err = sql.Open ("mysql" "root:admin123@tcp (127.0.0.1 err 3306) / dududu?charset=utf8") if erratic contacts nil {return err} err = db.Ping () / / verify the database connection if erratic connections {return err} return nil} type beautiful struct {spu_id string title string price string} func queryRowDemo () {sqlStr: = "select spu_id,title,price from dududu_shops where id =?" Var u beautiful err:=db.QueryRow (sqlStr,101) .Scan (& u.accountabilid.title language quou.price) if erratic roomnil {fmt.Println ("2", err)} fmt.Println (u)} func main () {err:=initDB () if erratic accounnil {fmt.Println ("1") Err) return} queryRowDemo ()} processing type (Handle Types)

Sqlx design and database/sql are used in the same way. Contains 4 major handle types:

Sqlx.DB-similar to sql.DB, represents a database.

Sqlx.Tx-similar to sql.Tx, it represents things.

Sqlx.Stmt-similar to sql.Stmt, stands for prepared statement.

Sqlx.NamedStmt-indicates prepared statement (named parameters is supported)

All handler types provide compatibility with database/sql, which means that when you call sqlx.DB.Query, you can directly replace it with sql.DB.Query. This makes it easy for sqlx to add to existing database projects.

In addition, sqlx has two cursor types:

Sqlx.Rows-similar to sql.Rows, Queryx returns.

Sqlx.Row-similar to sql.Row, QueryRowx returns.

Compared with the database/sql method, there is also a new syntax, that is, the implementation of directly transforming the acquired data into the structure implementation.

Get (dest interface {}, …) Error

Select (dest interface {}, …) Error

Build a table

All of the following examples are based on the following table structure.

CREATE TABLE `userinfo` (`uid` INT (10) NOT NULL AUTO_INCREMENT, `username` VARCHAR (64) DEFAULT NULL, `password` VARCHAR (32) DEFAULT NULL, `department` VARCHAR (64) DEFAULT NULL, `email` varchar (64) DEFAULT NULL, PRIMARY KEY (`uid`) ENGINE=InnoDB DEFAULT CHARSET=utf8Exec use

Exec and MustExec get a connection from the connection pool and only want the corresponding query operation. For drivers that do not support ad-hoc query execution, a prepared statement is created behind the execution of the operation. The connection is returned to the connection pool before the result is returned.

It should be noted that different database types use different placeholders. Mysql uses? As a placeholder.

MySQL use?

PostgreSQL uses 1, 1, 2, etc.

SQLite use? Or $1

Oracle uses: name

Exec adds and deletes the example

Query syntax using Query will be mentioned later

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBfunc init () {db, err: = sqlx.Open ("mysql", "stu:1234qwer@tcp (10.0.0.241 github.com/jmoiron/sqlx 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed," Err) return} Db = db} func main () {result, err: = Db.Exec ("INSERT INTO userinfo (username, password, department,email) VALUES (?,?)", "wd", "it", "wd@163.com") if err! = nil {fmt.Println ("insert failed,error:", err) return} id _: = result.LastInsertId () fmt.Println ("insert id is:", id) _, err1: = Db.Exec ("update userinfo set username = Where uid =? "," jack ", 1) if err1! = nil {fmt.Println (" update failed error: ", err1)} else {fmt.Println (" update success! ")} _, err2: = Db.Exec (" delete from userinfo where uid =? ", 1) if err2! = nil {fmt.Println (" delete error: " Err2)} else {fmt.Println ("delete success")}} / / insert id is: 1//update successsql pre-statement (Prepared Statements)

For most databases, when a query is executed, the declaration is already declared inside the sql statement database, and the declaration is in the database, and we can declare it in advance so that it can be reused elsewhere.

Stmt, err: = db.Prepare (`SELECT * FROM place WHERE telcode=? `) row = stmt.QueryRow (65) tx, err: = db.Begin () txStmt, err: = tx.Prepare (`SELECT * FROM place WHERE telcode=?`) row = txStmt.QueryRow

Of course, sqlx also provides Preparex () for extension, which can be directly used for structure transformation.

Stmt, err: = db.Preparex (`SELECT * FROM place WHERE telcode=? `) var p Placeerr = stmt.Get (& p852) Query

Query is the main method used to execute queries in database/sql, which returns row results. Query returns a sql.Rows object and an error object.

Rows should be used as a cursor rather than a series of results. Although the method of database-driven caching is different, getting one column of results each time through Next () iteration can effectively limit the use of memory when the query results are very large. Scan () uses reflect to map each column of sql results to data types of the go language, such as string, [] byte and so on. If you haven't traversed all the rows results, be sure to call rows.Close () before returning connection to the connection pool.

The error returned by Query may occur when server prepares the query, or when the query statement is executed. For example, you might get a bad connection level from the connection pool (although the database will try 10 times to discover or create a working connection). Generally speaking, errors are mainly caused by wrong sql statements, wrong similar matches, wrong domain names or table names, etc.

In most cases, Rows.Scan () copies the data obtained from the driver, regardless of how the driver uses the cache. The special type sql.RawBytes can be used to always get a zero-copy slice byte from the data returned by the driver. The next time Next is called, this value is no longer valid because the memory it points to has been driven to overwrite other data.

The connection used by Query is released after all the rows has been traversed through Next () or after calling rows.Close ().

Example:

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBfunc init () {db, err: = sqlx.Open ("mysql", "stu:1234qwer@tcp (10.0.0.241 github.com/jmoiron/sqlx 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed," Err) return} Db = db} func main () {rows, err: = Db.Query ("SELECT username,password,email FROM userinfo") if err! = nil {fmt.Println ("query failed,error:", err) return} for rows.Next () {/ / cycle result var username,password,email string err = rows.Scan (& username, & password) & email) println (username,password,email)}} / / wd 123 wd@163.com//jack 1222 jack@165.comQueryx

The Queryx and Query behaviors are similar, except that a sqlx.Rows object is returned, which supports extended scan behavior, and the data can be structurally transformed.

Example:

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBtype stu struct {Username string `db: "username" `Password string `db: "password" `Department string `db: "department" `Email string `db: "email" `} func init () {db, err: = sqlx.Open ("mysql") "stu:1234qwer@tcp (10.0.0.241fmt.Println 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed,", err) return} Db = db} func main () {rows, err: = Db.Queryx ("SELECT username,password,email FROM userinfo") if err! = nil {fmt.Println ("Qeryx failed,error:" Err) return} for rows.Next () {/ / Loop result var stu1 stu err = rows.StructScan (& stu1) / / convert to structure fmt.Println ("stuct data:", stu1.Username,stu1.Password)}} / / stuct data: wd 123//stuct data: jack 1222QueryRow and QueryRowx

Both QueryRow and QueryRowx fetch a piece of data from the database, but QueryRowx provides an scan extension that converts the result directly into a structure.

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBtype stu struct {Username string `db: "username" `Password string `db: "password" `Department string `db: "department" `Email string `db: "email" `} func init () {db, err: = sqlx.Open ("mysql") "stu:1234qwer@tcp (10.0.0.241if err 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed,", err) return} Db = db} func main () {row: = Db.QueryRow ("SELECT username,password,email FROM userinfo where uid =?", 1) / / QueryRow returned an error Error returns var username,password,email string err: = row.Scan (& username,&password,&email) if err! = nil {fmt.Println (err)} fmt.Printf ("this is QueryRow res: [% s:%s:%s]\ n", username,password,email) var s stu err1: = Db.QueryRowx ("SELECT username,password,email FROM userinfo where uid =?") via Scan 2). StructScan (& s) if err1! = nil {fmt.Println ("QueryRowx error:", err1)} else {fmt.Printf ("this is QueryRowx res:%v", s)}} / / this is QueryRow res: [wd: 123:wd@163.com] / / this is QueryRowx res: {jack 1222 jack@165.com} Get and Select (very commonly used)

Get and Select are very time-saving extensions that assign the result directly to the structure, which encapsulates StructScan for conversion. Get is used to get a single result and then Scan,Select is used to get the result slice.

Example:

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBtype stu struct {Username string `db: "username" `Password string `db: "password" `Department string `db: "department" `Email string `db: "email" `} func init () {db, err: = sqlx.Open ("mysql") "stu:1234qwer@tcp (10.0.0.241 fmt.Println 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed,", err) return} Db = db} func main () {var stus [] stu err: = Db.Select (& stus, "SELECT username,password,email FROM userinfo") if err! = nil {fmt.Println ("Select error") Err)} fmt.Printf ("this is Select res:%v\ n", stus) var s stu err1: = Db.Get (& s, "SELECT username,password,email FROM userinfo where uid =?", 2) if err1! = nil {fmt.Println ("GET error:", err1)} else {fmt.Printf ("this is GET res:%v") S)}} / / this is Select res: [{wd 123 wd@163.com} {jack 1222 jack@165.com}] / / this is GET res: {jack 1222 jack@165.com} transaction (Transactions)

Transaction operations are implemented in three ways:

Begin (): start a transaction

Commit (): commit transaction (execute sql)

Rollback (): rollback

Use process:

Tx, err: = db.Begin () err = tx.Exec (...) err = tx.Commit () / / or transactions extended with sqlx tx: = db.MustBegin () tx.MustExec (...) err = tx.Commit ()

Because the transaction is a state that is always connected, the Tx object must bind and control a single connection. A Tx saves a connection throughout its life cycle and then releases it when commit or Rollback () is called. You must be very careful when calling these functions, otherwise the connection will be occupied until it is garbage collected.

Examples of use:

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBfunc init () {db, err: = sqlx.Open ("mysql", "stu:1234qwer@tcp (10.0.0.241 github.com/jmoiron/sqlx 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed," Err) return} Db = db} func main () {tx, err: = Db.Beginx () _, err = tx.Exec ("insert into userinfo (username,password) values (?,?)", "Rose", "2223") if err! = nil {tx.Rollback ()} _, err = tx.Exec ("insert into userinfo (username,password) values (?)", "Mick" If err! = nil {fmt.Println ("exec sql error:", err) tx.Rollback ()} err = tx.Commit () if err! = nil {fmt.Println ("commit error")}} connection pool settings

By default, there is no limit to the growth of the connection pool, and connections are created as long as there are no free connections available in the connection pool. We can use DB.SetMaxOpenConns to set the maximum size of the pool. Unused connections are marked as idle and closed if not needed. To avoid making and closing a large number of connections, you can use DB.SetMaxIdleConns to set the maximum idle connection.

Note: the golang version of this setting method is at least 1.2.

DB.SetMaxIdleConns (n int) sets the maximum number of idle connections

DB.SetMaxOpenConns (n int) sets the maximum number of open connections

DB.SetConnMaxIdleTime (time.Second*10) interval

Example:

Package mainimport (_ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx"fmt") var Db * sqlx.DBfunc init () {db, err: = sqlx.Open ("mysql", "stu:1234qwer@tcp (10.0.0.241 github.com/jmoiron/sqlx 3307) / test?charset=utf8") if err! = nil {fmt.Println ("open mysql failed," Err) return} Db = db Db.SetMaxOpenConns (30) Db.SetMaxIdleConns (15)} case uses var Db * sqlx.DBdb, err: = sqlx.Open ("mysql", "root:admin123@tcp (127.0.0.1 Db 3306) / dududu?charset=utf8") Db = dbrows, err: = Db.Query ("SELECT spu_id,title,price FROM dududu_shops") if err! = nil {fmt.Println ("query failed,error:" Err) return} for rows.Next () {/ / Loop result var spu_id,title,price string err = rows.Scan (& spu_id, & title, & price) println (spu_id,title,price)} these are all the contents of the article "how golang connects to mysql databases" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.