GO using MySQL Database

This is a creation in Article, where the information may have evolved or changed.

Transferred from: https://github.com/astaxie/build-web-application-with-golang/blob/master/ebook/05.2.md

At present, the popular web site architecture is lamp, in which the M is MySQL, as a database, MySQL is free, open source, easy to use as the advantage of many web development of the back-end database storage engine.

MySQL Driver

Go in support of MySQL driver is more, there are several, some are support database/sql standard, and some are using their own implementation interface, commonly used are the following:

• Https://github.com/Go-SQL-Driver/MySQL support Database/sql, all with Go write.
• Https://github.com/ziutek/mymysql supports Database/sql, and also supports custom interfaces, all with go writing.
• Https://github.com/Philio/GoMySQL does not support Database/sql, custom interfaces, all with go write.

The following example I mainly take the first driver as an example (I am currently using it to drive the project), but also recommend that you use it, the main reasons:

• This drive is relatively new and maintenance is better
• Fully supports Database/sql interface
• Support KeepAlive, maintain a long connection, although the Star Fork Mymysql also support keepalive, but not thread-safe, this from the bottom of the support of KeepAlive.

Sample code

In the next few sections we will use the same database table structure: Database test, User table UserInfo, associated user Information table Userdetail.

create TABLE ' userinfo ' (' uid ' INT (Ten) not NULL auto_increment, ' username ' VARCHAR (64 ) NULL default NULL, ' Departname ' VARCHAR (+) null default NULL, ' created ' DATE null default NULL, PRIMARY KEY ('    UID ')) CREATE TABLE ' userdetail ' (' uid ' INT () not null DEFAULT ' 0 ', ' intro ' text null, ' profile ' text null, PRIMARY KEY (' uid '))  

The example below will demonstrate how to use the Database/sql interface to make a database table to be used for pruning and checking operations

Package Mainimport (_ "Github.com/go-sql-driver/mysql" "Database/sql" "FMT"//"Time") Func main () {db, err : = SQL. Open ("MySQL", "Astaxie:astaxie@/test?charset=utf8") Checkerr (ERR)//Insert Data stmt, err: = db.    Prepare ("INSERT userinfo SET username=?,departname=?,created=?") Checkerr (Err) res, err: = stmt. Exec ("Astaxie", "Development department", "2012-12-09") Checkerr (ERR) ID, err: = Res. Lastinsertid () Checkerr (err) fmt. PRINTLN (ID)//Update data stmt, err = db. Prepare ("Update userinfo set username=?")    where uid=? ") Checkerr (Err) res, err = stmt. Exec ("astaxieupdate", id) checkerr (ERR) affect, err: = Res. Rowsaffected () Checkerr (err) fmt. PRINTLN (affect)//query data rows, err: = db. Query ("SELECT * from UserInfo") Checkerr (Err) for rows.  Next () {var uid int var username string var department string var created string err = Rows. Scan (&uid, &username, &department, &created) Checkerr (err) FMt. PRINTLN (UID) fmt. PRINTLN (username) fmt. PRINTLN (department) FMT. Println (created)}//delete data stmt, err = db.    Prepare ("Delete from userinfo where uid=?") Checkerr (Err) res, err = stmt. Exec (ID) checkerr (ERR) affect, err = Res. Rowsaffected () Checkerr (err) fmt. PRINTLN (affect) db. Close ()}func Checkerr (err error) {if err! = Nil {panic (err)}}

We can see from the above code that the go Operation MySQL database is very convenient.

A few key functions I'll explain:

Sql. The open () function is used to turn on a registered database driver, the MySQL database driver is registered in Go-mysql-driver, and the second parameter is the DNS (Data Source Name). It is a database link and configuration information defined by Go-mysql-driver. It supports the following formats:

user@unix(/path/to/socket)/dbname?charset=utf8user:password@tcp(localhost:5555)/dbname?charset=utf8user:password@/dbnameuser:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname

Db. The Prepare () function is used to return the SQL operation that is ready to be executed and then return to the ready execution state.

Db. The Query () function is used to directly execute SQL to return the rows result.

stmt. The Exec () function is used to execute stmt prepared SQL statements

We can see that the parameters we pass in are all =? corresponding data, so that the way to do this can prevent SQL injection to some extent.