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

Question

我使用 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;
},

它适用于所有查询，除了带有 26 个参数的 INSERT 查询:

    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 is not a function at Query.execute (C:\MyProjects\TMS\node_modules\mysql2\lib\commands\command.js:30:14) at Connection.handlePacket (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js:449:32) at PacketParser.Connection.packetParser.p [as onPacket] (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js:72:12) at PacketParser.executeStart (C:\MyProjects\TMS\node_modules\mysql2\lib\packet_parser.js:76:16) at 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) at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:145:17)

Process finished with exit code 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 服务器上运行示例查询，它工作正常

Answer 1

缩小查询范围后，我意识到这是一个愚蠢的语法问题。我要感谢 Nico Haase 和 Martin 帮助我进行“头脑 Storm ”。

我感谢 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]