gomysql

go语言实现的mysql帮助库

• 使用说明
  • 实例化db
  • Create-Table
  • Model
  • Select
  • Insert
  • Update
  • Delete
  • Transaction
  • Read-Write-Splitting
  • Sql-Log

实例化db

package main

import (
	"log"
	
	"github.com/grpc-boot/gomysql"
)

func main() {
	db, err := gomysql.NewDb(gomysql.Options{
		Host:     "127.0.0.1",
		Port:     3306,
		DbName:   "users",
		UserName: "root",
		Password: "12345678",
	})

	if err != nil {
		log.Fatalf("init db failed with error: %v\n", err)
	}
}

Create-Table

package main

import (
	"testing"

    "github.com/grpc-boot/gomysql"
	"github.com/grpc-boot/gomysql/condition"
	"github.com/grpc-boot/gomysql/helper"
)

func TestDb_Exec(t *testing.T) {
	res, err := gomysql.Exec(db.Executor(), "CREATE TABLE IF NOT EXISTS `users` " +
		"(`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id'," +
		"`user_name` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '登录名'," +
		"`nickname` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '昵称'," +
		"`passwd` char(32) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '密码'," +
		"`email` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '邮箱'," +
		"`mobile` varchar(16) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '手机号'," +
		"`is_on` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '账号状态(1已启用，0已禁用)'," +
		"`created_at` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建时间'," +
		"`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
		"`last_login_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上次登录时间'," +
		"`remark` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '备注'," +
		"PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;")
	if err != nil {
		t.Fatalf("create table failed with error: %v\n", err)
	}

	count, err := res.RowsAffected()
	t.Logf("rows affected: %d error: %v\n", count, err)
}

Model

package main

import (
  "context"
  "fmt"
  "log"
  "strings"
  "time"

  "github.com/grpc-boot/gomysql"
  "github.com/grpc-boot/gomysql/helper"
)

var (
  DefaultUserModel = &UserModel{}
  db               *gomysql.Db
)

func init() {
  var err error
  db, err = gomysql.NewDb(gomysql.Options{
    Host:     "127.0.0.1",
    Port:     3306,
    DbName:   "users",
    UserName: "root",
    Password: "12345678",
  })

  if err != nil {
    log.Fatalf("init db failed with error: %v\n", err)
  }

  gomysql.SetLogger(func(query string, args ...any) {
    fmt.Printf("%s exec sql: %s args: %+v\n", time.Now().Format(time.DateTime), query, args)
  })

  gomysql.SetErrorLog(func(err error, query string, args ...any) {
    fmt.Printf("error: %v exec sql: %s args: %+v\n", err, query, args)
  })
}

func main() {
  current := time.Now()
  id, err := db.InsertWithInsertedIdContext(
    context.Background(),
    `users`,
    helper.Columns{"user_name", "nickname", "passwd", "is_on", "created_at", "updated_at"},
    helper.Row{"user1", "nickname1", strings.Repeat("1", 32), 1, time.Now().Unix(), time.Now().Format(time.DateTime)},
  )
  if err != nil {
    t.Fatalf("insert data failed with error: %v\n", err)
  }

  t.Logf("insert data with id: %d\n", id)

  user, err := gomysql.FindById(db.Executor(), id, DefaultUserModel)
  if err != nil {
    panic(err)
  }

  fmt.Printf("UserInfo: %+v\n", user)
}

type UserModel struct {
  Id          int64
  UserName    string
  NickName    string
  Passwd      string
  Email       string
  Mobile      string
  IsOn        uint8
  CreatedAt   int64
  UpdatedAt   time.Time
  LastLoginAt time.Time
  Remark      string
}

func (um *UserModel) PrimaryKey() string {
  return `id`
}

func (um *UserModel) TableName(args ...any) string {
  return `users`
}

func (um *UserModel) NewModel() gomysql.Model {
  return &UserModel{}
}

