gomysql

package module
v0.0.0-...-a894289 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 12, 2022 License: MIT Imports: 16 Imported by: 0

README

gomysql

移动到 github.com/hyahm/gosql

mysql 只是简单封装

  • 支持高并发
  • 支持更新和删除失败的日志记录
  • 支持驱动自带的连接池，
  • 避免连接过多导致的失败
  • 支持in的操作

bench

package main

import (
	"fmt"
	"github.com/hyahm/gomysql"
)

const Num = 1000

func main() {
        wg := &sync.WaitGroup{}
	wg.Add(1)
	go Insert8(wg)
	// go Insert5(wg)
	wg.Wait()
}

func Insert8(wg *sync.WaitGroup) {

	start := time.Now()
	conf := &gomysql.Sqlconfig{
		Host:               "127.0.0.1",
		UserName:           "cander",
		Password:           "123456",
		DbName:             "test",
		Port:               3306,
		MaxOpenConns:       100,
		MaxIdleConns:       10,
		ReadTimeout:        100 * time.Second,
		WriteTimeout:       100 * time.Second,
		WriteLogWhenFailed: true,
		ConnMaxLifetime:    30 * time.Second,
		// 删改查失败写入的文件
		LogFile:            ".failedlinux.sql", 
	}
	ch := make(chan int, Num)
	db, err := conf.NewDb()
	if err != nil {
		os.Exit(1)
	}

	for i := 0; i < Num; i++ {
		go func(i int) {
			db.Insert("insert into test(name, age) values(?,?)", fmt.Sprintf("test%d", i), i)
			ch <- 1
		}(i)

	}
	
	
	
	for i := 0; i < Num; i++ {
		<-ch
	}
	
	rows,err := db.GetRowsIn("select id from test where age in (?)", []string{"1","2","3","4","5"})
	if err != nil {
		os.Exit(1)
	}
	for rows.Next() {
		var id int64
	  	rows.Scan(&id)
	}
	rows.Close()
	log.Println("mysql8:", time.Since(start).Seconds())
	wg.Done()
}


// 高级方法
// 高级方法只有第一级的db有效，后面的都无视
type MeStruct struct {
	X int `json:"x"`
	Y int `json:"y"`
	Z int `json:"z"`
}

// 使用高级方法的第一个是对应数据库的字段
// default: 插入时候，如果没有传入值将使用数据库default的值， 如果没写就是默认值
// force： 修改的时候， 如果设置了force， 那么强制修改字段的值， 如果没写， 零值的时候不会修改值
// 主键必须设置 omitempty 并且不能有force
type Person struct {
	ID        int64    `db:"id,default"`
	FirstName string   `db:"first_name,force"`
	LastName  string   `db:"last_name"`
	Email     string   `db:"email,default,force"`
	Me        MeStruct `db:"me"`
	
}


func main() {
	conf := Sqlconfig{
		UserName:        "test",
		Password:        "123456",
		Port:            3306,
		DbName:          "test",
		Host:            "192.168.101.4",
		MultiStatements: true,
	}
	db, err := conf.NewDb()
	if err != nil {
		t.Fatal(err)
	}
	
	// 插入
	ps := &Person{
		FirstName: "cander",
		LastName:  "biao",
		Email:     "[email protected]",
		Me: MeStruct{
			X: 10,
			Y: 20,
			Z: 30,
		},
	}
	pss := make([]*Person, 0)
	for i := 0; i < 20; i++ {
		pss = append(pss, ps)
	}
	// $key  $value 是插入事的固定占位符， 在这个占位符之前不能有参数占位符？，如果有的话请使用 Insert处理
	// default: 如果为空， 那么为数据库的默认值
	// struct, 指针， 切片 默认值为 ""
	// 传入的 dest 值 可以是指针，可以是数据，可以是结构体
	result = db.InsertInterfaceWithoutID(pss, "insert into person($key) values($value)")
	if result.Err != nil {
		t.Fatal(err)
	}
	// 将会生成20条数据

	// 修改
	updateps := &Person{
		FirstName: "what is it",
		LastName:  "hyahm.com",
		Email:     "[email protected]",
		Me: MeStruct{
			X: 10,
			Y: 20,
			Z: 30,
		},
	}

	// $set 是固定占位符, 前面也必须没有参数占位符 ?
	// omitempty: 如果为空， 那么为数据库的默认值
	// 传入的值必须是指针或结构体
	result = db.UpdateInterface(updateps, "update person set $set where id=?", 1)
	if result.Err != nil {
		t.Fatal(err)
	}
	// 执行后会修改id为1的行
	persons := make([]*Category, 0)

	result = db.Select(&persons, "select * from Person")
	if result.Err != nil {
		fmt.Println(err)
	}

	for _, v := range persons {
		fmt.Printf("%#v", *v)
	}
}

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var ErrNotSupport = errors.New("dest type not support")
View Source 
var ErrRangeOut = errors.New("索引值超出范围")

