Detailed explanation of Go language MySQLCURD database operation example

MySQL

MySQL is the most common relational database in development. To use Go to control the database, Go needs to be built-indatabase/sqland drivego-sql-driver/mysqlTo achieve it,

To create a Go project, you need to reference the driver dependencies:

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

Using MySQL driver:

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

Open opens a database specified by dirverName, and dataSourceName specifies the data source, generally including at least the database file name and other necessary information for connection.

Initialize the connection

var db * //Declare a global db variable// Initialize MySQL functionfunc initMySQL() (err error) {
    dsn := "root:password@tcp(127.0.0.1:3306)/dbname"
    db, err = ("mysql", dsn)
    if err != nil {
        return
    }
    err = ()
    if err != nil {
        return
    }
    return
}
func main() {
    // Initialize MySQL    err := initMySQL()
    if err != nil {
        panic(err)
    }
    defer ()
}

You need to use the help after initializing the connection MySQLFunction to determine whether the connection is successful.

SetMaxOpenConns

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConnsSet the maximum number of connections to the database。
If n is greater than 0 and less than the maximum number of idle connections, the maximum number of idle connections is reduced to the limit that matches the maximum number of open connections.
If n <= 0, the maximum number of open connections will not be limited, and the default is 0 (unlimited).

SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConnsSet the maximum number of idle connections in the connection pool。
If n is greater than the maximum number of open connections, the new maximum number of idle connections will be reduced to the limit that matches the maximum number of open connections.
If n <= 0, idle connections are not retained.

CURD

To perform CURD operations, you need to establish a connection to the database and have data for operation (database and data table):

Initialize data

Create a database sql\_demo

CREATE DATABASE sql_demo;
USE sql_demo;

Create data table user

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Query SELECT

Easy to receive data, define auserStructure receives data:

type user struct {
    id   int
    age  int
    name string
}

Query a row of data

()Execute a query and expect to return up to one row of results (i.e. Row). QueryRow always returns a value that is not nil, and will not return a delayed error until the Scan method that returns the value is called. (For example: no results were found)

func (db *DB) QueryRow(query string, args ...interface{}) *Row

The example code is as follows:

// Query a row of datafunc queryRowDemo() (u1 *user, err error) {
    // Declare query statement    sqlStr := "SELECT id,name,age FROM user WHERE id = ?"
    // Declare a variable of type user    var u user
    // Execute the query and scan to u    err = (sqlStr, 1).Scan(&amp;, &amp;, &amp;)
    if err != nil {
        return nil, err
    }
    u1 = &amp;u
    return
}
func main() {
    // Initialize MySQL    err := initMySQL()
    if err != nil {
        panic(err)
    }
    defer ()
    u1, err := queryRowDemo()
    if err != nil {
        ("err:%s", err)
    }
    ("id:%d, age:%d, name:%s\n", , , )
」

The results are as follows:

id:1, age:111, name:22

Multi-line query

()Execute a query and return multiple rows of results (i.e. Rows), which is generally used to execute the select command. The parameter args represents the placeholder parameter in query.

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

The example code is as follows:

// Query multiple rows of datafunc queryMultiRowDemo() {
    sqlStr := "SELECT id,name,age FROM user WHERE id &gt; ?"
    rows, err := (sqlStr, 0)
    if err != nil {
        ("query data failed，err:%s\n", err)
        return
    }
    // After querying data, you need to close the database link    defer ()
    for () {
        var u user
        err := (&amp;, &amp;, &amp;)
        if err != nil {
            ("scan data failed, err:%v\n", err)
            return
        }
        ("id:%d name:%s age:%d\n", , , )
    }
}

Execution results:

id:1 name:111 age:22
id:3 name:Zhang San age:22

use()Read the data in the result set loop.

Add data INSERT

Add, delete, and update operations are usedExecmethod.

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

The example code is as follows:

// Add one line of datafunc insertRowDemo() {
    sqlStr := "INSERT INTO user(name, age) VALUES(?, ?)"
    result, err := (sqlStr, "Xiao Yu", 22)
    if err != nil {
        ("insert data failed, err:%v\n", err)
        return
    }
    id, err := ()
    if err != nil {
        ("get insert lastInsertId failed, err:%v\n", err)
        return
    }
    ("insert success, id:%d\n", id)
}

Execution results:

insert success, id:4

Update data UPDATE

// Update a set of datafunc updateRowDemo() {
    sqlStr := "UPDATE user SET age = ? WHERE id = ?"
    result, err := (sqlStr, 22, 1)
    if err != nil {
        ("update data failed, err:%v\n", err)
        return
    }
    n, err := ()
    if err != nil {
        ("get rowsaffected failed, err:%v\n", err)
        return
    }
    ("update success, affected rows:%d\n", n)
}

Delete data DELETE

// Delete a line of datafunc deleteRowDemo() {
    sqlStr := "DELETE FROM user WHERE id = ?"
    result, err := (sqlStr, 2)
    if err != nil {
        ("delete data failed, err:%d\n", err)
        return
    }
    n, err := ()
    if err != nil {
        ("get affected failed, err:%v\n", err)
        return
    }
    ("delete success, affected rows:%d\n", n)
}

Reference (learning) articles:Go language operation MySQL

The above is the detailed content of the CURD operation of Go language operation MySQL. For more information about Go MySQL CURD operation, please follow my other related articles!