How golang operates mysql data

What is MySQL

MySQL, sponsored and developed by Oracle, is a relational database management system widely used in Internet infrastructure. 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 uses a table-based data structure, with each row representing a record and each column representing a 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.

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