时间戳

首页 > 解决方案 > 使用 mysql2 运行大型 INSERT 查询时出现异常

mysql - 使用 mysql2 运行大型 INSERT 查询时出现异常

问题描述

我使用mysql2包对 MySQL 服务器运行查询

这是连接设置

getConnection: function () {


   let host = "hostname.com";
   let database = "database_name";
   let username = "username";
   let password = "pwd";


   let con = mysql.createConnection({
      host: host,
      user: username,
      password: password,
      database: database,
      connectTimeout: 15000
   });

   return con;
},

它适用于所有查询，但INSERT具有 26 个参数的查询除外：

    recordCreate: function (con, param1, param2,
       param3,  param4,  param5,  param6,  param7,
       param8,  param9,  param10, param11, param12,
       param13, param14, param15, param16, param17,
       param18, param19, param20, param21, param22,
       param23, param24, param25, param26, callback) {
       try {
         con.connect(function (err, result) {
                 try {
                      if (err) throw err;
                      con.query(“INSERT INTO tbldata (param1,
                           param2,  param3,  param4,  param5,  param6,
                           param7,  param8,  param9,  param10, param11,
                           param12, param13, param14, param15, param16,
                           param17, param18, param19, param20, param21,
                           param22, param23, param24, param25, param26
                   ) VALUES 
                   (?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?)”,[param1,  param2,  param3,  param4,  param5,
                         param6,  param7,  param8,  param9,  param10,
                         param11, param12, param13, param14, param15,
                         param16, param17, param18, param19, param20,
                         param21, param22, param23, param24, param25,
                         param26]
       , function (err) {
          try {
             if (err) throw err;
             callback(true);
          } catch (err) {
             callback(err);
          } finally {
             con.close();
          }
  });

我不断收到以下错误消息：

C:\MyProjects\TMS\node_modules\mysql2\lib\commands\command.js:30 this.onResult(err); ^

TypeError: this.onResult 不是 Connection.handlePacket (C:\MyProjects\TMS\node_modules) 的 Query.execute (C:\MyProjects\TMS\node_modules\mysql2\lib\commands\command.js:30:14) \mysql2\lib\connection.js:449:32) 在 PacketParser.Connection.packetParser.p [as onPacket] (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js:72:12) 在 PacketParser。 executeStart (C:\MyProjects\TMS\node_modules\mysql2\lib\packet_parser.js:76:16) 在 Socket.Connection.stream.on.data (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js :79:25) at Socket.emit (events.js:188:13) at addChunk (_stream_readable.js:288:12) at readableAddChunk (_stream_readable.js:269:11) at Socket.Readable.push (_stream_readable.js :224:10) 在 TCP.onStreamRead [as onread] (internal/stream_base_commons.js:145:17)

进程以退出代码 1 结束

重要的是要说明这些数据元素（参数）非常小。我们在这里讨论短 10 个字符的字符串和数字

例如

INSERT INTO tbldata (param1,  param2,  param3,  param4,  param5,
                     param6,  param7,  param8,  param9,  param10,
                     param11, param12, param13, param14, param15,
                     param16, param17, param18, param19, param20,
                     param21, param22, param23, param24, param25,
                     param26) VALUES 
                    (51, 'Test 51', '', '2019-12-01', '2019-12-01',
                      1, 1, 1, 3, 3, 1, 1, 0, 0, 0, 0, 0, 1, 1, 0, 
                      0, 1, 1, 1, 3, 3);

我尝试在 MySQL 服务器上运行示例查询，它工作正常

标签： mysqlnode.jsmysql2

解决方案

缩小查询范围后，我意识到这是一个愚蠢的语法问题。我要感谢Nico Haase并Martin为我的“头脑风暴”提供帮助。

我很欣赏mysql2团队的工作。我也希望他们能解决query方法获取超过 2 个参数的问题。

con.query("INSERT INTO tbldata (" +
                                "param1, " +
                                "param2, " +
                                "param3, " +
                                "param4, ", -- here is the syntax issue. Comma instead of plus
                                "param5) VALUES (?, ?, " +
                                "?, ?, " +
                                "?, ?, " +
                                "?)",
                                [param1,
                                    param2,
                                    param3,
                                    param4,
                                    param5]

推荐阅读