本文介绍如何使用Go database/sql包操作MySQL数据库执行的增、删、改、查。
MYSQL 基础
create database golangdev default charset utf8mb4;
use golangdev;
create table <name> (
colname coltype 修饰,
...
index <index-name> (col1, col2, ...)
) engine=innode default charset=utf8mb4;
colname：列名 小写英文字母、数字、_组成
coltype：
- 数值类型：int/bigint/float/double/decimal(m, n)
- 字符串：char(n)/varchar(n)
- 时间类型：date/datetime/time
- 文本类型：text/longtext/mediumtext
- 二进制类型：blob/longblob
注释
- 主键：primary key
- 唯一：unique
- 自动增长：auto_increment
- 默认值：default 0, default ''
- 是否为空：null/not null
- 注释：comment ""
索引 index
内置函数
- now()
- md5()
- date_format(time, layout)
- max() min() avg()
创建数据库示例
create table task (
id int primary key auto_increment,
name varchar(64) not null default "" comment "task name",
status int not null default 0 comment "0: new; 1: doing; 2: done",
start_time datetime,
completed_time datetime,
deadline_time datetime not null,
content text,
is_delete int default 0,
delete_time datetime default null,
index idex_name (name)
) engine=innodb default charset utf8mb4;
Go 操作 MySQL 数据库
Go 操作 MySQL 数据库主要涉及两个包：
database/sqlgithub.com/go-sql-driver/mysql
拼接 sql 字符串可以使用 ? 号占位，只能占位变量，不能是语句，示例：
result, err = db.Exec(`delete from test where name = ?;`, "xiexianbin")
解决的问题：
- 拼接麻烦
- SQL 注入
简单示例
package main
import (
"database/sql"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// 格式有对应的驱动定义
// user:password@tcp(host:port)/database?charset=utf8mb4&loc=PRC&parseTime=true
dataSourceName := "root:root@tcp(100.80.0.128:3306)/golangdev?charset=utf8mb4&loc=PRC&parseTime=true"
db, err := sql.Open("mysql", dataSourceName)
if err != nil {
log.Fatal(err)
return
}
log.Println(db)
// 测试连接状态
log.Println(db.Ping())
// 执行
// 创建数据库
_, err = db.Exec(`create table if not exists test(
id bigint primary key auto_increment,
name varchar(32) not null comment "test name"
) engine=innodb default charset utf8mb4;`)
if err != nil {
log.Println(err)
}
// 插入
result, err := db.Exec(`insert into test(name) value ("xiexianbin");`)
if err != nil {
log.Println(err)
}
log.Println(result.LastInsertId())
log.Println(result.RowsAffected())
// 读
result, err = db.Exec(`show tables;`)
if err != nil {
log.Println(err)
}
log.Println(result.LastInsertId())
log.Println(result.RowsAffected())
// 更新
result, err = db.Exec(`update test set name = ? where name = ?;`, "xianbin", "xiexianbin")
log.Println(err)
log.Println(result.LastInsertId())
log.Println(result.RowsAffected())
// 删除
result, err = db.Exec(`delete from test where name = ?;`, "xiexianbin")
if err != nil {
log.Println(err)
} else {
log.Println(result.LastInsertId())
log.Println(result.RowsAffected())
}
// 查询
var (
id int
name string
)
var line struct {
Id int
Name string
}
sql := `select id, name from test where id > ? order by id desc;`
rows, err := db.Query(sql, 1)
if err != nil {
log.Println(err)
} else {
for rows.Next() {
err = rows.Scan(&line.Id, &line.Name)
log.Println("line", line.Id, line.Name)
}
}
row := db.QueryRow(sql, 4)
err = row.Scan(&id, &name)
if err != nil {
log.Println(err)
}
log.Println(id, name)
}
事务示例
事务保证操作的原子问题
package main
import (
"database/sql"
"errors"
"log"
_ "github.com/go-sql-driver/mysql"
)
//func transfer(db *sql.DB, id int, money float64) error {
func transfer(tx *sql.Tx, id int, money float64) error {
if money < 0 {
var currentMony float64
err := tx.QueryRow(`select money from test where id = ?;`, id).Scan(¤tMony)
if err != nil {
return err
}
if currentMony < -money {
return errors.New("not enough money")
}
}
_, err := tx.Exec(`update test set money = money + ? where id = ?`, money, id)
if err != nil {
return err
}
return nil
}
func main() {
// 格式有对应的驱动定义
// user:password@tcp(host:port)/database?charset=utf8mb4&loc=PRC&parseTime=true
dataSourceName := "root:root@tcp(100.80.0.128:3306)/golangdev?charset=utf8mb4&loc=PRC&parseTime=true"
db, err := sql.Open("mysql", dataSourceName)
if err != nil {
log.Fatal(err)
return
}
// 测试连接状态
log.Println("ping", db.Ping())
// 执行
// 创建数据库
_, err = db.Exec(`create table if not exists test(
id bigint primary key auto_increment,
name varchar(32) not null comment "user name",
money decimal(21,5) not null default 0
) engine=innodb default charset utf8mb4;`)
if err != nil {
log.Println("create table err", err)
}
// 初始化用户
_, _ = db.Exec(`insert into test(name, money) value ("xie", 1000);`)
_, _ = db.Exec(`insert into test(name, money) value ("xian", 500);`)
// 转账，需要保证同时成功或失败
//var money float64 = 200
var money float64 = 1000
tx, err := db.Begin()
err1 := transfer(tx, 1, money)
if err1 != nil {
log.Println("tack in money err", err1.Error())
}
err2 := transfer(tx, 2, -money)
if err2 != nil {
log.Println("take out money err", err2.Error())
}
if err1 == nil && err2 == nil {
// 提交事务
tx.Commit()
} else {
// 回滚数据
tx.Rollback()
}
}
Statement
package main
import (
"database/sql"
"fmt"
"log"
"math/rand"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// 格式有对应的驱动定义
// user:password@tcp(host:port)/database?charset=utf8mb4&loc=PRC&parseTime=true
dataSourceName := "root:root@tcp(100.80.0.128:3306)/golangdev?charset=utf8mb4&loc=PRC&parseTime=true"
db, err := sql.Open("mysql", dataSourceName)
if err != nil {
log.Fatal(err)
return
}
// 测试连接状态
log.Println("ping", db.Ping())
// 执行
// 创建数据库
_, err = db.Exec(`create table if not exists test(
id bigint primary key auto_increment,
name varchar(32) not null comment "user name",
money decimal(21,5) not null default 0
) engine=innodb default charset utf8mb4;`)
if err != nil {
log.Println("create table err", err)
}
// 初始化用户
n := time.Now()
sql := `insert into test(name, money) value (?, ?);`
//for i := 0; i < 10000; i++ {
// _, _ = db.Exec(sql, fmt.Sprintf("xie_%d", i), rand.Float64())
//}
statement, err := db.Prepare(sql)
for i := 0; i < 10000; i++ {
_, _ = statement.Exec(fmt.Sprintf("xie_%d", i), rand.Float64())
}
fmt.Println("time:", time.Now().Sub(n))
}