Golang Operating Database

Basic concepts

• Open () –creates a DB
  
• Close ()-Closes the DB
  
• Query ()-Search
  
• Queryrow ()-Query row
  
• Exec ()-Perform operation, Update,insert,delete
  
• Row-a Row isn't a hash map, but an abstraction of a cursor
  
• Next ()
  
• Scan ()

Note: DB does not refer to a connection

Connecting to a database

Let's take MySQL for example, using Github.com/go-sql-driver/mysql, first we need to import the packages we need

Import ("Database/sql" _ "Github.com/go-sql-driver/mysql")

Note that we import github.com/go-sql-driver/mysql in front of a "", the operation is actually introduced to the package, rather than directly using the function inside the package, but instead called the package inside the INIT function, Import when actually executed the package inside the INIT function, initialized the inside of the variable, _ operation just said that the package introduced, I only initialize the inside of the INIT function and some variables, but often these init functions inside the register their own package inside the engine, so that the external can be easily used, There are many packages that implement Database/sql, and SQL is called in the init function. Register (name string, driver driver. Driver) Register yourself, and then you can use it externally.
We open a database handle using the open () function.

DB, err: = SQL. Open ("MySQL", "User:[email Protected] (ip:port)/database")

Write a complete:

Import ("Database/sql" _ "Github.com/go-sql-driver/mysql" "Log") func main () {    db, err: = SQL. Open ("MySQL", "User:[email Protected] (ip:port)/database")    if err! = Nil {        log. PRINTLN (Err)    }    //Here are some database operations    defer db. Close ()}

When we execute the open function, we do not get the validity of the database connection, when we perform the database operation, we will connect, when we need to know the validity of the connection after open, we can do it by ping ().

Err = db. Ping () if err! = Nil {    log. PRINTLN (ERR)}

We are usually accustomed to using close to close the database connection, but SQL. DB is designed to be a long-term effective type, we should not frequent open and close, instead we should build a SQL. DB, always use it when the program needs to do database operation, do not open and close in a method, should put SQL. DB is passed as a parameter to the method

Perform database operationsAdding and removing changes operation

The Exec () method is generally used to add or delete operations, here, for example:

stmt, err: = db. Prepare ("INSERT into User (Name,age) VALUES (?,?)") If err! = Nil {    log. PRINTLN (Err)}rs, err: = stmt. Exec ("go-test", +) if err! = Nil {    log. PRINTLN (ERR)}//We can get the inserted idid, err: = Rs. Lastinsertid ()//Can get influence on the number of rows affect, err: = Rs. Rowsaffected ()

Query operationsGeneral Query

var name stringvar age introws, err: = db. Query ("Select Name,age from user where id =?", 1) if err! = Nil {fmt. PRINTLN (Err)}defer rows. Close () for rows. Next () {err: = rows. Scan (&name, &age) if err! = Nil {fmt. PRINTLN (err)}}err = rows. ERR () if err! = Nil {fmt. PRINTLN (Err)}fmt. Println ("Name:", URL, "Age:", description)

We should get into the habit of shutting down rows, and don't forget the rows at any time. Close (). Even if this rows has been automatically closed after the cycle is complete, we define rows. Close () is no harm to us, because we cannot guarantee that rows will cycle properly.

Query a single record,

We use DB. Queryrow ()

var name string    Err = db. Queryrow ("Select name from user where id =?", 222). Scan (&name)

Err is returned when there is no result

Handling Null values

We use a record with an empty name field as an example.

var name nullstringerr: = db. Queryrow ("Select name from names WHERE id =?", id). Scan (&name) ... if name. Valid {        //use name. String} else {        //value is NULL}

In this case we usually use nullstring, but sometimes we don't care if the value is null, we just need it. He treats the line as if it were an empty string. At this point we can use []byte (null byte[] to convert to an empty string) or SQL. Rawbytes,

var col1, col2 []bytefor rows. Next () {    //Scan the value to []byte    err = rows. Scan (&col1, &col2)    if err! = Nil {        Panic (err. Error ())//Just For example purpose. You should use proper error handling instead of Panic    }    //Use the string value    FMT. Println (String (col1), String (col2))}

Use *sql. Rawbytes

Package Mainimport ("Database/sql" "Fmt" _ "Github.com/go-sql-driver/mysql") func main () {//Open database Co Nnection db, err: = SQL. Open ("MySQL", "user:[email protected]/dbname") if err! = Nil {panic (err. Error ())//Just For example purpose. You should use proper error handling instead of panic} defer db. Close ()//Execute the query rows, err: = db. Query ("Select * FROM table") if err! = Nil {panic (err. Error ())//Proper error handling instead of panic in your app}//Get column names columns, err: = rows. Columns () if err! = Nil {panic (err. Error ())//Proper error handling instead of panic in your app}//Make a slice for the values values: = Make ([] Sql. Rawbytes, Len (columns))//rows. Scan wants ' []interface{} ' as a argument, so we must copy the-//references into such a-slice//See Http://code.go Ogle.com/p/go-wiki/wiki/interfaceslice for details Scanargs: = Make ([]interface{}, Len (valUES)) for I: = range values {scanargs[i] = &values[i "}//Fetch rows for rows. Next () {//Get rawbytes from data err = rows.        Scan (Scanargs ...) If err! = Nil {panic (err.        Error ())///Proper error handling instead of panic in your app}//now does something with the data.        Here we just print each column as a string.            var value string for I, col: = range values {//Here we can check if the value is nil (NULL value)            If col = = Nil {value = "NULL"} else {value = string (col)} Fmt. Println (Columns[i], ":", Value)} FMT. Println ("-----------------------------------")} If Err = rows. ERR (); Err! = Nil {panic (err. Error ())//Proper error handling instead of panic in your app}}

Transaction

Use DB. Begin () to open a transaction and close it through the commit () and rollback () methods.

TX: = db. Begin () Tx. Rollback () Tx.commit ()

The Exec, Query, Queryrow and Prepare methods are all available on TX. Use methods and in *sql. DB is the same, the transaction must end with commit () or rollback ()

The Connection Pool

In Database/sql there is a very basic connection pool, you do not have much control, you can only set Setmaxidleconns and Setmaxopenconns, that is, the maximum idle connection and the maximum number of connections.

Db. Setmaxidleconns (n) db. Setmaxopenconns (N)

Golang Operating Database