Go language learning operation MYSQL implements CRUD

1. Introduction

GoOfficially provideddatabaseBag,databasePacked withsql/driver. This package is used to define the interface to operate the database, which ensures that the operation method is the same regardless of the database used. butGoThe official does not provide a database connectiondriverIf you want to operate the database, you also need a third-partydriverBag.

2. Download and install the driver

go-sql-driverDriver source code address:/go-sql-driver/mysql

2.1 Install the driver

go get -u /go-sql-driver/mysql

3. Anonymous import

Generally speaking, the data and methods in the package can be called after importing it. However, for database operations, you should not use the methods provided by importing the driver package directly, but should useThe unified method provided by the object. Therefore, importingMySQLWhen driving, anonymous import packages are used.

Anonymous import package: Only import the package but do not use the types and data in the package. Use anonymous method: add the line before the package path._

import (
    _ "/go-sql-driver/mysql"
)

After importing a database driver, the driver will be initialized and registered withGoofOn database/sqlHereinafter, this can be passeddatabase/sql The method provided by the package is to access the database.

4. Connect to the database

4.1 Connection method

usesqlIn the packageOpen()Function to connect to the database.

Open(driverName, dataSourceName string) (*DB, error)

driverName: The driver name used, such asmysql. (Register indatabase/sqlThe name used when it is used)
dataSourceName:Database connection information, format:[Username: Password @tcp(IP:port)/Database?charset=utf8],For example:root:123456@tcp(127.0.0.1:3306)/test?charset=utf8

4.2 Function

()ReturnedThe object isGoroutineConcurrency is safe.
Through database drivers, developers provide open and close operations to manage underlying database connections.
Help developers manage database connection pools. The connection being used is marked as busy, and after use, it returns to the connection pool and waits for the next use. Therefore, if the developer does not release the connection back to the connection pool, it will cause too many connections to exhaust system resources. </font>

4.3 Design objectives

The design goal is to be used as a long connection (one connection and multiple data interactions), and it is not suitable to switch frequently. Better practice is to be different for eachdatastoreBuild oneDBObjects, keep these objects open. If you need a short connection (one connection and data interaction), just put itDBPassed as parameterfunction, not infunctionmid switch.

5. Write operations (add, delete, modify)

5.1 Execution steps

Use firstPreparedStatementCome splicingsql。
Then call()implementSQL, return the execution result

5.2 Code Example

package main

import (
    "database/sql"
    "fmt"
    _ "/go-sql-driver/mysql"
    "time"
)
func main() {
    // Connect to the database    open, err := ("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8")
    checkError(err)
  //Insert data    //add(open)
    // Update data    //update(open)
    // Delete data    del(open)
}
//Insert datafunc add(open *)  {
    //Insert data    prepare, err := ("insert user set username=?,password=?,mobile=?,createtime=?")
    checkError(err)
    exec, err := ("Li Si", "123456", "17600000000", ().Unix())
    checkError(err)
    id, err := ()
    checkError(err)
    ("Insert Data ID: %d \n",id)
}
// renewfunc update(open *)  {
    prepare, err := ("update user set username=? where id =?")
    checkError(err)
    exec, err := ("Wang Wu", "18")
    checkError(err)
    rows, err := ()
    checkError(err)
    ("The data is updated successfully, affecting the number of pieces %d \n",rows)
}
// Delete datafunc del(open *)  {
    prepare, err := ("delete from user  where id =?")
    checkError(err)
    exec, err := ( "8")
    checkError(err)
    rows, err := ()
    checkError(err)
    ("The data is deleted successfully, affecting the number of pieces %d \n",rows)
}
//Detect errorfunc checkError(err error)  {
    if err != nil {
        panic("Operation failed:"+())
    }
}

6. Read operation (query)

6.1 Execution steps

1. Query multiple steps

Call()Method executionSQLstatement, return aRowsQuery results.
Will()The return value of the method isforThe condition of looping, iterative query data.
In the loop, by()Method reads each row of data.
Call()Close the query.

2. Query a single step

Call()Method executionSQLstatement, return aRowQuery results.
Then call()Read data.

6.2 Code Example

package main
import (
    "database/sql"
    "fmt"
    _ "/go-sql-driver/mysql"
)
func main() {
    // Connect to the database    db, err := ("mysql", "root:root@tcp(127.0.0.1:3306)/nsbd_app?charset=utf8")
    checkError(err)
    //Query multiple data    rows := queryRows(db)
    ("Multiple returns: \n%+v\n",rows)
    // Query a single piece of data    row := queryRow(db)
    ("Single return: \n%+v\n",row)
}

// Create a map object for the tabletype User struct {
    Uid        int
    UserName   string
    CreateTime int
    Birthday    //Some values may be NULL}

//Query multiple datafunc queryRows(db *) []User {
    stmt, err := ("select id,username,createtime,birthday from nsbd_user where id &lt; ?")
    checkError(err)
    rows, err := (30)
    // Delayed shutdown    defer ()
    checkError(err)
    user := new(User)
    //users := make([]User,5)
    var users []User
    for () {
        // The parameter order of the () method is very important and must correspond to the column of the query result (the quantity and order need to be consistent)        err := (&amp;, &amp;, &amp;, &amp;)
        checkError(err)
        users = append(users, *user)
    }
    return users
}
// Query a single piece of datafunc queryRow(db *) User {
    stmt, err := ("select id,username,createtime,birthday from nsbd_user where id = ?")
    checkError(err)
    user := new(User)
    err = (4).Scan(&amp;, &amp;, &amp;, &amp;)
    checkError(err)
    return *user
}
//Detect errorfunc checkError(err error) {
    if err != nil {
        panic("Operation failed:" + ())
    }
}

Output:

Multiple returns:
[{Uid:1 UserName:admin CreateTime:0 Birthday:{String:2017-04-15 Valid:true}} {Uid:2 UserName:u2 CreateTime:1605858072 Birthday:{String:1993-02-14 Valid:true}} {Uid:3 UserName:u3 CreateTime:1606289644 Birthday:{String:1991-05-31 Valid:true}} {Uid:4 UserName:u4 CreateTime:1610521164 Birthday:{String:1989-11-24 Valid:true}} {Uid:5 UserName:u5 CreateTime:1610588359 Birthday:{String: Valid:false}}]
Single return:
{Uid:4 UserName:u4 CreateTime:1610521164 Birthday:{String:1989-11-24 Valid:true}}

6.3 Things to note

()The order of parameters of the method is very important and must be related to the query result.columnCorresponding (both quantity and order need to be consistent);
GoIt is a strongly typed language. When querying data, the data type is first defined, and the field value isNULLWhen the data type should be defined as:、sql.NullInt64etc. and can be passedValidValue to determine whether the query value is an assigned state or an unassigned state.
every time()After operation, it is recommended to call it()。()Operation is an idempotent operation, even for closedrowsExecute againclose()It doesn't matter.

6.4 Why do you need to close the connection after query

because()A connection will be obtained from the database connection pool, and this underlying connection is in the result set (rows) will be marked as busy before closing. When traversal reads the last record, an internal happensEOFError, automatically called(). But if an exception occurs, exit the loop early.rowsIf it does not close, the connection will not return to the connection pool, and the connection will not close, then this connection will be occupied all the time. Therefore, it is usually useddefer ()to ensure that the database connection can be correctly placed back into the connection pool.

The above is the detailed content of Go language learning to implement CRUD. For more information about Go language operation MYSQL implementation of CRUD, please pay attention to my other related articles!