mysql - 从 Mysql 表中查询所有行并存储为缓存

Question

我有一张 table 需要在我的应用程序中进行一些收费。我正在尝试将此表加载到 JSON 结构中并将其用作缓存。这是我的方法:

table :

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 代码片段 - (我的目标是每 1 分钟使用 go 例程获取记录)

    // load the ratecard first
    lwratecardefinition = getRatecard()
    // update data every 1 second
    go func() {
        for {
            time.Sleep(time.Second)
            lwratecardefinition = getRatecard()
            fmt.Println(lwratecardefinition)
            }
    }()

dbfunction.go

package 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 值我想使用同一行的脉冲值。
请提出这里有什么问题以及该方法是否可以减少数据库调用以及逻辑。

Answer 1

But when I am executing the program I am only getting one row not all rows from mysql table

因为您的函数只返回单个 Lwratecardefinition struct，您在循环的每次迭代中覆盖的值。您想返回它们的一部分，例如:

func getRatecard() []Lwratecardefinition {
   ...
   var result []Lwratecardefinition
   for results.Next() {
     err = results.Scan(&ratecardid,&ratecardgroupid,&serviceid,&usagetypeid,&currencycode,&ledgerid,&pulse,&specialpulse,&normalrate,&offpeakset,&offpeakrate,&peakrate,&ondemandset,&ondemandrate)
     result = append(result, 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})
   }

   if (results.Err() != nil) {
      // something went wrong while reading records
      ...
   }
   return result
}