go mysql 增删改查

/* CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) UNIQUE NOT NULL, PASSWORD VARCHAR(100) NOT NULL, email VARCHAR(100) ); */ package main import ( "database/sql" "fmt" _ "github.com/mysql" ) type User struct { ID int Username string Password string Email string } func (user *User) AddUser() error { Db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") if err != nil { panic(err) } defer Db.Close() // 写 sql 语句 sqlStr := "insert into users(username , password , email) values(?,?,?)" //预编译 stmt, err := Db.Prepare(sqlStr) if err != nil { fmt.Println("预编译出现异常：", err) return err } //执行 _, erro := stmt.Exec(user.Username, user.Password, user.Email) if erro != nil { fmt.Println("执行出现异常：", erro) return erro } return nil } func (user *User) AddUser2() error { Db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") if err != nil { panic(err) } defer Db.Close() // 写 sql 语句 sqlStr := "insert into users(username , password , email) values(?,?,?)" //执行 _, erro := Db.Exec(sqlStr, user.Username, user.Password, user.Email) if erro != nil { fmt.Println("执行出现异常：", erro) return erro } return nil } func (user *User) DelUser() error { Db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") if err != nil { panic(err) } defer Db.Close() // 写 sql 语句 sqlStr := "delete from users where username = ?" //执行 _, erro := Db.Exec(sqlStr, user.Username) if erro != nil { fmt.Println("执行出现异常：", erro) return erro } return nil } func (user *User) ModUser(name string) error { Db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") if err != nil { panic(err) } defer Db.Close() // 写 sql 语句 sqlStr := "update users set username = ? where username = ?" //执行 _, erro := Db.Exec(sqlStr, name, user.Username) if erro != nil { fmt.Println("执行出现异常：", erro) return erro } return nil } //GetUserByID 根据用户的id从数据库中查询一条记录 func (user *User) GetUserByID() (*User, error) { Db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") if err != nil { panic(err) } defer Db.Close() //写sql语句 sqlStr := "select id,username,password,email from users where id = ?" //执行 row := Db.QueryRow(sqlStr, user.ID) //声明 var id int var username string var password string var email string err = row.Scan(&id, &username, &password, &email) if err != nil { return nil, err } u := &User{ ID: id, Username: username, Password: password, Email: email, } return u, nil } //GetUsers 获取数据库中所有的记录 func (user *User) GetUsers() ([]*User, error) { Db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") if err != nil { panic(err) } defer Db.Close() //写sql语句 sqlStr := "select id,username,password,email from users" //执行 rows, err := Db.Query(sqlStr) if err != nil { return nil, err } //创建User切片 var users []*User for rows.Next() { //声明 var id int var username string var password string var email string err := rows.Scan(&id, &username, &password, &email) if err != nil { return nil, err } u := &User{ ID: id, Username: username, Password: password, Email: email, } users = append(users, u) } return users, nil } func main() { // db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8") //if err != nil { // panic(err) //} user := User{ ID : 1, Username : "qxwe", Password : "123", Email : "rjpewr@163.com" , } //user.AddUser2() //user.DelUser() //user.ModUser("king") var userp *User userp ,err := user.GetUserByID() if err != nil { return } fmt.Println(userp) var mu []*User mu ,err = user.GetUsers() if err != nil { return } for _, v :=range mu { fmt.Println(v) } }