Mymysql's Database/sql interface uses

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

Recently saw a lot of friends on the Golang operation MySQL Database has questions, then this content for everyone to share it.

Mymysql, the original author of Ziutek from Poland, uses Golang to implement the Mymysql package according to the MySQL protocol standard. According to his introduction, this package can be used on MySQL 4.1 or later, and in version 5.0, 5.1, the actual validation of the project.

As long as the friend who has done the project with MySQL must have encountered the coding problem, set names almost become the standard of using MySQL in Chinese environment. Then Golang is no exception. However, since Database/sql uses a random connection pool and does not provide any means for all connections to perform an operation, the trouble falls on the driver's shoulder. For Chinese or something, the big foreigner has always not cared. I submitted this issue to Ziutek, but he was not satisfied with the plan he had finally achieved. Additional writing godrv is required. Register to set the encoding.

The broken pipe appears when the connection idle time exceeds the server's wait_timeout. For this issue, Ziutek's recommendation is to use Mymysql's Autorc, but Godrv is not AUTORC-based DATABASE/SQL implementation. In other words, to resolve this error, you can only use the Mymysql original interface.

In view of these problems, I can only fork the Ziutek project, to mymysql the corresponding patch. The main focus is on the improvement of the encoding settings, and the KeepAlive function is implemented by the way.

This article mainly describes the use of the fork version.

In the fork version, there is another improvement is to unify the go get installation interface, no need to install Mymysql sub-package, only need to

Go get Github.com/mikespook/mymysql

To complete the installation.

For the use, or the code to speak, the complete code to see here. For general purposes, only the use of the Database/sql interface is described here. In the actual project, I also recommend that you use this interface as much as possible to maintain compatibility with maximum availability assurance.

Import (    "log"    "Database/sql"    _ "Github.com/mikespook/mymysql/godrv")

Use the Mymysql Database/sql interface to import the Godrv package. The Database/sql driver is automatically registered in the INIT function of this package, so there is no need to use it after importing.

DB, err: = SQL. Open ("Mymysql", "tcp://127.0.0.1:3306/test/root/xxiyy?charset=utf8&keepalive=1200") if err! = Nil {    log. Fatal (ERR)}defer db. Close ()

Open a SQL using the Mymysql driver. DB connection. The DSN connection string is my main improvement to Ziutek's mymysql. Multiple connection modes are supported:

• Using the TCP protocol: [Tcp://addr/]dbname/user/password[?params]
• Using UNIX Sock:[unix://sockpath/]dbname/user/password[?params]

Where the addr of the TCP protocol must be a string containing the host name or IP, and a semicolon-delimited port number. such as localhost:3306, 192.168.3.2:3307. The Sockpath in the UNIX sock protocol must be the absolute path to the MySQL sock file.

In the DSN above, the optional content is in square brackets, which can be omitted. When the protocol information (within the first square brackets) is not specified, use tcp://127.0.0.1:3306/as the default value.

The params section is a parameter setting for the current database driver, and currently, there are only two parameters that can be set:

• CharSet: Set connection encoding for ' set names '.
• KeepAlive: PING is sent to the server every keepalive seconds.

It is particularly emphasized that if the password contains a slash (/), an asterisk (*) is required instead of the parsing rule. If the password contains an asterisk (*), you need to replace it with two asterisks (* *). For example:

• The original password [PASS/WD], which should be written in DSN as [PASS*WD].
• The original password [PASS*WD], which should be written in DSN as [PASS**WD].

stmt, err: = db. Prepare ("INSERT INTO ' test ' (' key ', ' value ') VALUES (?,?)") If err! = Nil {    log. Fatal (Err)}defer stmt. Close () rslt, err: = stmt. Exec ("name", "Foobar") if err! = Nil {    log. Fatal (Err)}if A, err: = Rslt. Rowsaffected (); Err! = Nil {    log. Print (Err)} else {    log. Printf ("[Ins]affected rows=%d", a)}if ID, err: = Rslt. Lastinsertid (); Err! = Nil {    log. Print (Err)} else {    log. Printf ("[Ins]last insert id=%d", id)}

In the Database/sql interface, there are several ways to execute an SQL statement. For security reasons, we strongly recommend that you do not use string concatenation of SQL statements directly, and if possible, the Prepare method that will appear in most other interfaces is preferred. After you execute the SQL statement, you can pass SQL. Result gets the number of rows affected by this execution, and the last inserted Id value. If AutoIncrement is not used as the primary key in the table, the Id value is always 0.

Rows, err: = db. Query ("SELECT * from ' Test '") if err! = Nil {    log. Fatal (Err)}defer rows. Close () for rows. Next () {    var k, v string    rows. Scan (&k, &v)    log. Printf ("[Rows]key=%s, value=%s", K, V)}

Query a two-dimensional table from which to get multiple rows of data using the Query method. It is important to note that SQL. The Rows ' Scan method receives a parameter that must be a pointer. This means that the address where the data is stored is ready before the Scan is called. The Scan internally uses reflection to identify and assign a parameter type, or to pass a []interface{}. You must call this at this point: rows. Scan (a ...) to get the expected results.

Row: = db. Queryrow ("SELECT * from ' Test ' where ' key ' =?", "name") var k, v Stringrow. Scan (&k, &v) log. Printf ("[Row]key=%s, value=%s", K, V)

For some queries, such as a primary key, only one row of data is read. You can then use Queryrow for a more streamlined reading.

RSLT, err = db. Exec ("Delete from ' Test '") if A, err: = Rslt. Rowsaffected (); Err! = Nil {    log. Print (Err)} else {    log. Printf ("[Del]affected rows=%d", a)}if ID, err: = Rslt. Lastinsertid (); Err! = Nil {    log. Print (Err)} else {    log. Printf ("[Del]last insert id=%d", id)}

For operations that do not require splicing of SQL, you can do so directly in SQL. Call the Exec method on the DB object. Returns the SQL. Result with SQL. Stmt's Exec method is consistent.

On the basis of the use, roughly the content. Database/sql also supports things, this part of the information you can refer to their documentation for learning.