Sequelize关联查询报错：未知列Song.ArtistIDARTIST/PlaylistIDPLAYLIST

我正在使用Sequelize进行MySQL查询，已定义音乐播放列表相关模型（Artist、Playlist、Song）并配置关联关系：

Artist模型 #

export interface IArtist {
    ID_ARTIST: number,
    NAME: string
}

export const Artist = seq.define<Model<IArtist>>("Artist", {
    ID_ARTIST: {
        primaryKey: true,
        type: DataTypes.INTEGER,
        allowNull: false,
        autoIncrement: true
    },
    NAME: DataTypes.STRING(30)
}, {
    timestamps: true,
    createdAt: true,
    updatedAt: false,
    tableName: "ARTISTS",
})

Playlist模型 #

export interface IPlaylist {
    ID_PLAYLIST: number,
    NAME: string
}

export const PlayList = seq.define<Model<IPlaylist>>("Playlist", {
    ID_PLAYLIST: {
        primaryKey: true,
        type: DataTypes.INTEGER,
        allowNull: false,
        autoIncrement: true,
    },
    NAME: DataTypes.STRING(20)
}, {
    timestamps: true,
    createdAt: true,
    updatedAt: false,
    tableName: "PLAYLISTS"
})

Song模型 #

export interface ISong {
    ID_SONG: number,
    ID_ARTIST: number,
    ID_PLAYLIST: number,
    DURATION: number,
    NAME: string
}

export const Song = seq.define<Model<ISong>>("Song", {
    ID_SONG: {
        primaryKey: true,
        type: DataTypes.INTEGER,
        allowNull: false,
        autoIncrement: true
    },
    ID_ARTIST: {
        type: DataTypes.INTEGER,
        references: {
            model: Artist,
            key: "ID_ARTIST"
        }
    },
    ID_PLAYLIST: {
        type: DataTypes.INTEGER,
        references: {
            model: PlayList,
            key: "ID_PLAYLIST"
        }
    },
    DURATION: DataTypes.INTEGER,
    NAME: DataTypes.STRING(40)
}, {
    timestamps: true,
    updatedAt: false,
    tableName: "SONGS"
})

关联配置 #

Artist.hasMany(Song)
PlayList.hasMany(Song)
Song.belongsTo(Artist, { foreignKey: "ID_ARTIST" })
Song.belongsTo(PlayList, { foreignKey: "ID_PLAYLIST" })

执行以下findAll查询时：

const query = await Song.findAll({
      include: {
        model: Artist,
        required: true
      },
      where: {
        ID_ARTIST: idArtist
      }
    })

出现错误：sqlMessage: "Unknown column 'Song.ArtistIDARTIST' in 'field list'"，Sequelize生成的SQL中包含不存在的Song.ArtistIDARTIST和Song.PlaylistIDPLAYLIST列。请问为何在未使用attributes字段时会出现此问题？

原因分析 #

问题出在关联配置不匹配：

• 定义Artist.hasMany(Song)时，Sequelize默认会用模型名+主键名生成外键（即ArtistIDARTIST），但你在Song模型里实际使用的外键是ID_ARTIST。
• 虽然Song.belongsTo(Artist)中指定了正确的外键，但对应的hasMany关联未同步指定外键，导致Sequelize生成查询时混淆了外键字段名，错误引用了不存在的列。

修复步骤 #

需要在hasMany关联中也明确指定外键，保持关联两端配置一致：

修改关联配置为：

Artist.hasMany(Song, { foreignKey: "ID_ARTIST" })
PlayList.hasMany(Song, { foreignKey: "ID_PLAYLIST" })
Song.belongsTo(Artist, { foreignKey: "ID_ARTIST" })
Song.belongsTo(PlayList, { foreignKey: "ID_PLAYLIST" })

额外说明 #

Sequelize的关联要求hasMany和belongsTo两端的外键配置完全匹配，否则会触发默认规则生成错误的外键名。即使belongsTo端配置正确，hasMany端不匹配的话，依然会导致查询时引用不存在的列。

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