RangeOutErr 错误信息

View Source 
var IgnoreCondition = []string{"where", "on"}

如果后面有or 或者 and，只用删除or 或者and 那么删除前面的where， on 关键字

View Source 
var IgnoreWords = []string{"or", "and"}

IgnoreWords 如果删除in的字段， 前面如果出现下面的关键字， 也删除

Functions

func InToSql 

func InToSql(cmd string, args ...interface{}) string

还原sql

func ToPGSql 

func ToPGSql(cmd string, args ...interface{}) string

func ToSql 

func ToSql(cmd string, args ...interface{}) string

还原sql

Types

type Actuator 

type Actuator struct {
	Table string
	Db    *Db
}

func (*Actuator) Create 

func (actuator *Actuator) Create(model interface{}) Result

func (*Actuator) Delete 

func (actuator *Actuator) Delete(model interface{}, where string) Result

func (*Actuator) Read 

func (actuator *Actuator) Read(model interface{}) Result

func (*Actuator) Update 

func (actuator *Actuator) Update(model interface{}, where string) Result

type Counter 

type Counter interface {
	uint8 | uint16 | uint | int | uint32 | uint64 |
		int32 | int64 | int8 | int16 |
		float32 | float64
}

type Curder 

type Curder interface {
	Read(model interface{}) Result
	Update(interface{}, string) Result
	Create(model interface{}) Result
	Delete(interface{}, string) Result
}

type Db 

type Db struct {
	*sql.DB

	Ctx context.Context
	// contains filtered or unexported fields
}

func (*Db) CreateDatabase 

func (d *Db) CreateDatabase(dbname string, overWrite bool) error

func (*Db) Delete 

func (d *Db) Delete(cmd string, args ...interface{}) Result

func (*Db) DeleteIn 

func (d *Db) DeleteIn(cmd string, args ...interface{}) Result

func (*Db) Flush 

func (d *Db) Flush()

func (*Db) GetConnections 

func (d *Db) GetConnections() int

func (*Db) GetOne 

func (d *Db) GetOne(cmd string, args ...interface{}) *sql.Row

func (*Db) GetOneIn 

func (d *Db) GetOneIn(cmd string, args ...interface{}) *sql.Row

func (*Db) GetRows 

func (d *Db) GetRows(cmd string, args ...interface{}) (*sql.Rows, error)

func (*Db) GetRowsIn 

func (d *Db) GetRowsIn(cmd string, args ...interface{}) (*sql.Rows, error)

func (*Db) Insert 

func (d *Db) Insert(cmd string, args ...interface{}) Result

func (*Db) InsertIn 

func (d *Db) InsertIn(cmd string, args ...interface{}) Result

func (*Db) InsertInterfaceWithID 

func (d *Db) InsertInterfaceWithID(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) InsertInterfaceWithIDIn 

func (d *Db) InsertInterfaceWithIDIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) InsertInterfaceWithoutID 

func (d *Db) InsertInterfaceWithoutID(dest interface{}, cmd string, args ...interface{}) Result

插入字段的占位符 $key, $value

func (*Db) InsertInterfaceWithoutIDIn 

func (d *Db) InsertInterfaceWithoutIDIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) InsertMany 

func (d *Db) InsertMany(cmd string, args ...interface{}) Result

func (*Db) NewCurder 

func (db *Db) NewCurder(table string) Curder

func (*Db) NewTx 

func (d *Db) NewTx() (*Tx, error)

func (*Db) Select 

func (d *Db) Select(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) SelectIn 

func (d *Db) SelectIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) Update 

func (d *Db) Update(cmd string, args ...interface{}) Result

func (*Db) UpdateIn 

func (d *Db) UpdateIn(cmd string, args ...interface{}) Result

func (*Db) UpdateInterface 

func (d *Db) UpdateInterface(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) UpdateInterfaceIn 

func (d *Db) UpdateInterfaceIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Db) Use 

func (d *Db) Use(dbname string, overWrite ...bool) (*Db, error)

type PGConn 

type PGConn struct {
	*pgxpool.Pool

	Ctx context.Context
	// contains filtered or unexported fields
}

func (*PGConn) Insert 

func (d *PGConn) Insert(cmd string, args ...interface{}) Result

func (*PGConn) InsertInterfaceWithID 

func (d *PGConn) InsertInterfaceWithID(dest interface{}, cmd string, args ...interface{}) Result

func (*PGConn) InsertInterfaceWithoutID 

func (d *PGConn) InsertInterfaceWithoutID(dest interface{}, cmd string, args ...interface{}) Result

func (*PGConn) InsertMany 

func (d *PGConn) InsertMany(cmd string, args ...interface{}) Result

func (*PGConn) InsertWithoutId 

func (d *PGConn) InsertWithoutId(cmd string, args ...interface{}) Result

func (*PGConn) Select 

func (d *PGConn) Select(dest interface{}, cmd string, args ...interface{}) Result

func (*PGConn) Update 

