mysql - NodeJs Mysql错误: ER_PARSE_ERROR Insert to multiple table

Question

我正在尝试使用一个查询将一个文件导入多个表。我尝试在 sequelpro 查询成功。但是当我在 nodejs 中实现时，我收到错误 ER_PARSE_ERROR。

这是我的 table :

CREATE TABLE `realisasi` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `periode` date NOT NULL,
  `totalpendapatan` float NOT NULL,
  `kwhpenjualan` float NOT NULL,
  `totalpelanggan` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `periode` (`periode`),
  CONSTRAINT `fk_periode` FOREIGN KEY (`periode`) REFERENCES `target_kinerja` (`periode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `target_kinerja` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `periode` date NOT NULL,
  `prediksi_pendapatan` float DEFAULT NULL,
  `kwh_penjualan` float NOT NULL,
  `total_pelanggan` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `periode` (`periode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

此 nodeJS 代码导入数据并尝试插入到多个表。

const storage = multer.memoryStorage();
const upload = multer({ storage }).single("userFile");

const buildInsertSQL = (
  periode,
  totalpendapatan,
  kwhpenjualan,
  totalpelanggan,
  kwh_penjualan,
  total_pelanggan

) => {
  return `INSERT INTO target_kinerja VALUES("${periode}",NULL,"${kwh_penjualan}","${total_pelanggan}"); 
INSERT INTO realisasi VALUES((SELECT periode from target_kinerja 
where periode = "${periode}"),"${totalpendapatan}","${kwhpenjualan}","${totalpelanggan}");
`;
};

app.post("/api/file", upload, (req, res) => {
  const data = req.file.buffer.toString();

  const [csv_header, ...rows] = data.split("\n");

  _.forEach(rows, row => {
    const [periode, totalpendapatan, kwhpenjualan, totalpelanggan, kwh_penjualan, total_pelanggan] = row.split(
      ","
    );

    runQueries(
      buildInsertSQL(
        formatDateForSQL(periode.trim()),
        Number.parseFloat(totalpendapatan.trim()),
        Number.parseFloat(kwhpenjualan.trim()),
        Number.parseInt(totalpelanggan.trim(), 10),
        Number.parseFloat(kwh_penjualan.trim()),
        Number.parseInt(total_pelanggan.trim(), 10)
      )
    );
  });

  res.redirect("/master_data");
});

但是我在进行这样的按摩时遇到了错误:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO realisasi VALUES((SELECT periode from target_kinerja where periode =' at line 1
    at Query.Sequence._packetToError (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/Connection.js:91:28)
    at Socket.<anonymous> (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/Connection.js:525:10)
    at Socket.emit (events.js:203:13)
    at addChunk (_stream_readable.js:294:12)
    --------------------
    at Protocol._enqueue (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/Users/putri/Documents/code/prediksi-app/node_modules/mysql/lib/Connection.js:201:25)
    at runQueries (/Users/putri/Documents/code/prediksi-app/index.js:126:14)
    at /Users/putri/Documents/code/prediksi-app/index.js:110:5
    at arrayEach (/Users/putri/Documents/code/prediksi-app/node_modules/lodash/lodash.js:516:11)
    at Function.forEach (/Users/putri/Documents/code/prediksi-app/node_modules/lodash/lodash.js:9342:14)
    at /Users/putri/Documents/code/prediksi-app/index.js:105:5
    at Layer.handle [as handle_request] (/Users/putri/Documents/code/prediksi-app/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/putri/Documents/code/prediksi-app/node_modules/express/lib/router/route.js:137:13)
    at Array.<anonymous> (/Users/putri/Documents/code/prediksi-app/node_modules/multer/lib/make-middleware.js:53:37) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO realisasi VALUES((SELECT periode from target_kinerja where periode =' at line 1",
  sqlState: '42000',
  index: 0,
  sql: 'INSERT INTO target_kinerja VALUES("2019/02/05",NULL,"3","120"); INSERT INTO realisasi VALUES((SELECT periode from target_kinerja where periode = "2019/02/05"),"3.98","3.53","152");'
}

你能帮忙解决这个问题吗？

Answer 1

我看到的是

INSERT ...; INSERT ...;
            ^  The error is at, or possibly right before this point.

不要将两个语句串在一起；分别运行它们。