本文詳細介紹如何在 Node.js 中透過 mysql 模組操作資料庫,從安裝到完整的 CRUD 流程一应俱全。
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 的完整教學。