MySQL database and Go language: how to perform data query?

With the continuous development of the Internet and data processing technology, databases are playing an increasingly important role as the core tool for data storage and management. In modern web development, MySQL, as one of the most widely used relational databases, is often used to store and manage data. The Go language has become one of the most popular programming languages ​​​​because of its efficiency, speed, and simplicity. It also has many excellent libraries and frameworks related to database operations. So, how to perform data query in MySQL database and Go language? This article will introduce this in detail.

1. Basics of Go language

First of all, we need to master some basic knowledge of Go language. The Go language provides the database/sql standard library, which is a lightweight data access layer that supports the access and operation of SQL databases. Using the database/sql package, we can easily perform operations such as database connection, query, and update.

The basic steps for data query using the database/sql standard library in Go language are as follows:

1. Open the database connection

By calling the sql.Open function Create a connection to the database. This function requires two parameters: driver name and data source name. In MySQL, the driver name is "mysql". The data source name contains all the information needed to connect to the database, such as database IP address, port, user name, password, database name, etc.

The sample code is as follows:

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

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

2. Build a SQL query statement

Use a SQL query statement to define the operation to be performed. In the Go language, you can use ? as a placeholder to replace parameters in the query. These parameters are automatically replaced with actual values ​​when executing the query, thus preventing SQL injection attacks.

The sample code is as follows:

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

3. Execute query operation

Execute the SQL query statement by calling the stmt.Query function or stmt.QueryRow function. The Query function returns a rowset object, which can be traversed to obtain the result set. The QueryRow function returns only a single row of the result set.

The sample code is as follows:

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

for rows.Next() {
  // 处理查询结果
}

4. Parse the query results

Use the rows.Scan function or the rows.Columns function to process the query results. The Scan function gets the values ​​of the current row and stores them into a specified variable, while the Columns function returns the column names of the result set.

The sample code is as follows:

var id int
var name string

err := rows.Scan(&id, &name)
if err != nil {
  panic(err)
}

2. Introduction to MySQL statements

After understanding the basic knowledge of Go language, we also need to master some knowledge of MySQL statements. MySQL supports a variety of query statements, such as SELECT, INSERT, UPDATE, DELETE, etc. In this article, we will introduce the SELECT query statement, which is one of the most commonly used query statements in MySQL.

1. SELECT statement

The SELECT statement is used to select data from one or more tables and return the result set to the client. The general form of the SELECT statement is as follows:

SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;

Among them, the column name represents the name of the column to be queried, separated by commas; the table name represents the name of the table to be queried; the WHERE clause is optional and is used Filter data.

The sample code is as follows:

SELECT id, name, age FROM users WHERE age > 18;

2. WHERE statement

The WHERE statement is used to select data that meets specified conditions from the table. The WHERE clause can contain one or more conditions, connected by AND or OR. Conditions are usually defined using comparison operators (such as >, <, =, !=) and logical operators (such as AND or OR).

The sample code is as follows:

SELECT id, name, age FROM users WHERE age > 18 AND name LIKE '%john%';

3. ORDER BY statement

The ORDER BY statement is used to sort the result set according to one or more specified columns. By default, the ORDER BY statement sorts in ascending order.

The sample code is as follows:

SELECT id, name, age FROM users WHERE age > 18 ORDER BY age DESC;

4. LIMIT statement

The LIMIT statement is used to limit the number of rows returned in the result set. You can use the OFFSET clause to specify where to start returning.

The sample code is as follows:

SELECT id, name, age FROM users WHERE age > 18 LIMIT 5 OFFSET 10;

3. Use Go language for MySQL query

1. Query a single result

The following is an example Function, used to query a user with id 1:

func getUserByID(db *sql.DB) (User, error) {
  var user User
  err := db.QueryRow("SELECT id, name, age FROM users WHERE id = ?", 1).Scan(&user.ID, &user.Name, &user.Age)
  if err != nil {
    return User{}, err
  }
  return user, nil
}

In this example function, we query the user with id 1 by calling the QueryRow function. The QueryRow function only returns one row from the result set, so we can use the Scan function to store the results into the User structure.

2. Query multiple results

The following is an example function to query all users older than 18 years old:

func getUsersByAge(db *sql.DB, age int) ([]User, error) {
  var users []User
  rows, err := db.Query("SELECT id, name, age FROM users WHERE age > ?", age)
  if err != nil {
    return nil, err
  }
  defer rows.Close()

  for rows.Next() {
    var user User
    err := rows.Scan(&user.ID, &user.Name, &user.Age)
    if err != nil {
      return nil, err
    }
    users = append(users, user)
  }

  return users, nil
}

In this example function , we call the Query function to query all users older than 18, and obtain all results by traversing the row set. As we iterate over the rowset, we store the results into the User structure via the Scan function and append them to the users slice.

4. Summary

In this article, we introduced how to use Go language to query MySQL data. We first introduced the basic knowledge of Go language, then explained the commonly used query statements in MySQL, and finally demonstrated how to use Go language to perform MySQL queries through sample code.

It should be noted that the above sample code is only a basic query operation. In actual development, we need to use different query statements and application models according to specific needs and scenarios. At the same time, the performance of query statements also needs to be considered. We should try to use optimization methods such as indexes to improve query performance.

The above is the detailed content of MySQL database and Go language: how to perform data query?. For more information, please follow other related articles on the PHP Chinese website!