简化mysql数据库 *** 作--像python一样使用golang

dbsdk

简化mysql数据库 *** 作

Installation

$ go get -u github.com/jingmingyu/dbsdk 

MYSQL sdk Usage: Init MySQL DB

    mysqldb := db_mysql.MYSQL{
		Host:     "127.0.0.1",
		Port:     "3306",
		Username: "test",
		Password: "test",
		Dbname:   "mytestdb",
	}

Select Method:

1. SelectToJson
2. SelectToRowsData

SelectToJson : Ordinary select like select / show master status

    import (
       sdk "github.com/jingmingyu/dbsdk"
       "encoding/json"
       "fmt"
       )
        
    type DBData struct {
       Created_by   *sdk.BaseInfo `json:"created_by"`
       Id           *sdk.BaseInfo `json:"id"`
       Operate_page *sdk.BaseInfo `json:"operate_page"`
       When_created *sdk.BaseInfo `json:"when_created"`
    }
		
	querysql := fmt.Sprintf("%s", "SELECT * FROM `dbtool_accesslog`")
	res, _ := mysqldb.SelectToJson(querysql)
	fmt.Println(res)

	// select one res to json 
	fmt.Println("this select for one")
	if err := json.Unmarshal([]byte(res[0]), &resdata); err == nil {
		fmt.Println(resdata.Operate_page.String)
	} else {
		fmt.Println(err)
	}

	// select all res to json 
	fmt.Println("this select for all")
	for i := 0; i< len(res) ; i++ {
	if err := json.Unmarshal([]byte(res[i]), &resdata); err == nil {
		fmt.Println(resdata.Operate_page.String)
	} else {
		fmt.Println(err)
		}
	}

result :

res：
[{
    "created_by": {
        "String": "",
        "Valid": false
    },
    "id": {
        "String": "10",
        "Valid": true
    },
    "operate_page": {
        "String": "zzzzzzzz",
        "Valid": true
    },
    "when_created": {
        "String": "2022-03-17T22:37:23+08:00",
        "Valid": true
    }
}]

select one res 
	Dashboard
select all res 
	Dashboard

SelectToRowsData: show engine innodb status

	querysql := fmt.Sprintf("%s", "show engine innodb status")
	res, _ := mysqldb.SelectToRowsData(querysql)
	fmt.Println(res.Data)

res:

[[{InnoDB true} { true} {
=====================================
2022-03-18 10:04:00 140672645170944 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5073576 srv_active, 0 srv_shutdown, 1710 srv_idle
srv_master_thread log flush and writes: 0
----------
...

DML Method:

1. insert

   insertsql := fmt.Sprintf("%s", "insert into `dbtool_accesslog`( `operate_page` , `when_created` ) VALUE ('zzzzzzzz',now())")
   res, _ := mysqldb.DirectExec(insertsql)
   fmt.Println(res)

2. delete

    deletesql := fmt.Sprintf("%s", "delete from `dbtool_accesslog`")
    res, _ := mysqldb.DirectExec(deletesql)
    fmt.Println(res)

3. update

	updatesql := fmt.Sprintf("%s", "update `dbtool_accesslog` set operate_page='ascd' ")
	res, _ := mysqldb.DirectExec(updatesql)
	fmt.Println(res)

TRX Exec Method:

1. DirectExec

	updatesql := fmt.Sprintf("%s", "update `dbtool_accesslog` set operate_page='ascd' ")
	res, _ := mysqldb.DirectExec(updatesql)
	fmt.Println(res)

	exec add/drop ddl
	addsql := fmt.Sprintf("%s", "alter table dbtool_accesslog add zzz int(2)")
	res, _ := mysqldb.DirectExec(addsql)
	fmt.Println(res)

	dropsql := fmt.Sprintf("%s", "alter table dbtool_accesslog drop  COLUMN zzz ")
	res, _ := mysqldb.DirectExec(dropsql)
	fmt.Println(res)

2. SingleTrxExec

	//single trx exec
	// 单个事务执行
	trxsql := fmt.Sprintf("%s", "insert into `dbtool_accesslog`( `operate_page` , `when_created` ) VALUE ('zzzzzzzz',now())")
	res, err := mysqldb.SingleTrxExec(trxsql)
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println(res)

4. ComTrxExec

	// combine trx exec
	// 组合事务顺序执行
	var trx []string
	trxsql1 := fmt.Sprintf("%s", "insert into `dbtool_accesslog`( `operate_page` , `when_created` ) VALUE ('zzzzzzzz',now())")
	trxsql2 := fmt.Sprintf("%s", "insert into `dbtool_accesslog`( `operate_page` , `when_created` ) VALUE ('bbbbb',now())")
	trxsql3 := fmt.Sprintf("%s", "insert into `dbtool_accesslog`( `operate_page` , `when_created` ) VALUE ('bbbbb',now())")
	trxsql4 := fmt.Sprintf("%s", "update `dbtool_accesslog` set operate_page='zzzzzzzz' where operate_page='ascd'")
	trxsql5 := fmt.Sprintf("%s", "insert into `dbtool_accesslog`( `operate_page` , `when_created` ) VALUE ('zzzzzzzz',now())")
	
	trx = append(trx,trxsql1,trxsql2,trxsql3,trxsql4,trxsql5)
	
	res, err := mysqldb.ComTrxExec(trx)
	
	if err != nil {
		fmt.Println(err)
	}
	
	for _,v := range res{
		fmt.Println(v)
	}