golang mysql query

Golang is an increasingly popular programming language that excels at handling large amounts of data and high concurrency performance. At the same time, MySQL, as a popular relational database system, is very compatible with Golang. In this article, we will explore how to execute MySQL queries in Golang.

1. Install MySQL driver

Before using Golang to query MySQL, you need to install the MySQL driver first. The most commonly used MySQL driver in Golang is "mysql", which can be installed through the following command:

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

This command will install the mysql driver in the Golang environment.

2. Connecting to MySQL database

Before performing MySQL query, you need to establish a connection with the database first. The following code can be used to establish a connection to the MySQL database:

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

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
}

In the above code, we use the "sql.Open" function to open the MySQL database from Golang with the following parameters:

• The first parameter: Specify the database driver used, here is "mysql"

• The second parameter: Specify the connection information of the MySQL database, the format is "username:password@tcp(host :port)/database_name"

  • username: the username of the MySQL database
  • password: the password of the MySQL database
  • host:port: the host and port of the MySQL database No.
  • database_name: The name of the MySQL database

After executing the query, you need to close the connection through the "db.Close()" statement.

3. Execute SELECT query

After establishing the connection, you can use Golang to execute MySQL query. The following is a simple sample code to execute a SELECT query statement:

rows, err := db.Query("SELECT name, age FROM users WHERE age > ?", 20)
if err != nil {
    panic(err.Error())
}
defer rows.Close()

for rows.Next() {
    var name string
    var age int
    err = rows.Scan(&name, &age)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(name, age)
}

• The "db.Query" function accepts a SQL query statement and its parameters, and returns the result set
• "rows. Next" loops through each row of data, and the "rows.Scan" function scans each row of data into a variable
• After completing the query, you need to call the "rows.Close" function to close the iterator of the result set

4. Perform INSERT, UPDATE, and DELETE operations

In Golang, performing INSERT, UPDATE, DELETE and other operations is basically the same as performing SELECT operations. The following is a sample code:

result, err := db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "John", 25)
if err != nil {
    panic(err.Error())
}
lastInsertId, _ := result.LastInsertId()
rowsAffected, _ := result.RowsAffected()
fmt.Printf("LastInsertId: %d, RowsAffected: %d
", lastInsertId, rowsAffected)

• The "db.Exec" function passes the SQL operation statement and its parameters to the MySQL database
• The "result.LastInsertId()" function returns the inserted data Primary key ID
• "result.RowsAffected()" function returns the number of affected rows

5. Using prepared statements

In Golang , you can use prepared statements to execute MySQL queries. Prepared statements can improve query performance because the MySQL server can cache the same query statements when executing multiple identical query statements, reducing the overhead of compiling SQL statements. The following is an example of using prepared statements:

stmt, err := db.Prepare("SELECT name, age FROM users WHERE age > ?")
if err != nil {
    panic(err.Error())
}
defer stmt.Close()

rows, err := stmt.Query(20)
if err != nil {
    panic(err.Error())
}
defer rows.Close()

for rows.Next() {
    var name string
    var age int
    err = rows.Scan(&name, &age)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(name, age)
}

• The "db.Prepare" function compiles the query statement into a prepared statement object
• In the prepared object, "?" is used to represent parameter placeholders
• Similar to "db.Query", you can use the "stmt.Query" function to execute preprocessed queries

6. Safety Consider

Security is very important when making MySQL queries. Golang provides some best practices for using MySQL queries, including:

• Do not use string concatenation to build SQL statements, you should use prepared statements and parameter placeholders
• Do not dynamically construct queries from user input as this may expose the database
• Entered data must be escaped or parameterized to prevent SQL injection attacks

These best practices can reduce the risk of attacks such as SQL injection.

Conclusion

In this article, we explored how to execute MySQL queries in Golang. After understanding the basic connection methods and syntax, you can use Golang to query the MySQL database and use best practices to ensure the security of the query. When using Golang for MySQL queries, always keep security issues in mind.

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