mysql go命令行_第五单元 go操作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

二、实现增删改查功能

1.数据库连接

用法:database, err := sqlx.Open(数据库驱动, 用户名:密码@tcp(db地址:端口)/数据库名)

database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")

示例代码

// test

package main

import (

"fmt"

_ "github.com/go-sql-driver/mysql"

"github.com/jmoiron/sqlx"

)

func main() {

_, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/1803a")

if err != nil {

fmt.Println("数据库连接失败", err.Error())

} else {

fmt.Println("数据库连接成功")

}

}

2.新建表结构

CREATE TABLE `person` (

`user_id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(260) DEFAULT NULL,

`sex` varchar(260) DEFAULT NULL,

`email` varchar(260) DEFAULT NULL,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

3.新增功能实现

type Person 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:root@tcp(127.0.0.1:3306)/test")

if err != nil {

fmt.Println("open mysql failed,", err)

return

}

Db = database

defer db.Close() // 注意这行代码要写在上面err判断的下面

}

func main() {

r, err := Db.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")

if err != nil {

fmt.Println("exec failed, ", err)

return

}

id, err := r.LastInsertId()

if err != nil {

fmt.Println("exec failed, ", err)

return

}

fmt.Println("insert succ:", id)

}

4.查询实现

代码示例

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"`

}

var Db *sqlx.DB

func init() {

database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/1803a")

if err != nil {

fmt.Println("open mysql failed,", err)

return

}

Db = database

//defer Db.Close() // 注意这行代码要写在上面err判断的下面

}

func main() {

var person []Person

err := Db.Select(&person, "select user_id, username, sex, email from person where user_id=?", 1)

if err != nil {

fmt.Println("exec failed, ", err)

return

}

fmt.Println("select succ:", person)

}

5.更新操作

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"`

}

var Db *sqlx.DB

func init() {

database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/1803a")

if err != nil {

fmt.Println("open mysql failed,", err)

return

}

Db = database

//defer Db.Close() // 注意这行代码要写在上面err判断的下面

}

func main() {

res, err := Db.Exec("update person set username=? where user_id=?", "stu0003", 1)

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)

}

6.删除操作

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"`

}

var Db *sqlx.DB

func init() {

database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/1803a")

if err != nil {

fmt.Println("open mysql failed,", err)

return

}

Db = database

//defer Db.Close() // 注意这行代码要写在上面err判断的下面

}

func main() {

res, err := Db.Exec("delete from person where user_id=?", 1)

if err != nil {

fmt.Println("exec failed, ", err)

return

}

row, err := res.RowsAffected()

if err != nil {

fmt.Println("rows failed, ", err)

}

fmt.Println("delete succ:", row)

}