golang 1.9 sp MySQL_golang mysql-1（初识）

golang 连接使用mysql

创建数据库和表

MariaDB [(none)]> create database golang;

Query OK, 1 row affected (0.00 sec)

MariaDB [golang]> GRANT ALL PRIVILEGES ON golang.* TO 'root'@'localhost' IDENTIFIED BY 'root';

Query OK, 0 rows affected (0.00 sec)

MariaDB [golang]> GRANT ALL PRIVILEGES ON golang.* TO 'root'@'%' IDENTIFIED BY 'root';

Query OK, 0 rows affected (0.00 sec)

MariaDB [golang]> CREATE TABLE person (

user_id int primary key auto_increment,

username varchar(260),

sex varchar(260),

email varchar(260)

);

Query OK, 0 rows affected (0.00 sec)

MariaDB [golang]> CREATE TABLE place (

country varchar(200),

city varchar(200),

telcode int

)

Query OK, 0 rows affected (0.00 sec)

下载golang mysql驱动和包

$ go get github.com/go-sql-driver/mysql

$ go get github.com/jmoiron/sqlx

insert 一条记录

讲解：

1. 首先init方法初始化数据库链接

2. Exec执行一条数据库语句，注意(?,?,?)这个后面要跟上相应的值

3. 执行成功返回插入的记录的id

package main

import (

"fmt"

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

"github.com/jmoiron/sqlx"

)

var Db *sqlx.DB

func init() {

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

if err != nil {

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

return

}

Db = database

}

func insert() {

cmd := "insert into person(username, sex, email)values(?,?,?)"

r, err := Db.Exec(cmd, "stu001", "man", "stu001@email.com")

if err != nil {

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

return

}

id, err := r.LastInsertId()

if err != nil {

fmt.Println("get insert id failed, err:", err)

return

}

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

}

func main() {

insert()

}

编译并运行

$ go build go_dev/day10/exercises/mysql-operation

$ ./mysql-operation

insert success,id: 1

$ ./mysql-operation

insert success,id: 2

数据库查看

MariaDB [golang]> select * from person;

+---------+----------+------+------------------+

| user_id | username | sex | email |

+---------+----------+------+------------------+

| 1 | stu001 | man | stu001@email.com |

| 2 | stu001 | man | stu001@email.com |

+---------+----------+------+------------------+

2 rows in set (0.00 sec)

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

}

var Db *sqlx.DB

func init() {

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

if err != nil {

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

return

}

Db = database

}

func mySelect() {

var person []Person

cmd := "select user_id, username, sex, email from person where user_id=?"

// 传入的一定是一个切片进去或数组的指针

err := Db.Select(&person, cmd,1)

if err != nil {

fmt.Println("select failed, err:", err)

return

}

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

}

func main() {

// insert()

mySelect()

}

编译并运行

$ ./mysql-operation

select success: [{1 stu001 man stu001@email.com}]

已经转换为结构体了

update 一条记录

package main

import (

"fmt"

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

"github.com/jmoiron/sqlx"

)

var Db *sqlx.DB

func init() {

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

if err != nil {

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

return

}

Db = database

}

func update() {

id, err := Db.Exec("update person set username=?, email=? where user_id=?", "stu002","stu002@email.com",2)

if err != nil {

fmt.Println("update failed, err:", err)

return

}

fmt.Println("update success:", id)

}

func main() {

// insert()

// mySelect()

update()

}

编译并运行

$ go build go_dev/day10/exercises/mysql-operation

$ ./mysql-operation

update success: {0xc4200a4070 0xc4200a2030}

数据库表查询, 修改完成

MariaDB [golang]> select * from person;

+---------+----------+------+------------------+

| user_id | username | sex | email |

+---------+----------+------+------------------+

| 1 | stu001 | man | stu001@email.com |

| 2 | stu002 | man | stu002@email.com |

+---------+----------+------+------------------+

2 rows in set (0.00 sec)

delete 一条记录

package main

import (

"fmt"

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

"github.com/jmoiron/sqlx"

)

var Db *sqlx.DB

func init() {

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

if err != nil {

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

return

}

Db = database

}

func del() {

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

if err != nil {

fmt.Println("delete failed, err:", err)

return

}

fmt.Println("delete success:", id)

}

func main() {

// insert()

// mySelect()

// update()

del()

}

编译并运行

$ go build go_dev/day10/exercises/mysql-operation

$ ./mysql-operation

delete success: {0xc42001a1c0 0xc420010b60}

查看数据库记录，stu002已经被删除了

MariaDB [golang]> select * from person;

+---------+----------+------+------------------+

| user_id | username | sex | email |

+---------+----------+------+------------------+

| 1 | stu001 | man | stu001@email.com |

+---------+----------+------+------------------+

1 row in set (0.00 sec)

作者： zhaogaolongsina

链接： https://blog.csdn.net/zhaogaolongsina/article/details/77126968

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

到此这篇关于“golang mysql-1(初识)”的文章就介绍到这了,更多文章或继续浏览下面的相关文章，希望大家以后多多支持Go语言编程网！

相关文章：