A column called ‘id‘ was added to the attributes of ‘users‘ but not marked with ‘primaryKey: true‘

最新推荐文章于 2022-10-05 15:13:28 发布

无知散人

最新推荐文章于 2022-10-05 15:13:28 发布

阅读量1.9k

点赞数

分类专栏： sequelize nodejs 文章标签： node.js vue.js

本文链接：https://blog.csdn.net/qq_41801484/article/details/112237931

版权

sequelize 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

nodejs

1 篇文章 0 订阅

订阅专栏

router.post('/login', jsonParser, function (req, res, next) {
  try {
    const User = sequelize.define('User', {
      id:DataTypes.INTEGER,
      name: DataTypes.STRING,
      code: DataTypes.STRING,
      create_date: DataTypes.DATE,
      update_date: DataTypes.DATE
    }, {
      tableName: 'users', // 指定表名
    })
    User.findAll().then(sss => {
      console.log('成功', sss)
      res.json(sss);
    }).catch(ert => {
      console.log('失败', ert)
      res.json(ert);
    })
  } catch (err) {
    console.log("-------------错误信息-------------")
    console.log(err)
  }
});

以上是按找sequelize官网写的一个小示例，

1.首先使用 sequeize.define创建一个模型，列声明也是采用官网推荐的简写语法，
2.User.findAll 尝试查询出users表中所有数据

此时报错

Error: A column called 'id' was added to the attributes of 'users' but not marked with 'primaryKey: true'
    at Function._addDefaultAttributes (C:\Users\11783\Desktop\tocity-service\node_modules\sequelize\lib\model.js:228:15)
    at Function.init (C:\Users\11783\Desktop\tocity-service\node_modules\sequelize\lib\model.js:1060:10)
    at Sequelize.define (C:\Users\11783\Desktop\tocity-service\node_modules\sequelize\lib\sequelize.js:440:11)
    at C:\Users\11783\Desktop\tocity-service\controller\users.js:29:26
    at Layer.handle [as handle_request] (C:\Users\11783\Desktop\tocity-service\node_modules\express\lib\router\layer.js:95:5)
    at next (C:\Users\11783\Desktop\tocity-service\node_modules\express\lib\router\route.js:137:13)
    at jsonParser (C:\Users\11783\Desktop\tocity-service\node_modules\body-parser\lib\types\json.js:101:7)
    at Layer.handle [as handle_request] (C:\Users\11783\Desktop\tocity-service\node_modules\express\lib\router\layer.js:95:5)
    at next (C:\Users\11783\Desktop\tocity-service\node_modules\express\lib\router\route.js:137:13)
    at Route.dispatch (C:\Users\11783\Desktop\tocity-service\node_modules\express\lib\router\route.js:112:3)

大致意思是说我们数据库中users表中id字段是主键，所以我们在创建模型声明列时，也应该注明该列为主键
修改代码：

router.post('/login', jsonParser, function (req, res, next) {
  try {
    const User = sequelize.define('User', {
    // 注明主键
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true
      },
      name: DataTypes.STRING,
      code: DataTypes.STRING,
      create_date: DataTypes.DATE,
      update_date: DataTypes.DATE
    }, {
      tableName: 'users', // 指定表名
    })
    User.findAll().then(sss => {
      console.log('成功', sss)
      res.json(sss);
    }).catch(ert => {
      console.log('失败', ert)
      res.json(ert);
    })
  } catch (err) {
    console.log("-------------错误信息-------------")
    console.log(err)
  }
});

问题二

问题描述

Executing (default): SELECT `id`, `name`, `code`, `create_date`, `update_date`, `createdAt`, `updatedAt` FROM `users` AS `User`;
失败 DatabaseError [SequelizeDatabaseError]: Unknown column 'createdAt' in 'field list'
    at Query.formatError (C:\Users\11783\Desktop\tocity-service\node_modules\sequelize\lib\dialects\mysql\query.js:239:16)
    at Query.run (C:\Users\11783\Desktop\tocity-service\node_modules\sequelize\lib\dialects\mysql\query.js:54:18)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  name: 'SequelizeDatabaseError',
  parent: Error: Unknown column 'createdAt' in 'field list'
      at Packet.asError (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\packets\packet.js:712:17)
      at Query.execute (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\commands\command.js:28:26)
      at Connection.handlePacket (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\connection.js:425:32)     
      at PacketParser.onPacket (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\connection.js:75:12)
      at PacketParser.executeStart (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\packet_parser.js:75:16)
      at Socket.<anonymous> (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\connection.js:82:25)
      at Socket.emit (events.js:210:5)
      at addChunk (_stream_readable.js:309:12)
      at readableAddChunk (_stream_readable.js:290:11)
      at Socket.Readable.push (_stream_readable.js:224:10) {
    code: 'ER_BAD_FIELD_ERROR',
    errno: 1054,
    sqlState: '42S22',
    sqlMessage: "Unknown column 'createdAt' in 'field list'",
    sql: 'SELECT `id`, `name`, `code`, `create_date`, `update_date`, `createdAt`, `updatedAt` FROM `users` AS `User`;',
    parameters: undefined
  },
  original: Error: Unknown column 'createdAt' in 'field list'
      at Packet.asError (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\packets\packet.js:712:17)
      at Query.execute (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\commands\command.js:28:26)
      at Connection.handlePacket (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\connection.js:425:32)
      at PacketParser.onPacket (C:\Users\11783\Desktop\tocity-service\node_modules\mysql2\lib\connection.js:75:12)
      at Socket.emit (events.js:210:5)
      at addChunk (_stream_readable.js:309:12)
      at readableAddChunk (_stream_readable.js:290:11)
      at Socket.Readable.push (_stream_readable.js:224:10) {
    code: 'ER_BAD_FIELD_ERROR',
    errno: 1054,
    sqlState: '42S22',
    sqlMessage: "Unknown column 'createdAt' in 'field list'",
    sql: 'SELECT `id`, `name`, `code`, `create_date`, `update_date`, `createdAt`, `updatedAt` FROM `users` AS `User`;',
    parameters: undefined
  },
  sql: 'SELECT `id`, `name`, `code`, `create_date`, `update_date`, `createdAt`, `updatedAt` FROM `users` AS `User`;',
  parameters: undefined
}

当你完全按着表列字段创建模型时，然后去执行代码的时候可能就会报这个错，仔细观察会发现生成的sql语句
中多了createdAt和updatedAt这两个列字段，这是因为sequelize会自动生成这两个字段，文档上也有说明，我们可以手动关闭或者将这两个字段改成我们自由的列名就可以了
修改后:

router.post('/login', jsonParser, function (req, res, next) {
  try {
    const User = sequelize.define('User', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true
      },
      name: DataTypes.STRING,
      code: DataTypes.STRING,
      // create_date: DataTypes.DATE,
      // update_date: DataTypes.DATE
    }, {
      tableName: 'users', // 指定表名
      // 修改自定义列名
      updatedAt: 'update_date',
      createdAt: 'create_date',
    })
    User.findAll().then(sss => {
      console.log('成功', sss)
      res.json(sss);
    }).catch(ert => {
      console.log('失败', ert)
      res.json(ert);
    })
  } catch (err) {
    console.log("-------------错误信息-------------")
    console.log(err)
  }
});