sql

README

database-mysql模块

找到database-mysql

摘自bilibili karos框架,加入自写的ORM框架

使用方式

database-mysql 是一个mysql客户端，包括了基本DML,性能监控,可以使用peach配置SDK加载配置文件。

example

示例数据表结构：

misc字段存储的是json字符串

+-------------+---------------+------+-----+-------------------+-------------------+
| Field       | Type          | Null | Key | Default           | Extra             |
+-------------+---------------+------+-----+-------------------+-------------------+
| id          | bigint        | NO   | PRI | NULL              | auto_increment    |
| name        | varchar(50)   | NO   | MUL |                   |                   |
| age         | int           | YES  |     | NULL              |                   |
| regist_time | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| status      | tinyint       | NO   |     | 0                 |                   |
| create_time | bigint        | NO   |     | 0                 |                   |
| misc        | varchar(1024) | NO   |     |                   |                   |
+-------------+---------------+------+-----+-------------------+-------------------+

1. 普通使用

import (
	"context"
	"time"

	"github.com/zdao-pro/sky_blue/pkg/log"
    "github.com/zdao-pro/sky_blue/pkg/database/sql"
	"github.com/stretchr/testify/assert"
)

func main() {
	log.Init(nil)
	c := &Config{
		DSN:          "test:test@tcp(127.0.0.1:3306)/test?timeout=5s&readTimeout=5s&writeTimeout=5s&parseTime=true&loc=Local&charset=utf8",
		ReadDSN:      []string{"test:test@tcp(127.0.0.1:3306)/test?timeout=5s&readTimeout=5s&writeTimeout=5s&parseTime=true&loc=Local&charset=utf8"},
		Active:       10,
		Idle:         10,
		IdleTimeout:  100 * time.Second,
		QueryTimeout: 1 * time.Second,
		ExecTimeout:  1 * time.Second,
		TranTimeout:  1 * time.Second,
	}
	db := sql.NewMySQL(c)
	if db == nil {
		log.Warn("error")
	}

	err := db.Ping(context.Background())
	if err != nil {
		log.Warn("ping error")
	}

	_, err = db.Exec(context.Background(), "insert into user_info set name = ?,age = ?", "name", 23)
	if nil != err {
		log.Error(err.Error())
	}
}

2. 配置文件使用方式

配置文件:

DSN: "test:test@tcp(127.0.0.1:3306)/test?timeout=5s&readTimeout=5s&writeTimeout=5s&parseTime=true&loc=Local&charset=utf8"
ReadDSN:
  - "test:test@tcp(127.0.0.1:3306)/test?timeout=5s&readTimeout=5s&writeTimeout=5s&parseTime=true&loc=Local&charset=utf8"
Active:       10
Idle:         10
IdleTimeout:  100
QueryTimeout: 1
ExecTimeout:  1
TranTimeout:  1

import (
	"context"
	"time"

	"github.com/zdao-pro/sky_blue/pkg/database/sql"
	"github.com/zdao-pro/sky_blue/pkg/log"
	"github.com/stretchr/testify/assert"
	"github.com/zdao-pro/sky_blue/pkg/peach"
	_ "github.com/zdao-pro/sky_blue/pkg/peach/apollo"
)

func main() {
	log.Init(nil)
	peach.Init(peach.PeachDriverApollo, "zdao_backend.sky_blue")
	var c Config
	peach.Get("mysql_test.yaml").UnmarshalYAML(&c)
	fmt.Println(c)
	db := sql.NewMySQL(&c)
	if db == nil {
		log.Warn("error")
	}

	err := db.Ping(context.Background())
	if err != nil {
		log.Warn("ping error")
	}

	_, err = db.Exec(context.Background(), "insert into user_info set name = ?,age = ?", "name", 423)
	if nil != err {
		log.Error(err.Error())
	}
}

3. ORM方式(推荐)

import (
	"context"
	"time"
	"error"

    "github.com/zdao-pro/sky_blue/pkg/database/sql"
	"github.com/zdao-pro/sky_blue/pkg/log"
	"github.com/stretchr/testify/assert"
	"github.com/zdao-pro/sky_blue/pkg/peach"
	_ "github.com/zdao-pro/sky_blue/pkg/peach/apollo"
)

