从含同名字段的多关联表数据库取数的SQL最佳实践咨询

一、优先使用Dart生态的ORM库减少原生SQL编写 #

• 目前Dart移动端/桌面端数据库场景有成熟的ORM方案可选，首推Drift（原Moor）、Floor，这类库可以从根源上解决你的痛点：
  • 定义表结构时直接声明外键关联关系，关联查询时无需手动编写原生SQL，库会自动处理同名字段的别名生成、结果到结构化对象的映射，不会出现字段归属不确定的问题
  • 支持类型安全的查询语法，编译期就能发现SQL语法错误，避免运行时才暴露问题
• 示例逻辑（以Drift为例）：定义好各表结构和关联后，你的查询可以简化为以下写法，完全不需要手动写长SQL和别名：

final query = select(products)
  .join([
    innerJoin(prices, prices.productId.equalsExp(products.id)),
    innerJoin(stockBalances, stockBalances.productId.equalsExp(products.id)),
    // 其余关联表同理
  ])
  ..where(prices.priceTypeId.equals(1) & mainUnits.isGeneral.equals(1));
final result = await query.get();
// 返回结果直接是带关联对象的结构化数据，直接取对应表的字段即可，无需处理重名

二、如果必须使用原生SQL的最佳实践 #

• 坚决不要使用SELECT *：即使没有同名字段问题，SELECT *也存在查询冗余字段、表结构变更后易引发映射错误、IO性能浪费等问题，生产环境禁止使用这种写法
• 公共逻辑抽象：
  • 把常用的多表关联逻辑封装为数据库视图，提前在视图中处理好所有同名字段的别名，业务代码中直接查视图即可，只需要写条件语句，无需每次重复写长SQL和别名：

    -- 提前创建视图
    CREATE VIEW product_full_info AS
    select Products.Id,
    Products.Name as ProductName,
    Products.Description,
    Products.Article,
    Products.Barcode,
    Products.Country,
    Prices.PriceValue,
    MainUnits.Name as MainUnitsName,
    StockBalances.Count,
    WareHouses.Name as WareHousesName,
    WareHouses.Address,
    WareHouses.Telephone,
    PriceTypes.Name as PriceTypesName
    from Products
    inner join Prices on Products.Id = Prices.ProductId
    inner join StockBalances on Products.Id = StockBalances.ProductId
    inner join WareHouses on StockBalances.WareHouseId = WareHouses.Id
    inner join PriceTypes on Prices.PriceTypeId = PriceTypes.Id
    inner join MainUnits on Prices.MainUnitId = MainUnits.Id;
    

    业务代码直接调用：

    var response = await _db.rawQuery('SELECT * FROM product_full_info WHERE PriceTypeId = 1 and IsGeneral = 1');
    

  • 可以写轻量的代码生成工具，根据表结构自动生成带别名的select字段段，无需手动逐个编写
• 结果映射统一处理：不要直接用返回的动态Map，定义专属的DTO数据类，实现固定的fromMap方法，明确每个别名对应到DTO的属性，完全避免不可预测的映射行为

内容的提问来源于stack exchange，提问作者che4len