1 简介

使用第三方开源的mysql库:

github.com/go-sql-driver/mysql （mysql驱动） github.com/jmoiron/sqlx （基于mysql驱动的封装）

命令行输入 ：

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

连接mysql:

database, err := sqlx.Open(“mysql”, “root:XXXX@tcp(127.0.0.1:3306)/test”) //database, err := sqlx.Open(“数据库类型”, “用户名:密码@tcp(地址:端口)/数据库名”)

2 连接mysql

var Db *sqlx.DB
// 1 连接
func init() {
    db, err := sqlx.Open("mysql", "root:123456@tcp(47.108.170.195:3306)/test")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

3 执行SQL语句

新建test数据库

create database test;

// 2 执行DDL语句
func ExecSQL() {
    var sql = `
CREATE TABLE person (
    first_name text,
    last_name text,
    email text
);`
    Db.Exec(sql)
    sql = `
CREATE TABLE place (
    country text,
    city text NULL,
    telcode integer
);`
    Db.Exec(sql)
}

4 增删改查

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}
type Place struct {
    Country string
    City    sql.NullString
    TelCode int
}

(1) Insert操作

// 3 插入
func Insert() {
    tx := Db.MustBegin()
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES (?, ?, ?)", "Jason", "Moiron", "jmoiron@jmoiron.net")
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES (?, ?, ?)", "John", "Doe", "johndoeDNE@gmail.net")
    tx.MustExec("INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)", "United States", "New York", nil)
    tx.MustExec("INSERT INTO place (country, city, telcode) VALUES (?, ?)", "China", "Hong Kong", "852")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES (?, ?)", "Singapore", "65")
    // Named queries can use structs
    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})
    tx.Commit()
}

(2) Select操作

// 4 查询
func Select() {
    // (1) 查询数据库, 存储结果集在 []Person
    people := []Person{}
    Db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
    jason, john := people[0], people[1]
    fmt.Printf("%#v\n%#v", jason, john)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}
    // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}
    // (2) 获取单个结果
    jason = Person{}
    Db.Get(&jason, "SELECT * FROM person WHERE first_name=?", "Jason")
    fmt.Printf("%#v\n", jason)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}
    // (3) 使用一个结构体来循环查询
    place := Place{}
    rows, _ := Db.Queryx("SELECT * FROM place")
    for rows.Next() {
        err := rows.StructScan(&place)
        if err != nil {
            log.Fatalln(err)
        }
        fmt.Printf("%#v\n", place)
    }
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}
    // (4) 批量插入
    personMaps := []map[string]interface{}{
        {"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"},
        {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"},
        {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"},
    }
    _, _ = Db.NamedExec(`INSERT INTO person (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)`, personMaps)
}

(3) Update操作

// 5 修改
func Update() {
    res, _ := Db.Exec("UPDATE person set first_name=? where email=?", "ws", "sbw@ab.co.nz")
    row, _ := res.RowsAffected()
    fmt.Println("update succ:", row)
}

(4) Delete操作

// 6 删除
func Delete() {
    res, _ := Db.Exec("DELETE FROM person where first_name=?", "ws")
    row, _ := res.RowsAffected()
    fmt.Println("delete succ: ", row)
}