16 go操作Mysql

mysql模块下载

mysql模块我们从github上下载，地址为：www.github.com/go-sql-driver/mysql

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

struct字段名一样

我们先创建一个表来做测试，sql语句如下

mysql> use test
Database changed
mysql> create table person (user_id int primary key auto_increment,     username varchar(260),     sex varchar(260),     email varchar(260)) charset utf8;
mysql>  CREATE TABLE place (     country varchar(200),     city varchar(200),     telcode int) charset utf8;

我们如果要操作数据库数据，首先要定义一个结构体，结构体的变量名字必须和表字段一样。

type Person struct{
    //  所有字段必须和数据库的保持一致，如果第一列使用了和数据库字段不一样的名字，那么就需要在
    //  ·· 之间写明真正的字段名
    UserId int `db:"user_id`
    Username string  `db:"username"`
    Sex string  `db:"sex"`
    Email string `db:"email"`
}
type Place struct{
    Country string `db:"country"`
    City string `db:"city"`
    TelCode  int  `db:"telcode"`
}

insert sql command

我们创建好数据库以后，那么就开始插入数据库。

package main 
import (
    "fmt"

    _ "github.com/go-sql-driver/mysql"   // 导入数据库模块
    "github.com/jmoiron/sqlx"   // 导入数据库模块
)
var Db *sqlx.DB   

func init() {
    // 把连接数据库的方法放在这里的话，那么函数一执行的话就会连接数据库了。
    // 连接格式为(数据库类型，用户名:密码@tcp(ip:port)/库名
    database, err := sqlx.Open("mysql", "root:123..aa@tcp(192.168.56.14:3306)/test")
    if err != nil {
        fmt.Println("open mysql faild,", err)
        return
    }
    Db = database
}

func main() {
    r, err := Db.Exec("insert into person values(?,?,?,?)", 2, "Leo", "man", "test@qq.com")   //执行插入动作的sql语句
    if err != nil {
        fmt.Println("exec sql command failed", err)
        return
    }
    id, err := r.LastInsertId()   // 插入成功后会返回这条记录的自增ID
    if err != nil {
        fmt.Println("get the insert ID failed", err)
        return
    }
    fmt.Println("insert successful, the id :", id)
}

update sql command

更新数据库的sql语句

// mysql_update
package main

import (
    "fmt"

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

var Db *sqlx.DB

func init() {
    conn, err := sqlx.Open("mysql", "root:123..aa@tcp(192.168.56.14:3306)/test")
    if err != nil {
        fmt.Println("connect mysql failed,", err)
        return
    }
    Db = conn
}

func main() {
    _, err := Db.Exec("update  person set username=? where user_id=?", "ljf", 1)
    if err != nil {
        fmt.Println("execute sql command happend a error,", err)
        return
    }
    fmt.Println("the result:", _)
}

select sql command

mysql查询语句。

// mysql_select
package main

import (
    "fmt"

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

type Person struct {
    UserId   int    `db:"user_id"`
    Username string `db:"username"`
    Sex      string `db:"sex"`
    Email    string `db:"email"`
}

type Place struct {
    Country string `db:"country"`
    City    string `db:"city"`
    TelCode int    `db:"telcode"`
}

var Db *sqlx.DB

func init() {
    database, err := sqlx.Open("mysql", "root:123..aa@tcp(192.168.56.14:3306)/test")
    if err != nil {
        fmt.Println("connect mysql failed", err)
        return
    }
    Db = database
}

func main() {
    var person []Person
    err := Db.Select(&person, "select * from person;")
    if err != nil {
        fmt.Println("exec failded", err)
        return
    }
    fmt.Println("select succ:", person)
}

delete sql command

删除的sql语句

// mysql_delete
package main

import (
    "fmt"

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

var Db *sqlx.DB

func init() {
    conn, err := sqlx.Open("mysql", "root:123..aa@tcp(192.168.56.14:3306)/test")
    if err != nil {
        fmt.Println("connect mysql failed,e:", err)
        return
    }
    Db = conn
}

func main() {
    result, err := Db.Exec("delete from person  where user_id=?", 2)
    if err != nil {
        fmt.Println("execute sql command error", err)
        return
    }

    fmt.Println("result", result)

}

转载于:https://www.cnblogs.com/liaojiafa/p/7414283.html