mysql - node.js - 如何扩展 mysql 结果对象

Question

我是一名长期的 PHP 开发人员，今天决定尝试一下 Node.js 和 Express。我正在尝试找到将结果合并到单个对象中的最佳方法。我可能会像 PHP 开发人员一样处理这个问题，并且需要一些帮助。提前致谢。

app.get('/api/posts', function(req, res) {
    url_parts = url.parse(req.url, true)
    query = url_parts.query
    current_page = query.page || 1
    items_per_page = 50
    start_index = (current_page - 1) * items_per_page
    max_page = 1000

    var getPosts = {
        db: function() {
            var posts = {}
            connection.query('SELECT COUNT(*) AS count FROM rss', function(err, rows1, fields) {
                if (!err)
                {
                    total_pages = Math.ceil(rows1[0].count / items_per_page)

                    if (start_index < rows1[0].count || start_index < max_page)
                    {
                        sql = 'SELECT id, title, image, width, height, url FROM rss ORDER BY date DESC LIMIT '+start_index+', '+items_per_page
                        connection.query(sql, function(err, rows2) {
                            if (!err)
                            {
                                for (var i in rows2)
                                {
                                    comments = 'SELECT comment FROM comments WHERE section_id = '+rows2[i].id+' ORDER BY date DESC'
                                    connection.query(comments, function(err2, rows3) {
                                        //COMBINE RESULTS HERE
                                        //rows2[i].comments = rows3
                                    });
                                }

                                //res.json(rows2)
                               // DISPLAY RESULTS HERE
                            }
                            else
                            {
                                console.log(err)
                            }
                        });
                    }
                }
                else
                {
                    console.log(err)
                }
            });
        }
    }

    getPosts.db();
});

Answer 1

无论你做什么，都不要使用 Node 创建循环。这只会创建一个阻塞调用。您需要使用回调。您可以手动执行这些操作或使用有帮助的库。我喜欢异步(https://github.com/caolan/async)，所以这里将循环代码替换为异步。

    async.forEachSeries(rows1, function(row, callback) {

       sql = 'SELECT id, title, image, width, height, url FROM rss ORDER BY date DESC LIMIT '+start_index+', '+ items_per_page
       connection.query(sql, function(err, rows2) {
           if (!err) {
               async.forEachSeries(rows2, function(row2, callback2) {
                      // do whatever processing you will do here.
                      // now call the callback2 to signal you have finished processing.
                      callback2();
               },function(err) {
                      // handle any errors that might occur in your 'loop' here.
               });
           }
    callback()
},function(err) {


});

对代码的另一个建议是永远不要手动构建 SQL。使用 ？参数。例如:

connection.query("SELECT id, title, image, width, height, url FROM rss ORDER BY date DESC LIMIT ?,?", [start_index, items_per_page], function(err, rows, fields) {
...do your processing
}

希望这有帮助。