Golang operation Database

Basic concepts Open ()-createsaDBClose ()

Basic concepts: Open ()-creates a DB Close ()-closes the DB Query ()-Query QueryRow ()-Query row Exec ()-execution operation, update, insert, delete Row-A row is not a hash map, but an unsupported action of a cursor Next () Scan () Note: DB is not A connectio

Basic Concepts

• Open ()-creates a DB
  
• Close ()-closes the DB
  
• Query ()-Query
  
• QueryRow ()-query rows
  
• Exec ()-execute operations, update, insert, delete
  
• Row-A row is not a hash map, but an aggregate action of a cursor
  
• Next ()
  
• Scan ()

Note:DB does not refer to a connection

Connect to database

Take mysql as an example. Use github.com/go-sql-driver/mysqlto import the desired package first.

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

Note that we used"",The operation actually introduces the package, instead of directly using the functions in the package, but calls the init function in the package. During import, the init function in the package is actually executed, the variable is initialized. The _ operation only means that the package is introduced. I only initialize the init function and some variables, but these init functions often register the engine in their own package, many packages that implement database/SQL can be easily used externally. SQL is called in the init function. register (name string, driver. driver), and then you can use it externally.
We use the Open () function to Open a database handle.

db, err := sql.Open("mysql", "user:password@tcp(ip:port)/database")

Write a complete:

Import ("database/SQL" _ "github.com/go-sql-driver/mysql" log ") func main () {db, err: = SQL. open ("mysql", "user: password @ tcp (ip: port)/database") if err! = Nil {log. Println (err)} // perform some database operations here defer db. Close ()}

When we execute the Open function, we will not obtain the database connection validity. We will connect the database only when we execute the database operation, when we need to know the connection validity after Open, we can Ping ().

err = db.Ping()if err != nil {    log.Println(err)}

We usually use Close to Close the database connection, but SQL. DB is an effective type designed for growth. We should not Open and Close frequently. Instead, we should establish an SQL statement. DB, which is always used when the program needs to perform database operations. Do not Open or Close it in a method. Instead, use SQL. DB is passed to the method as a parameter

Add, delete, and modify Database Operations

The Exec () method is generally used for adding, deleting, and modifying operations. Here, the addition is used as an example:

Stmt, err: = db. Prepare ("insert into user (name, age) values (?,?) ") If err! = Nil {log. Println (err)} rs, err: = stmt. Exec ("go-test", 12) if err! = Nil {log. println (err)} // we can obtain the inserted idid, err: = rs. lastInsertId () // you can obtain the number of affected rows affect, err: = rs. rowsAffected ()

General query operations

    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 develop the habit of disabling rows, and never forget rows at any time. close (). even if the rows is automatically closed after the loop is completed, we define the rows. close () is also good for us, because we cannot guarantee whether the rows will be finished normally.

Query a single record,

We use db. QueryRow ()

    var name string    err = db.QueryRow("select name from user where id = ?", 222).Scan(&name)

If no result is returned, the system returns the err.

Process null values

We use a record with the name field being null 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 about whether the value is Null. We just need to treat it as an empty string. In this case, we can use[] Byte (null byte [] can be converted to an empty string) orSQL. 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 connection    db, err := sql.Open("mysql", "user:password@/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 an argument, so we must copy the    // references into such a slice    // See http://code.google.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 do 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    }}

Transactions

Use db. Begin () to start 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 can all be used on tx. The usage is the same as in * SQL. DB. The transaction must end with Commit () or Rollback ().

The Connection Pool

There is a basic connection pool in database/SQL, and you do not have much control. You can only set SetMaxIdleConns and SetMaxOpenConns, that is, the maximum number of idle connections and the maximum number of connections.

db.SetMaxIdleConns(n)db.SetMaxOpenConns(n)