首先推薦官方文檔
https://www.npmjs.com/package/mysql
連接mysql數(shù)據(jù)庫
從node庫中引用mysql后肋演，可以用createConnection()創(chuàng)建一個連接，需要將一些配置參數(shù)傳給函數(shù)
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'me',
password : 'secret',
database : 'my_db'
});
數(shù)據(jù)庫連接的建立與關(guān)閉
通過mysql.createConnection()后可以獲得一個mysql connection對象盏求，可以通過這個對象調(diào)用connect()或者end()來建立或關(guān)閉連接。
connection.connect(); // 連接建立
connection.end(); // 連接關(guān)閉
注意：由于在node中的網(wǎng)路請求是異步的，所以數(shù)據(jù)庫查詢操作也是異步的，通過一個任務(wù)隊列來依次執(zhí)行测垛，所以end()并不會直接關(guān)閉，而是將存在任務(wù)隊列中的任務(wù)都發(fā)送出去后再關(guān)閉秧均。
以下是官網(wǎng)原文
- Every method you invoke on a connection is queued and executed in sequence.
- Closing the connection is done using
end()which makes sure all remaining queries are executed before sending a quit packet to the mysql server.
數(shù)據(jù)庫連接不關(guān)閉運行運行程序后程序是不會停止的食侮，要手動Ctrl+C才會停止
這是一個官網(wǎng)建議的連接實例
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'example.org',
user : 'bob',
password : 'secret'
});
connection.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
console.log('connected as id ' + connection.threadId);
});
簡單的增刪改查CURD
我現(xiàn)在有一個表，表中只有兩個列
- id 自增主鍵
- name varchar類型
執(zhí)行sql語句的方法
- 直接將要執(zhí)行的sql語句放入
connection.query(`delete from test where id= 1`, (error, results) => {
if (error) throw error;
console.log(results);
});
2.用?替換sql執(zhí)行時需要更改的參數(shù)
將需要作為參數(shù)的部分用?代替目胡，在后面?zhèn)魅雲(yún)?shù)作為?依次順序?qū)?yīng)的值锯七，需要注意的是，?替換進去的字符串值會被默認(rèn)加上' '誉己，比如這個"測試問號"和2被替換進?后sql語句是這樣的update test set name = '測試問號' where id = 2
connection.query(`update test set name = ? where id = ?`, ["測試問號", 2], (error, results) => {
if (error) throw error;
console.log(results);
});
3.設(shè)置執(zhí)行參數(shù)
sql執(zhí)行時可以帶超時參數(shù)眉尸，替換?值參數(shù)
connection.query({
sql: `update test set name = ? where id = ?`,
timeout: 40000, // 40s
values: ["測試問號", 2]
}, function (error, results, fields) {
if (error) throw error;
console.log(results);
});
接著引用官方的連接實例來一個完整的增刪查改：
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'example.org',
user : 'bob',
password : 'secret'
});
connection.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
console.log('connected as id ' + connection.threadId);
});
// 增
connection.query(`insert into test(name) values(?)`,"測試",(error, results) =>{
if (error) throw error;
console.log(results);
});
// 刪
connection.query(`delete from test where id= ?`,1,(error, results) =>{
if (error) throw error;
console.log(results);
});
// 查
connection.query('SELECT * from test', function (error, results, fields) {
if (error) throw error;
console.log("查詢結(jié)果是");
console.log(results);
});
// 改
connection.query(`update test set name = "測試修改" where id = 2`,(error, results) =>{
if (error) throw error;
console.log(results);
});
// 關(guān)閉連接
connection.end();
運行結(jié)果：
connected as id 286718
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 5,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0 }
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0 }
查詢結(jié)果是
[ RowDataPacket { id: 2, name: 'change Hellow JabinGP again' },
RowDataPacket { id: 3, name: '測試' },
RowDataPacket { id: 4, name: '測試' },
RowDataPacket { id: 5, name: '測試' } ]
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '(Rows matched: 1 Changed: 1 Warnings: 0',
protocol41: true,
changedRows: 1 }