// 使用时需在结构体tag指明orm
// 当字段存储的是int类型时间戳，需指明time_format:  unix(秒级时间戳) unixmilli(毫秒级时间戳) unixnano(纳秒级时间戳)，才可以自动解析为time.Time
// 当字段为json字符串,需要在解析结构体tag指明json, 才可以自动解析
// 结构体需要声明*sql.Model,并赋值
// 使用事务时,可以直接使用Begin(context.Background()),Commit(),Rollback(),不需要另外声明
type UserInfo struct {
	*sql.Model
	ID         int16     `orm:"id"`
	Name       string    `orm:"name"`
	Age        int       `orm:"age"`
	Status     uint8     `orm:"status"`
	RegistTime time.Time `orm:"regist_time"`
	CreateTime time.Time `orm:"create_time" time_format:"unixmilli"`
	MiscInfo   Misc      `orm:"misc"`
}

type Misc struct {
	Email string `json:"email"`
	Phone int    `json:"phone"`
}

func (u *UserInfo) Insert(name string, age int) (err error) {
	_, err = u.Exec(context.Background(), "insert into user_info set name = ?,age = ?", name, age)
	return
}

func (u *UserInfo) QueryUserByID(id int) error {
	err := u.Select(context.Background(), u, "select id,name,age,regist_time,status,create_time,misc from user_info where id = ?", id)
	if nil != err {
		return err
	}
	fmt.Println(u)
	return nil
}

func (u *UserInfo) QueryUserByName(name string) (*[]UserInfo, error) {
	list := make([]UserInfo, 0)
	err := u.Select(context.Background(), &list, "select id,name,age,regist_time,status,create_time,misc from user_info where name = ?", name)
	if nil != err {
		return nil, err
	}
	// fmt.Println(list)
	return &list, nil
}

func main() {
	log.Init(nil)
	peach.Init(peach.PeachDriverApollo, []string{"zdao_backend.sky_blue", "zdao_backend.common"})
	var c Config
	peach.Get("mysql_test.yaml").UnmarshalYAML(&c)
	db := sql.NewMySQL(&c)
	if db == nil {
		log.Warn("error")
	}
	user := UserInfo{
		Model: NewModel(db),
	}
	// user.Begin(context.Background())
	// user.Insert("test", 50)
	// user.Rollback()
	// user.Commit()
	user.QueryUserByID(145)
	fmt.Println(user)
}

Documentation

Index

• Variables
• type Config
• type DB
  • func NewMySQL(c *Config) (db *DB)
  • func Open(c *Config) (*DB, error)
  • func (db *DB) Begin(c context.Context) (tx *Tx, err error)
  • func (db *DB) Close() (err error)
  • func (db *DB) Exec(c context.Context, query string, args ...interface{}) (res sql.Result, err error)
  • func (db *DB) Master() *DB
  • func (db *DB) Ping(c context.Context) (err error)
  • func (db *DB) Prepare(query string) (*Stmt, error)
  • func (db *DB) Prepared(query string) (stmt *Stmt)
  • func (db *DB) Query(c context.Context, query string, args ...interface{}) (rows *Rows, err error)
  • func (db *DB) QueryRow(c context.Context, query string, args ...interface{}) *Row
• type DataType
• type Field
• type Model
  • func NewModel(db *DB) (md *Model)
  • func (m *Model) Begin(c context.Context) (err error)
  • func (m *Model) Commit() (err error)
  • func (m *Model) Exec(c context.Context, query string, args ...interface{}) (res sql.Result, err error)
  • func (m *Model) Query(c context.Context, query string, args ...interface{}) (*Rows, error)
  • func (m *Model) QueryRow(c context.Context, query string, args ...interface{}) *Row
  • func (m *Model) Rollback() (err error)
  • func (m *Model) Select(c context.Context, dest interface{}, query string, args ...interface{}) (err error)
• type Row
  • func (r *Row) Scan(dest ...interface{}) (err error)
• type Rows
  • func (rs *Rows) Close() (err error)
