Node.js 连接 MySQL 并进行数据库操作

安装 node-mysql

$ npm install mysql

创建测试表

//数据库名 NodeSample

CREATE TABLE `NodeSample`.`MyTable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `firstname` VARCHAR( 20 ) NOT NULL ,
  `lastname` VARCHAR( 20 ) NOT NULL ,
  `message` TEXT NOT NULL
) ENGINE = MYISAM ;

连接数据库

var sys = require('sys');
 
var Client = require('mysql').Client;
var client = new Client();
 
client.user = 'someuser';
client.password = 'password';
 
client.connect(function(error, results) {
  if(error) {
    console.log('Connection Error: ' + error.message);
    return;
  }
  console.log('Connected to MySQL');
});

打开数据库

ClientConnectionReady = function(client)
{
    client.query('USE NodeSample', function(error, results) {
        if(error) {
            console.log('ClientConnectionReady Error: ' + error.message);
            client.end();
            return;
        }
    });
};

完成数据库操作程序

 1 var sys = require('sys');
 2  
 3 var Client = require('mysql').Client;
 4 var client = new Client();
 5  
 6 client.user = 'someuser';
 7 client.password = 'password';
 8  
 9 console.log('Connecting to MySQL...');
10  
11 client.connect(function(error, results) {
12   if(error) {
13     console.log('Connection Error: ' + error.message);
14     return;
15   }
16   console.log('Connected to MySQL');
17   ClientConnectionReady(client);
18 });
19  
20 ClientConnectionReady = function(client)
21 {
22     client.query('USE NodeSample', function(error, results) {
23         if(error) {
24             console.log('ClientConnectionReady Error: ' + error.message);
25             client.end();
26             return;
27         }
28         ClientReady(client);
29     });
30 };
31  
32 ClientReady = function(client)
33 {
34   var values = ['Chad', 'Lung', 'Hello World'];
35   client.query('INSERT INTO MyTable SET firstname = ?, lastname = ? , message = ?', values,
36     function(error, results) {
37       if(error) {
38         console.log("ClientReady Error: " + error.message);
39         client.end();
40         return;
41       }
42       console.log('Inserted: ' + results.affectedRows + ' row.');
43       console.log('Id inserted: ' + results.insertId);
44     }
45   );
46   GetData(client);
47 }
48  
49 GetData = function(client)
50 {
51   client.query(
52     'SELECT * FROM MyTable',
53     function selectCb(error, results, fields) {
54       if (error) {
55           console.log('GetData Error: ' + error.message);
56           client.end();
57           return;
58       }
59       // Uncomment these if you want lots of feedback
60       //console.log('Results:');
61       //console.log(results);
62       //console.log('Field metadata:');
63       //console.log(fields);
64       //console.log(sys.inspect(results));
65  
66       if(results.length > 0)
67       {
68         var firstResult = results[0];
69         console.log('First Name: ' + firstResult['firstname']);
70         console.log('Last Name: ' + firstResult['lastname']);
71         console.log('Message: ' + firstResult['message']);
72       }
73   });
74  
75   client.end();
76   console.log('Connection closed');
77 };