一、nodejs连接mysql数据库
1. 安装mysql
npm install mysql --save
2.创建数据库
进入mysql，运行以下命令行创建数据库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文件 */
// 导入mysql模块
let mysql = require('mysql');
// 创建连接，返回一个connection对象
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// 建立连接
// connect()方法接受一个具有err参数的回调函数，若发生任何错误，它将提供详细的错误。
connection.connect(function(err) {
if (err) {
return console.error('error: ' + err.message);
}
console.log('Connected to the MySQL server.');
});
// 关闭连接（在connection对象上调用end()方法/destroy()方法）
// - end()： 确保在数据库连接关闭前始终执行所有剩余的查询
// - destroy()：强制关闭连接，保证不会为连接触发任何回调或者事件
connection .end( function (err) {
if (err) {
console.log('error: ' + err.message)
return
}
console.log('Close the database connection.')
})
4. 连接池
nodejs的mysql模块提供了内置的连接池功能。
1）创建连接池
请注意，连接池中所建立的连接是懒惰的。例如，如果您使用5个连接配置连接池，但是同时仅使用2个连接，则连接池仅创建2个连接。
// 创建连接池
const pool = mysql.createPool({
connectionLimit: 5, // 连接池最多可以创建连接数
host: 'localhost',
user: 'root',
password: '',
database: 'todoapp'
})
2）获取池中连接 / 释放连接 / 关闭删除连接
- pool.getConnection()：获取连接
- connnection.release()：释放当前连接
- connection.destroy()：关闭连接并从池中删除
pool.getConnection(function (err, connection) {
// execute query
// ...
connnection.release() // 释放当前连接
connection.destroy() // 关闭连接并从池中删除
})
3）关闭池中的所有连接
pool.end(function (err) {
if (err) {
console.log(err.message)
return
}
// close all connections
})
二、创建数据表
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
)`;
// 创建数据表
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语句和回调，回调函数有三个参数：
- error: 若语句执行期间发生错误，则存储详细错误
- results: 包含查询的结果
- fields: 包含结果字段信息(若有)
三、插入数据
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// 一次插入一行数据(方法一)
let sql = `INSERT INTO todos(title,completed)
VALUES('Learn how to insert a new row',true)`;
// execute the insert statment
connection.query(sql);
// 一次插入一行数据(方法二)
// ？：占位符，用来传递数据
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);
});
// 一次插入多行数据
let stmt = `INSERT INTO todos(title,completed) VALUES ? `;
let todos = [
['Insert multiple rows at a time', false],
['现在学习一次插入多行记录(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 获取插入的行数
console.log('Row inserted:' + results.affectedRows);
});
// close the database connection
connection.end();
四、查询数据
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
// 查询所有数据
let sql = `SELECT * FROM todos`;
connection.query(sql, (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results);
});
// 带条件查询
// 使用问号(?)作为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注入风险的方法(不推荐！！！)
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();
五、更新数据
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);
}
// 获取更新的行数
console.log('Rows affected:', results.affectedRows);
});
connection.end();
六、删除数据
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);
// 获取删除的行数
console.log('Deleted Row(s):', results.affectedRows);
});
connection.end();
七、从node.js调用mysql存储过程
1. 什么是存储过程
简单来说，存储过程就是一组sql语句集，可以实现一些较复杂的逻辑功能，有点类似于应用程序的一个功能函数。
2. 调用mysql存储过程
1）在调用之前，我们得创建一个存储过程：filterTodo，功能：根据completed字段的值来查询todos表中的行。
/* sql语句 */
// 创建存储过程的sql语句
CREATE PROCEDURE filterTodo(IN done BOOLEAN)
BEGIN
SELECT * FROM todos WHERE completed = done;
END;
2）调用存储过程filterTodo
/* sql语句 */
// 调用存储过程的sql语句
CALL filterTodo(true);
3. nodejs中调用mysql存储过程
let mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'todoapp'
});
let sql = `CALL filterTodo(?)`;
// 调用存储过程
connection.query(sql, true, (error, results, fields) => {
if (error) {
return console.error(error.message);
}
console.log(results[0]);
});
connection.end();