• type Stmt
  • func (s *Stmt) Close() (err error)
  • func (s *Stmt) Exec(c context.Context, args ...interface{}) (res sql.Result, err error)
  • func (s *Stmt) Query(c context.Context, args ...interface{}) (rows *Rows, err error)
  • func (s *Stmt) QueryRow(c context.Context, args ...interface{}) (row *Row)
• type TimeType
• type Tx
  • func (tx *Tx) Commit() (err error)
  • func (tx *Tx) Exec(query string, args ...interface{}) (res sql.Result, err error)
  • func (tx *Tx) Prepare(c context.Context, query string) (*Stmt, error)
  • func (tx *Tx) Query(query string, args ...interface{}) (rows *Rows, err error)
  • func (tx *Tx) QueryRow(query string, args ...interface{}) *Row
  • func (tx *Tx) Rollback() (err error)
  • func (tx *Tx) Stmt(stmt *Stmt) *Stmt

Variables

var (
	//ErrNoPtr ..
	ErrNoPtr = errors.New("noptr")
	//ErrNoResult ..
	ErrNoResult = errors.New("noResult")
)

var (
	// ErrStmtNil prepared stmt error
	ErrStmtNil = errors.New("sql: prepare failed and stmt nil")
	// ErrNoMaster is returned by Master when call master multiple times.
	ErrNoMaster = errors.New("sql: no master instance")
	// ErrNoRows is returned by Scan when QueryRow doesn't return a row.
	// In such a case, QueryRow returns a placeholder *Row value that defers
	// this error until a Scan.
	ErrNoRows = sql.ErrNoRows
	// ErrTxDone transaction done.
	ErrTxDone = sql.ErrTxDone
)

var TimeReflectType = reflect.TypeOf(time.Time{})

TimeReflectType ..

Types

type Config

type Config struct {
	DSN          string        `yaml:"DSN"`          // write data source name.
	ReadDSN      []string      `yaml:"ReadDSN"`      // read data source name.
	Active       int           `yaml:"Active"`       // pool
	Idle         int           `yaml:"Idle"`         // pool
	IdleTimeout  time.Duration `yaml:"IdleTimeout"`  // connect max life time.
	QueryTimeout time.Duration `yaml:"QueryTimeout"` // query sql timeout
	ExecTimeout  time.Duration `yaml:"ExecTimeout"`  // execute sql timeout
	TranTimeout  time.Duration `yaml:"TranTimeout"`  // transaction sql timeout
}

Config mysql config.

type DB

type DB struct {
	// contains filtered or unexported fields
}

DB database.

func NewMySQL

func NewMySQL(c *Config) (db *DB)

NewMySQL new db and retry connection when has error.

func Open

func Open(c *Config) (*DB, error)

Open opens a database specified by its database driver name and a driver-specific data source name, usually consisting of at least a database name and connection information.

func (*DB) Begin

func (db *DB) Begin(c context.Context) (tx *Tx, err error)

Begin starts a transaction. The isolation level is dependent on the driver.

func (*DB) Close

func (db *DB) Close() (err error)

Close closes the write and read database, releasing any open resources.

func (*DB) Exec

func (db *DB) Exec(c context.Context, query string, args ...interface{}) (res sql.Result, err error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*DB) Master

func (db *DB) Master() *DB

Master return *DB instance direct use master conn use this *DB instance only when you have some reason need to get result without any delay.

func (*DB) Ping

func (db *DB) Ping(c context.Context) (err error)

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (*DB) Prepare

