How to operate mysql data in golang

In modern software engineering, the skills required to build efficient and reliable server applications are in high demand. Among them, using Go language to call relational database is an indispensable foundation. In this article, we will use the standard packages in the Go language to query and insert MySQL data.

What is MySQL

MySQL is a relational database management system widely used in Internet infrastructure, developed under the sponsorship of Oracle Corporation. It supports multiple operating systems and programming languages, including the Go language, and has a series of features that support large data integration and high-performance queries.

MySQL data is based on tables. One row of data corresponds to one record, and one column of data corresponds to one field. It uses SQL language to operate on data, the most common SQL operations include SELECT, INSERT, UPDATE, DELETE and CREATE TABLE.

MySQL driver in Go language

In Go language, we can use third-party MySQL driver to connect and query MySQL database. However, the GO language standard library already contains the database/sql package. This package has implemented the standard SQL database interface and can interact with other convenient and popular SQL databases, including MySQL, PostgreSQL and SQLite. wait.

So, before we start building a Go application that queries MySQL, we need to install the MySql driver. You can use the command line command go get -u github.com/go-sql-driver/mysql for quick installation.

This is because in the Go language, all data access must be done through the SQL.DB connection. And the purpose of the driver is to implement the interface conventions in the functions of database/sql.

Establishing a connection

In the Go language, establishing a connection to the MySQL database is based on the connection string. The connection string contains all the parameters required to connect to MySQL. Such as user name, password, host name or IP address, port number and database name, etc.

db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
if err != nil {
    log.Fatal(err)
}

Query data

In Go language, you can use db.Query(), db.QueryRow()anddb.Exec() function queries and operates data in the MySQL database.

db.Query() The function is used to query multiple records and returns a *sql.Rows object. db.QueryRow() The function is used to query a single record and returns a *sql.Row object, similar to resultset, but it only returns the first row of records , usually used to query a single unique record. db.Exec() The function is used to execute non-query SQL statements, such as INSERT, UPDATE, and DELETE, returning a sql.Result object.

In this example, we use the db.Query() function to query all records. We will use a simple counter to count the number of records and print each line of data to the console.

rows, err := db.Query("SELECT id, name FROM users")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

count := 0
for rows.Next() {
    var id int
    var name string
    if err := rows.Scan(&id, &name); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("id=%d, name=%s\n", id, name)
    count++
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

fmt.Printf("Found %d users\n", count)

The rows.Close() function must be called after retrieving data to release the database resources associated with *sql.Rows. At the same time, the rows.Next() function is used in the code, which returns a Boolean value indicating whether there are more rows that can be processed. In addition, the rows.Scan() function is used in this example to copy the value of each row record into the id and name variables defined in this example. , and then prints each value in the console.

Insert data

We can also use Go language to insert data into the MySQL database. The simplest way is to use the db.Exec() function . We can pass the insert statement into the db.Exec() function and it will be executed in MySQL as a SQL statement.

result, err := db.Exec("INSERT INTO users(name, email) VALUES('John Doe', 'johndoe@gmail.com')")
if err != nil {
    log.Fatal(err)
}

rowsAffected, err := result.RowsAffected()
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Inserted %d rows into users table\n", rowsAffected)

We can use result.RowsAffected() to get the number of affected rows in the result set and use this value to print the number of updated data in the console.

Summary

In this article, we introduced the basic query operations of MySQL database using Go language. We learned how to use the standard library database/sql and third-party MySQL drivers to establish connections and query data. We also learned how to insert data into a MySQL database using the db.Exec() function. These concepts are an important part of building efficient and reliable server applications in Go and are an essential skill in modern software engineering.

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