func (um *UserModel) Assemble(br gomysql.BytesRecord) {
  fmt.Printf("updated_at:%s\n", br.String("updated_at"))
  um.Id = br.ToInt64("id")
  um.UserName = br.String("user_name")
  um.NickName = br.String("nickname")
  um.Passwd = br.String("passwd")
  um.Email = br.String("email")
  um.Mobile = br.String("mobile")
  um.IsOn = br.ToUint8("is_on")
  um.CreatedAt = br.ToInt64("created_at")
  um.UpdatedAt, _ = time.Parse(time.DateTime, br.String("updated_at"))
  um.LastLoginAt, _ = time.Parse(time.DateTime, br.String("last_login_at"))
  um.Remark = br.String("remark")
}

Select

package main

import (
	"testing"
	"time"

	"github.com/grpc-boot/gomysql/condition"
	"github.com/grpc-boot/gomysql/helper"
)

func TestDb_Find(t *testing.T) {
	// SELECT * FROM users WHERE id=1
	query := helper.AcquireQuery().
		From(`users`).
		Where(condition.Equal{"id", 1})

	defer query.Close()

	record, err := db.FindOne(query)
	if err != nil {
		t.Fatalf("want nil, got %v", err)
	}
	t.Logf("record: %+v\n", record)

	// SELECT * FROM users WHERE id IN(1, 2)
	query1 := helper.AcquireQuery().
		From(`users`).
		Where(condition.In[int]{"id", []int{1, 2}})
	defer query1.Close()

	records, err := db.FindTimeout(time.Second*2, query1)
	if err != nil {
		t.Fatalf("want nil, got %v", err)
	}
	t.Logf("records: %+v\n", records)

	// SELECT * FROM users WHERE user_name LIKE 'user%' AND created_at>= timestamp
	query2 := helper.AcquireQuery().
		From(`users`).
		Where(condition.And{
			condition.BeginWith{"user_name", "user"},
			condition.Gte{"created_at", time.Now().Add(-7 * 24 * time.Hour).Unix()},
		})
	defer query2.Close()

	records, err = db.FindTimeout(time.Second*2, query2)
	if err != nil {
		t.Fatalf("want nil, got %v", err)
	}
	t.Logf("records: %+v\n", records)

	// SELECT * FROM users WHERE (user_name LIKE 'animal' AND created_at BETWEEN timestamp1 AND timestamp2) OR user_name LIKE 'user%'
	query3 := helper.AcquireQuery().
		From(`users`).
		Where(condition.Or{
			condition.And{
				condition.BeginWith{"user_name", "animal"},
				condition.Between{"created_at", time.Now().Add(-30 * 7 * 24 * time.Hour).Unix(), time.Now().Unix()},
			},
			condition.BeginWith{"user_name", "user"},
		})
	defer query3.Close()

	records, err = db.FindTimeout(time.Second*2, query3)
	if err != nil {
		t.Fatalf("want nil, got %v", err)
	}
	t.Logf("records: %+v\n", records)
}

Insert

package main

import (
	"context"
	"strings"
	"testing"
	"time"

	"github.com/grpc-boot/gomysql/condition"
	"github.com/grpc-boot/gomysql/helper"
)

func TestDb_Insert(t *testing.T) {
  id, err := db.InsertWithInsertedIdContext(
    context.Background(),
    `users`,
    helper.Columns{"user_name", "nickname", "passwd", "is_on", "created_at", "updated_at"},
    helper.Row{"user1", "nickname1", strings.Repeat("1", 32), 1, time.Now().Unix(), time.Now().Format(time.DateTime)},
  )
  if err != nil {
    t.Fatalf("insert data failed with error: %v\n", err)
  }

  t.Logf("insert data with id: %d\n", id)
}

Update

package main

import (
	"context"
	"testing"
	"time"

	"github.com/grpc-boot/gomysql/condition"
	"github.com/grpc-boot/gomysql/helper"
)

func TestDb_Update(t *testing.T) {
  rows, err := db.UpdateWithRowsAffectedContext(
    context.Background(),
    `users`,
    `last_login_at=?`,
    condition.Equal{Field: "id", Value: 1},
    time.Now().Format(time.DateTime),
  )

  if err != nil {
    t.Fatalf("update data failed with error: %v\n", err)
  }

  t.Logf("update data rows affected: %d", rows)
}

Delete

package main

import (
	"context"
	"testing"
	"time"

	"github.com/grpc-boot/gomysql/condition"
	"github.com/grpc-boot/gomysql/helper"
)

