javascript 使用nodejs查询mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20780587/
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 query mysql database
提问by Arnold
I use mysql module nodejs-mysqlI have two tables, Their struct is like this:
我使用 mysql 模块nodejs-mysql我有两个表，它们的结构是这样的：
Table nicks
表刻痕
id |nick |
--------------
1 |Arnold |
2 |Bob |
Table order
表顺序
nick |money |
---------------
Arnold |12 |
Arnold |43 |
Arnold |3 |
Bob |32 |
Bob |2 |
I want get a json object whose struct is like this:
我想要一个 json 对象，它的结构是这样的：
[
{id:1, nick:'Arnold', order:[{money:12},{money:43},{money:3}]},
{id:2, nick:'Bob', order[{money:32},{money:2}]}
]
so what should I do?I use nodejs
那我该怎么办？我使用 nodejs
what I have try:
我有什么尝试：
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'example.org',
db : 'db'
user : 'user',
password : 'secret'
});
connection.connect();
connection.query('select * from nicks',function(err,data){
//here I travese the data array,and select the order table to get the money filed data.
});
I know how to create a query with node.js, I just don't know a method to get the results I want.I don't know how to make a proper query.
我知道如何使用 node.js 创建查询，我只是不知道获得我想要的结果的方法。我不知道如何进行正确的查询。
回答by robertklep
Here's another solution:
这是另一个解决方案：
var mysql = require('mysql');
var conn = mysql.createConnection({
host : 'localhost',
database : 'test',
});
var query = ' \
SELECT id, nicks.nick, GROUP_CONCAT(money) AS money \
FROM nicks, orders \
WHERE orders.nick = nicks.nick \
GROUP BY id';
conn.query(query, function(err, rows, fields) {
rows.forEach(function(row) {
row.order = row.money.toString().split(',').map(function(value) {
return { money : Number(value) };
});
delete row.money;
});
// as an example, we'll print the object as JSON
console.log(JSON.stringify(rows, null, 2));
});
回答by kaushikb9
Follow the documentation at https://github.com/felixge/node-mysqlYou need to setup a connection and query the db
按照https://github.com/felixge/node-mysql 上的文档 您需要设置连接并查询数据库
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'example.org',
user : 'bob',
password : 'secret'
});
var queryString = "SELECT * FROM nicks n JOIN order o ON n.nick=o.nick";
connection.query(queryString, function(err, rows) {
var outputJSON = [];
for row in rows{
outputJSON.push(row);
}
return outputJSON.toJSON()
});
You need to implement the function toJSON that formats your output by picking only the desired fields you need for you JSON
您需要通过仅选择您需要的 JSON 字段来实现格式化输出的函数 toJSON
