使用NodeJS在MySQL中运行SQL文件 [英] Use NodeJS to run an SQL file in MySQL

问题描述

我正在为Node.js使用 mysql 插件，到目前为止我所做的一切都很棒.

I am using the mysql plugin for nodejs and it is fantastic at doing everything I need so far.

但是我遇到了一个绊脚石.我创建了一个MySQL提供程序，该提供程序可以导出mysql池:

However I have come across a stumbling block. I have created a MySQL provider that exports a mysql pool:

var mysql = require('mysql');
var mysqlPool  = mysql.createPool({
  host     : '127.0.0.1',
  user     : 'root',
  password : ''
});

mysqlPool.getConnection(function(err, connection) {
  connection.query("INSERT INTO ....

我可以选择，创建，插入等一切正常，但是我遇到了一个任务，在该任务中，我想同时运行一个带有约10个不同命令的小型SQL字符串.我已经考虑过要执行以下其中一项操作:

I can select, create, insert, etc all fine, but I've come across a task where I would like to run a small SQL string with about 10 different commands together. I've thought about doing one of the following:

1. 使用mysql对数据库执行SQL文件
2. 运行query并启用multipleStatements

1. Execute a SQL file against a database using mysql
2. Run a query and enable multipleStatements

我已经编写了一些代码来作为子进程执行mysql，但是我真的很想避免这样做:

I have written some code to execute mysql as a child process, but I would really love to avoid doing this:

var cp = require("child_process");
var cmdLine = "mysql --user=autobuild --password=something newdb < load.sql";
cp.exec(cmdLine, function(error,stdout,stderr) {
    console.log(error,stdout,stderr);
});  

选项2的问题是我宁愿不为每个查询启用multiStatement，而只对一个特定的查询启用.我曾经考虑过要创建一个新的连接，但是只是在想其他方式可以做到这一点.

The problem with option two is I would rather not enable multipleStatements for every query, just this one particular one. I have thought about creating a new connection, but just thinking of other ways this could be done.

TL; DR? 使用NodeJS和MySQL如何在数据库中执行以下操作:

TL;DR? Using NodeJS and MySQL how can I execute the following into a database:

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20) );

CREATE TABLE sofa (name VARCHAR(20), owner VARCHAR(20) );

CREATE TABLE table (name VARCHAR(20), owner VARCHAR(20) );

非常感谢任何分享想法的人

Thanks so much for anyone who shares their ideas

推荐答案

您可以使用名为multipleStatements的连接选项:

You can use the connection option called multipleStatements:

// Works with the pool too.
var connection = mysql.createConnection({multipleStatements: true});

然后，您可以通过以下查询:

Then, you can pass the queries like these:

connection.query('CREATE 1; CREATE 2; SELECT 3;', function(err, results) {
  if (err) throw err;

  // `results` is an array with one element for every statement in the query:
  console.log(results[0]); // [create1]
  console.log(results[1]); // [create2]
  console.log(results[2]); // [select3]
});

这篇关于使用NodeJS在MySQL中运行SQL文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！