Go使用sqlx操作MySQL完整指南

# Go使用sqlx操作MySQL完整指南## 1. 安装依赖```bash
go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx

2. 数据库基础操作

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"
)// 定义全局db对象
var db *sqlx.DB// 用户结构体
type User struct {ID       int    `db:"id"`Username string `db:"username"`Password string `db:"password"`Age      int    `db:"age"`
}// 初始化数据库连接
func initDB() (err error) {// 连接数据库dsn := "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True"db, err = sqlx.Connect("mysql", dsn)if err != nil {return err}// 设置连接池参数db.SetMaxOpenConns(100)db.SetMaxIdleConns(10)return nil
}// 创建数据库
func createDatabase() error {_, err := db.Exec("CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4")return err
}// 删除数据库
func dropDatabase() error {_, err := db.Exec("DROP DATABASE IF EXISTS test")return err
}// 创建表
func createTable() error {sql := `CREATE TABLE IF NOT EXISTS users(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(50) NOT NULL,age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`_, err := db.Exec(sql)return err
}// 删除表
func dropTable() error {_, err := db.Exec("DROP TABLE IF EXISTS users")return err
}// 插入单条数据
func insertUser(user User) error {sql := "INSERT INTO users(username, password, age) VALUES (?, ?, ?)"_, err := db.Exec(sql, user.Username, user.Password, user.Age)return err
}// 批量插入数据
func batchInsertUsers(users []User) error {sql := "INSERT INTO users(username, password, age) VALUES (:username, :password, :age)"_, err := db.NamedExec(sql, users)return err
}// 查询单个用户
func getUserByID(id int) (User, error) {var user Usersql := "SELECT * FROM users WHERE id=?"err := db.Get(&user, sql, id)return user, err
}// 查询多个用户
func getUsers(age int) ([]User, error) {var users []Usersql := "SELECT * FROM users WHERE age > ?"err := db.Select(&users, sql, age)return users, err
}// 更新用户
func updateUser(user User) error {sql := "UPDATE users SET password=?, age=? WHERE username=?"_, err := db.Exec(sql, user.Password, user.Age, user.Username)return err
}// 删除用户
func deleteUser(id int) error {sql := "DELETE FROM users WHERE id=?"_, err := db.Exec(sql, id)return err
}func main() {// 初始化数据库连接if err := initDB(); err != nil {fmt.Printf("init db failed, err:%v\n", err)return}defer db.Close()// 创建数据库if err := createDatabase(); err != nil {fmt.Printf("create database failed, err:%v\n", err)return}// 创建表if err := createTable(); err != nil {fmt.Printf("create table failed, err:%v\n", err)return}// 插入单个用户user1 := User{Username: "张三",Password: "123456",Age:      20,}if err := insertUser(user1); err != nil {fmt.Printf("insert user failed, err:%v\n", err)return}// 批量插入用户users := []User{{Username: "李四", Password: "123456", Age: 21},{Username: "王五", Password: "123456", Age: 22},{Username: "赵六", Password: "123456", Age: 23},}if err := batchInsertUsers(users); err != nil {fmt.Printf("batch insert users failed, err:%v\n", err)return}// 查询单个用户user, err := getUserByID(1)if err != nil {fmt.Printf("get user failed, err:%v\n", err)return}fmt.Printf("user:%#v\n", user)// 查询多个用户userList, err := getUsers(20)if err != nil {fmt.Printf("get users failed, err:%v\n", err)return}fmt.Printf("users:%#v\n", userList)// 更新用户user1.Password = "654321"if err := updateUser(user1); err != nil {fmt.Printf("update user failed, err:%v\n", err)return}// 删除用户if err := deleteUser(1); err != nil {fmt.Printf("delete user failed, err:%v\n", err)return}// 删除表if err := dropTable(); err != nil {fmt.Printf("drop table failed, err:%v\n", err)return}// 删除数据库if err := dropDatabase(); err != nil {fmt.Printf("drop database failed, err:%v\n", err)return}
}

3. 事务操作示例

// 使用事务进行转账
func transfer(fromUsername, toUsername string, amount int) error {tx, err := db.Beginx() // 开启事务if err != nil {return err}// 在事务中执行多个SQL操作sql1 := "UPDATE users SET balance = balance - ? WHERE username = ?"sql2 := "UPDATE users SET balance = balance + ? WHERE username = ?"// 执行第一个SQLresult1, err := tx.Exec(sql1, amount, fromUsername)if err != nil {tx.Rollback() // 回滚事务return err}// 检查影响行数rows1, err := result1.RowsAffected()if err != nil {tx.Rollback()return err}if rows1 != 1 {tx.Rollback()return fmt.Errorf("转出账户不存在")}// 执行第二个SQLresult2, err := tx.Exec(sql2, amount, toUsername)if err != nil {tx.Rollback()return err}// 检查影响行数rows2, err := result2.RowsAffected()if err != nil {tx.Rollback()return err}if rows2 != 1 {tx.Rollback()return fmt.Errorf("转入账户不存在")}// 提交事务return tx.Commit()
}

4. 常用查询技巧

4.1 IN查询

func getUsersByIDs(ids []int) ([]User, error) {query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)if err != nil {return nil, err}query = db.Rebind(query)var users []Usererr = db.Select(&users, query, args...)return users, err
}

4.2 分页查询

func getUsersByPage(page, pageSize int) ([]User, error) {offset := (page - 1) * pageSizesql := "SELECT * FROM users LIMIT ? OFFSET ?"var users []Usererr := db.Select(&users, sql, pageSize, offset)return users, err
}

4.3 模糊查询

func searchUsers(keyword string) ([]User, error) {sql := "SELECT * FROM users WHERE username LIKE ?"var users []Usererr := db.Select(&users, sql, "%"+keyword+"%")return users, err
}

5. 注意事项

1. 始终记得关闭数据库连接
2. 使用事务时要确保正确处理回滚和提交
3. 使用预处理语句防止SQL注入
4. 合理设置连接池参数
5. 处理所有可能的错误
6. 使用合适的字段类型和索引优化查询性能

6. 最佳实践

1. 使用结构体标签映射数据库字段
2. 统一错误处理
3. 使用连接池
4. 合理组织代码结构
5. 编写单元测试
6. 记录必要的日志
7. 定期备份数据库