带有 Node.js 的 MySQL

I've just started getting into Node.js. I come from a PHP background, so I'm fairly used to using MySQL for all my database needs.

How can I use MySQL with Node.js?

转载于:https://stackoverflow.com/questions/5818312/mysql-with-node-js

csdnceshi74
7*4 Personal preference I suppose, it depends on the ecosystem you're developing in, if you're in a team, etc. This original post is ancient, and a lot has changed in the Node landscape where its far more commonplace for front and back end work. I'd say if you have time to give Node a go, and its great paired with things like socket.io for real-time web sockets.
2 年多之前 回复
csdnceshi67
bug^君 What do you prefer, PHP or Node.js? I hopped into PHP/MySQL, but am thinking of switching to node since it would prolly feel much more natural considering the syntax is JS syntax
2 年多之前 回复
csdnceshi74
7*4 actually went with node-mysql for a few reasons, mainly because it's in fairly active development, and seems to be the most widely used. I also really like the multipleStatements function.
接近 8 年之前 回复
csdnceshi77
狐狸.fox what did you end up going with? there's some good information below, I would be interested in hearing what your experiences were
接近 8 年之前 回复

7个回答

Check out the node.js module list

  • node-mysql — A node.js module implementing the MySQL protocol
  • node-mysql2 — Yet another pure JS async driver. Pipelining, prepared statements.
  • node-mysql-libmysqlclient — MySQL asynchronous bindings based on libmysqlclient

node-mysql looks simple enough:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret',
});

connection.connect(function(err) {
  // connected! (unless `err` is set)
});

Queries:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
csdnceshi58
Didn"t forge Well, the site is now updated. You'll have to search your (required) module at node-modules.com
大约 3 年之前 回复
csdnceshi60
℡Wang Yan That syntax you have there isn't working for me...? it keeps on trying to perform a string replacment on the object.
接近 6 年之前 回复
weixin_41568174
from.. Plus you can get your database name adding 'database' to the connection object
接近 7 年之前 回复
csdnceshi50
三生石@ USE 'SET' not 'VALUES'... haha... this solved my issue
接近 7 年之前 回复
csdnceshi67
bug^君 try using <> instead of !=
大约 7 年之前 回复
csdnceshi52
妄徒之命 how can we use != in query i tried "id != ?" it returns error
大约 7 年之前 回复
weixin_41568184
叼花硬汉 +1 for node-mysql. Also, node-mysql2 appears to be an option (link on the node-mysql-native page) for prepared statements. I'm using it now, and I notice no problems (no idea if it works correctly under the hood).
7 年多之前 回复
csdnceshi70
笑故挽风 The '?' in the query always escapes the input. Important to prevent SQL-Injections - you can use the escape function instead to manually escape dynamic params.
7 年多之前 回复
csdnceshi73
喵-见缝插针 Makes sense, +1'd for the same reason (and for not having to change syntax if/when prepared statements are implemented).
7 年多之前 回复
csdnceshi71
Memor.の Hmm, I should have used the term query builder. My point was that with this syntax, you can build a safer query with minimal code.
7 年多之前 回复
csdnceshi73
喵-见缝插针 I was under the impression that node-mysql does not yet have prepared statements implemented. The syntax just looks similar. Instead, it appears that, for now, special characters are being escaped.
7 年多之前 回复
csdnceshi78
程序go +1 for node-mysql too. What can better than just requireing a javascript library
大约 8 年之前 回复
csdnceshi56
lrony* github.com/bminer/node-mysql-queues for transactions and multiple statement support for use with node-mysql.
接近 9 年之前 回复
csdnceshi71
Memor.の +1 for node-mysql actually making it easier to use prepared statements than to not use them
9 年多之前 回复

node-mysql is probably one of the best modules out there used for working with MySQL database which is actively maintained and well documented.

KnexJs can be used as an SQL query builder in both Node.JS and the browser. I find it easy to use. Let try it - Knex.js

$ npm install knex --save
# Then add one of the following (adding a --save) flag:
$ npm install pg
$ npm install sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install mariasql
$ npm install strong-oracle
$ npm install oracle
$ npm install mssql


var knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

You can use it like this

knex.select('*').from('users')

or

knex('users').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Here is production code which may help you.

Package.json

{
  "name": "node-mysql",
  "version": "0.0.1",
  "dependencies": {
    "express": "^4.10.6",
    "mysql": "^2.5.4"
  }
}

Here is Server file.

var express   =    require("express");
var mysql     =    require('mysql');
var app       =    express();

var pool      =    mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'address_book',
    debug    :  false
});

function handle_database(req,res) {

    pool.getConnection(function(err,connection){
        if (err) {
          connection.release();
          res.json({"code" : 100, "status" : "Error in connection database"});
          return;
        }   

        console.log('connected as id ' + connection.threadId);

        connection.query("select * from user",function(err,rows){
            connection.release();
            if(!err) {
                res.json(rows);
            }           
        });

        connection.on('error', function(err) {      
              res.json({"code" : 100, "status" : "Error in connection database"});
              return;     
        });
  });
}

app.get("/",function(req,res){-
        handle_database(req,res);
});

app.listen(3000);

Reference : https://codeforgeek.com/2015/01/nodejs-mysql-tutorial/

csdnceshi68
local-host This code seems screwed up.. many errors including Cannot read property 'release' of undefined
接近 3 年之前 回复

Since this is an old thread just adding an update:

To install the MySQL node.js driver:

If you run just npm install mysql, you need to be in the same directory that your run your server. I would advise to do it as in one of the following examples:

For global installation:

npm install -g mysql

For local installation:

1- Add it to your package.json in the dependencies:

"dependencies": {
    "mysql": "~2.3.2",
     ...

2- run npm install


Note that for connections to happen you will also need to be running the mysql server (which is node independent)

To install MySQL server:

There are a bunch of tutorials out there that explain this, and it is a bit dependent on operative system. Just go to google and search for how to install mysql server [Ubuntu|MacOSX|Windows]. But in a sentence: you have to go to http://www.mysql.com/downloads/ and install it.

weixin_41568127
?yb? npm install --save mysql will install it add it to your package.json automatically
大约 3 年之前 回复

connect the mysql database by installing a library. here, picked the stable and easy to use node-mysql module.

npm install mysql@2.0.0-alpha2

var http = require('http'),
   mysql = require('mysql');

var sqlInfo = {
   host: 'localhost',
   user: 'root',
   password: 'urpass',
   database: 'dbname'
}
client = mysql.createConnection(sqlInfo);

client.connect();

For NodeJS mysql connecting and querying example

csdnceshi76
斗士狗 I like this comment because it shows where to put database name
接近 6 年之前 回复
csdnceshi72
谁还没个明天 "smart" quotes (‘’) turn out not to be that smart in js files.
大约 6 年之前 回复
csdnceshi63
elliott.david As far as I know alpha releases are never to be concerned as 'stable'. Correct me if I'm wrong. Alpha has the possibility to dramatically change it's API before going to final which is highly unwanted in production (and even development) code. That is, if the version numbering follows the semver.org guidelines.
大约 7 年之前 回复
var express = require('express');
var router = express.Router();

/* GET home page. */

var mysql = require('mysql');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "****",
    database: "nodejs"
});

con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
});
module.exports = con;

Follow the link http://datainflow.com/nodejs-mysql-connectivity/

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问