DIV CSS 佈局教程網

Node.js 操作 MySQL 資料庫:增刪改查完整教學
編輯:關於JavaScript     

本文詳細介紹如何在 Node.js 中透過 mysql 模組操作資料庫,從安裝到完整的 CRUD 流程一应俱全。

安裝 mysql 模組

 npm install mysql 

資料庫準備

假設 MySQL Server 位於 192.168.0.108,登入帳號為 root@123456。接著建立 test 資料庫,並在裡頭新增一張 users 表:

基本操作

連接資料庫:

 var mysql=require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.108', user : 'root', password : '123456', database : 'test1', port:'3306' }); connection.connect();

插入一筆 user:

 var usr={name:'zhangsan',password:'pwdzhangsan',mail:'zhangsan@gmail.com'}; connection.query('insert into users set ?', usr, function(err, result) { if (err) throw err; console.log('inserted zhangsan'); console.log(result); console.log('\n'); });

更新 user(帶條件):

 connection.query('update users set password="ddd" where name="zhangsan"', {password:'ppp'}, function(err, result) { if (err) throw err; console.log('updated zhangsan\'s password to ddd'); console.log(result); console.log('\n'); }); 

刪除一筆 user(帶條件):

 connection.query('delete from users where name="zhangsan"', {password:'ppp'}, function(err, result) { if (err) throw err; console.log('deleted zhangsan'); console.log(result); console.log('\n'); });

查詢所有 user:

 connection.query('select * from users', function(err, rows, fields) { if (err) throw err; console.log('selected after deleted'); for(var i= 0,usr;usr=rows[i++];){ console.log('user nae='+usr.name + ', password='+usr.password); } console.log('\n'); }); 

關閉連線:

connection.end();

如此一來基本的 CRUD 就完成了。mysql 模組的完整功能可參考官方文件:

https://www.npmjs.com/package/mysql

https://github.com/felixge/node-mysql

以下是把上述流程串起來的完整 demo 程式碼:

 var mysql=require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.108', user : 'root', password : '123456', database : 'test1', port:'3306' }); connection.connect(); var usr={name:'zhangsan',password:'pwdzhangsan',mail:'zhangsan@gmail.com'}; connection.query('insert into users set ?', usr, function(err, result) { if (err) throw err; console.log('inserted zhangsan'); console.log(result); console.log('\n'); }); connection.query('select * from users', function(err, rows, fields) { if (err) throw err; console.log('selected after inserted'); for(var i= 0,usr;usr=rows[i++];){ console.log('user nae='+usr.name + ', password='+usr.password); } console.log('\n'); }); connection.query('update users set password="ddd" where name="zhangsan"', {password:'ppp'}, function(err, result) { if (err) throw err; console.log('updated zhangsan\'s password to ddd'); console.log(result); console.log('\n'); }); connection.query('select * from users', function(err, rows, fields) { if (err) throw err; console.log('selected after updated'); for(var i= 0,usr;usr=rows[i++];){ console.log('user nae='+usr.name + ', password='+usr.password); } console.log('\n'); }); connection.query('delete from users where name="zhangsan"', {password:'ppp'}, function(err, result) { if (err) throw err; console.log('deleted zhangsan'); console.log(result); console.log('\n'); }); connection.query('select * from users', function(err, rows, fields) { if (err) throw err; console.log('selected after deleted'); for(var i= 0,usr;usr=rows[i++];){ console.log('user nae='+usr.name + ', password='+usr.password); } console.log('\n'); }); connection.end();

值得補充的是,實際專案中建議改用參數化查詢(如使用 ? 佔位符搭配值物件),既能避免 SQL 注入風險,也能讓程式碼更清晰。以上就是 Node.js 操作 MySQL 的完整教學。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved