一、nodejs連接mysql數(shù)據(jù)庫(kù)
1. 安裝mysql
npm install mysql --save
2.創(chuàng)建數(shù)據(jù)庫(kù)
進(jìn)入mysql儡炼，運(yùn)行以下命令行創(chuàng)建數(shù)據(jù)庫(kù)todoapp
create table books(
id int not null auto_increment primary key,
isbn varchar(20) not null,
openid varchar(50) not null,
title varchar(100) not null,
image varchar(100),
author varchar(50),
publisher varchar(50) not null,
price varchar(100),
levelnum float,
keyword varchar(100),
bookcatalog varchar(1000),
gist varchar(1000)
) default charset utf8;
3. 從nodejs連接mysql
/* js文件 */
// 導(dǎo)入mysql模塊
let mysql = require('mysql');
// 創(chuàng)建連接，返回一個(gè)connection對(duì)象
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// 建立連接
// connect()方法接受一個(gè)具有err參數(shù)的回調(diào)函數(shù)，若發(fā)生任何錯(cuò)誤，它將提供詳細(xì)的錯(cuò)誤膘魄。
connection.connect(function(err) {
if (err) {
return console.error('error: ' + err.message);
}
console.log('Connected to the MySQL server.');
});
// 關(guān)閉連接（在connection對(duì)象上調(diào)用end()方法/destroy()方法）
// - end()： 確保在數(shù)據(jù)庫(kù)連接關(guān)閉前始終執(zhí)行所有剩余的查詢
// - destroy()：強(qiáng)制關(guān)閉連接，保證不會(huì)為連接觸發(fā)任何回調(diào)或者事件
connection .end( function (err) {
if (err) {
console.log('error: ' + err.message)
return
}
console.log('Close the database connection.')
})
4. 連接池
nodejs的mysql模塊提供了內(nèi)置的連接池功能竭讳。
1）創(chuàng)建連接池
請(qǐng)注意创葡，連接池中所建立的連接是懶惰的。例如绢慢，如果您使用5個(gè)連接配置連接池灿渴，但是同時(shí)僅使用2個(gè)連接，則連接池僅創(chuàng)建2個(gè)連接胰舆。
// 創(chuàng)建連接池
const pool = mysql.createPool({
connectionLimit: 5, // 連接池最多可以創(chuàng)建連接數(shù)
host: 'localhost',
user: 'root',
password: '',
database: 'todoapp'
})
2）獲取池中連接 / 釋放連接 / 關(guān)閉刪除連接
- pool.getConnection()：獲取連接
- connnection.release()：釋放當(dāng)前連接
- connection.destroy()：關(guān)閉連接并從池中刪除
pool.getConnection(function (err, connection) {
// execute query
// ...
connnection.release() // 釋放當(dāng)前連接
connection.destroy() // 關(guān)閉連接并從池中刪除
})
3）關(guān)閉池中的所有連接
pool.end(function (err) {
if (err) {
console.log(err.message)
return
}
// close all connections
})
二逻杖、創(chuàng)建數(shù)據(jù)表
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// connect to the MySQL server
connection.connect(function(err) {
if (err) {
return console.error('error: ' + err.message);
}
let createTodos = `create table if not exists todos(
id int primary key auto_increment,
title varchar(255)not null,
completed tinyint(1) not null default 0
)`;
// 創(chuàng)建數(shù)據(jù)表
connection.query(createTodos, function(err, results, fields) {
if (err) {
console.log(err.message);
}
});
connection.end(function(err) {
if (err) {
return console.log(err.message);
}
});
});
query() 方法接受SQL語(yǔ)句和回調(diào)，回調(diào)函數(shù)有三個(gè)參數(shù)：
- error: 若語(yǔ)句執(zhí)行期間發(fā)生錯(cuò)誤思瘟，則存儲(chǔ)詳細(xì)錯(cuò)誤
- results: 包含查詢的結(jié)果
- fields: 包含結(jié)果字段信息(若有)
三、插入數(shù)據(jù)
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// 一次插入一行數(shù)據(jù)(方法一)
let sql = `INSERT INTO todos(title,completed)
VALUES('Learn how to insert a new row',true)`;
// execute the insert statment
connection.query(sql);
// 一次插入一行數(shù)據(jù)(方法二)
// 闻伶？：占位符滨攻，用來(lái)傳遞數(shù)據(jù)
let stmt = `INSERT INTO todos(title,completed)
VALUES(?,?)`;
let todo = ['Insert a new row with placeholders', false];
// execute the insert statment
connection.query(stmt, todo, (err, results, fields) => {
if (err) {
return console.error(err.message);
}
// 獲取插入的id
console.log('Todo Id:' + results.insertId);
});
// 一次插入多行數(shù)據(jù)
let stmt = `INSERT INTO todos(title,completed) VALUES ? `;
let todos = [
['Insert multiple rows at a time', false],
['現(xiàn)在學(xué)習(xí)一次插入多行記錄(by www.yiibai.com)', true],
['It should work perfectly', true]
];
// execute the insert statment
connection.query(stmt, [todos], (err, results, fields) => {
if (err) {
return console.error(err.message);
}
// get inserted rows 獲取插入的行數(shù)
console.log('Row inserted:' + results.affectedRows);
});
// close the database connection
connection.end();
四、查詢數(shù)據(jù)
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// 查詢所有數(shù)據(jù)
let sql = `SELECT * FROM todos`;
connection.query(sql, (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results);
});
// 帶條件查詢
// 使用問(wèn)號(hào)(?)作為completed字段的占位符值
let sql = `SELECT * FROM todos WHERE completed=?`;
connection.query(sql, [true], (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results);
});
// 防止SQL注入，有兩種方法:
// - 使用占位符(?)
// - 使用mysql的escape()方法
let id = process.argv[2]; // pass argument to query
// 存在sql注入風(fēng)險(xiǎn)的方法(不推薦９馊啤！！)
let sql = `SELECT * FROM todos WHERE id=` + id ;
connection.query(sql, (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results);
});
// 使用占位符(?)玄叠，防止sql注入
let sql = `SELECT * FROM todos WHERE id=?`;
connection.query(sql, [1], (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results);
});
// 使用mysql的escape()方法祸穷，防止sql注入
let sql = `SELECT * FROM todos WHERE id=` + mysql.escape(id);
connection.query(sql, (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results);
});
// close the database connection
connection.end();
五、更新數(shù)據(jù)
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// update statment
let sql = `UPDATE todos
SET completed = ?
WHERE id = ?`;
let data = [false, 1];
// execute the UPDATE statement
connection.query(sql, data, (error, results, fields) => {
if (error){
return console.error(error.message);
}
// 獲取更新的行數(shù)
console.log('Rows affected:', results.affectedRows);
});
connection.end();
六停蕉、刪除數(shù)據(jù)
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// DELETE statment
let sql = `DELETE FROM todos WHERE id = ?`;
// delete a row with id 1
connection.query(sql, 1, (error, results, fields) => {
if (error)
return console.error(error.message);
// 獲取刪除的行數(shù)
console.log('Deleted Row(s):', results.affectedRows);
});
connection.end();
七愕鼓、從node.js調(diào)用mysql存儲(chǔ)過(guò)程
1. 什么是存儲(chǔ)過(guò)程
簡(jiǎn)單來(lái)說(shuō)，存儲(chǔ)過(guò)程就是一組sql語(yǔ)句集慧起，可以實(shí)現(xiàn)一些較復(fù)雜的邏輯功能菇晃，有點(diǎn)類似于應(yīng)用程序的一個(gè)功能函數(shù)。
2. 調(diào)用mysql存儲(chǔ)過(guò)程
1）在調(diào)用之前蚓挤，我們得創(chuàng)建一個(gè)存儲(chǔ)過(guò)程：filterTodo磺送，功能：根據(jù)completed字段的值來(lái)查詢todos表中的行。
/* sql語(yǔ)句 */
// 創(chuàng)建存儲(chǔ)過(guò)程的sql語(yǔ)句
CREATE PROCEDURE filterTodo(IN done BOOLEAN)
BEGIN
SELECT * FROM todos WHERE completed = done;
END;
2）調(diào)用存儲(chǔ)過(guò)程filterTodo
/* sql語(yǔ)句 */
// 調(diào)用存儲(chǔ)過(guò)程的sql語(yǔ)句
CALL filterTodo(true);
3. nodejs中調(diào)用mysql存儲(chǔ)過(guò)程
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
let sql = `CALL filterTodo(?)`;
// 調(diào)用存儲(chǔ)過(guò)程
connection.query(sql, true, (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results[0]);
});
connection.end();
