gorm columnsWithAlias=true 字段名返回 表名+列名

问题

第三方库

• gorm.io/driver/mysql
• gorm.io/gorm

使用gorm连接mysql进行连表查询的时候，如果返回结果中出现重名的字段名，并且查询前没有为这些重名的字段名取别名，则不能快速区分某个字段名是来自于哪张表的。 （这句话貌似有点绕...）

下面举个栗子。有两张表city和tmp_city。他们都有city_id、city_name、country_id字段。当同时查询两个表的这些字段的时候，结果如下：

func getColumns() {
   // 省略错误处理，下面会有完整代码
   db, _ := gorm.Open(mysql.Open("root:password@tcp(localhost:3306)/one_db?charset=utf8mb4&"), &gorm.Config{})
   rows, _ := db.Raw("SELECT * FROM city, tmp_city LIMIT 1;").Rows()
   columns, _ := rows.Columns()
   fmt.Println("columns: ", columns)
   // columns:  [city_id city_name country_id city_id city_name country_id c_time]
}

city和tmp_city信息如下：

mysql> desc city;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| city_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| city_name  | varchar(50) | NO   |     | NULL    |                |
| country_id | int(11)     | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc tmp_city;
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field      | Type        | Null | Key | Default           | Extra                       |
+------------+-------------+------+-----+-------------------+-----------------------------+
| city_id    | int(11)     | NO   | PRI | NULL              | auto_increment              |
| city_name  | varchar(50) | NO   |     | NULL              |                             |
| country_id | int(11)     | NO   |     | NULL              |                             |
| c_time     | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> select * from city, tmp_city limit 1;
+---------+-----------+------------+---------+-----------+------------+---------------------+
| city_id | city_name | country_id | city_id | city_name | country_id | c_time              |
+---------+-----------+------------+---------+-----------+------------+---------------------+
|       1 | italy     |          7 |       1 | italy     |          7 | 2022-01-13 09:18:50 |
+---------+-----------+------------+---------+-----------+------------+---------------------+

解决办法

gorm在连接数据库的时候加上columnsWithAlias=true参数，在获取字段名的时候就会显示为表名+列名。

package main

import (
   "fmt"

   "github.com/sirupsen/logrus"
   "gorm.io/driver/mysql"
   "gorm.io/gorm"
   "gorm.io/gorm/logger"
)

func DSN(host, port, username, password, databaseName string) string {
   // columnsWithAlias=true: add table alias to column names
   return fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=true&loc=Local&timeout=5s&readTimeout=5s&writeTimeout=5s&columnsWithAlias=true",
      username, password, host, port, databaseName)
}

type DBRunner struct {
   *gorm.DB
}

func NewDBRunner(host, port, username, password, databaseName string) (s *DBRunner, err error) {
   db, err := gorm.Open(mysql.Open(DSN(host, port, username, password, databaseName)), &gorm.Config{
      Logger: logger.Default.LogMode(logger.Info),
   })
   if err != nil {
      return nil, err
   }
   s = &DBRunner{DB: db}
   return
}

func (s *DBRunner) Close() {
   dbConn, err := s.DB.DB()
   if err != nil {
      logrus.Errorln("get db error: ", err)
      return
   }
   if err := dbConn.Close(); err != nil {
      logrus.Errorln("close db error: ", err)
   }
}

func (s *DBRunner) ExecuteAndPrintColumnName(querySQL string) error {
   rows, err := s.DB.Raw(querySQL).Rows()
   if err != nil {
      return fmt.Errorf("execute sql error: %v", err)
   }

   columns, err := rows.Columns()
   if err != nil {
      return fmt.Errorf("get columns error: %s", err)
   }
   fmt.Println("column names: ", columns)
   // column names:  [city.city_id city.city_name city.country_id tmp_city.city_id tmp_city.city_name tmp_city.country_id tmp_city.c_time]
   return nil
}

func main() {
   dbRunner, err := NewDBRunner("localhost", "3306", "root", "password", "one_db")
   if err != nil {
      logrus.Errorln(err)
      return
   }
   defer dbRunner.Close()
   if err := dbRunner.ExecuteAndPrintColumnName("SELECT * FROM city, tmp_city LIMIT 1;"); err != nil {
      logrus.Errorln(err)
   }
}

第三方库相关源码

第三库源码链接

• 字段说明
• 提取columnsWithAlias字段
• 拼接 列名+表名

// Config is a configuration parsed from a DSN string.
// If a new Config is created instead of being parsed from a DSN string,
// the NewConfig function should be used, which sets default values.
type Config struct {
   //...
   // 字段说明
   ColumnsWithAlias        bool // Prepend table alias to column names
   //...
}


func parseDSNParams(cfg *Config, params string) (err error) {
   for _, v := range strings.Split(params, "&") {
      param := strings.SplitN(v, "=", 2)
      if len(param) != 2 {
         continue
      }

      // cfg params
      switch value := param[1]; param[0] {
      // ...
      // 提取columnsWithAlias字段
      case "columnsWithAlias":
         var isBool bool
         cfg.ColumnsWithAlias, isBool = readBool(value)
         if !isBool {
            return errors.New("invalid bool value: " + value)
         }
      // ...
}


func (rows *mysqlRows) Columns() []string {
   if rows.rs.columnNames != nil {
      return rows.rs.columnNames
   }

   columns := make([]string, len(rows.rs.columns))
   if rows.mc != nil && rows.mc.cfg.ColumnsWithAlias {
      for i := range columns {
         if tableName := rows.rs.columns[i].tableName; len(tableName) > 0 {
            // 拼接 表名 + 列名
            columns[i] = tableName + "." + rows.rs.columns[i].name
         } else {
            columns[i] = rows.rs.columns[i].name
         }
      }
   } else {
      for i := range columns {
         columns[i] = rows.rs.columns[i].name
      }
   }

   rows.rs.columnNames = columns
   return columns
}