go使用mysql实现增删改查操作

1、安装MySQL驱动

go get -u github.com/go-sql-driver/mysql

2、go连接MySQL

import ("database/sql""log"_ "github.com/go-sql-driver/mysql" // 导入 mysql 驱动
)type Users struct {ID    intName  stringEmail string
}var db *sql.DBfunc init() {// 连接MySQL数据库var err errordb, err = sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?parseTime=true")if err != nil {log.Fatal(err)}// 测试连接if err = db.Ping(); err != nil {log.Fatal(err)}// 创建用户表createTableSQL := `CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY,name varchar(64) NOT NULL,email varchar(64) NOT NULL UNIQUE);`// 执行 SQL 语句_, err = db.Exec(createTableSQL)if err != nil {log.Fatal(err)}
}

3、users表增删改查SQL

// CreateUser 创建新用户
func (u *Users) CreateUser() (int64, error) {stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")if err != nil {return 0, err}res, err := stmt.Exec(u.Name, u.Email)if err != nil {return 0, err}return res.LastInsertId()
}// GetUserByID 根据 ID 获取用户
func (u *Users) GetUserByID(id int) error {row := db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id)return row.Scan(&u.ID, &u.Name, &u.Email)
}// GetAllUsers 获取所有用户
func GetAllUsers() ([]*Users, error) {rows, err := db.Query("SELECT id, name, email FROM users")if err != nil {return nil, err}defer rows.Close()var users []*Usersfor rows.Next() {user := &Users{}if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {return nil, err}users = append(users, user)}if err := rows.Err(); err != nil {return nil, err}return users, nil
}// UpdateUser 更新用户信息
func (u *Users) UpdateUser() (int64, error) {stmt, err := db.Prepare("UPDATE users SET name = ?, email = ? WHERE id = ?")if err != nil {return 0, err}res, err := stmt.Exec(u.Name, u.Email, u.ID)if err != nil {return 0, err}return res.RowsAffected()
}// DeleteUser 删除用户
func (u *Users) DeleteUser() (int64, error) {stmt, err := db.Prepare("DELETE FROM users WHERE id = ?")if err != nil {return 0, err}res, err := stmt.Exec(u.ID)if err != nil {return 0, err}return res.RowsAffected()
}

4、操作增删改查操作

// 创建用户
user := &Users{Name: "buddha", Email: "[e-mail]"}
id, err := user.CreateUser()
if err != nil {log.Fatalf("Failed to create user: %v", err)
}
fmt.Printf("Created user with ID: %d\n", id)// 获取用户
user = &Users{}
if err := user.GetUserByID(int(id)); err != nil {log.Fatalf("Failed to get user: %v", err)
}
fmt.Printf("User: ID: %d, Name: %s, Email: %s\n", user.ID, user.Name, user.Email)// 更新用户
user.Name = "buddha2080"
user.Email = "[e-mail]"
affectedRows, err := user.UpdateUser()
if err != nil {log.Fatalf("Failed to update user: %v", err)
}
fmt.Printf("Updated %d rows\n", affectedRows)// 获取所有用户
users, err := GetAllUsers()
if err != nil {log.Fatalf("Failed to get all users: %v", err)
}
for _, u := range users {fmt.Printf("User: id: %d, name: %s, email: %s\n", u.ID, u.Name, u.Email)
}// 删除用户
affectedRows, err = user.DeleteUser()
if err != nil {log.Fatalf("Failed to delete user: %v", err)
}
fmt.Printf("Deleted %d rows\n", affectedRows)

整体测试代码如下：

// main.go
package mainimport ("database/sql""fmt""log"_ "github.com/go-sql-driver/mysql" // 导入 mysql 驱动
)type Users struct {ID    intName  stringEmail string
}var db *sql.DBfunc init() {// 打开或创建一个 SQLite 数据库文件var err errordb, err = sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?parseTime=true")if err != nil {log.Fatal(err)}// 测试连接if err = db.Ping(); err != nil {log.Fatal(err)}// 创建用户表createTableSQL := `CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY,name varchar(64) NOT NULL,email varchar(64) NOT NULL UNIQUE);`// 执行 SQL 语句_, err = db.Exec(createTableSQL)if err != nil {log.Fatal(err)}
}// CreateUser 创建新用户
func (u *Users) CreateUser() (int64, error) {stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")if err != nil {return 0, err}res, err := stmt.Exec(u.Name, u.Email)if err != nil {return 0, err}return res.LastInsertId()
}// GetUserByID 根据 ID 获取用户
func (u *Users) GetUserByID(id int) error {row := db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id)return row.Scan(&u.ID, &u.Name, &u.Email)
}// GetAllUsers 获取所有用户
func GetAllUsers() ([]*Users, error) {rows, err := db.Query("SELECT id, name, email FROM users")if err != nil {return nil, err}defer rows.Close()var users []*Usersfor rows.Next() {user := &Users{}if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {return nil, err}users = append(users, user)}if err := rows.Err(); err != nil {return nil, err}return users, nil
}// UpdateUser 更新用户信息
func (u *Users) UpdateUser() (int64, error) {stmt, err := db.Prepare("UPDATE users SET name = ?, email = ? WHERE id = ?")if err != nil {return 0, err}res, err := stmt.Exec(u.Name, u.Email, u.ID)if err != nil {return 0, err}return res.RowsAffected()
}// DeleteUser 删除用户
func (u *Users) DeleteUser() (int64, error) {stmt, err := db.Prepare("DELETE FROM users WHERE id = ?")if err != nil {return 0, err}res, err := stmt.Exec(u.ID)if err != nil {return 0, err}return res.RowsAffected()
}func main() {fmt.Println("main函数开始...")// 创建用户user := &Users{Name: "buddha", Email: "[e-mail]"}id, err := user.CreateUser()if err != nil {log.Fatalf("Failed to create user: %v", err)}fmt.Printf("Created user with ID: %d\n", id)// 获取用户user = &Users{}if err := user.GetUserByID(int(id)); err != nil {log.Fatalf("Failed to get user: %v", err)}fmt.Printf("User: ID: %d, Name: %s, Email: %s\n", user.ID, user.Name, user.Email)// 更新用户user.Name = "buddha2080"user.Email = "[e-mail]"affectedRows, err := user.UpdateUser()if err != nil {log.Fatalf("Failed to update user: %v", err)}fmt.Printf("Updated %d rows\n", affectedRows)// 获取所有用户users, err := GetAllUsers()if err != nil {log.Fatalf("Failed to get all users: %v", err)}for _, u := range users {fmt.Printf("User: id: %d, name: %s, email: %s\n", u.ID, u.Name, u.Email)}// 删除用户affectedRows, err = user.DeleteUser()if err != nil {log.Fatalf("Failed to delete user: %v", err)}fmt.Printf("Deleted %d rows\n", affectedRows)fmt.Println("main函数结束...")
}