func TestDb_Delete(t *testing.T) {
  ctx, cancel := context.WithTimeout(context.Background(), time.Second)
  defer cancel()
  rows, err := db.DeleteWithRowsAffectedContext(
    ctx,
    `users`,
    condition.Equal{Field: "id", Value: 1},
  )

  if err != nil {
    t.Fatalf("delete data failed with error: %v\n", err)
  }
  t.Logf("delete data rows affected: %d", rows)
}

Transaction

package main

import (
	"context"
	"testing"
	"time"
	
	"github.com/grpc-boot/gomysql"
	"github.com/grpc-boot/gomysql/condition"
	"github.com/grpc-boot/gomysql/helper"
)

func TestDb_BeginTx(t *testing.T) {
  ctx, cancel := context.WithTimeout(context.Background(), time.Second*2)
  defer cancel()
  tx, err := db.BeginTx(ctx, nil)
  if err != nil {
    t.Fatalf("begin failed with error: %v", err)
  }

  query := helper.AcquireQuery().
    From(`users`).
    Where(condition.Equal{"id", 1})
  defer query.Close()
  records, err := gomysql.Find(tx, query)
  if err != nil {
    tx.Rollback()
    t.Fatalf("query failed with error: %v", err)
  }

  if len(records) != 1 {
    tx.Rollback()
    t.Fatal("row not exists")
  }

  res, err := gomysql.Update(tx, `users`, "updated_at=?", condition.Equal{"updated_at", records[0].String("updated_at")}, time.Now().Format(time.DateTime))
  if err != nil {
    tx.Rollback()
    t.Fatalf("update failed with error: %v", err)
  }

  tx.Commit()
  count, _ := res.RowsAffected()
  t.Logf("updated count: %d", count)
}

Read-Write-Splitting

支持failover和failback

package main

import (
  "testing"

  "github.com/grpc-boot/gomysql"
  "github.com/grpc-boot/gomysql/condition"
)

func TestPool_Random(t *testing.T) {
	opt := gomysql.PoolOptions{
		Masters: []gomysql.Options{
			{
				Host:     "127.0.0.1",
				Port:     3306,
				DbName:   "users",
				UserName: "root",
				Password: "12345678",
			},
			{
				Host:     "127.0.0.1",
				Port:     3306,
				DbName:   "users",
				UserName: "root",
				Password: "12345678",
			},
		},
		Slaves: []gomysql.Options{
			{
				Host:     "127.0.0.1",
				Port:     3306,
				DbName:   "users",
				UserName: "root",
				Password: "12345678",
			},
			{
				Host:     "127.0.0.1",
				Port:     3306,
				DbName:   "users",
				UserName: "root",
				Password: "12345678",
			},
			{
				Host:     "127.0.0.1",
				Port:     3306,
				DbName:   "users",
				UserName: "root",
				Password: "12345678",
			},
		},
	}

	pool, err := gomysql.NewPool(opt)
	if err != nil {
		t.Fatalf("want nil, got %v", err)
	}

	query := helper.AcquireQuery().
		From(`users`).
		Where(condition.Equal{"id", 1})
	defer query.Close()

    record, err := pool.FindOne(gomysql.TypeMaster, query)
    if err != nil {
      t.Fatalf("find one error: %v", err)
    }
    t.Logf("query records: %+v", record)
  
    record, err = pool.FindOne(gomysql.TypeSlave, query)
    if err != nil {
      t.Fatalf("find one error: %v", err)
    }
    t.Logf("query records: %+v", record)
}

Sql-Log

package main

import (
	"fmt"
	"time"

	"github.com/grpc-boot/gomysql"
)

func init() {
  // 输出sql和参数到标准输入，修改func定制自己的日志，方便分析sql
  gomysql.SetLogger(func(query string, args ...any) {
    fmt.Printf("%s exec sql: %s args: %+v\n", time.Now().Format(time.DateTime), query, args)
  })

  // 记录错误日志
  gomysql.SetErrorLog(func(err error, query string, args ...any) {
    fmt.Printf("error: %v exec sql: %s args: %+v\n", err, query, args)
  })
}