doujieyu7062 2015-09-10 12:22 采纳率: 0%
浏览 98
已采纳

sqlx库提供了奇怪的base64编码的外观结果

I am using this library from http://jmoiron.github.io/sqlx/ to do a query. Following the documentation led me to the code below.

func cities(w http.ResponseWriter, r *http.Request, _ httprouter.Params) error {
    var main string
    var secondary string
    var limit string
    queryParams := make(map[string]interface{})

    if k := r.PostFormValue("keyword"); k != "" {
        main = "city.name LIKE :keyword"
        queryParams["keyword"] = k + "%"
    }

    if sk := r.PostFormValue("secondaryKeyword"); sk != "" && sk != "null" {
        secondary = "OR city.name = :secondaryKeyword"
        queryParams["secondaryKeyword"] = sk
    }

    if mr := r.PostFormValue("maxResults"); mr != "" {
        limit = "LIMIT :maxResults"
        queryParams["maxResults"] = mr
    }

    if lr := r.PostFormValue("lastRequest"); lr != "" && lr == "1" {
        limit = ""
    }

    query := fmt.Sprintf(`
        SELECT
            city.geonameid AS cityId,
            city.name AS cityName,
            COALESCE(admin1.name_local, '') AS admin1Name,
            country.name AS countryName,
            CONCAT_WS(' ', city.name, city.asciiname, country.name) AS searchString
        FROM geonames_cities1000 AS city
            INNER JOIN geonames_countryinfo AS country
                ON city.iso_alpha2 = country.iso_alpha2
            LEFT OUTER JOIN geonames_admin1_codes_ascii as admin1
                ON admin1.code = CONCAT(city.iso_alpha2, '.', city.admin1_code)
        WHERE %s %s
        ORDER BY city.name ASC %s;
    `, main, secondary, limit)

    nstmt, err := sql.DB.PrepareNamed(query)
    if err != nil {
        return err
    }

    rows, err := nstmt.Queryx(queryParams)
    if err != nil {
        return err
    }

    results := []interface{}{}

    for rows.Next() {
        row := make(map[string]interface{})
        err := rows.MapScan(row)
        if err != nil {
            return err
        }
        results = append(results, row)
    }

    b, err := json.Marshal(results)
    if err != nil {
        return err
    }
    w.Write(b)
    return nil
}

Sending a POST request postman chrome plugin with these values:

keyword: "tron"
maxResults: 7
lastRequest: 0

gave this JSON output:

[
    {
        "admin1Name": "VXR0YXJhZGl0",
        "cityId": 1605268,
        "cityName": "VHJvbg==",
        "countryName": "VGhhaWxhbmQ=",
        "searchString": "VHJvbiBUcm9uIFRoYWlsYW5k"
    },
    {
        "admin1Name": "Q2FsYWJyaWE=",
        "cityId": 8949073,
        "cityName": "VHJvbmNh",
        "countryName": "SXRhbHk=",
        "searchString": "VHJvbmNhIFRyb25jYSBJdGFseQ=="
    },
    {
        "admin1Name": "QXJhZ29u",
        "cityId": 3107444,
        "cityName": "VHJvbmNow7Nu",
        "countryName": "U3BhaW4=",
        "searchString": "VHJvbmNow7NuIFRyb25jaG9uIFNwYWlu"
    },
    {
        "admin1Name": "UHVlYmxh",
        "cityId": 8859151,
        "cityName": "VHJvbmNvbmFs",
        "countryName": "TWV4aWNv",
        "searchString": "VHJvbmNvbmFsIFRyb25jb25hbCBNZXhpY28="
    },
    {
        "admin1Name": "U2NobGVzd2lnLUhvbHN0ZWlu",
        "cityId": 2821000,
        "cityName": "VHLDtm5kZWw=",
        "countryName": "R2VybWFueQ==",
        "searchString": "VHLDtm5kZWwgVHJvbmRlbCBHZXJtYW55"
    },
    {
        "admin1Name": "U8O4ci1UcsO4bmRlbGFn",
        "cityId": 3133880,
        "cityName": "VHJvbmRoZWlt",
        "countryName": "Tm9yd2F5",
        "searchString": "VHJvbmRoZWltIFRyb25kaGVpbSBOb3J3YXk="
    },
    {
        "admin1Name": "VG9uZ3Nh",
        "cityId": 1252408,
        "cityName": "VHJvbmdzYQ==",
        "countryName": "Qmh1dGFu",
        "searchString": "VHJvbmdzYSBUcm9uZ3NhIEJodXRhbg=="
    }
]

Why are the results like this? Putting the results in spew.Dump() will give something like this (I'm just outputting one of the rows):

(map[string]interface {}) (len=5) {
  (string) (len=11) "countryName": ([]uint8) (len=6 cap=6) {
   00000000  4e 6f 72 77 61 79                                 |Norway|
  },
  (string) (len=12) "searchString": ([]uint8) (len=26 cap=26) {
   00000000  54 72 6f 6e 64 68 65 69  6d 20 54 72 6f 6e 64 68  |Trondheim Trondh|
   00000010  65 69 6d 20 4e 6f 72 77  61 79                    |eim Norway|
  },
  (string) (len=6) "cityId": (int64) 3133880,
  (string) (len=8) "cityName": ([]uint8) (len=9 cap=9) {
   00000000  54 72 6f 6e 64 68 65 69  6d                       |Trondheim|
  },
  (string) (len=10) "admin1Name": ([]uint8) (len=15 cap=15) {
   00000000  53 c3 b8 72 2d 54 72 c3  b8 6e 64 65 6c 61 67     |S..r-Tr..ndelag|
  }

What do I do wrong here?'

EDIT:

I tried doing as Elwinar suggested with structs instead. But something really weird is going on.

This code works:

type City struct {
    AdminName    string `json:"admin1Name"   db:"admin1Name"`
    CityID       int64  `json:"cityId"       db:"cityId"`
    CityName     string `json:"cityName"     db:"cityName"`
    CountryName  string `json:"countryName"  db:"countryName"`
    SearchString string `json:"searchString" db:"searchString"`
}

But this code does not work and outputs error "missing destination name cityId":

type City struct {
    CityId       int64  `json:"cityId"       db:"cityId"`
    CityName     string `json:"cityName"     db:"cityName"`
    Admin1Name   string `json:"admin1Name"   db:"admin1Name"`
    CountryName  string `json:"countryName"  db:"countryName"`
    SearchString string `json:"searchString" db:"searchString"`
}

What is the difference?

Solution: Must separate the tags with space key. Can not do spacing with tab key, and can not use commas to separate tags.

  • 写回答

2条回答 默认 最新

  • donglaohua1671 2015-09-10 13:20
    关注

    As spew.Dump tells you, the SQL driver returns []uint8 for textual column. In fact, it is equivalent to []byte, which json.Marshal encode as base64 strings.

    The simplest way to work around that is to scan your rows into a real struct (which sqlx does quite well with StructScan), which will have string fields so that json.Marshal will display them as you expect. You can control the name of both the SQL and JSON names of the field using tags, so your entity can have conventionnal names in every language…

    Example:

    type City struct {
        AdminName    string `json:"admin1Name"   sql:"admin1Name"`
        CityID       int64  `json:"cityId"       sql:"cityId"`
        CityName     string `json:"cityName"     sql:"cityName"`
        CountryName  string `json:"countryName"  sql:"countryName"`
        SearchString string `json:"searchString" sql:"searchString"`
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题