mysql10.1_10.1Go Mysql

第十章 Go Mysql

准备好mysql数据库服务端数据。

创建test数据库

MariaDB [(none)]> create database test;

Query OK, 1 row affected (0.04 sec)

MariaDB [(none)]> use test;

Database changed

准备好数据表 person

MariaDB [test]> 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;

Query OK, 0 rows affected (0.09 sec)

准备好place表

CREATE TABLE place (

country varchar(200),

city varchar(200),

telcode int

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

查看表结构

MariaDB [test]> desc person;

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

| Field | Type | Null | Key | Default | Extra |

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

| user_id | int(11) | NO | PRI | NULL | auto_increment |

| username | varchar(260) | YES | | NULL | |

| sex | varchar(260) | YES | | NULL | |

| email | varchar(260) | YES | | NULL | |

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

4 rows in set (0.05 sec)

MariaDB [test]> desc place;

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

| Field | Type | Null | Key | Default | Extra |

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

| country | varchar(200) | YES | | NULL | |

| city | varchar(200) | YES | | NULL | |

| telcode | int(11) | YES | | NULL | |

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

3 rows in set (0.00 sec)

1.1. mysql驱动

命令行输入 :

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

go get github.com/jmoiron/sqlx

1.2. go-mysql增删改查

插入数据

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:redhat@tcp(123.206.16.61:3306)/test")

if err != nil {

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

return

}

Db = database

}

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)

}

查询数据

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:redhat@tcp(123.206.16.61:3306)/test")

if err != nil {

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

return

}

Db = database

}

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)

}

更新数据

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:redhat@tcp(123.206.16.61:3306)/test")

if err != nil {

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

return

}

Db = database

}

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)

}

删除数据

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:redhat@tcp(123.206.16.61:3306)/test")

if err != nil {

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

return

}

Db = database

}

func main() {

/*

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

if err != nil {

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

return

}

*/

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)

}

1.3. mysql事务

mysql事务特性:

1) 原子性

2) 一致性

3) 隔离性

4) 持久性

golang MySQL事务应用:

1) import (“github.com/jmoiron/sqlx")

2) Db.Begin() 开始事务

3) Db.Submit() 提交事务

4) Db.Rollback() 回滚事务

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值