doujieyu7062
doujieyu7062
采纳率0%
2015-09-10 12:22

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

  • IT行业问题
  • 计算机技术
  • it技术
  • 编程语言问答
  • 互联网问答
已采纳

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 donglaohua1671 6年前

    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"`
    }
    
    点赞 评论 复制链接分享
  • doufan9290 doufan9290 6年前

    Package encoding/json will marshal []byte slices base64 encoded (see http://golang.org/pkg/encoding/json/#Marshal). So the problem is just in the output via json.Marshal(results).

    Somewhere your code calls a function which produces a []byte which is nicely hidden in one of these {}interfaces . Get rid of each of the empty interfaces, use strings where appropriate and the problem will either vanish or the solution will be obvious.

    (Calling json.Marshal and outputting the result for debug purpose might not be a good idea, especially if you do not really know what you are actuall marshaling.)

    点赞 评论 复制链接分享

为你推荐