func (d *PGConn) Update(cmd string, args ...interface{}) Result

func (*PGConn) UpdateInterface 

func (d *PGConn) UpdateInterface(dest interface{}, cmd string, args ...interface{}) Result

type Result 

type Result struct {
	Err           error
	Sql           string // sql并不是真实的执行的sql， 仅供参考
	LastInsertId  int64
	LastInsertIds []int64
	// RowsAffected returns the number of rows affected by an
	// update, insert, or delete. Not every database or database
	// driver may support this.
	RowsAffected int64
}

返回的结果

type Sqlconfig 

type Sqlconfig struct {
	UserName                string
	Password                string
	Host                    string
	Port                    int
	DbName                  string
	ClientFoundRows         bool
	AllowCleartextPasswords bool
	InterpolateParams       bool
	ColumnsWithAlias        bool
	MultiStatements         bool
	ParseTime               bool
	TLS                     bool
	ReadTimeout             time.Duration
	Timeout                 time.Duration
	WriteTimeout            time.Duration
	AllowOldPasswords       bool
	Charset                 string
	Loc                     string
	MaxAllowedPacket        uint64 // insert 插入大量数据的时候会用到 或者用insertmany
	Collation               string
	MaxOpenConns            int           // 请设置小于等于mysql 的max_connections值， 建议等于max_connections
	MaxIdleConns            int           // 如果设置了 MaxOpenConns， 那么此直将等于 MaxOpenConns
	ConnMaxLifetime         time.Duration // 连接池设置
	WriteLogWhenFailed      bool
	LogFile                 string
	Debug                   bool // 打印sql
}

func (*Sqlconfig) CreateDB 

func (s *Sqlconfig) CreateDB(name string) (*Db, error)

不存在就创建database

func (*Sqlconfig) GetMysqlDataSource 

func (s *Sqlconfig) GetMysqlDataSource() string

func (*Sqlconfig) GetPostgreDataSource 

func (s *Sqlconfig) GetPostgreDataSource() string

func (*Sqlconfig) NewMysqlDb 

func (s *Sqlconfig) NewMysqlDb() (*Db, error)

如果tag 是空的, 那么默认dbname

func (*Sqlconfig) NewPGPool 

func (s *Sqlconfig) NewPGPool() (*PGConn, error)

type Tx 

type Tx struct {
	Ctx context.Context
	// contains filtered or unexported fields
}

func (*Tx) Close 

func (t *Tx) Close() error

func (*Tx) Commit 

func (t *Tx) Commit() error

func (*Tx) Delete 

func (t *Tx) Delete(cmd string, args ...interface{}) Result

func (*Tx) DeleteIn 

func (t *Tx) DeleteIn(cmd string, args ...interface{}) Result

func (*Tx) GetOne 

func (t *Tx) GetOne(cmd string, args ...interface{}) *sql.Row

func (*Tx) GetOneIn 

func (t *Tx) GetOneIn(cmd string, args ...interface{}) *sql.Row

func (*Tx) GetRows 

func (t *Tx) GetRows(cmd string, args ...interface{}) (*sql.Rows, error)

func (*Tx) GetRowsIn 

func (t *Tx) GetRowsIn(cmd string, args ...interface{}) (*sql.Rows, error)

func (*Tx) Insert 

func (t *Tx) Insert(cmd string, args ...interface{}) Result

func (*Tx) InsertIn 

func (t *Tx) InsertIn(cmd string, args ...interface{}) Result

func (*Tx) InsertInterfaceWithID 

func (t *Tx) InsertInterfaceWithID(dest interface{}, cmd string, args ...interface{}) Result

func (*Tx) InsertInterfaceWithIDIn 

func (t *Tx) InsertInterfaceWithIDIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Tx) InsertInterfaceWithoutID 

func (t *Tx) InsertInterfaceWithoutID(dest interface{}, cmd string, args ...interface{}) Result

插入字段的占位符 $key, $value

func (*Tx) InsertInterfaceWithoutIDIn 

func (t *Tx) InsertInterfaceWithoutIDIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Tx) InsertMany 

func (t *Tx) InsertMany(cmd string, args ...interface{}) Result

func (*Tx) Rollback 

func (t *Tx) Rollback() error

func (*Tx) Select 

func (t *Tx) Select(dest interface{}, cmd string, args ...interface{}) Result

func (*Tx) SelectIn 

func (t *Tx) SelectIn(dest interface{}, cmd string, args ...interface{}) Result

func (*Tx) Update 

func (t *Tx) Update(cmd string, args ...interface{}) Result

func (*Tx) UpdateIn 

func (t *Tx) UpdateIn(cmd string, args ...interface{}) Result

func (*Tx) UpdateInterface 

func (t *Tx) UpdateInterface(dest interface{}, cmd string, args ...interface{}) Result

func (*Tx) UpdateInterfaceIn 

func (t *Tx) UpdateInterfaceIn(dest interface{}, cmd string, args ...interface{}) Result

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.