How to query mysql in golang

With the development of the Internet, data processing and management have become a vital part of various industries. A database management system (DBMS) is a software system for managing and accessing databases that many businesses use in daily operations and management. MySQL is one of the commonly used relational database management systems. During development, we may need to use programming language to connect and operate MySQL. This article will introduce how to use go language to query MySQL database.

1. Install the MySQL driver

The official website of go language provides the MySQL database driver and supports many different MySQL versions. Installing the MySQL driver requires that the system has a go language development environment and a MySQL database.

1. Open the command line and use the following command to install the MySQL driver:

go get github.com/go-sql-driver/mysql

The go-sql-driver package provides basic functions for MySQL queries. You can now use it to perform MySQL queries.

2. Establish a database connection

Before using the go language to perform MySQL queries, you need to establish a connection to the MySQL database. To connect to the MySQL database, you need to specify the user name, password, database name, host address and other information.

1. Open the go language editor (for example: VSCode) and create a new file named db.go.
2. Enter the following code in the file:

package main

import (
    "database/sql"
    "fmt"
    "log"

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

func main() {

    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/databaseName")

    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()

    err = db.Ping()

    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Successful database connection")
}

In the above code, we need to specify the driver name and database connection string when we use the sql.Open() function to open the MySQL database connection. The connection string specifies the username and password, as well as the MySQL host address and port number. When connecting to MySQL, the MySQL driver will be responsible for processing the connection response. If the connection to this database is successful, a pointer to the database object will be returned. After all matters are resolved, you need to use db.Close() to close the connection to ensure that there is no excessive consumption of database resources due to connection leaks.

3. Execute the query statement

After obtaining the SQL database connection, you can start executing the query and operation statements. The following is a command example of a simple query statement:

rows, err := db.Query("SELECT id, name, age FROM user")

The above query statement can query the id, name and age fields in the table named user. Using the db.Query() method will execute the query and return the row objects (rows) of the query results and possible errors (err). The result data of the query is wrapped in row objects (rows).

Of course, if the SQL statement we need to execute contains some parameters, we need to use placeholders. An example is as follows:

rows, err := db.Query("SELECT id, name, age FROM user WHERE name = ? AND age = ?", "John", 30)

The placeholder is automatically converted to the correct SQL format before querying, and the parameters required for the query are passed to the SQL driver. This approach prevents SQL injection attacks and keeps query statements safe.

4. Processing query results

After querying the MySQL database, we can use the Rows object to read data from the result set. The Rows object provides a set of methods for manipulating the result data of the query. For example, you can use the Scan() method to extract each column (column) in each row (row) in the result set:

for rows.Next() {
    var id int
    var name string
    var age int
    err := rows.Scan(&id, &name, &age)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("ID:%d, Name:%s, Age:%d", id, name, age)
}

In the above code, use rows.Next() and rows.Scan( ) method extracts each row of data and outputs it to the console. When the rows.Next() method points to the next row, you can use the rows.Scan() method to extract the row data in that row. The parameter of the Scan() method is the address of each column of data.

5. Complete code example

The following is a complete example program that uses go language to query MySQL, which can be saved as a db.go file:

package main

import (
    "database/sql"
    "fmt"
    "log"

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

func main() {

    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/databaseName")

    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()

    err = db.Ping()

    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Successful database connection")

    rows, err := db.Query("SELECT id, name, age FROM user")

    if err != nil {
        log.Fatal(err)
    }

    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var age int
        err := rows.Scan(&id, &name, &age)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("ID:%d, Name:%s, Age:%d
", id, name, age)
    }

    err = rows.Err()

    if err != nil {
        log.Fatal(err)
    }

}

Summary

In this article, we learned how to query the MySQL database in go language. First you need to install the MySQL driver and then establish a connection to the MySQL database. After obtaining a database connection, you can execute queries and operation statements and use Rows objects to read data from the result set. Finally, we provide a complete Go language program example that demonstrates how to connect, query and read the MySQL database.

The above is the detailed content of How to query mysql in golang. For more information, please follow other related articles on the PHP Chinese website!