The Go language is gradually favored by developers for its efficient and concise syntax. In actual development, database operations are one of the inevitable tasks. Although the standard library providesdatabase/sqlPackages support database operations, but they are a bit cumbersome to use.
sqlxThe package as an extension library, it is indatabase/sqlBased on the basis of this, it provides a higher level of convenience and greatly simplifies database operations. This article will introduce how to pass/jmoiron/sqlxPackage to operate on the MySQL database.
Preparation
First, make sure your Go environment is built and the MySQL database is installed and running. Next, installsqlxPackage and MySQL driver:
go get /jmoiron/sqlx go get /go-sql-driver/mysql
Connect to MySQL database
Before using the database, we need to establish a connection to MySQL. In Go, a connection string is usually used to specify some information about the database. Here is a sample code that demonstrates how to connect to a MySQL database:
package main
import (
"fmt"
_ "/go-sql-driver/mysql" // You must not forget to import the database driver "/jmoiron/sqlx"
)
var db *
type User struct {
ID int64 `db:"id"`
Name string `db:"name"`
Age int `db:"age"`
}
func initDB() (err error) {
dsn := "root:123456@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
// You can also use MustConnect to panic directly if the connection is not successful // db = ("mysql", dsn)
db, err = ("mysql", dsn)
if err != nil {
("connect DB failed, err:%v\n", err)
return
}
(20) // Set the maximum number of connections to the database connection pool (10) // Set the maximum number of idle connections in the database connection pool return
}
In this example, replace with your own MySQL configuration.
Database operations
1. Create a table
Next, let's create a sample table. We can useExecThe method executes SQL statements to create a table.
func CreateTable(db *) (err error) {
// Write SQL statements sqlStr := `create table if not exists users (
id bigint primary key auto_increment,
name varchar(20),
age int default 1
);`
_, err = (sqlStr)
return err
}
existmainCalled in a functionCreateTable(db)to ensure that the table is created after the connection.
2. Insert data
// Insert the user and get the IDfunc insertUser(db *, name string, age int) (int64, error) {
result, err := ("INSERT INTO users(name, age) VALUES(?, ?)", name, age)
if err != nil {
return 0, err
}
id, err := ()
if err != nil {
return 0, err
}
return id, nil
}
3. Query data
// Query a single user recordfunc getUser(db *, id int64) (*User, error) {
var user User
err := (&user, "SELECT * FROM users WHERE id=?", id)
if err != nil {
return nil, err
}
return &user, nil
}
// Query all user recordsfunc getAllUsers(db *, id int64) ([]User, error) {
var users []User
err := (&users, "SELECT * FROM users where id > ?", id)
if err != nil {
return nil, err
}
return users, nil
}
4. Update data
// Update user informationfunc updateUser(db *, id int64, name string, age int) (int64, error) {
result, err := ("UPDATE users SET name=?, age=? WHERE id=?", name, age, id)
if err != nil {
return 0, err
}
rowsAffected, err := ()
if err != nil {
return 0, err
}
return rowsAffected, nil
}
5. Delete data
// Delete user recordsfunc deleteUser(db *, id int64) (int64, error) {
result, err := ("DELETE FROM users WHERE id=?", id)
if err != nil {
return 0, err
}
rowsAffected, err := ()
if err != nil {
return 0, err
}
return rowsAffected, nil
}
6. Use named parameters to operate
// Insert the user with named parametersfunc insertUserNamed(db *, name string, age int) (int64, error) {
query := `INSERT INTO users(name, age) VALUES(:name, :age)`
result, err := (query, map[string]interface{}{
"name": name,
"age": age,
})
if err != nil {
return 0, err
}
id, err := ()
if err != nil {
return 0, err
}
return id, nil
}
// Query the user with named parametersfunc getUsersNamed(db *, name string) ([]User, error) {
query := `SELECT * FROM users WHERE name = :name`
var users []User
rows, err := (query, map[string]interface{}{
"name": name,
})
if err != nil {
return nil, err
}
defer ()
for () {
var user User
err := (&user)
if err != nil {
("scan failed, err:%v\n", err)
continue
}
users = append(users, user)
}
return users, nil
}
7. Test the code
func Run() {
// Initialize the database err := initDB()
if err != nil {
("init DB failed, err:%v\n", err)
return
}
defer () // Note that this line of code should be written below the err judgment above
// Create table err = CreateTable(db)
if err != nil {
("create table failed, err:%v\n", err)
return
}
// Insert data id, err := insertUser(db, "Alex", 18)
if err != nil {
("insert user failed, err:%v\n", err)
return
}
("insert success, the id is:", id)
// Query a single piece of data user, err := getUser(db, id)
if err != nil {
("get user failed, err:%v\n", err)
return
}
("user:%#v\n", user)
// Query multiple data users, err := getAllUsers(db, 0)
if err != nil {
("get all users failed, err:%v\n", err)
return
}
("users:%#v\n", users)
// Update data rowsAffected, err := updateUser(db, id, "Alex", 20)
if err != nil {
("update user failed, err:%v\n", err)
return
}
("update success, affected rows:", rowsAffected)
// Delete data rowsAffected, err = deleteUser(db, id)
if err != nil {
("delete user failed, err:%v\n", err)
return
}
("delete success, affected rows:", rowsAffected)
// Insert data using named parameters id, err = insertUserNamed(db, "Alex", 19)
if err != nil {
("insert user named failed, err:%v\n", err)
return
}
("insert named success, the id is:", id)
// Query data using named parameters users, err = getUsersNamed(db, "Alex")
if err != nil {
("get users named failed, err:%v\n", err)
return
}
("users named:%#v\n", users)
("exec SQL success")
}
We can see thatsqlxStill need to comparedatabase/sqlIt's much simpler.
Summarize
passsqlxPackage, we can interact with MySQL databases more simply in Go, reducing boilerplate code and improving code readability.
This is the end of this article about using sqlx to operate MySQL in Go. For more related content on Go sqlx to operate MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!