go - sqlx 库给出了奇怪的 base64 编码结果

Question

我正在使用来自 http://jmoiron.github.io/sqlx/ 的这个库做一个查询。按照文档，我找到了下面的代码。

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
}

使用这些值发送 POST 请求 postman chrome 插件:

keyword: "tron"
maxResults: 7
lastRequest: 0

给出这个 JSON 输出:

[
    {
        "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=="
    }
]

为什么会出现这样的结果？将结果放入 spew.Dump()会给出这样的结果(我只是输出其中一行):

(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|
  }

我做错了什么？'

编辑:

我尝试按照 Elwinar 的建议使用结构来代替。但是一些非常奇怪的事情正在发生。

此代码有效:

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"`
}

但此代码不起作用并输出错误“缺少目的地名称 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"`
}

有什么区别？

解决方案:必须用空格键分隔标签。不能用tab键做空格，不能用逗号分隔标签。

Answer 1

正如 spew.Dump 告诉您的那样，SQL 驱动程序为文本列返回 []uint8。实际上，它相当于[]byte，将json.Marshal编码为base64字符串。

解决这个问题的最简单方法是将您的行扫描到一个真正的结构中(sqlx 使用 StructScan 做得很好)，它将具有 string 字段，以便json.Marshal 将按照您的预期显示它们。您可以使用标签控制字段的 SQL 名称和 JSON 名称，因此您的实体可以具有每种语言的约定名称......

例子:

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"`
}