使用 NodeJS 在 MySQL 中运行 SQL 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22276763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use NodeJS to run an SQL file in MySQL
提问by Mark
I am using the mysqlplugin for nodejs and it is fantastic at doing everything I need so far.
我正在使用nodejs的mysql插件，到目前为止，它非常擅长做我需要的一切。
However I have come across a stumbling block. I have created a MySQL provider that exports a mysql pool:
然而，我遇到了一个绊脚石。我创建了一个导出 mysql 池的 MySQL 提供程序：
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 ....
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:
我可以选择、创建、插入等一切正常，但我遇到了一个任务，我想运行一个包含大约 10 个不同命令的小型 SQL 字符串。我已经考虑过执行以下操作之一：
- Execute a SQL file against a database using mysql
- Run a
queryand enablemultipleStatements
- 使用 mysql 对数据库执行 SQL 文件
- 运行
query并启用multipleStatements
I have written some code to execute mysqlas a child process, but I would really love to avoid doing this:
我已经编写了一些代码mysql作为子进程执行，但我真的很想避免这样做：
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);
});
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.
选项二的问题是我宁愿不为每个查询启用 multipleStatements，只启用一个特定的查询。我想过创建一个新的连接，但只是想其他方法可以做到这一点。
TL;DR?Using NodeJS and MySQL how can I execute the following into a database:
特尔；博士？使用 NodeJS 和 MySQL 如何将以下内容执行到数据库中：
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
非常感谢任何分享他们想法的人
回答by jmingov
You can use the connection option called multipleStatements:
您可以使用名为的连接选项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]
});
回答by Justin
Here is a big .sqlfile friendly way to progmatically execute multiple queries against MySQL without using the multipleStatementsproperty and a massive buffer. Please note this is not the most efficient way to upload to mysql.
这是一种大.sql文件友好的方式，可以在不使用multipleStatements属性和大量缓冲区的情况下以编程方式对 MySQL 执行多个查询。请注意，这不是上传到 mysql 的最有效方式。
var mysql = require('mysql');
var fs = require('fs');
var readline = require('readline');
var myCon = mysql.createConnection({
host: 'localhost',
port: '3306',
database: '',
user: '',
password: ''
});
var rl = readline.createInterface({
input: fs.createReadStream('./myFile.sql'),
terminal: false
});
rl.on('line', function(chunk){
myCon.query(chunk.toString('ascii'), function(err, sets, fields){
if(err) console.log(err);
});
});
rl.on('close', function(){
console.log("finished");
myCon.end();
});
回答by TVG
This will do the trick:
这将解决问题：
var express = require("express");
var bodyParser = require("body-parser");
var mysql = require('mysql');
var fs = require('fs');
var app = express();
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(express.static(__dirname));
var defaultConnection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database: 'utpDatabase'
});
function dbCall_file (endpoint, operation, settings, filename){
app.post(endpoint, function(request, response){
var data = request.body;
var path = 'path/to/queries/' + filename
var connection = (settings == 'default') ? defaultConnection : settings;
var callback = function(arg){
var query = arg.replace(/{{[ ]{0,2}([a-zA-Z0-9\.\_\-]*)[ ]{0,2}}}/g, function(str, mch){ return data[mch]});
connection.query(query, function(err, rows){
if (!err){
var toClient = (operation == 'select') ? rows : {success: true};
response.send(toClient);
} else {
console.log(err);
response.send({error: err, query: query});
}
});
};
fs.readFile(path, 'utf8', function(err, data){
if (!err){
callback(data);
} else {
callback(err);
}
});
});
};
Then in your .sql file wrap your node variables in double curlies- for example, if you want to query first names for node variable data.firstName from your post call:
然后在您的 .sql 文件中将您的节点变量包裹在双卷曲中 - 例如，如果您想从您的 post 调用中查询节点变量 data.firstName 的名字：
SELECT * FROM users WHERE name={{ firstName }}
