mysql 汇总表缓存表,mysql - 从Mysql表中查询所有行并存储为缓存 - SO中文参考 - www.soinside.com...

我需要一张桌子来对我的应用程序进行一些收费。我试图将此表加载为JSON结构并将其用作缓存。这是我的方法：

表格：CREATE TABLE `lwratecarddefinition` (

`RATECARDID` int(10) DEFAULT NULL AUTO_INCREMENT,

`RATECARDGROUPID` int(10) DEFAULT NULL,

`SERVICEID` varchar(10) DEFAULT NULL,

`USAGETYPEID` varchar(10) DEFAULT NULL,

`CURRENCYCODE` varchar(4) DEFAULT NULL,

`LEDGERID` varchar(15) DEFAULT NULL,

`PULSE` varchar(10) DEFAULT NULL,

`SPECIALPULSE` varchar(10) DEFAULT NULL,

`NORMALRATE` varchar(10) DEFAULT NULL,

`OFFPEAKSET` varchar(2) DEFAULT NULL,

`OFFPEAKRATE` varchar(10) DEFAULT NULL,

`PEAKRATE` varchar(10) DEFAULT NULL,

`ONDEMANDSET` varchar(2) DEFAULT NULL,

`ONDEMANDRATE` varchar(2) DEFAULT NULL,

KEY `idx_lwratecarddefinition_RATECARDID` (`RATECARDID`),

KEY `idx_lwratecarddefinition_RATECARDGROUPID` (`RATECARDGROUPID`),

KEY `idx_lwratecarddefinition_USAGETYPEID` (`USAGETYPEID`),

KEY `idx_lwratecarddefinition_SERVICEID` (`SERVICEID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

main.go代码段-(我的目标是使用go例程每1分钟获取一次记录)// load the ratecard first

lwratecardefinition = getRatecard()

// update data every 1 second

go func() {

for {

time.Sleep(time.Second)

lwratecardefinition = getRatecard()

fmt.Println(lwratecardefinition)

}

}()

dbfunction.gopackage main

import (

"database/sql"

_ "github.com/go-sql-driver/mysql"

)

type Lwratecardefinition struct {

Ratecardid int `json:"ratecardid"`

Ratecardgroupid int `json:"ratecardgroupid"`

Serviceid int `json:"serviceid"`

Usagetypeid int `json:"usagetypeid"`

Currencycode int `json:"currencycode"`

Ledgerid string `json:"ledgerid"`

Pulse int `json:"pulse"`

Specialpulse int `json:"specialpulse"`

Normalrate int `json:"normalrate"`

Offpeakset int `json:"offpeakset"`

Offpeakrate int `json:"offpeakrate"`

Peakrate int `json:"peakrate"`

Ondemandset int `json:"ondemandset"`

Ondemandrate int `json:"ondemandrate"`

}

var lwratecardefinition Lwratecardefinition

func getRatecard() Lwratecardefinition {

db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/bcsdb")

// if there is an error opening the connection, handle it

if err != nil {

panic(err.Error())

}

// defer the close till after the main function has finished

// executing

defer db.Close()

var ratecardid,ratecardgroupid,serviceid,usagetypeid,currencycode,pulse,specialpulse,normalrate,offpeakset,offpeakrate,peakrate,ondemandset,ondemandrate int

var ledgerid string

results, err := db.Query("select ratecardid,ratecardgroupid,serviceid,usagetypeid,currencycode,ledgerid,pulse,specialpulse,normalrate,offpeakset,offpeakrate,peakrate,ondemandset,ondemandrate from lwratecarddefinition")

if err != nil {

panic(err.Error())

}

for results.Next() {

err = results.Scan(&ratecardid,&ratecardgroupid,&serviceid,&usagetypeid,&currencycode,&ledgerid,&pulse,&specialpulse,&normalrate,&offpeakset,&offpeakrate,&peakrate,&ondemandset,&ondemandrate)

lwratecardefinition = Lwratecardefinition{Ratecardid: ratecardid, Ratecardgroupid: ratecardgroupid, Serviceid: serviceid, Usagetypeid: usagetypeid, Currencycode: currencycode, Ledgerid: ledgerid, Pulse: pulse, Specialpulse: specialpulse, Normalrate: normalrate, Offpeakset: offpeakset, Offpeakrate: offpeakrate, Peakrate: peakrate, Ondemandset: ondemandset, Ondemandrate: ondemandrate}

}

return lwratecardefinition

}

但是当我执行程序时，我只能从mysql表中得到一行，而不是所有行。我需要所有行将其用作缓存。输出：{4 508 1 201 1 DATA 60 30 2 1 3 1 1 5}

另外，我的最终目标是使用这些值来找到速率和脉搏。如果Ratecardgroupid = X值和Serviceid = Y值我想使用同一行中的脉冲值。请在这里提出问题，以及是否可以通过这种方法减少逻辑调用来减少数据库调用。