数据库操作

LiAng2023年12月23日大约 4 分钟约 801 字

操作MySQL

安装

使用第三方开源的mysql库: http://github.com/go-sql-driver/mysql （mysql驱动） http://github.com/jmoiron/sqlx （基于mysql驱动的封装）

go get github.com/go-sql-driver/mysql 
go get github.com/jmoiron/sqlx

创建msql数据库test 创建数据库表user

CREATE TABLE `user` (
`user_id` int(10) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`sex` varchar(20) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

基础操作

连接mysql

package main

import (
    "fmt"

    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    //                                   用户名:密码@tcp(ip:port)/数据库名
    database, err := sqlx.Open("mysql", "root:liangzhiwei@tcp(127.0.0.1:3306)/test")
    if err != nil {
        fmt.Println("open mysql failed, ", err)
        return
    }
    fmt.Println(database)
}

&{0xc0000229c0 mysql false 0xc00007ec60}

插入数据

package main

import (
    "fmt"

    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

// 用户结构体
type Users struct {
    UserId   int    `db:"user_id"`
    Username string `db:"username"`
    Sex      string `db:"sex"`
    Email    string `db:"email"`
}

// 数据库指针
var db *sqlx.DB

func init() {
    database, err := sqlx.Open("mysql", "root:liangzhiwei@tcp(127.0.0.1:3306)/test")
    if err != nil {
        fmt.Println("open mysql failed, ", err)
        return
    }
    db = database
}

func main() {
    sql := "insert into user(username,sex,email) values(?,?,?)"
    value := [3]string{"user01", "man", "user01@163.com"}

    //执行sql语句
    r, err := db.Exec(sql, value[0], value[1], value[2])
    if err != nil {
        fmt.Println("exec failed,", err)
        return
    }

    //查询最后一个用户ID，判断是否插入成功
    id, err := r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed,", err)
        return
    }
    fmt.Println("insert succ", id)

}

insert succ 2

查询数据

func main() {
    var users []Users
    sql := "select user_id, username,sex,email from user where user_id=? "
    err := db.Select(&users, sql, 2)
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }
    fmt.Println("select succ:", users)
}

select succ: [{2 user01 man user01@163.com}]

更新数据

处理带有 /的特殊字符

query = strings.Replace(query, "'", "\\'", -1)

func main() {
    //执行SQL语句
    sql := "update user set username =? where user_id = ?"
    res, err := db.Exec(sql, "user002", 2)

    if err != nil {
        fmt.Println("exec failed,", err)
        return
    }

    //查询影响的行数，判断修改插入成功
    row, err := res.RowsAffected()
    if err != nil {
        fmt.Println("rows failed", err)
    }

    fmt.Println("update succ:", row)
}

update succ: 1

删除数据

func main() {
    sql := "delete from user where user_id=?"

    res, err := db.Exec(sql, 2)
    if err != nil {
        fmt.Println("exce failed,", err)
        return
    }

    row, err := res.RowsAffected()
    if err != nil {
        fmt.Println("row failed, ", err)
    }
    fmt.Println("delete succ: ", row)

}

delete succ:  1

事务操作

package main

import (
    "fmt"

    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

// 用户结构体
type Users struct {
    UserId   int    `db:"user_id"`
    Username string `db:"username"`
    Sex      string `db:"sex"`
    Email    string `db:"email"`
}

// 数据库指针
var db *sqlx.DB

func init() {
    database, err := sqlx.Open("mysql", "root:liangzhiwei@tcp(127.0.0.1:3306)/test")
    if err != nil {
        fmt.Println("open mysql failed, ", err)
        return
    }
    db = database
}

func main() {
    //开启事务
    conn, err := db.Begin()
    if err != nil {
        fmt.Println("begin failed :", err)
        return
    }

    //执行插入语句
    r, err := conn.Exec("insert into user(username, sex, email)values(?, ?, ?)", "user01", "man", "usre01@163.com")
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback() //出现异常，进行回滚操作
        return
    }

    id, err := r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback()
        return
    }
    fmt.Println("insert succ:", id)

    r, err = conn.Exec("insert into user(username, sex, email)values(?, ?, ?)", "user02", "man", "user02@163.com")
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback()
        return
    }
    id, err = r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback()
        return
    }
    fmt.Println("insert succ:", id)

    conn.Commit()
}

insert succ: 3
insert succ: 4

故意把第二个sql语句的字段改成错的，再执行

r, err = conn.Exec("insert into user(username2, sex, email)values(?, ?, ?)", "user02", "man", "user02@163.com")

insert succ: 5
exec failed,  Error 1054 (42S22): Unknown column 'username2' in 'field list'

数据库并没有新增数据，因为事务确实开启，出现一个错误，插入直接回滚，一条都没有插入成功。