func (db *DB) Prepare(query string) (*Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

func (*DB) Prepared

func (db *DB) Prepared(query string) (stmt *Stmt)

Prepared creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

func (*DB) Query

func (db *DB) Query(c context.Context, query string, args ...interface{}) (rows *Rows, err error)

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryRow

func (db *DB) QueryRow(c context.Context, query string, args ...interface{}) *Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

type DataType

type DataType string

DataType ..

const (
	//Bool ..
	Bool DataType = "bool"
	//Int ..
	Int DataType = "int"
	//Uint ..
	Uint DataType = "uint"
	//Float ..
	Float DataType = "float"
	//String ..
	String DataType = "string"
	//Time ..
	Time DataType = "time"
	//Bytes ..
	Bytes DataType = "bytes"
	//Struct ..
	Struct DataType = "struct"
)

type Field

type Field struct {
	Name       string
	FieldType  reflect.Type
	TimeFormat string //
	DataType   DataType
}

Field ...

type Model

type Model struct {
	DB *DB
	Tx *Tx
}

Model ..

func NewModel

func NewModel(db *DB) (md *Model)

NewModel ..

func (*Model) Begin

func (m *Model) Begin(c context.Context) (err error)

Begin starts a transaction. The isolation level is dependent on the driver.

func (*Model) Commit

func (m *Model) Commit() (err error)

Commit commits the transaction.

func (*Model) Exec

func (m *Model) Exec(c context.Context, query string, args ...interface{}) (res sql.Result, err error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*Model) Query

func (m *Model) Query(c context.Context, query string, args ...interface{}) (*Rows, error)

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*Model) QueryRow

func (m *Model) QueryRow(c context.Context, query string, args ...interface{}) *Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

func (*Model) Rollback

func (m *Model) Rollback() (err error)

Rollback aborts the transaction.

func (*Model) Select

func (m *Model) Select(c context.Context, dest interface{}, query string, args ...interface{}) (err error)

Select ..

type Row

type Row struct {
	*sql.Row
	// contains filtered or unexported fields
}

Row row.

func (*Row) Scan

func (r *Row) Scan(dest ...interface{}) (err error)

Scan copies the columns from the matched row into the values pointed at by dest.

type Rows

type Rows struct {
	*sql.Rows
	// contains filtered or unexported fields
}

Rows rows.

func (*Rows) Close

func (rs *Rows) Close() (err error)

Close closes the Rows, preventing further enumeration. If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.

type Stmt

type Stmt struct {
	// contains filtered or unexported fields
}

Stmt prepared stmt.

func (*Stmt) Close

func (s *Stmt) Close() (err error)

Close closes the statement.

func (*Stmt) Exec

func (s *Stmt) Exec(c context.Context, args ...interface{}) (res sql.Result, err error)

Exec executes a prepared statement with the given arguments and returns a Result summarizing the effect of the statement.

func (*Stmt) Query

func (s *Stmt) Query(c context.Context, args ...interface{}) (rows *Rows, err error)

Query executes a prepared query statement with the given arguments and returns the query results as a *Rows.

func (*Stmt) QueryRow

func (s *Stmt) QueryRow(c context.Context, args ...interface{}) (row *Row)

QueryRow executes a prepared query statement with the given arguments. If an error occurs during the execution of the statement, that error will be returned by a call to Scan on the returned *Row, which is always non-nil. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

type TimeType

type TimeType int64

TimeType ..

const (
	//UnixSecond ..
	UnixSecond TimeType = 1
	//UnixMillisecond ..
	UnixMillisecond TimeType = 2
	//UnixNanosecond ..
	UnixNanosecond TimeType = 3
)

type Tx

type Tx struct {
	// contains filtered or unexported fields
}

Tx transaction.

func (*Tx) Commit

func (tx *Tx) Commit() (err error)

Commit commits the transaction.

func (*Tx) Exec

func (tx *Tx) Exec(query string, args ...interface{}) (res sql.Result, err error)

Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.

func (*Tx) Prepare

func (tx *Tx) Prepare(c context.Context, query string) (*Stmt, error)

Prepare creates a prepared statement for use within a transaction. The returned statement operates within the transaction and can no longer be used once the transaction has been committed or rolled back. To use an existing prepared statement on this transaction, see Tx.Stmt.

func (*Tx) Query

func (tx *Tx) Query(query string, args ...interface{}) (rows *Rows, err error)

Query executes a query that returns rows, typically a SELECT.

func (*Tx) QueryRow

func (tx *Tx) QueryRow(query string, args ...interface{}) *Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

func (*Tx) Rollback

func (tx *Tx) Rollback() (err error)

Rollback aborts the transaction.

func (*Tx) Stmt

func (tx *Tx) Stmt(stmt *Stmt) *Stmt

Stmt returns a transaction-specific prepared statement from an existing statement.