node.js操作mysql数据库

nodeJS 操作 MySQL

1. 在数据库（如例所示lzq）里建1张表（如例所示teachers）。

2. 安装nodeJS版MySQL驱动：
   npm install mysql;

3. 执行startSQL.js文件：
   node startSQL.js

4. 测试环境:
   node.js 0.6.12

5. 共2个js文件：startSQL.js and CRUD.js.

startSQL.js ------------------------------->
01
var CRUD = require('./CRUD'),
02
    sys = require('util'),
03
    client = require('mysql').createClient({'host':'localhost',
04
                                            'port':3306,
05
                                            'user':'root',
06
                                            'password':'admin'}),
07
    ClientConenctionReady = function(client){
08
        var value = ['10','fillp','abc'],
09
        insertSQLString = 'insert into teachers SET id = ?, name = ? , pwd = ?',
10
        selectSQLString = 'select * from teachers',
11
        updateSQLString = "update teachers set NAME='ipone' where ID=4",
12
        deleteSQLString = 'delete from teachers where ID=10';
13
        
14
        console.log('ClientConnectionReady'),
15
        console.log('/n')
16
        // lzq is the name of database.
17
        client.query('USE lzq', function(error, results) {
18
            if(error){
19
                console.log('ClientConnectionReady Error: ' + error.message),
20
                client.end();
21
                return;
22
            }
23
            console.log('Connecting to MySQL...'),
24
            console.log('Connected to MySQL automatically'),
25
            console.log('/n'),
26
            console.log('connection success...'),
27
            console.log('/n'),
28
 
29
            client.end(),
30
            console.log('Connection closed'),
31
            console.log('/n');
32
        });
33
    };
CRUD.js -------------------------------->

view sourceprint?
01
var CRUD = {
02
     _insert : function(client,insertSQLString,value)
03
     {
04
         client.query(insertSQLString, value,  function(error, results)
05
             {
06
                     if(error)
07
                     {
08
                        console.log("ClientReady Error: " + error.message),
09
                        client.end();
10
                        return;
11
                     }
12
                        console.log('Inserted: ' + results.affectedRows + ' row.'),
13
                        console.log('insert success...');
14
             }
15
         );
16
     },
17
     _select : function(client,selectSQLString)
18
     {
19
         client.query(selectSQLString,  function selectCb(error, results, fields)
20
             {
21
                         if (error)
22
                         {
23
                            console.log('GetData Error: ' + error.message),
24
                            client.end();
25
                            return;
26
                         }
27
                        if(results.length > 0)
28
                        {
29
                            var firstResult,
30
                            resultSet = '';
31
                            for(var i = 0, len = results.length; i < len; i++)
32
                            {
33
                                firstResult = results[i],
34
                                resultSet += 'id:' + firstResult['id'] + '' + 'name:' + firstResult['name'] + '' + 'pwd:' + firstResult['pwd'] + '/n'; 
35
                            }
36
                        }
37
                        console.log(resultSet);
38
                        /* 添加功能：使查询结果集返回到客户端并保证此函数的通用性. */
39
             }
40
        );
41
     },
42
     _update : function(client,updateSQLString)
43
     {
44
        client.query(updateSQLString,function(error, results)
45
            {
46
                if(error)
47
                {
48
                    console.log("ClientReady Error: " + error.message),
49
                    client.end();
50
                    return;
51
                }
52
                console.log('update success...');
53
            }
54
        );
55
     },
56
     _delete : function(client,deleteSQLString)
57
     {
58
        client.query(deleteSQLString,  function(error, results)
59
            {
60
                if(error)
61
                {
62
                    console.log("ClientReady Error: " + error.message),
63
                    client.end();
64
                    return;
65
                }
66
                console.log('delete success...');
67
            }
68
        );
69
     }
70
};
71
    exports._insert = CRUD._insert,
72
    exports._select = CRUD._select,
73
    exports._update = CRUD._update,
74
    exports._delete = CRUD._delete;